MySQL的limit优化2

一、底层原理

在 MySQL 8.0 中，当使用 limit offset, count 进行分页查询时，如果 offset 非常大（例如 limit 200000, 10），性能会显著下降。

这是因为 MySQL 需要扫描 offset + count 行数据，然后丢弃前 offset 行，只返回最后的 count 行

二、基本语法与功能

• LIMIT子句的基本语法格式为limit offset, count。其中，offset是可选参数，表示返回记录行的偏移量，即从哪一行开始返回，默认为0；count表示返回的行数。

• 例如，select * from table_name limit 5；表示查询table_name表中的前5条记录；

• select * from table_name limit 3, 5；则表示从第4条记录开始获取，共获取5条记录

三、limit 200000, 10；以下优化方案

1、使用覆盖索引（Covering Index）

原理：通过索引直接获取查询所需的所有字段，避免回表查询数据行

优化步骤：

• 创建覆盖索引，确保索引包含 select 和 where 涉及的所有字段。

• 改写查询，仅从索引中获取数据

示例：

假设需要查询 orders 表的分页数据，并按 order_date 排序：

关键点：

• 子查询 SELECT id FROM orders ... 仅扫描索引，避免回表。

• 外层查询通过 id 快速定位数据行

2、游标分页（Cursor-based Pagination）

原理：基于上一页的最后一条记录的值（如自增 ID 或时间戳）来获取下一页数据，避免使用 offset

示例：

假设按自增 id 分页：

优点：

• 完全避免 offset，性能稳定（时间复杂度 O(1)）

• 适合有序且连续的字段（如自增 ID、时间戳）

缺点：

• 不支持随机跳页（只能“上一页”和“下一页”）

3、延迟关联（Deferred Join）

原理：先通过子查询快速定位目标行的主键，再关联原表获取完整数据

示例：

优化效果：

• 子查询仅扫描索引，避免回表

• 外层查询通过主键快速定位数据行

4、使用范围条件分页

原理：通过 WHERE 条件缩小数据范围，减少扫描的行数

示例：

适用场景：

• 主键或唯一键有序且连续

• 分页逻辑允许按范围查询