limit慢查询的原理和优化

最新推荐文章于 2025-04-17 09:48:09 发布

原创最新推荐文章于 2025-04-17 09:48:09 发布 · 2k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库开发 #sql

本文探讨了MySQL中使用Limit进行分页查询时遇到的性能瓶颈及其原因，并提出了三种优化方案，包括直接使用主键定位、子查询结合In操作以及Join替代In操作的方法。

参考文献：limit offset慢查询背后的原因和解法

先来看看limit的使用

select * from table_a where age<34 order by age desc limit rows,pageSize;

最近在做项目的时候，偶然间发现 limit 0,100 比 limit 10000,100慢很多，也就意味着在这个是时间内其他很多操作都会被IO阻塞，严重影响了性能。

怎么产生的呢？

limit并不是说从数据库的多少行开始，拿到多少行数据就给你返回这么easy。相反，按上面limit 10000，100 他是一条一条读取到10100行，然后根据pagesize设置，舍弃前10000行数据

由于我使用的MySQL引擎是innoDB，并且age也不是我设置的主键，所以该索引是一个非聚簇索引，那么也就是说在非聚簇索引B+树上会无效遍历得到前10000个数据节点，并且每一个数据节点里面只包含相应的主键值——也就是说在这一个废节点上我们还需要拿着主键再去聚簇索引树上找到相应的值放入结果集中。这么一想，这就是10000次无效数据查询啊！

为什么MySQL一定要让你去查这些无效数据呢？因为MySQL的实现分为引擎层和数据层，limit 只能作用于引擎层返回的结果集，因此对引擎层来说，他也不知道前10000个是会扔掉的数据，只能先一股脑地往上传。

如何优化？

优化1.0

select * from table_a where id>10000 limit 100;

这里我们就相当于直接使用到了聚簇索引树，直接将数据行定位到了第10000行，避免了前面10000的无效查询，这就很像我们所想的查询那样—— 从数据库的多少行开始，拿到多少行数据。

优化2.0

但是上面这个也只能适合业务简单的时候，但是大部分情况下，很多查询是需要带有条件的，我们必须要使用到非聚集索引树。

例如我们先实现根据年龄进行排序然后分页展示，那么我们就可以这样做

select * from table_a where id in(
    select id from table_a where age<34 order by age desc
) limit 10000,100 ;

子查询根据非聚簇索引查到年龄小于34的id，免去了10000次无效查询
根据id在聚集索引上利用IN查询遍历数据

该做法虽然也会查询到10000个无用数据，但是由于使用到了主键索引，并不会再去回表查询，在性能上还是有所提高

我们讲limit放在子查询里面，应该还能进一步优化，减少了IN查询的负担

优化3.0

对于IN操作，一旦数量上去了情况还是不太好，这里还能使用JOIN代替IN。

select * from table_a as a
inner join 
(select id from table_a where age < 34 order by age desc limit 10000,100) as b 
on a.id=b.id;

这种做法经过测试会比最原始的SQL快10倍。

//看其他大佬说的hhh

这里还需要注意的是，MySQL的JOIN有一个优化点，即用小表做驱动表去驱动大表。

比如对于 t1 left join t2 的情况，就建议把记录数较小的表放在前面，前面的表示驱动表，会扫描t1所有记录然后再去t2查询。

如果t1有M条记录，t2 N条，使用t2的索引的情况下，时间复杂度是M * logN左右，因此M的影响，也即t1的记录数对时间影响更大。

不过这里由于使用的是INNER JOIN，MySQL对INNER JOIN会自动使用小表，因此问题不大，实测下来耗时也相差无几。