1. 业务场景描述
在某电商平台，对商品订单数据进行统计分析时，后台报表接口响应时间经常超过5秒，严重影响业务体验。进一步定位发现，涉及千万级别的order和order_item表，多表JOIN和聚合查询导致MySQL查询性能瓶颈。为了保证统计接口的实时性与可用性，需要对慢查询进行系统优化。
关键痛点：
2. 技术选型过程
为了解决上述问题，我们评估了以下几种方案：
方案A：在主库打开慢查询日志+使用EXPLAIN手动优化
方案B：使用MySQL Proxy/中间件做SQL路由及分片
方案C：引入Elasticsearch做离线统计
方案D（最终选型）：主库+备库读写分离 + 组合索引优化 + SQL重写 + 分区分表方案
选型理由：
3. 实现方案详解
3.1 开启慢查询日志与收集数据
在my.cnf中开启慢查询日志，并设置合理阈值（例如2秒）：
[mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 log_queries_not_using_indexes = ON
重启后，让MySQL开始记录慢查询。
3.2 使用pt-query-digest分析日志
借助Percona Toolkit：
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
报告中会列出最耗时、最频繁的SQL以及全表扫描等信息。
3.3 EXPLAIN分析瓶颈SQL
以典型慢查询为例：
SELECT oi.product_id, SUM(oi.quantity) AS total_sold FROM order_item oi JOIN `order` o ON oi.order_id = o.id WHERE o.status = 'COMPLETED' AND o.created_at BETWEEN '2023-01-01' AND '2023-06-30' GROUP BY oi.product_id;
执行EXPLAIN：
+—-+————-+——-+————+——+—————+——+———+———————-+——-+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+———————-+——-+———-+————-+
| 1 | SIMPLE | o | NULL | ALL | idx_status | NULL | NULL | NULL |2000000| 10.00 | Using where |
| 1 | SIMPLE | oi | NULL | ref | idx_order_id | idx_order_id | 4 | test.o.id | 500000| 100.00 | Using index |
+—-+————-+——-+————+——+—————+——+———+———————-+——-+———-+————-+
可以看到订单表o全表扫描，需要优化索引。
3.4 添加组合索引
针对order(status, created_at)添加组合索引：
ALTER TABLE `order` ADD INDEX idx_status_created_at (status, created_at);
再次执行EXPLAIN：
| type: range, key: idx_status_created_at, rows: 50000, Extra: Using where; Using index
大幅减少扫描行数。
3.5 SQL重写与分区
分区表：
ALTER TABLE `order` PARTITION BY RANGE ( YEAR(created_at) ) ( PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION pMax VALUES LESS THAN MAXVALUE );
重写SQL使分区裁剪生效：
… WHERE created_at >= ‘2023-01-01’ AND created_at < ‘2023-07-01’ …
保证时间范围在单个或少数分区。
3.6 读写分离
使用MySQL Proxy或中间件（如Atlas、MyCAT）将读请求路由到从库，减轻主库压力。
JS配置示例（Sequelize+XORM）：
const sequelize = new Sequelize('db', 'user', 'pass', {
dialect: 'mysql',
replication: {
read: [{ host: 'slave1', username: 'user', password: 'pass' }],
write: { host: 'master', username: 'user', password: 'pass' }
}
});
4. 踩过的坑与解决方案
坑1：索引列顺序错误导致无效索引。
解决：严格按照WHERE和GROUP BY字段顺序设计组合索引。
坑2：分区表改造在线迁移复杂。
解决：采用pt-online-schema-change工具在线拆分分区、添加索引。
坑3：读写分离一致性问题。
解决：针对关键业务使用session.pin或读写同连接，确保读到最新数据。
坑4：过度使用IN子查询引起临时表。
解决：改写为JOIN或EXISTS，或使用窗口函数（MySQL 8.0+）。
5. 总结与最佳实践
通过以上实战方法，可以将统计接口响应时间从5秒优化至500ms以内。在实际项目中，建议结合自身业务特点，灵活运用上述手段，持续监控并优化数据库性能。
到此这篇关于MySQL性能优化之慢查询优化实战指南的文章就介绍到这了,
