MySQL 8.0性能诊断：从慢查询日志到根因分析
MySQL性能问题的表象多种多样——接口超时、CPU飙高、磁盘IO打满——但根因往往落在三类问题上：低效SQL、索引缺失或失效、锁等待。诊断的起点不是猜测，而是数据。慢查询日志是MySQL性能调优的第一手数据源。
慢查询日志配置与分析
开启慢查询日志
-- 动态开启（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5; -- 超过500ms记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 未用索引也记录
SET GLOBAL min_examined_row_limit = 100; -- 扫描行少于100不记录
-- 持久化配置（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1使用mysqldumpslow做快速统计
# 按查询时间排序，取Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log
# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log使用pt-query-digest做深度分析
# 生成查询分析报告
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 指定时间段分析
pt-query-digest --since '2026-07-25 00:00:00' --until '2026-07-25 06:00:00' \
/var/log/mysql/slow.log
# 输出关键信息：
# - Rank：查询排名
# - Query ID：查询指纹
# - Response time：总响应时间及占比
# - Calls：执行次数
# - R/Call：平均每次响应时间
# - V/M：方差/均值比，越高说明性能越不稳定EXPLAIN执行计划深度解读
找到慢查询后，用EXPLAIN分析执行计划。MySQL 8.0推荐使用EXPLAIN FORMAT=TREE获得更直观的执行树：
EXPLAIN FORMAT=TREE
SELECT o.order_id, o.amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.create_time > '2026-07-01'
AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;关键字段判断逻辑
type列（从优到差）：
- system/const：单行查找，最优
- eq_ref：唯一索引关联，次优
- ref：非唯一索引查找
- range：索引范围扫描
- index：全索引扫描（比ALL好，但仍然慢）
- ALL：全表扫描，必须优化
Extra列关键信息：
- Using index：覆盖索引，不需要回表
- Using where：在存储引擎返回数据后做过滤
- Using filesort：需要额外排序（大结果集时极慢）
- Using temporary：创建临时表（GROUP BY无索引时常见）
索引优化实战：从理论到操作
索引选择性计算
索引的选择性 = 该列不同值数量 / 总行数。选择性越高，索引越有效：
-- 计算各列的选择性
SELECT
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
COUNT(DISTINCT customer_id) / COUNT(*) AS customer_selectivity,
COUNT(DISTINCT create_time) / COUNT(*) AS time_selectivity,
COUNT(*) AS total_rows
FROM orders;
-- 示例结果：
-- status_selectivity: 0.0003 (极低，只有几个状态值)
-- customer_selectivity: 0.12 (中等)
-- time_selectivity: 0.85 (高，几乎每条记录时间不同)复合索引的列顺序
复合索引遵循最左前缀原则。列顺序由以下因素决定：
- 等值条件列放在前面
- 范围条件列放在后面
- 排序列放在最后
-- 查询条件：status = 'PAID' AND create_time > '2026-07-01'
-- 排序：ORDER BY amount DESC
-- 错误索引：create_time在前
CREATE INDEX idx_time_status ON orders(create_time, status);
-- type=range，status过滤依赖Using where，无法利用索引
-- 正确索引：status等值在前，time范围在后，amount排序最后
CREATE INDEX idx_status_time_amount ON orders(status, create_time, amount);
-- type=range，Using index condition，排序可用索引函数索引（MySQL 8.0+）
-- 对JSON字段创建函数索引
CREATE INDEX idx_data_json_extract
ON orders((CAST(JSON_EXTRACT(data, '$.region') AS CHAR(20))));
-- 对日期列创建函数索引
CREATE INDEX idx_create_date
ON orders((DATE(create_time)));锁等待诊断与优化
锁等待监控
-- 查看当前锁等待
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query,
b.trx_started AS blocking_started
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
-- MySQL 8.0更简洁的写法
SELECT * FROM performance_schema.data_lock_waits\G死锁分析
-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G
-- 在LATEST DETECTED DEADLOCK段查看详情
-- 开启死锁完整日志记录
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 死锁信息会写入error log，便于事后分析常见锁优化策略
-- 1. 缩小事务粒度：大事务拆分
-- 反模式：一个事务中做太多操作
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁行
-- ... 执行大量业务逻辑 ...
UPDATE orders SET status = 'PAID' WHERE id = 100; -- 长时间持锁
COMMIT;
-- 正模式：按依赖关系拆分事务
-- 事务1：只做余额扣减
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 事务2：只做订单状态更新
BEGIN;
UPDATE orders SET status = 'PAID' WHERE id = 100;
COMMIT;
-- 2. 按固定顺序访问资源，避免死锁
-- 所有事务按id升序更新
UPDATE inventory SET stock = stock - 1 WHERE product_id = LEAST(pid1, pid2);
UPDATE inventory SET stock = stock - 1 WHERE product_id = GREATEST(pid1, pid2);数据库高可用架构：读写分离与MGR
MySQL Group Replication配置
# my.cnf - MGR单主模式配置
[mysqld]
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "10.0.0.1:33061"
group_replication_group_seeds = "10.0.0.1:33061,10.0.0.2:33061,10.0.0.3:33061"
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF
# 启动MGR
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password'
FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;ProxySQL读写分离
-- 配置后端MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (10, '10.0.0.1', 3306, 1); -- 写
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (20, '10.0.0.2', 3306, 1); -- 读
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (20, '10.0.0.3', 3306, 1); -- 读
-- 配置读写分离规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1); -- SELECT FOR UPDATE走写
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 20, 1); -- 普通SELECT走读
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;数据迁移实战：大表在线变更
# 使用gh-ost做在线DDL（无锁变更）
gh-ost \
--user="admin" --password="xxx" --host="10.0.0.1" \
--database="production" --table="orders" \
--alter="ADD COLUMN region VARCHAR(20) DEFAULT 'CN'" \
--allow-on-master \
--initial-rows-estimate=50000000 \
--chunk-size=5000 \
--max-load='Threads_running=100' \
--critical-load='Threads_running=500' \
--execute
# 关键参数说明：
# --chunk-size：每批处理行数，根据服务器负载动态调整
# --max-load：达到此负载暂停复制
# --critical-load：达到此负载中止操作MySQL性能调优是一个持续迭代的过程。从慢查询日志定位问题SQL，用EXPLAIN验证执行计划，通过索引优化和SQL改写降低资源消耗，再用读写分离和在线DDL应对高并发和变更需求。每次调优后都要用压测数据验证效果，避免”感觉快了”的假象。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-xing-neng-diao-you-shi-zhan-man-cha-xun-zhen-duan/
