MySQL Limit优化

Tang7O

MySQL

发布于：2022年3月16日

次浏览

字数统计: 750字   |   阅读时长: 2分

耗时本质

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。

当一个表有几百万的数据的时候成了问题！

如 select * from table limit 0,10 这个没有问题，当 limit 100000,10 的时候就很慢。

原因本质：

1. limit 语句的查询时间和其实记录（offset）的大小成正比。
2. mysql 的 limit 语句是很方便，但是对于记录很多：百万，千万级别的表并不适合直接使用。

例如： limit 10000,20 的意思扫描满足条件的 10020 行，扔掉 前面的 10000 行，返回最后的 20 行，问题就在这里。limit 2000000，30 扫描了 2000030 行，慢的都堵死了，甚至会导致磁盘 IO 100%消耗，但是 limit 30 这样的语句才扫描 30 行。

优化手段

去掉或者利用 limit offset,size 中的 offset。

不是直接使用limit，而是首先获取到 offset 的 id 然后再使用 limit size 来获取数据。

对 limit 分页问题的性能优化方法

如果数据是连续不中断的可以使用 between and 来代替 limit 查询。

利用表的覆盖索引来加速分页查询

覆盖索引：

就是 select 的数据列只从索引中就能获得，不必读取数据行。也就是说：查询列要被所创建的索引覆盖。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外 mysql 中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。在我们的例子中，我们知道 id 字段是主键，自然就包含了默认的主键索引。

这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

1
2
3

#覆盖索引只包含id列 的时间显著优于 select * 不言而喻
select * from order_table where company_id = 1 and mark =0 order by id desc limit 200000 ,20;
select id from order_table where company_id = 1 and mark =0 order by id desc limit 200000 ,20;

如果我们要查询所有列，有两种方法，一种是 id>= 的形式，另一种就是利用 join：

1
2
3

#两者用的都是一个原理嘛，所以效果也差不多
SELECT * FROM xxx WHERE ID > =(select id from xxx limit 1000000, 1) order by id limit 20;
SELECT * FROM xxx a JOIN (select id from xxx limit 1000000, 20) b ON a.ID = b.id;

上述子查询的方式虽然比直接使用 Limit 要快很多，但是当数据量过大（千万级别）时，子查询需要很长时间，依旧不够快。

去掉子查询

1
2
3
4
5
6

#仅仅使用 id<max and limit size;
#每次查询前获取上一页最小id作为下一页的最大id使用 假设为：800000001
#首页查询
select * from order_table where company_id = 1 and mark =0 order by id desc limit 200000;
#非首页查询  
select * from order_table where company_id = 1 and mark =0 and id < 800000001 order by id desc limit 200000;

更新于：2022年4月13日

MySQL