神奇的MySQL排序，索引、优化器与Limit

MySQL 很有意思，发现我越来越喜欢它。不因为别的，就因为同事一个问题花一天，而我大多数情况下，我都能够避免。节约的时间，我可以用来学习其他的知识。

最近关注了一下 MySQL 的 order by 排序功能。发现了一些很有意思的事情，我们一起来分享一下。

1、排序字段有索引，无条件查询的情况下，竟然奇慢无比。

select * from xttblog order by name desc;

上面的查询语句中，name 字段有索引，但是查询效果和没索引一样。这是因为优化器认为走二级索引再去回表成本比全表扫描排序更高。所以选择走全表扫描。

2、在问题 1 的基础上，我添加一个 limit，结果又变快了。

select * from xttblog order by name desc limit 10;

太神奇了。紧紧加了一个 limit，效率变高了。这是因为无条件查询且 order by name limit m 中 m 值较小，是可以走索引的。因为优化器认为根据索引有序性去回表查数据，然后得到 m 条数据，就可以终止循环，那么成本比全表扫描小，则选择走二级索引。

3、在问题 2 的基础上，我们再把 name 上的索引去掉。结果也是比较快。

这是因为，即便没有二级索引，MySQL 针对 order by limit 也做了优化，采用堆排序。

4、同一条 SQL，为什么有时候排序很快，有时候很慢。

select * from xttblog where name = '' order by name desc; -- 慢
select * from xttblog where name = '' order by name desc; -- 快

这个和扫描的行数有关系。如果扫描的行数过多，sort_buffer 这块内存放不下，就需要进行在磁盘中排序，也就是文件排序。内存和磁盘不是一个级别的。因此有时候慢，有时候快，是因为条件不一样。

总结，排序时，排序字段尽量加上索引。另外 limit 也很重要。除此之外，注意 where 条件。更多关于排序方面的内容，我后面再仔细讲。

： » 神奇的MySQL排序，索引、优化器与Limit