MySQL的limit分页查询性能问题
Table of Contents
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?。
