17370845950

使用游标分页替代大偏移量LIMIT，为排序字段建立索引，选择必要字段并利用覆盖索引，结合延迟关联减少数据扫描，可显著提升分页查询性能。

分页查询在数据量大时容易变慢，特别是在使用 LIMIT offset, size 且 offset 非常大的情况下。MySQL 需要跳过大量记录，导致性能下降。以下是几种有效的优化策略。

1. 避免大偏移量的 LIMIT 分页

当执行类似 SELECT * FROM table LIMIT 100000, 20 的语句时，MySQL 仍需扫描前 100000 条记录。这种“跳过”操作成本很高。

优化方法：

• 使用基于游标的分页（也叫键集分页），利用上一页最后一条记录的主键或排序字段作为下一页的起点。
• 例如按主键升序分页：SELECT * FROM table WHERE id > 100000 ORDER BY id LIMIT 20
• 这种方式能利用索引快速定位，避免全表扫描或大量跳过。

2. 确保排序字段有合适的索引

分页通常伴随 ORDER BY 子句。如果排序字段没有索引，MySQL 会进行 filesort，严重影响性能。

建议：

• 为 ORDER BY 字段建立索引，尤其是主键或唯一字段。
• 若排序字段和过滤条件组合使用，考虑创建联合索引。
• 比如查询 WHERE status = 1 ORDER BY create_time，应建立 (status, create_time) 联合索引。

3. 减少不必要的数据加载

不要使用 SELECT *，只选择需要的字段。

• 减少 I/O 和网络传输开销。
• 如果可能，用覆盖索引（Covering Index）让查询直接从索引中获取数据，避免回表。
• 例如：索引包含 (user_id, name, create_time)，查询只取这三个字段，则无需访问数据行。

4. 使用延迟关联（Deferred Join）

适用于需要排序和分页的大表查询。

思路是先通过索引找出符合条件的主键，再与原表关联获取完整数据。

示例：

SELECT t.* 
FROM table t
INNER JOIN (
    SELECT id FROM table 
    WHERE status = 1 
    ORDER BY create_time 
    LIMIT 100000, 20
) AS tmp USING(id);

这样子查询只走索引，速度快，外层再根据主键查具体数据。

基本上就这些。核心是避免大偏移、善用索引、减少数据处理量。实际应用中结合业务场景选择合适方案，效果更明显。