入職這些年我發(fā)現(xiàn)自己干得最多的活不是寫業(yè)務(wù)代碼也不是搭架構(gòu)而是跟一堆數(shù)據(jù)庫常用腳本打交道。無論是幫業(yè)務(wù)部門查一條慢SQL還是半夜被叫起來看主從延遲手里沒幾個(gè)趁手的腳本真就寸步難行。今天這篇不整虛的把我實(shí)際工作中反復(fù)在用、踩過坑之后修正過的腳本整理出來按場景分類講清楚每個(gè)腳本為什么這么寫、參數(shù)怎么調(diào)、遇到什么問題怎么改都一并交代。適合剛接觸數(shù)據(jù)庫運(yùn)維的開發(fā)者也適合想把手頭腳本整理得更規(guī)范的DBA。1. 信息查詢與元數(shù)據(jù)腳本先把家底盤清楚1.1 三分鐘摸清實(shí)例整體狀況接手一臺新的數(shù)據(jù)庫服務(wù)器第一件事不是急著優(yōu)化而是先搞清楚這臺實(shí)例到底什么配置、什么負(fù)載、什么水位。我習(xí)慣先跑一套體檢三連-- 查看實(shí)例版本、運(yùn)行時(shí)長、字符集等基礎(chǔ)信息 SELECT VERSION() AS version, NOW() AS current_time, hostname AS host_name, port AS port, character_set_server AS charset, collation_server AS collation; -- 查看關(guān)鍵運(yùn)行狀態(tài)值重點(diǎn)看連接數(shù)和慢查詢 SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Slow_queries; SHOW GLOBAL STATUS LIKE Uptime; -- 查看InnoDB buffer pool命中率相關(guān)指標(biāo) SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;這幾個(gè)腳本看著簡單但信息密度很高。版本和端口就不說了Threads_connected和Threads_running這兩個(gè)值一定要配合看前者代表當(dāng)前建立的連接數(shù)后者代表真正在執(zhí)行SQL的線程數(shù)。如果連接數(shù)飆到幾百但Threads_running一直不高說明大部分連接是閑置連接問題多半出在應(yīng)用側(cè)連接池配置上而不是數(shù)據(jù)庫本身。InnoDB buffer pool命中率有個(gè)經(jīng)驗(yàn)公式1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)這個(gè)值常年低于99%說明內(nèi)存配置或查詢模式有問題光靠加內(nèi)存未必管用可能得從SQL層面找原因。1.2 快速定位大表與膨脹空間剛接手一個(gè)庫業(yè)務(wù)方問我哪些表最大、哪些表膨脹得厲害我從來不看information_schema因?yàn)槟莻€(gè)統(tǒng)計(jì)信息在數(shù)據(jù)量大的時(shí)候本身就不準(zhǔn)掃描也慢。直接用系統(tǒng)表查快得多-- 查看指定庫中各表的行數(shù)、數(shù)據(jù)大小、索引大小MySQL 8.0 SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema your_db ORDER BY total_mb DESC LIMIT 20;這里有個(gè)細(xì)節(jié)必須提醒table_rows是估算值InnoDB引擎下誤差可能很大尤其頻繁增刪的表。真要精確行數(shù)還是得SELECT COUNT(*)但大表別在生產(chǎn)上直接跑低峰期再操作。另外我還會加一個(gè)查詢看哪些表的數(shù)據(jù)量和索引量比例失調(diào)。曾遇到一張表數(shù)據(jù)才200MB索引卻占了1.5GB查下來是建了一堆冗余的聯(lián)合索引刪掉三個(gè)索引后空間直接釋放寫入性能也提升了。索引不是越多越好這個(gè)后面專門講。1.3 找出從未被使用的索引索引是有維護(hù)成本的每次寫入都要更新索引結(jié)構(gòu)。很多系統(tǒng)上線久了當(dāng)初拍腦袋建的索引根本沒人用白白拖累寫入性能。查索引使用情況MySQL有現(xiàn)成的統(tǒng)計(jì)表-- 查看索引使用統(tǒng)計(jì)需開啟performance_schemaMySQL 5.7 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL ORDER BY COUNT_READ ASC LIMIT 20;COUNT_READ長期為0的索引基本就是死索引。但刪之前務(wù)必確認(rèn)兩點(diǎn)第一查詢計(jì)劃里是否真的沒用到有些統(tǒng)計(jì)值低但偶爾高峰用到的索引不能動第二有沒有唯一性約束依賴這個(gè)索引。我習(xí)慣把候選索引先標(biāo)記為INVISIBLEMySQL 8.0支持觀察一周確認(rèn)沒有報(bào)錯(cuò)再真正DROP這個(gè)操作可以說非常穩(wěn)妥。2. 備份與恢復(fù)腳本平時(shí)用不上出事保命2.1 邏輯備份腳本的正確姿勢說到備份很多人第一反應(yīng)就是mysqldump一把梭。但我見過太多因?yàn)閰?shù)沒加對導(dǎo)致備份恢復(fù)失敗的案例。我最常用的一套邏輯備份腳本長這樣#!/bin/bash # 按庫備份保留最近7天帶時(shí)間戳 BACKUP_DIR/data/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_LISTdb1 db2 db3 for DB in $DB_LIST; do mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ -u$DB_USER -p$DB_PASS $DB \ | gzip $BACKUP_DIR/${DB}_${DATE}.sql.gz done # 清理7天前的備份 find $BACKUP_DIR -name *.sql.gz -mtime 7 -delete這幾個(gè)參數(shù)每個(gè)都有講究--single-transaction不加這個(gè)InnoDB表備份時(shí)會對全表加鎖線上業(yè)務(wù)直接卡住。加上后基于MVCC一致性快照不鎖表。--routines --triggers --events不加這三個(gè)存儲過程、觸發(fā)器、事件調(diào)度器全部丟失。很多人恢復(fù)后發(fā)現(xiàn)跑批任務(wù)不見了就是栽在這。--set-gtid-purgedOFFGTID模式下的老坑。不關(guān)掉這個(gè)問題備份文件里帶著GTID信息恢復(fù)到新實(shí)例時(shí)會因?yàn)槭聞?wù)ID沖突直接報(bào)錯(cuò)。注意--single-transaction對MyISAM表無效MyISAM依舊會鎖表。如果庫里還有MyISAM表建議要么遷移到InnoDB要么備份安排在業(yè)務(wù)低谷期執(zhí)行。2.2 更穩(wěn)的物理備份xtrabackupmysqldump在數(shù)據(jù)量超過幾十GB時(shí)就不太合適了備份慢、恢復(fù)更慢而且邏輯備份恢復(fù)時(shí)重放SQL耗時(shí)很長。這時(shí)候我直接用物理備份工具做全量增量備份#!/bin/bash # 全量物理備份腳本 BACKUP_BASE/data/backup/xtra DATE$(date %Y%m%d) FULL_DIR$BACKUP_BASE/full_$DATE xtrabackup \ --backup \ --target-dir$FULL_DIR \ --userbackup_user \ --passwordyour_password \ --parallel4 \ --compress # 備份完成后再做一步prepare保證備份集可用于恢復(fù) xtrabackup --prepare --target-dir$FULL_DIR很多人備份完直接拷走恢復(fù)的時(shí)候才發(fā)現(xiàn)xtrabackup --prepare這步?jīng)]做數(shù)據(jù)文件處于不一致狀態(tài)根本起不來。--prepare的原理類似InnoDB崩潰恢復(fù)把redo log回放到數(shù)據(jù)文件里這步必須執(zhí)行且prepare后的備份集不能再增量備份。物理備份的恢復(fù)動作也比邏輯備份簡單直接把備份集解壓拷回?cái)?shù)據(jù)目錄改好屬主權(quán)限啟動實(shí)例即可。我實(shí)測過100GB的數(shù)據(jù)xtrabackup備份加恢復(fù)全程不到半小時(shí)mysqldump可能要折騰兩個(gè)小時(shí)以上。2.3 備份不等于高枕無憂恢復(fù)演練腳本這是我最想強(qiáng)調(diào)的一點(diǎn)不做恢復(fù)演練的備份等于沒有備份。遇到過不止一次平時(shí)備份任務(wù)天天跑真到恢復(fù)那天發(fā)現(xiàn)備份文件損壞、參數(shù)不兼容、磁盤空間不夠全抓瞎。我現(xiàn)在的習(xí)慣是每月至少做一次完整的恢復(fù)演練腳本固定如下#!/bin/bash # 恢復(fù)到臨時(shí)實(shí)例并校驗(yàn)數(shù)據(jù)完整性 RESTORE_DIR/data/restore_test PORT3307 DATA_DIR$RESTORE_DIR/data # 1. 解壓最新全量備份 LATEST_BACKUP$(ls -t /data/backup/mysql/*.sql.gz | head -1) gunzip -c $LATEST_BACKUP $RESTORE_DIR/restore.sql # 2. 直接初始化一個(gè)新實(shí)例 mysqld --no-defaults \ --datadir$DATA_DIR \ --port$PORT \ --socket$RESTORE_DIR/mysql.sock \ --initialize-insecure # 3. 導(dǎo)入數(shù)據(jù) mysql --socket$RESTORE_DIR/mysql.sock -uroot $RESTORE_DIR/restore.sql # 4. 關(guān)鍵表行數(shù)校驗(yàn) mysql --socket$RESTORE_DIR/mysql.sock -uroot -e SELECT COUNT(*) FROM your_db.key_table;恢復(fù)演練不只是驗(yàn)證備份文件可用還能順便驗(yàn)證備份腳本里的參數(shù)變化是否兼容當(dāng)前版本。比如之前從MySQL 5.7升級到8.0后老的備份恢復(fù)腳本就因?yàn)閟ql_mode差異跑掛過演練第一個(gè)發(fā)現(xiàn)問題。3. 巡檢與監(jiān)控腳本日常保健勝過臨時(shí)搶救3.1 連接與線程狀態(tài)實(shí)時(shí)巡檢數(shù)據(jù)庫出問題十有八九先反應(yīng)在連接和線程上。我寫了一個(gè)一鍵巡檢腳本每分鐘跑一次把異常情況直接打到日志里-- 檢查當(dāng)前連接數(shù)是否超過閾值這里是200 SHOW STATUS WHERE variable_name Threads_connected; -- 查看當(dāng)前所有非Sleep狀態(tài)的SQL SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC; -- 查看處于鎖等待的會話 SELECT * FROM information_schema.innodb_trx WHERE trx_state LOCK WAIT;information_schema.processlist這個(gè)表是排查問題的第一現(xiàn)場。看到time特別長的Query別急著KILL先看state字段如果是Sending data可能真的是大查詢?nèi)绻荳aiting for table metadata lock多半是DDL和DML打架了如果是Waiting for handler commit可能是磁盤刷盤太慢。有一次線上業(yè)務(wù)卡死我用這個(gè)腳本查出來一個(gè)time2400的ALTER TABLE在等元數(shù)據(jù)鎖后面堵了一串查詢。找到根源后把那個(gè)一直沒提交的長事務(wù)KILL掉業(yè)務(wù)立刻恢復(fù)。沒有這個(gè)巡檢思路光看連接數(shù)飚高是定位不到根因的。3.2 慢查詢與錯(cuò)誤日志分析腳本慢查詢?nèi)罩疽欢ㄒ_但開了不分析等于白開。我習(xí)慣把慢查詢?nèi)罩竞湾e(cuò)誤日志都統(tǒng)一到一個(gè)目錄然后用腳本做每日分析#!/bin/bash # 每日慢查詢統(tǒng)計(jì)輸出Top10耗時(shí)SQL SLOW_LOG/var/log/mysql/slow-query.log TODAY$(date %Y%m%d) # 統(tǒng)計(jì)當(dāng)天慢查詢總數(shù) TOTAL$(grep -c Time: $SLOW_LOG || true) echo $TODAY slow query total: $TOTAL # 提取慢查詢中的SQL文本統(tǒng)計(jì)出現(xiàn)頻率 grep -A2 Query_time $SLOW_LOG | \ grep ^SELECT\|^UPDATE\|^DELETE\|^INSERT | \ sort | uniq -c | sort -rn | head -20這個(gè)腳本的思路是先看量再看質(zhì)。慢查詢多不一定是壞事要看是否集中在少數(shù)幾條SQL上。如果每天都有一批SQL穩(wěn)定出現(xiàn)在慢查詢列表且執(zhí)行計(jì)劃沒變說明SQL本身有問題需要從索引和改寫兩個(gè)方向處理。有個(gè)經(jīng)驗(yàn)分享慢查詢?nèi)罩纠锏腞ows_examined遠(yuǎn)比Query_time重要。Rows_examined幾十萬但只返回幾行說明掃描了大量數(shù)據(jù)索引沒走對或者索引選擇性差。優(yōu)化這類SQL收益大且確定。3.3 磁盤與表空間水位監(jiān)控?cái)?shù)據(jù)庫的磁盤滿了是真的會寫不進(jìn)去任何數(shù)據(jù)的而且一旦滿盤恢復(fù)起來極其痛苦。所以磁盤監(jiān)控我寧可多寫幾個(gè)腳本也不愿意半夜被叫起來。#!/bin/bash # 磁盤與關(guān)鍵目錄監(jiān)控超過80%告警 THRESHOLD80 ALERT_FILE/data/monitor/disk_alert.log df -h | grep -E ^/dev/(sd|vd) | while read line; do USAGE$(echo $line | awk {print $5} | sed s/%//) MOUNT_POINT$(echo $line | awk {print $6}) if [ $USAGE -ge $THRESHOLD ]; then echo $(date %Y-%m-%d %H:%M:%S) WARN: $MOUNT_POINT usage $USAGE% $ALERT_FILE fi done # InnoDB表空間大小分布Top10 mysql -u monitor_user -p$PASSWORD -e SELECT table_schema, SUM(data_length index_length) / 1024 / 1024 / 1024 AS total_gb FROM information_schema.tables GROUP BY table_schema ORDER BY total_gb DESC; $ALERT_FILE告警閾值80%是我個(gè)人的習(xí)慣不一定適合所有人。如果業(yè)務(wù)有大量臨時(shí)排序或日志寫入建議把閾值調(diào)到70%給臨時(shí)文件留出緩沖。數(shù)據(jù)目錄和日志目錄最好分開監(jiān)控binlog目錄漲速快一天幾個(gè)GB很正常單獨(dú)看才不會忽略。另外提醒一個(gè)容易忽略的點(diǎn)監(jiān)控腳本本身也要做清理監(jiān)控日志文件無限增長會把磁盤反過來寫滿。logrotate配一下或者用find ... -mtime 30 -delete做輪轉(zhuǎn)。4. 性能優(yōu)化輔助腳本找到瓶頸、對癥下藥4.1 直擊性能瓶頸的SQL定位線上系統(tǒng)變慢最忌諱的就是憑感覺調(diào)參數(shù)。我的做法是先把壓在庫上的SQL揪出來分析執(zhí)行計(jì)劃再決定怎么改。下面這個(gè)腳本是我最常用的SQL分析三板斧-- 1. 開啟執(zhí)行計(jì)劃分析 EXPLAIN ANALYZE SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status PAID AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE是MySQL 8.0才有的它不只是給出執(zhí)行計(jì)劃還會真實(shí)執(zhí)行SQL并返回每一步的耗時(shí)和掃描行數(shù)。對于一條慢SQL先用它看瓶頸在哪一步是索引掃描行數(shù)太多還是排序耗時(shí)太長還是回表次數(shù)太多。我遇到過一條SQL加了個(gè)索引之后從2秒提升到50毫秒以為是索引生效了但用EXPLAIN ANALYZE一查發(fā)現(xiàn)真正的問題是ORDER BY create_time DESC觸發(fā)了filesort。后來把索引改成(user_id, status, create_time)讓索引天然有序徹底消除filesort才真正解決問題。沒有EXPLAIN ANALYZE我只能看到加索引變快了看不到完整的優(yōu)化鏈路。4.2 大表碎片整理腳本InnoDB表在頻繁更新和刪除后會產(chǎn)生大量碎片。表現(xiàn)就是表的數(shù)據(jù)量沒怎么漲但物理文件越來越大全表掃描越來越慢。整理碎片的方法很直接-- 查看表碎片率 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb, ROUND(data_free / data_length * 100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema your_db AND data_free 100 * 1024 * 1024 -- 只列出碎片超過100MB的表 ORDER BY fragment_pct DESC LIMIT 20;對于碎片率高的表執(zhí)行ALTER TABLE table_name ENGINEInnoDB即可重建表、回收碎片空間。但這里有幾個(gè)必須注意的坑這個(gè)操作會鎖表盡管MySQL 5.6是Online DDL但數(shù)據(jù)量巨大的表仍可能引發(fā)長時(shí)間元數(shù)據(jù)鎖務(wù)必安排在維護(hù)窗口。整理碎片前先看磁盤剩余空間重建表需要約等于原表大小的額外空間。MySQL 8.0之后這個(gè)動作可以用ALTER TABLE ... FORCE代替效果更明確。有個(gè)取巧的辦法如果表上有定期歸檔任務(wù)歸檔后碎片自然產(chǎn)生建議把碎片整理加入每月的自動任務(wù)比一次性整理大表風(fēng)險(xiǎn)低得多。4.3 關(guān)鍵參數(shù)調(diào)優(yōu)自查腳本網(wǎng)上到處是MySQL優(yōu)化必須改這10個(gè)參數(shù)的文章但盲目調(diào)參翻車的案例我見得太多了。參數(shù)調(diào)優(yōu)最靠譜的依據(jù)是自己的監(jiān)控?cái)?shù)據(jù)我寫了一個(gè)自查腳本輸出當(dāng)前實(shí)際運(yùn)行情況對應(yīng)的參數(shù)建議-- 查看緩存命中率的真實(shí)數(shù)據(jù) SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; SHOW GLOBAL STATUS LIKE Innodb_row_lock_current_waits; SHOW GLOBAL STATUS LIKE Innodb_row_lock_time; -- 查看當(dāng)前實(shí)際生效的參數(shù)值 SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE transaction_isolation;這里我最想展開講的是innodb_flush_log_at_trx_commit。這個(gè)參數(shù)只有三個(gè)取值0、1、2很多優(yōu)化文章建議改成0或2來提升性能但代價(jià)是事務(wù)提交時(shí)可能丟失最多1秒的日志這在金融、訂單等場景是絕對不可接受的。我的原則是核心業(yè)務(wù)必須保持1非核心報(bào)表庫可以考慮20絕不推薦。調(diào)參之前先問自己三個(gè)問題改這個(gè)參數(shù)要解決的問題是什么改完如何驗(yàn)證確實(shí)解決了回滾方案是什么答不上來任何一個(gè)就不要動。5. 常見問題與排查技巧實(shí)錄5.1 連接數(shù)打滿排查現(xiàn)象Threads_connected達(dá)到max_connections上限新連接直接報(bào)Too many connections。排查順序先看Threads_running如果這個(gè)值不高說明大量連接是Sleep狀態(tài)應(yīng)用連接池沒釋放。查information_schema.processlist看Sleep連接來自哪些host定位是哪個(gè)應(yīng)用。看應(yīng)用連接池配置maxPoolSize是不是設(shè)置得過大、idleTimeout是不是沒生效。如果是突發(fā)流量導(dǎo)致臨時(shí)調(diào)大max_connections能救急但根治要看應(yīng)用側(cè)連接復(fù)用。我以前遇到過一個(gè)案例某服務(wù)每執(zhí)行一次查詢就新建連接用完不歸還連接池形同虛設(shè)。排查完發(fā)現(xiàn)是應(yīng)用框架的配置項(xiàng)被誤改了maximumPoolSize寫成了1000而數(shù)據(jù)庫max_connections才500必然打滿。腳本只是定位工具根因分析還得靠對業(yè)務(wù)側(cè)的理解。5.2 鎖等待問題處理現(xiàn)象業(yè)務(wù)報(bào)錯(cuò)里出現(xiàn)Lock wait timeout exceeded或者Deadlock found。處理思路-- 查看當(dāng)前所有事務(wù) SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 查看鎖等待關(guān)系 SELECT waiting_trx_id, waiting_thread, waiting_query, blocking_trx_id, blocking_thread, blocking_query FROM sys.innodb_lock_waits;sys.innodb_lock_waits是MySQL 5.7及以上版本提供的視圖直接列出誰在等鎖、誰持有鎖。優(yōu)先處理持有鎖的那個(gè)長事務(wù)如果業(yè)務(wù)上允許直接把它KILL掉。但KILL是治標(biāo)根源通常在兩條事務(wù)沒及時(shí)提交應(yīng)用層忘記commit、或者事務(wù)范圍過大一個(gè)事務(wù)里做了太多查詢和計(jì)算。死鎖的話MySQL會自動回滾其中一個(gè)事務(wù)應(yīng)用側(cè)要有重試機(jī)制。這個(gè)事情別依賴數(shù)據(jù)庫幫你想辦法應(yīng)用層做重試才是正解。5.3 主從延遲排查主從延遲是另一種常見的數(shù)據(jù)庫沒事但業(yè)務(wù)很慢的場景。查主從狀態(tài)用下面的腳本SHOW SLAVE STATUS\G -- 關(guān)鍵字段 -- Seconds_Behind_Master: 從庫落后主庫的秒數(shù) -- Slave_IO_Running: IO線程是否正常必須是Yes -- Slave_SQL_Running: SQL線程是否正常必須是Yes -- Last_IO_Error / Last_SQL_Error: 有值必查這是報(bào)錯(cuò)現(xiàn)場Seconds_Behind_Master為0不代表沒延遲它只是估算值主庫大事務(wù)執(zhí)行期間這個(gè)值可能顯示0因?yàn)閺膸旄緵]追上主庫當(dāng)前的binlog位置。更可靠的判斷標(biāo)準(zhǔn)是對比主從的Master_Log_File/Read_Master_Log_Pos與從庫執(zhí)行到的Relay_Master_Log_File/Exec_Master_Log_Pos。主從延遲最常見的原因按優(yōu)先級排列大事務(wù)一條SQL更新幾百萬行、從庫硬件性能弱、從庫上跑著分析查詢搶資源、DDL在從庫被打斷。解決方向也很明確把大事務(wù)拆小、控制從庫上的分析負(fù)載、保障主從硬件規(guī)格一致。5.4 快速定位性能拐點(diǎn)最后一個(gè)腳本也是我調(diào)優(yōu)時(shí)候最重要的參考依據(jù)通過performance_schema拿到真實(shí)的等待事件分布。-- 等待事件Top10MySQL 5.7 SELECT EVENT_NAME, COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS wait_seconds FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;看到wait_seconds最大的等待事件基本就鎖定了瓶頸所在。io/sql/file類等待大說明磁盤I/O扛不住wait/synch/mutex類等待大說明并發(fā)爭用嚴(yán)重可能需要拆分熱點(diǎn)或升級配置idle類占比高說明數(shù)據(jù)庫很閑問題大概率在應(yīng)用側(cè)。這個(gè)腳本的價(jià)值在于把感覺慢變成數(shù)據(jù)說話。我優(yōu)化完某個(gè)參數(shù)或SQL之后都會跑一次這個(gè)查詢做前后對比用數(shù)據(jù)確認(rèn)優(yōu)化是否真的有效而不是感覺似乎快了一點(diǎn)。6. 數(shù)據(jù)訂正與批量操作腳本小心駛得萬年船6.1 大批量數(shù)據(jù)更新的安全寫法日常工作中總有改數(shù)據(jù)的需求比如訂正一條狀態(tài)、批量覆蓋一個(gè)字段。這類操作看著簡單翻車概率卻極高尤其是幾百萬行的大表直接UPDATE鎖表能把整個(gè)業(yè)務(wù)拖死。我現(xiàn)場更的每條大語句都遵循同一個(gè)模式-- 分批更新每批1000條循環(huán)執(zhí)行 DELIMITER $$ CREATE PROCEDURE batch_update() BEGIN DECLARE v_count INT DEFAULT 1; WHILE v_count 0 DO UPDATE orders SET status ARCHIVED WHERE status OLD AND id IN ( SELECT id FROM ( SELECT id FROM orders WHERE status OLD LIMIT 1000 ) tmp ); SET v_count ROW_COUNT(); -- 每批之間歇一下給其他事務(wù)留機(jī)會 DO SLEEP(0.1); END WHILE; END$$ DELIMITER ; CALL batch_update();外層套一層SELECT id FROM (...)子查詢是因?yàn)镸ySQL不允許在UPDATE的子查詢里直接查同一張表。SLEEP(0.1)是為了讓出部分鎖資源避免長事務(wù)囤積大量行鎖。這種方式雖然慢但安全度完全不同實(shí)測200萬行也就跑十幾分鐘比起鎖死核心表讓整個(gè)業(yè)務(wù)癱瘓這點(diǎn)時(shí)間完全值得。6.2 誤更新后的快速恢復(fù)方案批量更新的另一面是萬一條件寫錯(cuò)了怎么辦。我給自己留的后手是定期維護(hù)一份關(guān)鍵表的變更前快照-- 大操作前先備份目標(biāo)表到備份表 CREATE TABLE orders_backup_20240101 AS SELECT * FROM orders; -- 如果操作出錯(cuò)用備份表恢復(fù) -- 先刪掉錯(cuò)誤數(shù)據(jù)再按主鍵從備份表恢復(fù) DELETE FROM orders; INSERT INTO orders SELECT * FROM orders_backup_20240101;這個(gè)方案雖然簡單粗暴但在關(guān)鍵時(shí)刻能救命。注意幾點(diǎn)備份表本身也占空間大表要確認(rèn)磁盤夠用恢復(fù)是物理復(fù)制業(yè)務(wù)側(cè)如果在這期間有新寫入會丟失這部分?jǐn)?shù)據(jù)所以誤操作發(fā)生后第一時(shí)間要停止業(yè)務(wù)寫入再執(zhí)行恢復(fù)。其實(shí)最理想的方案是開啟binlog并用binlog2sql這類工具做行級回滾但這類工具的部署和學(xué)習(xí)成本較高。如果團(tuán)隊(duì)剛起步備份表方案先頂上,后續(xù)再逐步完善。6.3 主鍵沖突與唯一鍵沖突處理批量導(dǎo)入數(shù)據(jù)時(shí)最容易碰到的就是主鍵或唯一鍵沖突。常見場景從A庫導(dǎo)入B庫、重新跑批任務(wù)、數(shù)據(jù)回刷。我通常用INSERT ... ON DUPLICATE KEY UPDATE來平滑處理INSERT INTO user_profile (user_id, nickname, level, update_time) VALUES (10001, 張三, 5, NOW()) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), level VALUES(level), update_time NOW();這個(gè)寫法的含義是有沖突就更新沒沖突就插入。用起來要格外小心它會覆蓋目標(biāo)記錄的全部字段如果只想更新部分字段就只列出那幾列。還有一個(gè)容易被忽視的點(diǎn)這個(gè)語句對唯一鍵沖突同樣生效不一定非主鍵不可。如果是純導(dǎo)入場景不想覆蓋原有數(shù)據(jù)那就用INSERT IGNORE遇到?jīng)_突直接跳過。兩種寫法差異很大先想清楚業(yè)務(wù)意圖再選我見過因?yàn)橛缅e(cuò)導(dǎo)致數(shù)據(jù)被覆蓋寫壞的教訓(xùn)很深刻。7. 腳本管理的工程化實(shí)踐腳本寫得再好沒有一套管理規(guī)范時(shí)間一長就變成一團(tuán)亂麻。我自己是從下面幾件事里受益最多的分享出來。版本管理。數(shù)據(jù)庫腳本也是代碼必須進(jìn)版本庫。有些公司把SQL腳本扔在共享文件夾里版本靠文件名標(biāo)注v2_final_最終版這種管理方式遲早出事。我的習(xí)慣是每個(gè)數(shù)據(jù)庫變更腳本按日期和序號命名比如20240115_01_add_index_orders.sql提交到統(tǒng)一的代碼倉庫和代碼一起評審、一起發(fā)布。命名規(guī)范。腳本命名建議包含三個(gè)要素操作對象、操作類型、日期。db_backup_20240115.sh比backup.sh好在哪一年后你翻出來不用打開文件就知道這個(gè)腳本是干什么的、什么時(shí)候?qū)懙?、針對哪個(gè)庫的。敏感信息處理。腳本里直接寫數(shù)據(jù)庫密碼是大忌。我現(xiàn)在的基本做法是腳本從環(huán)境變量讀取密碼環(huán)境變量由部署平臺注入代碼庫里只保留模板。雖然多了一道工序但密碼泄露的風(fēng)險(xiǎn)大幅降低。執(zhí)行前評審。涉及生產(chǎn)庫的DDL和不常見DML無論多緊急都要先走評審。哪怕是DBA自己執(zhí)行的腳本也先在測試環(huán)境完整跑一遍確認(rèn)影響行數(shù)在預(yù)期范圍內(nèi)再上生產(chǎn)。這個(gè)習(xí)慣幫我擋掉了至少三次足以釀成大事故的操作。我個(gè)人還有一個(gè)小習(xí)慣每個(gè)核心腳本開頭都用注釋寫清楚這腳本什么時(shí)候用、怎么用、踩過哪些坑。這個(gè)習(xí)慣救過我很多次。半年后你面對一個(gè)陌生或者陌生的腳本注釋里那三行字比任何文檔都管用。有一次團(tuán)隊(duì)里一位同事半夜調(diào)備份恢復(fù)正是靠著腳本頭部注釋里的注意該腳本依賴/data/backup/.env文件存在快速定位了問題少走了不少彎路。做數(shù)據(jù)庫運(yùn)維這些年最大的體會是所謂常用腳本不是一份到處都能抄的標(biāo)準(zhǔn)答案而是你在一次次故障處理、一次次數(shù)據(jù)恢復(fù)、一次次深夜值班中沉淀下來的自己的答案。今天的這些腳本是我實(shí)踐中的一部分積累希望能給你的腳本庫帶來一些靈感。你在實(shí)際工作里積累了什么好用的腳本或者踩過什么獨(dú)特的坑歡迎一起聊聊也許你踩過的那個(gè)坑正好是我下一個(gè)腳本要解決的問題。