引言
在日常的数据库运维工作中，我们经常需要对 MySQL 数据库进行诊断和性能分析。本文将介绍一套全面的 MySQL 诊断脚本，适用于 MySQL 8.0（兼容 8.0.15 及以上版本），涵盖事务锁分析、性能瓶颈定位、配置检查、连接状态监控和慢查询分析等多个方面。
一、事务与锁相关分析
1. InnoDB 引擎状态
SHOW ENGINE INNODB STATUS;这条命令是诊断 InnoDB 问题的首要工具，它会返回包括事务、锁、死锁等在内的详细信息。输出内容分为多个部分：
事务状态
锁等待情况
死锁信息（如果有）
缓冲池统计
I/O 统计等
2. 锁等待关系查询
SELECTt_wait.processlist_id AS waiting_thread,r.sql_text AS waiting_query,t_block.processlist_id AS blocking_thread,b.sql_text AS blocking_query
FROMperformance_schema.data_lock_waits lw
JOINperformance_schema.data_locks req_lock ON lw.REQUESTING_ENGINE_LOCK_ID = req_lock.engine_lock_id
JOINperformance_schema.data_locks blk_lock ON lw.BLOCKING_ENGINE_LOCK_ID = blk_lock.engine_lock_id
JOINperformance_schema.threads t_wait ON req_lock.thread_id = t_wait.thread_id
JOINperformance_schema.threads t_block ON lw.BLOCKING_THREAD_ID = t_block.thread_id
JOINperformance_schema.events_statements_current r ON req_lock.thread_id = r.thread_id
JOINperformance_schema.events_statements_current b ON t_block.thread_id = b.thread_id;这个查询可以清晰地展示当前数据库中的锁等待关系，包括：
等待线程ID
被阻塞的查询
阻塞线程ID
造成阻塞的查询
二、性能相关查询
1. 耗时最长的SQL
SELECT digest_text AS query,count_star AS exec_count,sum_timer_wait / 1000000000 AS total_latency_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;这个查询可以帮助我们找出执行耗时最长的SQL语句，是性能优化的首要目标。
2. 扫描行数较多的SQL
SELECT digest_text AS query,count_star AS exec_count,sum_rows_examined,sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_rows_examined > 100000
ORDER BY sum_rows_examined DESC
LIMIT 20;这个查询可以找出那些扫描了大量行但返回较少数据的SQL，这类SQL通常可以通过添加合适的索引来优化。
三、配置检查
MySQL的配置对性能有重大影响，以下是一些关键配置项的检查：
InnoDB 缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';连接与并发相关
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'thread_cache_size';临时表与排序缓冲区
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'sort_buffer_size';四、连接情况统计
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Aborted_connects';
SHOW STATUS LIKE 'Connections';这些统计信息可以帮助我们了解数据库的连接情况：
Threads_created过高可能表示thread_cache_size不足Aborted_connects表示异常连接尝试次数
五、慢查询分析
SHOW STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';慢查询是性能问题的重要指标，这些命令可以帮助我们：
查看慢查询总数
检查慢查询日志是否开启
查看慢查询时间阈值
检查是否记录未使用索引的查询
结语
这套 MySQL 诊断脚本涵盖了数据库性能分析的多个关键方面，可以帮助DBA快速定位问题。建议定期运行这些诊断命令，特别是在性能问题出现时，可以为我们提供宝贵的第一手资料。
记住，数据库性能优化是一个持续的过程，需要结合这些诊断信息和实际业务场景来制定优化策略。
)
