MySQL高偏移量Limit查询卡顿问题求助

问题原因 #

当使用LIMIT offset, size进行高偏移量分页时，MySQL需要先扫描offset + size条符合条件的记录，再丢弃前offset条，仅返回后size条。对于偏移量接近总数据量的场景（比如319950，总数据32万），这意味着要扫描几乎全部数据，再进行大量数据丢弃操作。如果查询需要回表获取完整行数据（比如SELECT *），会产生极高的IO开销，直接导致查询卡顿。

从你的EXPLAIN结果来看，虽然使用了ix_shoppingTrip_starttime索引，但预估扫描行数7700多万远大于实际32万，说明统计信息可能存在偏差，进一步加剧了优化器的低效选择，但核心问题还是大偏移量的LIMIT机制本身。

解决方案 #

1. 采用游标分页（推荐） #

放弃基于偏移量的分页，改用基于上一页最后一条记录的游标定位，避免扫描前面的所有行。由于startTripTime可能存在重复值，需要结合主键ID作为唯一定位条件。

具体SQL示例

• 第一页查询：

SELECT * FROM shoppingTrip 
WHERE startTripTime > '2022-06-23 00:00:00' 
  AND endTripTime <= '2022-06-23 23:59:59'
ORDER BY startTripTime ASC, ID ASC 
LIMIT 150;

• 后续分页查询（记录上一页最后一条的startTripTime和ID，比如last_start_time = '2022-06-23 23:58:10'，last_id = 123456）：

SELECT * FROM shoppingTrip 
WHERE startTripTime > '2022-06-23 00:00:00' 
  AND endTripTime <= '2022-06-23 23:59:59'
  AND (startTripTime > 'last_start_time' 
       OR (startTripTime = 'last_start_time' AND ID > last_id))
ORDER BY startTripTime ASC, ID ASC 
LIMIT 150;

这种方式利用索引直接定位到上一页的末尾，无需扫描前面的大量数据，查询效率不会随分页深度下降。

2. 创建优化的复合索引 #

为上述游标查询创建复合索引，让MySQL可以完全通过索引完成过滤和排序，避免回表：

CREATE INDEX ix_shoppingTrip_start_end_id ON shoppingTrip (startTripTime, endTripTime, ID);

该索引覆盖了查询的过滤条件（startTripTime、endTripTime）、排序条件（startTripTime、ID），优化器可以直接通过索引获取符合条件的行，无需回表查询全量数据。

3. 更新表统计信息 #

运行以下命令更新表的统计信息，让优化器能更准确地估算扫描行数，选择更高效的执行计划：

ANALYZE TABLE shoppingTrip;

额外说明 #

如果你必须保留基于偏移量的分页（比如需要支持跳页），可以考虑：

• 先查询符合条件的主键ID，再通过主键关联获取完整数据，减少回表次数：

SELECT st.* 
FROM shoppingTrip st
JOIN (
    SELECT ID FROM shoppingTrip 
    WHERE startTripTime > '2022-06-23 00:00:00' 
      AND endTripTime <= '2022-06-23 23:59:59'
    ORDER BY startTripTime ASC, ID ASC 
    LIMIT 319950, 150
) AS ids ON st.ID = ids.ID
ORDER BY st.startTripTime ASC, st.ID ASC;

这种方式让子查询仅扫描索引获取ID，再通过主键快速定位行数据，比直接SELECT * LIMIT高效，但仍不如游标分页。

内容的提问来源于stack exchange，提问作者Mert Kara