MySQL深度分页优化问题

MySQL深度分页优化问题

一、Limit使用

limit子句可以用于强制select语句返回指定的记录数。

select 字段 from 表名 limit 参数1,参数2;
select 字段 from 表名 limit 参数2 offset 参数1;（为了与 PostgreSQL 兼容）
  • 参数1：指定第一个返回记录行的偏移量，从0开始
  • 参数2：指定返回记录行的最大数目

如果只给定一个参数，那么表示返回的最大记录行数目。

如果第二个参数为-1，表示第一个参数的偏移量之后的所有的数据。

二、深度分页优化

查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低。比如下面的查询：

# 普通分页查询
test> select * from t_demo order by id limit 1000000, 10
[2024-01-18 20:26:41] 在 396 ms (execution: 346 ms, fetching: 50 ms) 内检索到从 1 开始的 10 行

这条SQL在执行的过程中，通过非聚簇索引去查询主键，然后拿到主键再通过聚簇索引进行回表查询，查询到满足条件的1000010条数据，丢弃前面的1000000条，返回最后10条。

优化的思路也非常明确：

  • 减少回表的次数
  • 尽量通过索引来查询

2.1 范围查询

当可以保证 ID 的连续性时，根据 ID 范围进行分页是比较好的解决方案：

# 连续id时，根据id范围进行分页
test> select * from t_demo where id > 1000000 and id <= 1000010 order by id
[2024-01-18 20:26:41] 在 69 ms (execution: 8 ms, fetching: 61 ms) 内检索到从 1 开始的 10 行
# 通过记录上次查询结果的最后一条记录的ID进行下一页的查询
test> select * from t_demo where id > 1000000 limit 10
[2024-01-18 20:26:41] 在 76 ms (execution: 6 ms, fetching: 70 ms) 内检索到从 1 开始的 10 行

但是一般来说，实际生产中很少会去使用数据库的自增ID，所以这种优化方式的局限性比较大，而且也不是很有必要。

2.2 子查询

阿里《Java开发手册》中也有明确的优化方式：

image-20240118203734467

我们先来看子查询，子查询的思路是：先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会高一点。

# 子查询
test> select * from t_demo where id >= (select id from t_demo limit 1000000, 1) limit 10
[2024-01-18 20:26:41] 在 210 ms (execution: 177 ms, fetching: 33 ms) 内检索到从 1 开始的 10 行

不过，子查询的结果会先产生一张新的表，会影响数据库的性能，所以应该避免使用子查询，并且id>=(...) 也限制了ID必须是递增的，这同样不适用比较复杂的场景。

2.3 inner join 延迟关联

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引，然后减少回表。不同点是，延迟关联使用了 INNER JOIN 代替子查询。

# inner join 延迟关联
test> select t1.* from t_demo t1
      inner join (select id from t_demo limit 1000000, 1) t2
      on t1.id >= t2.id
      limit 10
[2024-01-18 20:26:42] 在 211 ms (execution: 181 ms, fetching: 30 ms) 内检索到从 1 开始的 10 行

2.4 覆盖索引

索引中已经包含了所有需要获取的字段的查询方式称为覆盖索引。

# 覆盖索引
test> select id, a, b from t_demo
      order by a
      limit 1000000, 10
[2024-01-18 20:26:42] 在 279 ms (execution: 248 ms, fetching: 31 ms) 内检索到从 1 开始的 10 行

覆盖索引的好处：

  • 避免 InnoDB 表进行索引的二次查询，也就是回表操作： InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
  • 可以把随机 IO 变成顺序 IO 加快查询效率： 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。