如何有效优化得物面试中的MySQL深度分页以提升查询性能

在去年秋季招聘中面试得物时被询问的一个问题。值得注意的是，数据库优化在面试中相当常见，许多大厂如阿里、腾讯、用友、京东和小红书等都曾涉及此问题。

深度分页的概述

查询偏移量过大的情形我们称之为深度分页。这种情况通常会导致查询性能显著降低。例如，以下查询在无法利用索引的情况下，需要跳过1000000条记录才能获取10条结果：

# MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录  
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

深度分页的优化建议

以下内容将以MySQL数据库为例，探讨如何优化深度分页。

使用范围查询

在能够确保ID的连续性的情况下，基于ID范围的分页是一个较为有效的解决方案：

# 查询指定 ID 范围的数据  
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id  
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询：  
SELECT * FROM t_order WHERE id > 100000 LIMIT 10

然而，这种优化方法的适用性较为有限，且一般项目的ID也无法保证完全连续。

利用子查询

通过先查询出limit第一个参数对应的主键值，再依此值进行过滤和限制，可以提高查询效率。

阿里巴巴的《Java开发手册》中也有相关描述：

利用延迟关联或子查询来优化深度分页场景。

图片

# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询  
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;

不过，使用子查询会生成一个新表，可能会影响性能，因此应尽量避免大量使用子查询。此外，这种方法仅适用于正序的ID。在复杂的分页场景中，通常需要通过过滤条件筛选符合条件的ID，此时的ID往往是离散且不连续的。

当然，我们也可以利用子查询获取目标分页的ID集合，但这种写法相对繁琐，不如使用INNER JOIN进行延迟关联。

使用INNER JOIN进行延迟关联

延迟关联的优化思路与子查询相似，都是将条件转移到主键索引树，从而减少回表的次数。不同之处在于，延迟关联采用INNER JOIN替代了子查询。

SELECT t1.* FROM t_order t1  
INNER JOIN (SELECT id FROM t_order limit 1000000, 1) t2  
ON t1.id >= t2.id  
LIMIT 10;

覆盖索引的应用

覆盖索引是指索引中已包含了所有查询所需字段的查询方式。

覆盖索引的优势：

  • 避免InnoDB表进行索引的二次查询，也就是回表操作: InnoDB是以聚集索引的顺序存储的，使用二级索引查询数据时，需通过主键进行二次查询才能获取真实数据。而在覆盖索引中，二级索引的键值可以直接获取所有数据，避免了对主键的二次查询，从而减少了IO操作，提升查询效率。
  • 将随机IO转变为顺序IO，提高查询效率: 覆盖索引按键值顺序存储，适合IO密集型的范围查找，因此相较于随机读取每一行数据，利用覆盖索引可减少IO操作。
# 如果只需查询 id, code, type 这三列，可建立 code 和 type 的覆盖索引  
SELECT id, code, type FROM t_order  
ORDER BY code  
LIMIT 1000000, 10;

然而，当查询的结果集占表的总行数的很大一部分时，可能就不会使用索引，自动转换为全表扫描。虽然可以通过 FORCE INDEX 强制查询优化器走索引，但这种提升效果通常不明显。

参考资料