千万级MySQL分页查询超时优化实战：从7s超时到10ms响应

一、问题背景

在处理千万级核心业务表big_data_table（7000万+数据）的分页查询时，遇到了严重的性能瓶颈。业务中需要查询指定时间范围内的主键ID并分页，初始SQL如下：

SELECT ID FROM big_data_table 
WHERE create_time BETWEEN '2025-12-01 00:00:00' AND '2025-12-01 23:59:59' 
ORDER BY ID ASC LIMIT 200 OFFSET 0;

这条仅查询主键ID、限制200条结果的简单SQL，在生产环境中超时超过7秒，直接导致依赖该查询的业务流程阻塞，影响正常业务运转。

二、问题诊断：从EXPLAIN执行计划入手

MySQL性能优化的核心是先定位问题，而非盲目调整。通过执行EXPLAIN分析上述SQL的执行计划，我们发现了三个关键异常点，这也是导致查询超时的核心原因：

1. 索引选择偏差：优化器未使用create_time字段的已有索引，反而选择了主键索引PRIMARY，背离了时间筛选的查询场景。

2. 扫描范围过大：执行计划预估扫描行数达5万+，实际触发全索引扫描（遍历整个主键索引树），而非精准的范围扫描，无效扫描成本过高。

3. 执行类型错误：type字段显示为index（全索引扫描），而非预期的range（范围扫描），执行效率大幅降低。

深层原因：MySQL优化器依赖表的统计信息判断执行成本，由于统计信息过期，优化器误判“走主键索引排序”的成本更低，最终导致需要遍历整个主键索引树后再做时间过滤，性能急剧下降。

三、优化方案：从索引设计到查询重构

针对上述问题，我们采用“索引适配→查询优化→长期重构”的阶梯式方案，逐步将查询响应时间从秒级优化到毫秒级。

3.1 第一步：创建最优联合覆盖索引

查询的核心诉求是“按create_time筛选、按ID排序、查询ID”，因此设计“筛选字段+排序字段”的联合覆盖索引是关键，既能适配筛选和排序，又能避免回表。

CREATE INDEX idx_create_time_id ON big_data_table (create_time, id);

该联合索引的核心优势的如下：

• 覆盖索引特性：索引中直接包含id字段，查询时无需回表访问原表数据，大幅减少磁盘IO开销。

• 范围扫描适配：通过create_time字段可快速定位目标时间范围内的数据，避免全表或全索引遍历。

• 排序开销消除：索引内部已按create_time排序，且包含id字段，无需额外执行排序操作，减少CPU消耗。

3.2 第二步：强制索引与查询语法优化

创建索引后，由于表统计信息未及时更新，优化器可能仍会选择错误的主键索引，此时需通过FORCE INDEX强制指定目标索引，同时优化时间范围写法，避免数据漏查。

SELECT id FROM big_data_table
FORCE INDEX (idx_create_time_id)
WHERE create_time >= '2025-12-01 00:00:00'
  AND create_time < '2025-12-02 00:00:00'
ORDER BY id ASC
LIMIT 200;

关键优化细节说明：

• 用>= + <替代BETWEEN：避免漏查毫秒级数据（如2025-12-01 23:59:59.999），确保数据查询的完整性。

• 显式指定索引：跳过优化器的错误成本评估，强制走idx_create_time_id联合索引，确保范围扫描生效。

3.3 第三步：长期最优方案——游标分页

当数据量达到千万级，LIMIT OFFSET的性能会随偏移量增大呈指数级下降（偏移量越大，扫描的无效数据越多）。因此，长期优化需改用游标分页，彻底解决大偏移量性能问题。

-- 第一页（初始游标，last_id为上一页最后一条数据的ID，首次查询设为0）
SELECT id FROM big_data_table
WHERE create_time >= '2025-12-01 00:00:00'
  AND create_time < '2025-12-02 00:00:00'
  AND id > 0
ORDER BY id ASC
LIMIT 200;

-- 下一页（用上一页查询结果的最后一条ID作为游标，示例last_id=10000）
SELECT id FROM big_data_table
WHERE create_time >= '2025-12-01 00:00:00'
  AND create_time < '2025-12-02 00:00:00'
  AND id > 10000
ORDER BY id ASC
LIMIT 200;

游标分页的核心逻辑：用id > last_id替代OFFSET，让查询始终只扫描当前页所需的数据，走范围扫描，性能不随分页页码增大而衰减，适配千万级数据的高频分页场景。

四、效果验证

通过上述三步优化，查询性能得到质的提升，优化前后的执行计划与性能对比如下表所示：

五、生产环境落地注意事项

千万级表的索引操作和查询优化需兼顾业务可用性，落地时需注意以下4点：

1. 索引创建时效：7000万数据量创建联合索引，SSD环境下约30-60分钟，HDD环境需2-4小时，务必在业务低峰期（如凌晨）执行，避免影响业务读写。

2. 无锁DDL方案：核心业务表不可直接用ALTER TABLE创建索引（会短暂阻塞写操作），建议使用pt-online-schema-change工具，实现无锁创建索引。

3. 统计信息更新：索引创建后，需执行ANALYZE TABLE big_data_table更新表统计信息，确保MySQL优化器获取最新数据分布，避免再次出现索引选择偏差。

4. 磁盘空间预留：索引会占用额外存储空间，7000万条数据的(create_time, id)联合索引约占5-10G，需预留至少1.5倍于索引数据量的磁盘空间，防止磁盘满溢。

六、总结与通用优化思路

6.1 核心总结

千万级数据分页查询优化的核心是减少无效数据扫描和最大化利用索引，关键两点：一是设计适配查询场景的联合覆盖索引，二是用游标分页替代LIMIT OFFSET，彻底解决大偏移量性能问题。

6.2 通用优化思路

所有千万级MySQL分页场景，均可遵循“索引适配→查询重构→执行验证”的三步原则：

1. 索引适配：根据WHERE筛选字段和ORDER BY排序字段，设计“筛选字段+排序字段”的联合覆盖索引，兼顾筛选、排序、查询需求。

2. 查询重构：避免大偏移量的LIMIT OFFSET，改用游标分页；优化查询语法，避免隐式转换、数据漏查等问题。

3. 执行验证：通过EXPLAIN持续监控执行计划，确认索引使用正确、执行类型为range，及时发现并解决优化器误判问题。

本质上，千万级MySQL查询优化的核心是“让SQL走最优索引、扫描最少数据”，只要围绕这一核心，就能从根源上提升查询性能。