MySQL中limit对于order by的优化

一、 无limit的场景对于order by如何优化

考虑无limit的order by语句，下面假设age是一个普通索引。

1.1 查询无覆盖索引

select * from emp order by age;

此句中，查询的字段和order by后面的age并非在同一个索引中，因此如果走了索引，查询步骤应该是：

1. 走age的索引，会带出排序后的数据的id
2. 将这些id逐个回表查询，查询的次数和表长一样

MySQL优化器会认为查询数据量大，回表数据量也大，因此还不如直接全表记录扫描出来放到temp表中，然后sort一遍，这样就避免了多次回表。

1.2 查询覆盖索引

select age from emp order by age;

此句中，查询的字段age和order by的字段同属一个索引，因此优化器也会选择走索引。

二、limit对于order by语句的优化

我们知道，order by先于limit执行，因此优化器如果没有对order by和limit同时使用的场景的特殊优化，那么就会和无limit的情况一样去考虑，大多数情况下，会全表扫描。

但这并不是我们想要的，例如我们使用limit 10, 10这条语句，实际上只需要MySQL进行排序10 + 10次，然后将这20次回表即可，最后将排序的结果取出后面10条，就是我们要的结果，这个开销可能远小于全表扫描。

在mysql中，默认开启了prefer_ordering_index的选项，目的是在order by搭配limit的语句中，加入对于limit的考虑，如果权衡出来在offset + row_count的数据范围内，索引+回表的方式优于全表扫描，就会选择走索引的方式。

2.1 开启prefer_ordering_index（默认）

2.1.1. 走了索引

select * from emp order by age limit 10;

上面这句，优化器权衡后，发现只需要排序出前10个，回表10次，开销小，会走索引

2.1.2. 没走索引

select * from emp order by age limit 500;

上面这句，优化器权衡后，不走索引，选择全表扫描。

select * from emp order by age limit 5000, 10;

上面这句，实际上需要排序5000 + 10 = 5010条数据，回表5010次开销巨大，MySQL选择了全表扫描。

2.2. 关闭prefer_ordering_index

2.2.1. 关闭后优化器不管limit了

select * from emp order by age limit 1;

即使我们限制了结果集大小仅为1，回表开销很小，但是优化器只考虑order by的操作，忽视了limit，所以选择全表扫描。

2.3. 如何手动优化强制走索引

select * from emp order by age limit 100000, 10;

例如上述的场景，不管开不开prefer_ordering_index，都很有可能全表扫描，因为就算考虑了order by考虑进了limit，他也需要排序100000 + 10条数据，然后把后面10条拿出来，所以offset + row_count比较小的时候，才会自动走索引。
但是我们看中的是limit后面这个row_count比较小，但是大offset导致了order by需要排序回表负担过大。
实际上我们可以使用子查询，先用覆盖索引走查出这10个age，然后再和大表连接，这样只会有10次回表。

1. 错误做法

select * from emp where id in (select id from emp order by age limit 1);

MySQL不支持in中写limit的写法。

2. 正确做法：连接子查询表

select a.* from emp a, (select id from emp order by age) b where a.id = b.id;

三、limit对于order by提前退出的优化

limit [offset, ] row_count

MySQL 只要排序到能够确定了这 row_count 行结果，就会停止排序，而不是整个排序。换言之，他只会排序offset + row_count个数据。

-- 如果我们只需要找到emp中工资最高的2个员工的信息
select * from emp order by salary desc limit 2;

假设salary没索引，选择全表排序，这种情况下，并不会对所有数据根据salary排序，而是选出最大的两个。我们知道，选出最大两个的复杂度是O(N)，而全表排序，就不是这个复杂度了。

参考资料

https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html