Mysql 大表limit查询优化原理

优化前( 查询耗时 114.1s)

explain select * from link_exec_task limit 80000, 10   # 查询耗时 114.1s

优化后( 查询耗时 0.121s)

explain select * from link_exec_task a
INNER JOIN (select id from link_exec_task limit 80000, 10) b on a.id = b.id   #0.121s

原理：
观察以下sql 查询计划

查询优化的原因：：
主键ID形成的索引是聚簇索引(B+树)，叶子节点存的是记录(数据)；而普通字段锁形成的索引是非聚簇索引，非聚簇索引的叶子节点里面存的是记录的ID。

查找第8w条，聚簇索引和非聚簇索引对比：

聚簇索引方式：由于你的叶子存的是数据，一个叶子节点占用空间大，故而一次IO可能查询到少数的叶子节点，故而需要IO很多次，才能找到第8w条记录。
非聚簇索引方式：由于你的叶子存的是记录的ID，一个叶子节点占用空间小，故而一次IO可以查询到大量的叶子节点，故而只需要少次IO次，就能快速找到第8w条记录的ID。最后找到10条记录的ID后，再通过连表查询，10条记录的innner join 处理起来就很快了。

故而上面就算我查询哪怕没有用到 status字段进行查询，Innodb执行引擎也会借助status索引字段锁使用的非聚簇索引，来快使找到第8w条记录。

问：为什么借助的是status索引字段，而不使用其他非聚簇索引字段？
答：因为会挑一个索引字段空间占用最小的字段，在该表的索引字段中，就status字段的空间占用最小，这样一次磁盘IO可有查询出更多的索引页。

问：子查询里面可能select id吗？我还能select 其他吗？
答：正常情况下不行，例如你select * ，那么此时你的查询耗时会和优化前查询耗时一样，因为此时你的查询不是覆盖索引查询，由于非聚簇索引里面没有你要select的内容，故而innodb直接就不借助非聚簇索引找第8w条记录；而是直接从聚簇索引里，找出第8w条记录，故而速度会变得很慢。
当然，除非你select 的字段已经在非聚簇索引树里面了，此时还是使用覆盖索引，还是走非聚簇索引，查询速度也会快。