引言
在日常的數據庫運維工作中，我們經常需要對 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快速定位問題。建議定期運行這些診斷命令，特別是在性能問題出現時，可以為我們提供寶貴的第一手資料。
記住，數據庫性能優化是一個持續的過程，需要結合這些診斷信息和實際業務場景來制定優化策略。
)
