MySQL性能优化之慢查询优化实战指南

目录

• 1.php 业务场景描述
• 2. 技术选型过程
• 3. 实现方案详解
• 3.1 开启慢查询日志与收集数据
• 3.2 使用pt-query-digest分析日志
• 3.编程客栈3 EXPLAIN分析瓶颈SQL
• 3.4 添加组合索引
• 3.5 SQL重写与分区
• 3.6 读写分离
• 4. 踩过的坑与解决方案
• 5. 总结与最佳实践

1. 业务场景描述

在某电商平台，对商品订单数据进行统计分析时，后台报表接口响应时间经常超过5秒，严重影响业务体验。进一步定位发现，涉及千万级别的order和order_item表，多表JOIN和聚合查询导致mysql查询性能瓶颈。为了保证统计接口的实时性与可用性，需要对慢查询进行系统优化。

关键痛点：

• 表数据量大（订单表超过2000万行）
• 多表关联和复杂聚合（SUM、GROUP BY）
• 高并发读请求影响主库负载

2. 技术选型过程

为了解决上述问题，我们评估了以下几种方案：

方案A：在主库打开慢查询日志+使用EXPLAIN手动优化

方案B：使用MySQL Proxy/中间件做SQL路由及分片

方案C：引入Elasticsearch做离线统计

方案D（最终选型）：主库+备库读写分离 + 组合索引优化 + SQL重写 + 分区分表方案

选型理由：

• A方案可快速定位并优化单条SQL，但无法构建整体可扩展体系
• B方案需要中间件改造成本高，团队不具备足够维护经验
• C方案脱离MySQL生态，数据同步延迟高，无法满足实时性
• D方案在现有架构上扩展成本较低，可渐进式上线，兼顾实时性与可维护性

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 EXPLwww.devze.comAIN分析瓶颈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       |

+----+-------------+--javascript-----+------------+------+---------------+------+---------+----------------------+-------+----------+-------------+

|  1 | SIMPLE      | o     | NULL       | ALL  |js 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. 总结与最佳实践

• 常规优化步骤：慢日志→分析报告→EXPLAIN→补索引→SQL重写。
• 大表建议分区分表，结合分区裁剪减少扫描范围。
• 生产环境上线前使用pt-query-digest+EXPLAIN验证性能。
• 读写分离及缓存（如Redis）配合使用，可进一步提升读性能。
• 定期回顾慢日志：随着数据增长，不断迭代优化。

通过以上实战方法，可以将统计接口响应时间从5秒优化至500ms以内。在实际项目中，建议结合自身业务特点，灵活运用上述手段，持续监控并优化数据库性能。

到此这篇关于MySQL性能优化之慢查询优化实战指南的文章就介绍到这了,更多相关MySQL慢查询优化内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！