MySQL分页limit m,n 慢问题优化思路

前言

最近梳理遇到的问题，发现mysql分页的问题，组里的同学还是很容易忽视的。有必要记录一下。

问题

一个管理端的老系统，主要提供供货商查询历史订单信息，有反馈查询打开特别慢，后来发现，他们回溯查询历史订单，页数很深，导致分页特别慢。

分页SQL

用于分页的sql说明：

 // 查询admin表中前10条记录，从第一条开始
 1.SELECT * FROM admin LIMIT 10
 2.SELECT * FROM admin LIMIT 0, 10; -- 其实0可以省略不写
 3.SELECT * FROM admin LIMIT 10 OFFSET 0

上述1～3的sql是等价的，语法的不同写法，不做过多介绍。

查询过程

大家知道mysql Innodb的表主键情况是B+树结构存储数据，那么limit m，n，比如limit 100000, 10 ,他的执行过程是什么样呢? 很遗憾，mysql 并不知道100001 在哪里，他会取出100010条数据，然后丢弃掉100000条数据。这也是为什么越深的limit的，越慢。

下图是聚簇索引和非聚簇索引的查询情况：

我们以这条sql情况分析：

 SELECT * FROM admin where name="aa" order by LIMIT 100000, 10;

我们假设有100W条名字是aa的数据。
他的过程是：

1. 先从非聚簇索引查询找到100010个名字是“aa”的主键id列表。
2. 回表从聚簇索引中，找到这100010条数据，取出
3. 返回最后的10条

疑惑

你可能觉得既然第一步已经找到了100010个aa id，为什么不取最后10个回表查询呢？不过很可惜，由于我们是取所有数据（不是id），mysql会回表查询所有数据。
大家可以观察InnoDB中有buffer pool。里面存有最近访问过的数据页，包括数据页和索引页，下来可以看下，这里不再演示。

优化

思路：避免数据量大时扫描、读取过多的记录。

优化sql

select * from admin a inner join (select id from admin where name=“aa” limit 100000,5) b on a.id=b.id;

“select id from admin where name=“aa” limit 100000,5” 可以利用覆盖索引快速查询出id，然后在根据id回表查询5个数据的具体值，这样就能大量的减少数据库的IO，从而大幅提升性能。

总结

mysql的坑还是很多的，但是从原理上考虑问题，解决方案还是有的。