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