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