MySQL性能问题的排查入口
数据库慢不是模糊的感觉,是有数据可查的客观指标。性能调优的第一步是开启慢查询日志,找出真正的瓶颈语句,而不是凭猜测优化。
这篇文章从慢查询定位、执行计划分析、索引优化和参数调优四个层面,给出MySQL 8.0生产环境的性能调优方案。
慢查询日志配置与分析
开启慢查询日志:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 未用索引也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
生产环境long_query_time建议先设2秒,收集一周数据后逐步下调到0.5秒。直接设0.1秒会产生大量日志影响IO。
用mysqldumpslow分析慢日志Top N:
# 按查询时间排序,取前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log
更强大的工具是pt-query-digest,输出详细的查询分析报告:
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
报告中关注三个指标:Query_time的95百分位、Rows_examined(扫描行数)、Rows_sent(返回行数)。Rows_examined/Rows_sent比值超过100的查询基本都有优化空间。
EXPLAIN执行计划逐字段解读
拿到慢SQL后,用EXPLAIN分析执行计划:
EXPLAIN SELECT o.order_id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 20;
关键字段解读:
– type:访问类型,从好到差:system > const > eq_ref > ref > range > index > ALL。出现ALL说明走了全表扫描,必须加索引
– key:实际使用的索引,NULL表示没走索引
– rows:预估扫描行数,越小越好
– Extra:额外信息。出现Using filesort(额外排序)或Using temporary(临时表)需要优化
常见问题与对策:
1. type=ALL + rows很大 → 加WHERE条件对应的索引
2. Extra=Using filesort → ORDER BY字段需要索引覆盖
3. key=NULL但possible_keys有值 → 索引选择错误,用FORCE INDEX或优化索引
4. rows远大于实际返回行数 → 索引区分度不够,需要优化索引列顺序
索引优化:从创建到维护的完整方案
索引创建原则:
1. WHERE条件列建索引,高选择性列在前
2. ORDER BY和GROUP BY列建索引,避免filesort
3. 多列查询建联合索引,遵循最左前缀原则
4. 覆盖索引(Covering Index)减少回表
-- 联合索引:status选择性低放后面,created_at选择性高放前面
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
-- 覆盖索引:查询只需要order_id和amount,索引直接返回不走回表
ALTER TABLE orders ADD INDEX idx_cover_paid (status, created_at, order_id, amount);
索引失效的五种常见场景:
-- 1. 对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-30'; -- 索引失效
-- 改写为范围查询
SELECT * FROM orders WHERE created_at >= '2026-07-30'
AND created_at < '2026-07-31'; -- 索引有效
-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 12345; -- order_no是varchar,索引失效
SELECT * FROM orders WHERE order_no = '12345'; -- 索引有效
-- 3. LIKE以通配符开头
SELECT * FROM products WHERE name LIKE '%手机'; -- 索引失效
SELECT * FROM products WHERE name LIKE '华为%'; -- 索引有效
-- 4. OR条件中有无索引列混合
SELECT * FROM orders WHERE status = 'PAID' OR remark = '急单'; -- 索引失效
-- 改写为UNION
SELECT * FROM orders WHERE status = 'PAID'
UNION
SELECT * FROM orders WHERE remark = '急单';
-- 5. 联合索引跳过前缀列
-- INDEX(a, b, c)
SELECT * FROM t WHERE b = 1 AND c = 2; -- 索引失效,跳过了a
SELECT * FROM t WHERE a = 1 AND c = 2; -- 只走a的索引,c无法利用
索引维护:
索引不是越多越好。每个索引增加写操作的开销(INSERT/UPDATE/DELETE都需要更新索引)。生产环境建议:
– 单表索引数量控制在5-8个
– 定期检查冗余索引和未使用索引
-- 查找未使用的索引(MySQL 8.0 sys库)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';
-- 查找冗余索引
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';
MySQL参数调优
参数调优基于硬件配置和业务负载。以下是一个64GB内存、SSD盘服务器的推荐配置:
[mysqld]
# InnoDB缓冲池,设为物理内存的60%-75%
innodb_buffer_pool_size = 48G
# 缓冲池实例数,每个实例不低于1GB
innodb_buffer_pool_instances = 8
# 日志配置
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 # 生产必须为1,保证事务安全
# 并发配置
innodb_thread_concurrency = 0 # 0表示不限制,由InnoDB自行管理
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 连接配置
max_connections = 500
wait_timeout = 600
interactive_timeout = 600
# 查询缓存(MySQL 8.0已移除,不配置)
# 临时表配置
tmp_table_size = 256M
max_heap_table_size = 256M
# 排序缓冲
sort_buffer_size = 4M
join_buffer_size = 4M
innodb_flush_log_at_trx_commit参数说明:
– 设为1:每次事务提交都刷盘,最安全但最慢
– 设为2:每次提交写入OS缓存,每秒刷盘,性能提升明显
– 主库必须设1,从库可设2
大表优化:分区与归档策略
单表数据超过5000万行后,查询性能开始明显下降。分区和归档是两种处理思路:
按时间范围分区:
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
PARTITION p202601 VALUES LESS THAN (202602),
PARTITION p202602 VALUES LESS THAN (202603),
PARTITION p202603 VALUES LESS THAN (202604),
PARTITION p202604 VALUES LESS THAN (202605),
PARTITION p202605 VALUES LESS THAN (202606),
PARTITION p202606 VALUES LESS THAN (202607),
PARTITION p202607 VALUES LESS THAN (202608),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
冷数据归档:
超过6个月的订单数据迁移到归档库,主库只保留近6个月热数据。用pt-archiver工具在线迁移:
pt-archiver --source h=main-db,D=production,t=orders --dest h=archive-db,D=archive,t=orders --where "created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)" --limit 1000 --commit-each --progress 1000
MySQL性能调优不是一次性的工作,而是一个持续监测-分析-优化的循环。慢查询日志是起点,EXPLAIN是分析工具,索引和参数配置是手段。每次优化后用基准测试验证效果,避免改了反而更慢。
原创文章,作者:小编,如若转载,请注明出处:https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-cong-man-cha-xun-zhen/