今天咱们来聊聊一道常见的面试题：MySQL深分页如何优化？
在实际开发中，分页查询是很常见的需求，但当分页深度过大时，性能问题就会随之出现。比如，你可能在第 1000 页时发现查询耗时从毫秒级飙升到几十秒，甚至更久。
一、深度分页是什么？
在 MySQL 中，分页通常是通过 LIMIT 和 OFFSET 实现的。比如，你想获取第 10001 到第 10010 条数据，SQL 就是：
SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 10000;
看起来没啥问题，对吧？但问题就出在 OFFSET 上。MySQL 要返回这 10 条数据，必须先扫描并跳过前 10000 条记录。随着分页深度增加，OFFSET 越来越大，MySQL 需要扫描的行数也越来越多。这就像是在大海里捞针，捞了 10000 次，最后只捡起 10 根针，效率可想而知。
二、深度分页为啥这么慢？
- 大量 I/O 操作：MySQL 需要扫描大量行来跳过
OFFSET，这会产生大量磁盘 I/O，尤其是当数据量大且没有索引时。 - 内存消耗：扫描大量行意味着更多数据被加载到内存中，增加了内存压力。
- 锁竞争：在高并发场景下，深度分页查询可能会锁定大量行，影响其他查询的性能。
三、如何优化深度分页？
1. 索引优化
索引是提升查询性能的“神器”。确保排序和过滤的列上有索引，这样 MySQL 可以快速定位到目标数据，减少扫描行数。比如：
SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 10000;
如果 id 列上有索引，MySQL 可以直接通过索引快速跳过前 10000 条记录，而不是全表扫描。
2. 覆盖索引
如果查询只涉及少数几列，可以使用覆盖索引来避免回表操作。覆盖索引包含查询所需的所有列，直接从索引中获取数据，无需访问表的数据页。比如：
CREATE INDEX idx_cover ON table_name (column1, column2);
SELECT column1, column2 FROM table_name WHERE column1 = ? AND column2 = ?;
这种方式大大减少了 I/O 操作，提升了查询性能。
3. 标记分页
标记分页是通过保存上一次查询的最后一个记录的标记（通常是唯一标识符）来实现的。这种方法不使用 OFFSET，而是通过 WHERE 子句来获取下一页的数据。比如：
SELECT * FROM table_name WHERE id > last_id ORDER BY id LIMIT 20;
这种方法尤其适用于有序的、连续的分页请求，避免了大量行的扫描。
4. 分区表
如果数据集非常大，可以考虑使用分区表。分区可以将表分成更小的块，减少每次查询需要扫描的数据量。MySQL 支持多种分区方法，如范围分区、列表分区等。
例如，假设有一个包含销售记录的表 sales，其中有一列 sale_date，表示销售的日期。我们可以按年份对这个表进行分区：
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_id INT,
quantity INT,
sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024)
);
这样，查询时 MySQL 只会访问相关的分区，大大提高了查询效率。
5. 缓存结果
如果分页查询的结果不会频繁变化，可以考虑缓存查询结果。缓存可以显著减少数据库的负载，尤其是在高并发的场景下。比如，可以使用 Redis 来缓存分页结果。
6. 使用外部搜索引擎
对于特别复杂或数据量巨大的场景，可以考虑使用外部搜索引擎，如 Elasticsearch 或 Solr。这些工具专为处理大数据集和复杂查询而设计，通常比传统数据库更高效。
四、实践中的注意事项
- 合理选择分页大小：分页大小直接影响查询性能和用户体验。较小的分页大小可以减少每次查询的负担，但会增加分页请求的次数。选择合适的分页大小需要权衡这两者的关系。
- 监控和分析查询性能：使用 MySQL 的性能监控工具（如
EXPLAIN和慢查询日志）来分析查询的执行计划和性能瓶颈。 - 考虑用户体验：在某些情况下，用户可能并不需要非常精确的分页数据。可以考虑使用“加载更多”按钮或无限滚动来替代传统分页。
五、总结
深度分页问题是 MySQL 分页查询中的一个常见难题，但通过合理的优化策略，我们可以有效提升查询效率。具体来说，可以通过以下几种方式来优化：
- 索引优化：确保排序和过滤的列上有索引。
- 覆盖索引：减少回表操作，直接从索引中获取数据。
- 标记分页：避免使用
OFFSET，通过WHERE子句获取下一页数据。 - 分区表：将大表分成多个分区，减少查询扫描的数据量。
- 缓存结果：缓存分页查询结果，减少数据库负载。
- 使用外部搜索引擎：在复杂场景下，使用 Elasticsearch 或 Solr 等工具。
最后分享一份大彬精心整理的大厂面试手册，包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等高频面试题，非常实用，有小伙伴靠着这份手册拿过字节offer~
围观朋友⭕：dabinjava
