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

关注

原创最新推荐文章于 2026-09-18 13:14:32 发布 968 阅读 AI 写同款· GEO 优化 ›

ShardingSphere-JDBC分库分表实战与性能优化分库分表是解决数据库水平扩展的核心技术，通过将数据分散到多个物理节点实现读写负载均衡。其实现原理主要依赖SQL解析、路由分发和结果归并三大引擎，其中分片算法设计直接影响查询性能。作为轻量级JDBC增强驱动，ShardingSphere-JDBC凭借对MyBatis/Hibernate等ORM框架的无缝兼容，成为处理千万级数据表的首选方案，特别适用于电商订单、物流跟踪等高并发场景。通过inline表达式配置分片键与算法，开发者可以快速实现水平分片，配合HikariCP连接池还能有效避免广播查询导致的连接耗尽问阅读详情

一、问题背景

在处理千万级核心业务表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的执行计划，我们发现了三个关键异常点，这也是导致查询超时的核心原因：

索引选择偏差：优化器未使用create_time字段的已有索引，反而选择了主键索引PRIMARY，背离了时间筛选的查询场景。
扫描范围过大：执行计划预估扫描行数达5万+，实际触发全索引扫描（遍历整个主键索引树），而非精准的范围扫描，无效扫描成本过高。
执行类型错误：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，让查询始终只扫描当前页所需的数据，走范围扫描，性能不随分页页码增大而衰减，适配千万级数据的高频分页场景。

四、效果验证

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

核心指标	优化前	优化后
执行类型（type）	`index`（全索引扫描）	`range`（范围扫描）
使用索引（key）	`PRIMARY`（主键索引）	`idx_create_time_id`（联合索引）
预估扫描行数（rows）	54,964	272,064（实际有效扫描行数大幅减少）
响应时间	7秒+（超时）	<10ms

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

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

索引创建时效：7000万数据量创建联合索引，SSD环境下约30-60分钟，HDD环境需2-4小时，务必在业务低峰期（如凌晨）执行，避免影响业务读写。
无锁DDL方案：核心业务表不可直接用ALTER TABLE创建索引（会短暂阻塞写操作），建议使用pt-online-schema-change工具，实现无锁创建索引。
统计信息更新：索引创建后，需执行ANALYZE TABLE big_data_table更新表统计信息，确保MySQL优化器获取最新数据分布，避免再次出现索引选择偏差。
磁盘空间预留：索引会占用额外存储空间，7000万条数据的(create_time, id)联合索引约占5-10G，需预留至少1.5倍于索引数据量的磁盘空间，防止磁盘满溢。