1. 數(shù)據(jù)庫性能排查的黃金五步法當線上數(shù)據(jù)庫出現(xiàn)性能問題時很多DBA會陷入手忙腳亂的狀態(tài)。根據(jù)我多年處理生產(chǎn)環(huán)境數(shù)據(jù)庫性能問題的經(jīng)驗建議按照以下五個關鍵檢查點進行系統(tǒng)性排查。這套方法在MySQL、Oracle等主流關系型數(shù)據(jù)庫中普遍適用能快速定位80%以上的性能瓶頸。重要提示性能排查一定要有方法論避免無頭蒼蠅式的檢查。以下順序是根據(jù)問題出現(xiàn)概率和排查效率優(yōu)化的結果。1.1 第一步檢查慢查詢?nèi)罩韭樵內(nèi)罩臼菙?shù)據(jù)庫性能問題的第一現(xiàn)場證據(jù)。以MySQL為例通過以下配置開啟慢查詢監(jiān)控-- 查看當前慢查詢配置 SHOW VARIABLES LIKE slow_query%; SHOW VARIABLES LIKE long_query_time; -- 臨時設置慢查詢閾值(單位秒) SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log ON;關鍵分析要點重點關注執(zhí)行時間超過閾值的TOP 10查詢檢查出現(xiàn)頻率高的重復查詢模式注意沒有使用索引的查詢rows_examined遠大于rows_sent典型問題特征# Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 2 Rows_examined: 500000 SELECT * FROM orders WHERE status pending AND create_time 2023-01-01;這個查詢掃描了50萬行卻只返回2條數(shù)據(jù)明顯存在索引缺失問題。1.2 第二步EXPLAIN分析執(zhí)行計劃對發(fā)現(xiàn)的慢SQL必須使用EXPLAIN進行執(zhí)行計劃分析EXPLAIN SELECT * FROM users WHERE username LIKE john% AND age 25;需要重點關注的字段字段正常值異常值問題原因typeconst/ref/rangeALL全表掃描key索引名NULL未使用索引rows小數(shù)大數(shù)掃描行數(shù)過多ExtraUsing indexUsing filesort需要優(yōu)化排序常見問題處理出現(xiàn)Using temporary查詢需要優(yōu)化臨時表使用Using filesort需要添加合適的索引優(yōu)化排序Select tables optimized away這是理想狀態(tài)1.3 第三步索引有效性檢查索引是數(shù)據(jù)庫性能的核心。檢查索引問題需要多維度驗證索引缺失檢查-- 查找WHERE條件中常用但未索引的列 SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_db; -- 查找高選擇性的未索引列 SELECT column_name, count(*) as cnt FROM table_name GROUP BY column_name ORDER BY cnt DESC LIMIT 10;索引冗余檢查-- 查找重復或冗余索引 SELECT * FROM sys.schema_redundant_indexes;索引使用統(tǒng)計-- 查看索引使用頻率 SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db;索引優(yōu)化經(jīng)驗法則為高頻查詢條件創(chuàng)建復合索引遵循最左前綴原則設計索引避免在索引列上使用函數(shù)區(qū)分度低的列不適合單獨建索引1.4 第四步系統(tǒng)資源監(jiān)控當SQL本身沒問題時需要檢查系統(tǒng)資源狀況數(shù)據(jù)庫連接數(shù)SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;緩沖池使用率-- InnoDB緩沖池命中率 SELECT (1 - (SELECT variable_value FROM performance_schema.global_status WHERE variable_name Innodb_buffer_pool_reads) / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name Innodb_buffer_pool_read_requests)) * 100 AS buffer_pool_hit_ratio;鎖等待情況-- 查看當前鎖等待 SELECT * FROM sys.innodb_lock_waits; -- 長事務檢查 SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60;關鍵閾值參考連接數(shù)使用率 70% 需要預警緩沖池命中率 95% 需要優(yōu)化鎖等待時間 500ms 需要關注1.5 第五步硬件I/O性能檢查最后需要排除硬件層面的瓶頸磁盤I/O延遲# Linux下檢查磁盤延遲 iostat -dx 1關注await列正常應10msSWAP使用情況free -h vmstat 1swap使用率0說明內(nèi)存不足網(wǎng)絡延遲ping -c 5 database_host traceroute database_host數(shù)據(jù)庫網(wǎng)絡延遲應1ms2. 典型性能問題處理實錄2.1 案例一索引失效導致查詢變慢問題現(xiàn)象 用戶報告訂單查詢接口響應時間從200ms突增到5s排查過程從慢日志發(fā)現(xiàn)大量類似查詢SELECT * FROM orders WHERE user_id 123 AND status completed ORDER BY create_time DESC LIMIT 10;EXPLAIN顯示全表掃描type: ALL key: NULL rows: 500000 Extra: Using filesort檢查現(xiàn)有索引SHOW INDEX FROM orders; -- 發(fā)現(xiàn)只有單獨的user_id索引和status索引解決方案 創(chuàng)建復合索引ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);效果驗證 執(zhí)行計劃變?yōu)閠ype: ref key: idx_user_status_time rows: 15 Extra: Backward index scan查詢時間恢復至50ms左右2.2 案例二連接池耗盡導致服務不可用問題現(xiàn)象 應用頻繁報Too many connections錯誤排查過程檢查連接數(shù)SHOW STATUS LIKE Threads_connected; -- 顯示400/400查看連接來源SELECT user, host, db, command, time FROM information_schema.processlist;發(fā)現(xiàn)大量sleep狀態(tài)的連接| app_user | 10.0.0.% | orders_db | Sleep | 500 |問題原因 應用未正確關閉數(shù)據(jù)庫連接連接池配置過大導致耗盡解決方案優(yōu)化應用連接管理設置連接超時SET GLOBAL wait_timeout 60; SET GLOBAL interactive_timeout 60;使用連接池中間件3. 性能優(yōu)化工具箱3.1 必備監(jiān)控命令命令用途關鍵指標SHOW ENGINE INNODB STATUSInnoDB狀態(tài)鎖等待、死鎖SHOW PROCESSLIST當前會話長事務、阻塞操作SHOW GLOBAL STATUS全局狀態(tài)QPS、TPS、緩存命中率SHOW GLOBAL VARIABLES系統(tǒng)變量配置參數(shù)檢查3.2 常用性能分析工具pt-query-digest# 分析慢查詢?nèi)罩?pt-query-digest /var/log/mysql/mysql-slow.logsys schema-- 查看未使用索引 SELECT * FROM sys.schema_unused_indexes; -- 查看冗余索引 SELECT * FROM sys.schema_redundant_indexes;Percona Toolkitpt-index-usage索引使用分析pt-visual-explain可視化執(zhí)行計劃4. 預防性維護建議4.1 日常監(jiān)控項關鍵指標監(jiān)控QPS/TPS波動慢查詢數(shù)量變化連接數(shù)使用率緩沖池命中率定期健康檢查-- 每周執(zhí)行一次 ANALYZE TABLE important_table; OPTIMIZE TABLE fragmented_table;4.2 容量規(guī)劃要點磁盤空間監(jiān)控數(shù)據(jù)文件增長趨勢日志文件輪轉情況性能基準測試業(yè)務高峰期前進行壓力測試比較版本升級前后的性能差異我在實際運維中發(fā)現(xiàn)很多性能問題都是日積月累的小問題爆發(fā)的。建議建立定期檢查機制在問題影響用戶前就發(fā)現(xiàn)并解決。對于核心業(yè)務表最好在開發(fā)階段就進行索引設計和SQL評審這比事后優(yōu)化要高效得多。