MySQL 查询大偏移量(LIMIT)问题分析

大偏移量查询缓慢?

示例:(假设age字段有索引)

SELECT * FROM test WHERE age>18 LIMIT 10000000 ,10;

分析MySQL的 LIMIT 10000000 , 10

LIMIT: 会进行两步操作

1. OFFSET:跳过多少行数据
2. LIMIT: 取多少行数据

性能消耗在哪里了?

我们针对三部操作看性能消耗在哪里了

OFFSET操作

OFFSET是跳过多少行数据,
例子:
比如 OFFSET 10000000并不是直接从 10000001 开始计数,
而是整整要从第1个扫描到 10000001 个数据,然后开始计数)

注意区别
ORDER id OFFSET 10000000 是跳过 id 的前面的10000000 条数据从 10000001条开始,需要扫描到 10000001 条
WHERE id >10000000: 是直接定位到 id=10000000 的数据取大于它的数据

问题 2

直接 OFFSET 是全表扫描.
所以 OFFSET 越大,需要扫码的数据行数越多,消耗越大.

LIMIT 操作

LIMIT 10 操作本身是没太大消耗的,就是查询数据的时候只取多少条数据(这里是取 10 条),主要是前面的ORDER回表与OFFSET行数跨越的消耗.

如何优化?

主要有两条路线:

1. 避免全表扫描
   使用覆盖索引(子查询)去避免全表扫描

--普通分页(大)
--耗时: 1.6s
EXPLAIN SELECT * FROM `user` LIMIT 9000002,10;

SELECT * FROM `user` LIMIT 9000002,10;

普通的 explain

ALL 表示全表扫描的,性能很低

--覆盖索引
--耗时: 944ms
EXPLAIN SELECT * FROM `user` a JOIN (
    SELECT id FROM `user` LIMIT 9000001,10
) AS b ON a.id = b.id;

SELECT * FROM `user` a JOIN (
    SELECT id FROM `user` LIMIT 9000001,10
) AS b ON a.id = b.id;

覆盖索引分析

• 内部的子查询(SELECT id FROM user LIMIT 9000001,10): 使用的 index(索引),而不是 ALL(全表扫描)性能高很多

• 后面的 join 因为只有 10 行数据,性能消耗并不高

• a.id=b.id 使用的(eq_ref)并且只有 10 行数据,开销几乎可以忽略(相对于 LIMIT 9000001,10)

• 我们可以单独测试子查询的耗时

-- 子查询单独测试
--耗时: 1s
EXPLAIN  SELECT id FROM `user` LIMIT 9000001,10;
SELECT id FROM `user` LIMIT 9000000,10;

子查询几乎占了查询命令中 100% 的时耗,其他的 join 操作几乎 0 时耗

结论:
1. 使用覆盖索引(index)查询比直接 全表查询性能优越非常多,(我这里是 40%)
2. 最耗时的还是 OFFSET 操作(60% 的性能无法跨越)

2. 尽量不要使用 OFFSET 大偏移量查询,而是使用 where 快速定位.

--耗时: 19ms
SELECT * FROM `user` WHERE id>  9000004 LIMIT 10;

结论

• 使用 where 精确定位几乎0 损耗(性能比 覆盖索引的 OFFSET 高近50 倍)

参考:
https://juejin.cn/post/7270800456862466087
https://www.51cto.com/article/683765.html
https://blog.youkuaiyun.com/hellokitty_nba/article/details/123824417
https://juejin.cn/post/7094807113364406309