深分页性能问题源于MySQL扫描过多无用数据，核心解决思路是减少扫描量。可通过禁止深分页、优化查询条件、使用书签法（如按ID或时间戳分页）、延迟关联、覆盖索引、缓存、归档或分库分表等方式优化。其中书签法效率高但需唯一递增索引，且受删除和并发影响；延迟关联适用于大表复杂查询，但需注意子查询性能与排序一致性。选择方案应基于数据量、查询模式和业务需求综合评估，并通过EXPLAIN分析执行计划，测试对比效果后实施监控。
深分页，说白了就是LIMIT语句里的offset太大，导致MySQL不得不扫描大量不需要的数据，性能自然就下来了。核心在于避免扫描过多无用数据。
解决方案
解决MySQL深分页问题的策略有很多，没有银弹，需要根据具体场景选择。
禁止深分页： 最简单粗暴，如果业务上确实不需要，直接限制分页深度，比如超过100页就直接返回空或者提示用户。
优化SQL查询条件： 看看能不能通过更精确的查询条件，减少需要扫描的数据量。 比如，如果你的查询基于时间范围，确保时间范围的索引覆盖了大部分查询。
使用书签（Seek Method）： 记录上一页的最后一条数据的id或者其他唯一索引字段，下一页查询的时候，直接根据这个id进行条件过滤，而不是使用offset。 比如：
SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT page_size;
这种方式避免了扫描前面的记录，直接定位到需要的数据。延迟关联/子查询优化： 先通过子查询或者关联查询获取到需要分页的id，然后再根据这些id去查询实际的数据。 比如：
SELECT t1.* FROM table1 t1 INNER JOIN (SELECT id FROM table1 ORDER BY id LIMIT offset, page_size) t2 ON t1.id = t2.id;
这种方式可以减少主查询需要扫描的数据量。使用覆盖索引： 如果你的查询只需要用到索引字段，那么可以使用覆盖索引，避免回表查询，提高查询效率。
优化排序： 确保排序字段上有索引，避免filesort。
缓存： 对于访问频率高、数据变化不频繁的深分页数据，可以考虑使用缓存，比如Redis或者Memcached。
数据归档： 将历史数据归档到其他存储介质，减少主表的数据量。
分库分表： 如果数据量实在太大，可以考虑分库分表，将数据分散到多个数据库或者表中。
Elasticsearch等搜索引擎： 对于复杂的搜索和分页需求，可以考虑使用Elasticsearch等搜索引擎，它们在全文检索和分页方面做了很多优化。
如何选择适合我的深分页优化方案？
选择方案的关键在于了解你的数据特点、查询模式和业务需求。
- 数据量： 数据量越大，越需要考虑分库分表或者使用搜索引擎。
- 查询模式： 如果查询条件比较固定，可以使用书签或者覆盖索引。如果查询条件比较灵活，可能需要考虑使用搜索引擎。
- 业务需求： 如果业务上允许限制分页深度，那么直接禁止深分页是最简单的方案。如果业务上需要支持任意深度的分页，那么需要选择其他的优化方案。
一般来说，可以按照以下步骤进行选择：
-
评估： 评估当前深分页的性能瓶颈，可以使用MySQL的
EXPLAIN
命令分析SQL语句的执行计划。 - 尝试： 先尝试一些简单的优化方案，比如优化SQL查询条件、使用覆盖索引等。
- 测试： 对比不同方案的性能，选择最适合你的方案。
- 监控： 监控深分页的性能，及时发现和解决问题。
书签（Seek Method）方案的具体实现和注意事项？
书签方案，也叫游标分页，是避免深分页性能问题的常用方法。它基于上次查询的结果来定位下一次查询的起始位置，而不是通过offset来跳过前面的数据。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录，或需要将该能力封装成可公开复用的 Codex skill。
实现方式：
记录上次查询的最后一个ID： 每次查询后，记录下当前页最后一条数据的ID（或者其他唯一索引字段）。
-
下次查询使用ID作为条件： 下一次查询时，使用这个ID作为条件，查询大于该ID的数据。
-- 第一次查询 SELECT * FROM table ORDER BY id LIMIT 10; -- 假设第一次查询的最后一条数据的ID是100 -- 第二次查询 SELECT * FROM table WHERE id > 100 ORDER BY id LIMIT 10;
注意事项：
ID必须是唯一索引： 书签方案依赖于ID的唯一性，如果ID不是唯一索引，可能会导致数据重复或者遗漏。
ID必须是递增的： 书签方案依赖于ID的递增性，如果ID不是递增的，可能会导致数据错乱。
删除数据的影响： 如果在分页过程中有数据被删除，可能会导致数据遗漏。 需要考虑数据删除策略，比如使用逻辑删除，或者在查询时排除已删除的数据。
并发问题： 在高并发场景下，可能会出现并发问题，导致数据重复或者遗漏。 可以考虑使用乐观锁或者悲观锁来解决并发问题。
-
排序字段： 如果需要按照其他字段排序，需要确保排序字段上有索引，并且在查询条件中包含该字段。
-- 按照create_time排序 SELECT * FROM table WHERE (create_time, id) > ('2023-10-27 10:00:00', 100) ORDER BY create_time, id LIMIT 10;这里需要使用联合索引
(create_time, id)
，并且在查询条件中同时包含create_time
和id
，才能保证分页的正确性。
延迟关联/子查询优化方案的适用场景和潜在问题？
延迟关联/子查询优化，简单来说，就是先把需要分页的ID查出来，然后再根据这些ID去查实际的数据。 这种方法可以减少主查询需要扫描的数据量，提高查询效率。
适用场景：
- 大表分页： 适用于数据量非常大的表，因为可以减少主查询需要扫描的数据量。
- 复杂查询： 适用于查询条件比较复杂，导致主查询需要扫描大量无用数据的场景。
- 回表代价高： 适用于回表代价比较高的场景，比如需要回表查询大量的TEXT或者BLOB字段。
潜在问题：
- 子查询性能： 如果子查询的性能本身就很差，那么延迟关联/子查询优化可能不会带来明显的性能提升。 需要优化子查询的性能，比如使用索引、避免全表扫描等。
-
ID重复： 如果子查询返回的ID有重复，可能会导致主查询返回的数据量大于
page_size
。 需要在子查询中去重，比如使用DISTINCT
关键字。 -
排序问题： 如果需要在主查询中排序，需要确保子查询返回的ID是有序的。 可以在子查询中使用
ORDER BY
子句，并且在主查询中使用相同的排序方式。 - 死锁风险： 在某些情况下，延迟关联/子查询优化可能会导致死锁。 需要注意事务隔离级别和锁的使用，避免死锁的发生。
示例：
-- 原始查询 SELECT * FROM table WHERE ... ORDER BY id LIMIT offset, page_size; -- 延迟关联/子查询优化 SELECT t1.* FROM table t1 INNER JOIN (SELECT id FROM table WHERE ... ORDER BY id LIMIT offset, page_size) t2 ON t1.id = t2.id;
总的来说，延迟关联/子查询优化是一种有效的深分页优化方案，但需要根据具体场景进行评估和测试，避免引入新的性能问题。
