MySQL优化案例--limit优化

案例SQL

生产有这样一条sql，其中val是辅助索引

select * from test where val=4 limit 300000,5;

​

查询原理

查询过程：

查询到索引叶子节点数据。根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

类似于下面这张图：

像上面这样，需要查询300005次索引节点，查询300005次聚簇索引的数据，最后再将结果过滤掉前300000条，取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

优化思路

肯定会有人问：既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O，类似于下面图片的过程：

改造SQL

​select * from test a inner join (select id from test where val=4 limit 300000,5) b on ​​a.id=b.id​​;

有效避免的回表IO消耗