1. 项目背景与挑战去年接手公司订单系统优化时我遇到了一个棘手的问题——单表5亿数据的查询性能已经跌至谷底。核心接口的响应时间从最初的200ms逐渐恶化到8-12秒高峰期经常触发超时告警。这个MySQL订单表承载了公司6年来的所有交易记录字段多达40余个最要命的是还存在大量JOIN关联查询。关键指标预警当单表数据量超过5000万时B树索引的层级会显著增加随机IO次数呈指数级上升。我们的5亿数据表即便有索引查询时也需要至少5次磁盘寻道。2. 性能瓶颈定位2.1 慢查询分析通过pt-query-digest工具分析一周的慢日志发现三类典型问题范围查询未走索引WHERE create_time BETWEEN 2020-01-01 AND 2023-12-31跨表JOIN消耗70%时间订单表与用户表、商品表的3表关联分页查询性能衰减LIMIT 100000, 20需要遍历前10万条记录2.2 存储引擎诊断使用SHOW ENGINE INNODB STATUS发现Buffer pool命中率仅82%建议95%索引长度达8.7GB但key_buffer_size仅配置4GB存在大量行锁等待平均等待时间达120ms3. 优化方案设计与实施3.1 索引优化策略重新设计复合索引时遵循左前缀原则-- 旧索引低效 ALTER TABLE orders ADD INDEX idx_user (user_id); -- 新索引覆盖查询 ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, create_time);实测效果用户订单查询从3.2s → 80ms索引体积减少37%从8.7GB→5.5GB3.2 冷热数据分离按时间维度拆分-- 热表最近1年数据 CREATE TABLE orders_hot LIKE orders; -- 冷表历史数据 CREATE TABLE orders_cold LIKE orders;配合定时任务迁移数据热表体积控制在8000万条以内。3.3 分库分表实战采用ShardingSphere实现水平分片# 分片规则配置 spring: shardingsphere: sharding: tables: orders: actual-data-nodes: ds$0..7.orders_$0..15 table-strategy: inline: sharding-column: user_id algorithm-expression: orders_$-{user_id % 16} database-strategy: inline: sharding-column: company_id algorithm-expression: ds$-{company_id % 8}分片后效果单表数据量降至3000万条平均查询响应时间稳定在150ms以内写入TPS提升6倍4. 深度优化技巧4.1 索引跳跃扫描对于WHERE statuspaid AND create_time2023-01-01这类查询如果status的区分度低只有5种状态可以强制索引跳跃SELECT /* SKIP_SCAN(orders idx_status_time) */ * FROM orders WHERE statuspaid AND create_time2023-01-01;4.2 异步并行查询对大结果集查询采用多线程获取// 使用CompletableFuture并行查询 ListCompletableFutureListOrder futures shards.stream() .map(shard - CompletableFuture.supplyAsync(() - queryShard(shard))) .toList(); ListOrder result futures.stream() .flatMap(f - f.join().stream()) .collect(Collectors.toList());5. 避坑指南分片键选择避免用UUID等随机值会导致跨分片查询。我们最终选用user_idcompany_id组合分布式事务对于支付订单这类强一致性场景采用Seata的AT模式UPDATE orders SET statuspaid WHERE id100 /* Seata AT */JOIN优化将关联查询拆分为单表查询内存拼接使用宽表预聚合常用字段禁止跨分片JOIN二级索引陷阱分库分表后普通二级索引失效需要改用全局索引表CREATE TABLE global_idx ( col_name VARCHAR(50), col_value VARCHAR(100), shard_key VARCHAR(50), PRIMARY KEY(col_name, col_value) );6. 监控体系建设部署PrometheusGrafana监控看板重点监控分片倾斜度最大/最小分片数据量比跨分片查询比例索引命中率慢查询TOP10配置报警规则示例- alert: ShardImbalance expr: max(shard_size_bytes) / min(shard_size_bytes) 1.5 for: 1h经过半年优化系统在双11期间平稳支撑了峰值QPS 12万/秒的流量平均响应时间保持在200ms以内。这次实战让我深刻体会到大数据量下的SQL优化70%靠架构设计30%靠参数调优。 
长河君 · 2026/11/3 23:18:29 · 阅读 1.2k MySQL海量数据性能优化实战：从慢查询到分库分表
1. 项目背景与挑战去年接手公司订单系统优化时我遇到了一个棘手的问题——单表5亿数据的查询性能已经跌至谷底。核心接口的响应时间从最初的200ms逐渐恶化到8-12秒高峰期经常触发超时告警。这个MySQL订单表承载了公司6年来的所有交易记录字段多达40余个最要命的是还存在大量JOIN关联查询。关键指标预警当单表数据量超过5000万时B树索引的层级会显著增加随机IO次数呈指数级上升。我们的5亿数据表即便有索引查询时也需要至少5次磁盘寻道。2. 性能瓶颈定位2.1 慢查询分析通过pt-query-digest工具分析一周的慢日志发现三类典型问题范围查询未走索引WHERE create_time BETWEEN 2020-01-01 AND 2023-12-31跨表JOIN消耗70%时间订单表与用户表、商品表的3表关联分页查询性能衰减LIMIT 100000, 20需要遍历前10万条记录2.2 存储引擎诊断使用SHOW ENGINE INNODB STATUS发现Buffer pool命中率仅82%建议95%索引长度达8.7GB但key_buffer_size仅配置4GB存在大量行锁等待平均等待时间达120ms3. 优化方案设计与实施3.1 索引优化策略重新设计复合索引时遵循左前缀原则-- 旧索引低效 ALTER TABLE orders ADD INDEX idx_user (user_id); -- 新索引覆盖查询 ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, create_time);实测效果用户订单查询从3.2s → 80ms索引体积减少37%从8.7GB→5.5GB3.2 冷热数据分离按时间维度拆分-- 热表最近1年数据 CREATE TABLE orders_hot LIKE orders; -- 冷表历史数据 CREATE TABLE orders_cold LIKE orders;配合定时任务迁移数据热表体积控制在8000万条以内。3.3 分库分表实战采用ShardingSphere实现水平分片# 分片规则配置 spring: shardingsphere: sharding: tables: orders: actual-data-nodes: ds$0..7.orders_$0..15 table-strategy: inline: sharding-column: user_id algorithm-expression: orders_$-{user_id % 16} database-strategy: inline: sharding-column: company_id algorithm-expression: ds$-{company_id % 8}分片后效果单表数据量降至3000万条平均查询响应时间稳定在150ms以内写入TPS提升6倍4. 深度优化技巧4.1 索引跳跃扫描对于WHERE statuspaid AND create_time2023-01-01这类查询如果status的区分度低只有5种状态可以强制索引跳跃SELECT /* SKIP_SCAN(orders idx_status_time) */ * FROM orders WHERE statuspaid AND create_time2023-01-01;4.2 异步并行查询对大结果集查询采用多线程获取// 使用CompletableFuture并行查询 ListCompletableFutureListOrder futures shards.stream() .map(shard - CompletableFuture.supplyAsync(() - queryShard(shard))) .toList(); ListOrder result futures.stream() .flatMap(f - f.join().stream()) .collect(Collectors.toList());5. 避坑指南分片键选择避免用UUID等随机值会导致跨分片查询。我们最终选用user_idcompany_id组合分布式事务对于支付订单这类强一致性场景采用Seata的AT模式UPDATE orders SET statuspaid WHERE id100 /* Seata AT */JOIN优化将关联查询拆分为单表查询内存拼接使用宽表预聚合常用字段禁止跨分片JOIN二级索引陷阱分库分表后普通二级索引失效需要改用全局索引表CREATE TABLE global_idx ( col_name VARCHAR(50), col_value VARCHAR(100), shard_key VARCHAR(50), PRIMARY KEY(col_name, col_value) );6. 监控体系建设部署PrometheusGrafana监控看板重点监控分片倾斜度最大/最小分片数据量比跨分片查询比例索引命中率慢查询TOP10配置报警规则示例- alert: ShardImbalance expr: max(shard_size_bytes) / min(shard_size_bytes) 1.5 for: 1h经过半年优化系统在双11期间平稳支撑了峰值QPS 12万/秒的流量平均响应时间保持在200ms以内。这次实战让我深刻体会到大数据量下的SQL优化70%靠架构设计30%靠参数调优。