MySQL 如何深度分页问题

在实际的数据库应用场景中，我们常常会遇到需要进行分页查询的需求。对于少量数据的分页查询，MySQL 可以轻松应对。然而，当我们需要进行深度分页（即从大量数据的中间位置开始获取少量数据）时，就会面临性能严重下降的问题。本文将深入探讨 MySQL 深度分页的问题，并介绍子查询和滚动 ID 这两种有效的解决方式。

深度分页问题背景

深度分页通常是指使用 LIMIT m, n 语句进行查询，其中 m 是偏移量，n 是要返回的记录数。当 m 的值非常大时，就会出现深度分页的情况。例如：

select * from user where sex='女' order by age limit 100000000, 10;

这条 SQL 语句的目的是从 user 表中筛选出性别为女的记录，按照年龄排序，然后跳过前面的 100000000 条记录，返回接下来的 10 条记录。

深度分页性能问题原因

LIMIT 主要在 Server 层执行，而不是存储引擎层。在 Server 层，执行器调用存储引擎 API 获取数据时，会先获取到全部符合条件的数据，然后在发送给客户端的时候才会进行 LIMIT 操作。具体来说，Server 层在执行上述 SQL 时，每获取到一条数据，会查看该数据是否是第 100000001 条数据，如果不是，就不会发送到客户端，只进行 limit_count 计数，直到获取到第 100000001 条数据才开始发送到客户端。

此外，如果查询的字段需要回表扫描，每一次查询都会拿着 age 列的二级索引查到的主键值去回表。对于 LIMIT 10000000, 10 这样的查询，就会回表 10000000 次，效率极低。我们可以使用 EXPLAIN 查看查询计划来进一步分析：

explain select * from user where sex = '女' order by age limit 10000000, 10;

会发现这条记录通常不会走age索引,而是选择全表扫描+文件排序,这是因为优化器认为选择age的效率甚至比不上全表扫描+文件排序
所以当翻页靠后时,查询会变得很慢,因为随着偏移量的增加,我们需要排序和扫描的非目标行数据也会越来越多,这些数据再扫描后都会被丢弃。

解决深度分页问题的方法

子查询优化

子查询优化的核心思想是先在二级索引上获取需要的主键 ID，然后再根据这些 ID 去主键索引中获取完整的数据，避免了直接在主键索引上进行大量的偏移操作。

示例代码如下：

SELECT * 
FROM USER 
WHERE sex = '女' AND id >= (SELECT id FROM USER WHERE sex = '女' ORDER BY age LIMIT 10000000,1) 
ORDER BY age LIMIT 10;

在这个查询中，子查询 SELECT id FROM user WHERE sex = ‘女’ ORDER BY age LIMIT 10000000, 1 会在 age 列的二级索引上操作，由于二级索引只包含索引列和主键值，数据量相对较小，跳过 10000000 条记录的开销也会小很多。主查询再根据子查询返回的 id 在主键索引上定位到起始位置，获取接下来的 10 条记录，减少了扫描的数据量和回表次数，从而提高了查询性能。

滚动 ID 方式

滚动 ID 方式不支持跳页，但可以在顺序分页的场景下有效解决深度分页问题。其原理是记录上一页的最后一条记录的 ID，然后在查询下一页时，只查询 ID 大于该记录 ID 的数据。

示例代码如下：

假设上一页的最后一条记录的 ID 是 last_id：

SELECT * FROM user WHERE sex = '女'  AND id > last_id ORDER BY age LIMIT 10;

在实际应用中，当用户请求第一页数据时，正常执行查询：

SELECT * FROM user WHERE sex = '女' ORDER BY age LIMIT 10;

记录下这一页的最后一条记录的 ID，当用户请求下一页数据时，使用上述滚动 ID 的查询语句。这种方式避免了大量的偏移操作，每次查询只需要从上次记录的 ID 之后开始扫描，大大提高了查询效率。

总结

深度分页是 MySQL 中一个常见且棘手的问题，使用子查询和滚动 ID 这两种方式可以在不同的场景下有效解决该问题。子查询适用于需要跳页的场景，通过在二级索引上操作减少回表次数和扫描数据量；滚动 ID 方式适用于顺序分页的场景，通过记录上一页的最后一条记录的 ID 避免大量的偏移操作。在实际应用中，我们需要根据具体的业务需求选择合适的解决方案。