MySQL的limit分页查询性能问题

MySQL的limit分页查询性能问题

MySQL的limit分页查询性能问题

问题产生原因

基于我这边的一个history表,这个查询需要4.2-5.6ms(每次执行需要的时间都会变化很正常)

SELECT * FROM history LIMIT 10000, 10;

-- 如果有其它条件
SELECT * FROM history WHERE <条件> LIMIT 10000, 10;

mysql的limit 10000,10意思是跳过10000行,返回10000行后面的10行,但是它本质上是查询出10010行,然后把10000行丢弃,再返回10行,可见如果跳过的行数量越多,它查询肯定也就越慢

解决方法一

如果我们只select一个id字段(id是主键),同样是先查10010后丢掉10000,但是由于id是主键,并且也因为只查一列数据,数据量小的多,所以查询速度会非常快

SELECT id FROM history LIMIT 10000, 10;

-- 如果有其它条件
SELECT id FROM history WHERE <条件> LIMIT 10000, 10;

然后我们用自连接的方法,查出这10个id对应的数据,这样执行下来就快的多了(大约1.9-2.1ms)

SELECT * FROM history h1 JOIN (SELECT id FROM history LIMIT 10000, 10) h2 ON h1.id=h2.id;

-- 如果有where条件,把where条件放在子查询里
SELECT * FROM history h1 JOIN (SELECT id FROM history WHERE <条件> LIMIT 10000, 10) h2 ON h1.id=h2.id;

JOIN就是INNER JOIN的简写,所以我们也可以用where的形式来写这个INNER JOIN

SELECT * FROM history h1 , (SELECT id FROM history LIMIT 10000, 10) h2 WHERE h1.id=h2.id;

-- 如果有where条件,把where条件放在子查询里
SELECT * FROM history h1 , (SELECT id FROM history WHERE <条件> LIMIT 10000, 10) h2 WHERE h1.id=h2.id;

理论上,我们也可以用IN的方法去查,但实际上这样的语句mysql是不支持的,会报错的,所以这种方法不行

SELECT * FROM history WHERE id IN (SELECT id FROM history LIMIT 10000, 10);

This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
这个版本的MySQL还不支持‘LIMIT & IN/ALL/ANY/SOME 子查询’。注:我的mysql是8.0.29的,在我写这篇文章时几乎接近最新了,可见这个方法一直都是不支持的。

解决方法二

用子查询查出10条数据中的第一个id,即limit 10000, 1,然后用id>=的方式来查,耗时大约1.9-2.2ms

SELECT * FROM history WHERE id>=(SELECT id FROM history LIMIT 10000, 1) LIMIT 10;

-- 如果有where条件,把条件放在子查询里
SELECT * FROM history WHERE id>=(SELECT id FROM history WHERE <条件> LIMIT 10000, 1) LIMIT 10;

要求:id必须是自增的。

更进一步,上边那个子查询其实都可以不要,因为你前一页查出来的时候,返回的数据中就有id,我们只要把前一页的最大id拿去查,这样就不需要用到子查询了(pre_max_id为上一页最大id)

SELECT * FROM history WHERE id > pre_max_id LIMIT 10;

-- 如果有其它条件
SELECT * FROM history WHERE id > pre_max_id AND <条件> LIMIT 10;

Stack overlfow中的这个回答不错:Why does MYSQL higher LIMIT offset slow the query down?

打赏

订阅评论
提醒
guest

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

0 评论
内联反馈
查看所有评论
0
希望看到您的想法,请您发表评论x
()
x

扫码在手机查看
iPhone请用自带相机扫
安卓用UC/QQ浏览器扫

MySQL的limit分页查询性能问题