order by优化-mysql

原文地址：http://leihuang.org/2015/05/15/order-by-mysql/

上篇博客讲到mysql的分页优化，其中有谈到order 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;

• 在非连续的索引键部分上做 ORDER BY

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

• 同时使用了 ASC 和 DESC：

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

• 用于搜索记录的索引键和做 ORDER BY 的不是同一个：

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，然后可以直接去磁盘取需要的数据了。