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
