大偏移量查询缓慢?

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

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

分析MySQL的 LIMIT 10000000 , 10

LIMIT: 会进行三步操作

1. ORDER: 排序
2. OFFSET:跳过多少行数据
3. LIMIT: 取多少行数据

性能消耗在哪里了?

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

ORDER 操作

MySQL 返回的数据排序方式只有两种:

1. 默认(不设置排序字段):使用主键进行排序
2. 指定: 指定排序字段.(示例:ORDER age DESC)

问题 1

如果你的条件where字段与排序order字段不一样,就会回表查询 order字段再用 order 字段排序

所以

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

条件字段 age,排序字段主键id(默认)

1. 会先扫描 age 的索引拿到 age>18 的所有主键 id
2. 将所有(可能有千万条)主键 id回表并排序(性能消耗巨大)
3. …

OFFSET操作

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

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

问题 2

所以 OFFSET 越大,需要扫码的数据行数越多,消耗越大.

LIMIT 操作

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

如何优化?

主要有两条路线:

1. 减少不必要的回表(ORDER字段与where字段相同)
   示例:

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

解释:
1. 走 age 的索引,取出根据 age 排序的第 10000001 到 10000010 条数据的主键
2. 根据主键(10 个)回表查询数据

• 或者使用覆盖索引(子查询)去避免回表(查询的字段包含在索引中就不用回表)
  建立一个(id,age)的索引

SELECT * FROM test a join (select id from test where age > 18 limit 10000000, 10) b on a.id=b.id;

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

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

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