当mysql遇上大偏移量的limit

前言

前段时间项目中的慢sql预警邮件有类似这样一条慢sql报警了,后来进行了优化，我们的目标是没有慢sql,sql具体如下

select * from test  order by createTime desc limit 300000,5;

这条sql当偏移量小的时候查询效率还是可以的，但是如果test表中的数据很大而且limit偏移量也很大的时候就会查询效率问题。这里来和大家一起看看为何会出现这种问题。

sql优化

上面这种sql,查询耗时太长了，肯定是过不了慢sql预警的。后来优化之后的sql如下

select * from test a inner join (select id from test order by createTime desc 
limit 300000,5) b on a.id=b.id;

两个sql的查询效率相差很大，为什么会出现上面的结果？我们看一下select * from test  order by createTime desc limit 300000,5;的查询过程：先是查询到索引叶子节点数据，再根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。类似于下面这张图：

像上面这样，需要查询300005次索引节点，查询300005次聚簇索引的数据，最后再将结果过滤掉前300000条，取出最后5条。mysql耗费了大量随机I/O在查询聚簇索引的数据上，而有300000次随机I/O查询到的数据是不会出现在结果集当中的。显然这300000次的I/O查询是没有必要的。而优化之后的sql是利用子查询的覆盖索引，快速定位到符合条件的id，再根据这个主键id去查询对应的记录行，类似下图

对于覆盖索引想必大家也不陌生，我们都知道索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引的叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。