orderby優化-mysql

orderby優化-mysql

rder by關鍵字的優化，今天就來對其進行分析。

首先看mysql官方文檔，是如何來談order by關鍵字的優化的。8.2.1.15 ORDER BY Optimization

下面的語句是可以用到索引來排序的。key_part1,key_part2表示兩個合並起來的兩個縮影索引

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;
SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;

下面的語句是用不到索引的。

SELECT * FROM t1 ORDER BY key1, key2;

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

記住最關鍵的一句話：一條sql不能同時使用兩個索引，如果需要的話則需要建立聯合索引

想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非額外的排序過程。如果不能使用索引，可以試著遵循以下策略：

增加 sort_buffer_size 的值。

增加 read_rnd_buffer_size 的值。

再來看看上篇文章中提到的情形，如下

select * from a order by subject_code   //用不上索引
select id from a order by subject_code  //能用上索引
select subject_code from a order by subject_code    //能用上索引
select * from a where subject_code = XX order by subject_code   //能用上索引

第一條語句為什麼用不到索引，都是由於二級索引的問題：

innodb的二級索引 存的是 當前column+對應的主鍵, 查詢時用 主鍵值去 主鍵索引中查詢相對應的row.

In InnoDB, each record in a secondary index contains the primary key
columns for the row, as well as the columns specified for the
secondary index. InnoDB uses this primary key value to search for the
row in the clustered index.

select * from a order by subject_code

這條語句如果用subject_code上的索引來排序, 則 按subject_code索引的順序 去主鍵索引查, 反而不如做filesort來的快.

於是就有了這樣的優化

SELECT *
FROM `XXX` AS `x`
INNER JOIN(
    SELECT `id`
    FROM `XXX`
    ORDER BY `id` DESC
    LIMIT M,N
) AS `t`
USING(`id`)

子查詢用到了覆蓋索引，所以不需要掃描磁盤就找到了所需要的行的id，然後可以直接去磁盤取需要的數據了。