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行。

mysql如何跳过10000行

  • 1、直接定位到id=10000肯定不行的,因为id自增可能不是为1;
  • 2、并且中间还可能删除了数据导致id不连续;
  • 3、并且如果排序行不是id行,那id=10000的行有可能处于任何一行;
  • 4、更何况还有些表没有id,就算有id,在有查询条件的情况下,id=10000的记录肯定也不是真正的第10000条。

所以mysql必须逐行计数,直到计到了10000行,才会开始取出它后面的10行数据,毫无疑问,如果跳过的行数量越多,计数次数就越多,所用时间就越久,查询肯定也就越慢

解决方法一

如果我们只select一个id字段(id是主键),虽然也是计数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>=的方式来查(倒序就是id<=),耗时大约1.9-2.2ms

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

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

-- 倒序
SELECT * FROM history WHERE id<=(SELECT id FROM history ORDER BY id DESC LIMIT 5000, 1) ORDER BY id DESC LIMIT 10;

要求

  • 1、id必须是自增的,并且必须是按id正序或倒序排序;
  • 2、用户必须是点上一页或下一页,而不是直接输入页码跳页。

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

SELECT * FROM history WHERE id > pre_last_id LIMIT 10;

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

要求

  • 1、id必须是自增的,并且必须是按id正序或倒序排序;
  • 2、用户必须是点上一页或下一页,而不是直接输入页码跳页。

Stack overlfow中的这几个回答不错:
Why does MYSQL higher LIMIT offset slow the query down?
MySQL ORDER BY / LIMIT performance: late row lookups

打赏

订阅评论
提醒
guest

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

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

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

MySQL的limit分页查询性能问题