優(yōu)實(shí)戰(zhàn)：從配置、索引到SQL與監(jiān)控)
MySQL 8.0 基礎(chǔ)優(yōu)化實(shí)戰(zhàn)指南從配置到性能調(diào)優(yōu)很多人以為裝好MySQL 8.0、把數(shù)據(jù)導(dǎo)進(jìn)去數(shù)據(jù)庫就算能用了。直到某天業(yè)務(wù)慢得像蝸牛爬開發(fā)跑過來喊了一句數(shù)據(jù)庫卡了你才發(fā)現(xiàn)默認(rèn)配置和真實(shí)負(fù)載之間隔著一整座山。這篇指南就是要把MySQL 8.0從配置文件、索引設(shè)計(jì)、SQL寫法、鎖與事務(wù)、監(jiān)控巡檢這五條線串起來講清楚優(yōu)化時(shí)先動(dòng)哪里、后動(dòng)哪里以及每個(gè)操作背后的原因。不管你是正在接手一個(gè)MySQL 8.0項(xiàng)目的開發(fā)者還是已經(jīng)踩過幾個(gè)坑但想系統(tǒng)梳理一遍的運(yùn)維同學(xué)按這個(gè)順序走一遍基本能把數(shù)據(jù)庫從跑得動(dòng)推到跑得快。我這些年處理過的MySQL性能問題里至少有一半以上不是機(jī)器不行而是配置沒跟業(yè)務(wù)對(duì)齊、索引沒建對(duì)、SQL寫法太隨性。所以這篇指南不給那種裝完點(diǎn)下一步的流水賬只講真正影響性能的關(guān)鍵動(dòng)作。1. 基礎(chǔ)配置優(yōu)化先別急著動(dòng)參數(shù)先搞懂這幾個(gè)關(guān)鍵項(xiàng)MySQL 8.0安裝完用的是一套非常保守的默認(rèn)配置目的只是保證它能跑起來、兼容性最好生產(chǎn)環(huán)境的壓力和這套配置完全不匹配。舉一個(gè)最常見的例子innodb_buffer_pool_size默認(rèn)只有128M假如你機(jī)器有32G內(nèi)存不把這個(gè)參數(shù)調(diào)大InnoDB的緩存池只夠裝十分之一不到的熱點(diǎn)數(shù)據(jù)剩下的請(qǐng)求全部落到磁盤I/O上。磁盤再快也遠(yuǎn)不如內(nèi)存快性能自然上不去。所以我調(diào)優(yōu)的第一件事永遠(yuǎn)是結(jié)合業(yè)務(wù)特點(diǎn)把基礎(chǔ)參數(shù)先立起來然后再談索引和SQL。順序反了的話你后面再努力也可能是在給錯(cuò)誤的配置環(huán)境打補(bǔ)丁。1.1 連接數(shù)別亂調(diào)大要點(diǎn)是算峰值并發(fā)max_connections默認(rèn)是151開發(fā)環(huán)境甚至小網(wǎng)站都?jí)蛴玫坏交顒?dòng)大促或定時(shí)任務(wù)并發(fā)場(chǎng)景就特別容易打滿。打滿之后的表現(xiàn)很典型應(yīng)用日志里全是Too many connections新請(qǐng)求進(jìn)不來老請(qǐng)求又堵著不結(jié)束整個(gè)鏈路雪崩。最簡單的確認(rèn)方式是執(zhí)行下面這條SQL看當(dāng)前連接數(shù)和配置上限的差距SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;調(diào)優(yōu)時(shí)你可能會(huì)想那直接把max_connections設(shè)成2000不就完了還真不行。每個(gè)連接都會(huì)占用線程和內(nèi)存資源連接數(shù)設(shè)得太大CPU會(huì)大量浪費(fèi)在線程切換上請(qǐng)求響應(yīng)反而更慢。一般做法是先估算業(yè)務(wù)峰值時(shí)的并發(fā)連接數(shù)然后在這個(gè)基礎(chǔ)上預(yù)留30%-40%的余量。比如你用壓力測(cè)試得出500并發(fā)已經(jīng)能扛住業(yè)務(wù)峰值那么max_connections設(shè)在800左右就差不多了。另外程序側(cè)連接池的設(shè)置也很關(guān)鍵。很多應(yīng)用框架默認(rèn)的連接池是20到50個(gè)連接一個(gè)實(shí)例不夠就多開幾個(gè)實(shí)例這比在數(shù)據(jù)庫端放開幾千個(gè)連接要健康得多。我實(shí)測(cè)過把同樣請(qǐng)求壓力從連接池200個(gè)連接改成數(shù)據(jù)庫2000個(gè)連接連接池方案下的平均延遲小了一個(gè)數(shù)量級(jí)。別把數(shù)據(jù)庫當(dāng)成無狀態(tài)的服務(wù)去猛連連接是個(gè)昂貴的資源。1.2 InnoDB緩沖池與日志文件性能地基innodb_buffer_pool_size是MySQL里最重要的性能參數(shù)沒有之一。InnoDB會(huì)把數(shù)據(jù)頁、索引頁、變更緩沖等都放在這個(gè)內(nèi)存區(qū)域里所有讀寫操作先跟這塊內(nèi)存打交道內(nèi)存不夠了才落盤。經(jīng)驗(yàn)上這個(gè)值可以設(shè)置到物理內(nèi)存的60%到75%。舉個(gè)例子32G內(nèi)存的機(jī)器可以給到20G左右64G內(nèi)存給到40G到48G。如果你的服務(wù)器上不只跑MySQL還要跑應(yīng)用進(jìn)程那得按實(shí)際可用內(nèi)存重新算不要把機(jī)器搞到開始用swap。MySQL 8.0支持動(dòng)態(tài)調(diào)整緩沖池大小方便先看效果再固化到配置文件SET GLOBAL innodb_buffer_pool_size 20 * 1024 * 1024 * 1024;但注意這個(gè)動(dòng)態(tài)設(shè)置在重啟后會(huì)丟失調(diào)整到滿意的值之后記得同步修改my.cnf。緩沖池有沒有起作用看命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;命中的比例越高說明內(nèi)存緩存越有效。再來看innodb_log_file_size。MySQL 8.0默認(rèn)是48M這個(gè)值對(duì)寫密集的業(yè)務(wù)來說實(shí)在太小了。redo log太小意味著日志切換特別頻繁每一次切換都要做一次checkpoint把臟頁刷到磁盤磁盤持續(xù)高負(fù)載整個(gè)數(shù)據(jù)庫的寫性能就會(huì)明顯下滑。在寫多讀少的業(yè)務(wù)里我習(xí)慣把innodb_log_file_size調(diào)整到512M甚至1G同時(shí)也要觀察磁盤空間和恢復(fù)時(shí)間日志太大崩潰恢復(fù)時(shí)花費(fèi)的時(shí)間也會(huì)更長。正常運(yùn)維周期內(nèi)1G這個(gè)檔位對(duì)很多中等規(guī)模業(yè)務(wù)都算合理。innodb_flush_log_at_trx_commit這個(gè)參數(shù)建議你有意識(shí)地選一次。默認(rèn)值1表示每次事務(wù)提交都把日志刷到磁盤最安全但每次提交都要等一次磁盤fsync性能開銷大改成2表示每次提交只寫入操作系統(tǒng)緩存每秒再刷一次盤性能明顯好但MySQL進(jìn)程崩潰時(shí)可能丟最多1秒的事務(wù)。如果是金融交易類業(yè)務(wù)老老實(shí)實(shí)用1大多數(shù)互聯(lián)網(wǎng)業(yè)務(wù)能接受極小概率的秒級(jí)丟失可以用2換性能。很多團(tuán)隊(duì)完全沒有考慮過這個(gè)參數(shù)其實(shí)它和你的可靠性要求是直接相關(guān)的。1.3 字符集、時(shí)區(qū)與幾個(gè)會(huì)話級(jí)參數(shù)的坑MySQL 8.0默認(rèn)字符集已經(jīng)是utf8mb4這比5.7時(shí)代默認(rèn)latin1強(qiáng)很多但建庫時(shí)還是要顯式確認(rèn)一遍。尤其要注意默認(rèn)的排序規(guī)則utf8mb4_0900_ai_ci是不區(qū)分大小寫的如果業(yè)務(wù)要求用戶名、訂單號(hào)查詢時(shí)必須區(qū)分大小寫這個(gè)默認(rèn)值會(huì)讓你查出來的結(jié)果比預(yù)期更多而且還會(huì)讓LIKE查詢帶上額外的排序規(guī)則轉(zhuǎn)換成本。建表時(shí)按實(shí)際需要指定COLLATE utf8mb4_bin或者utf8mb4_0900_as_cs比事后改表省心得多。時(shí)區(qū)這個(gè)坑也很隱蔽。MySQL 8.0的系統(tǒng)時(shí)區(qū)和應(yīng)用服務(wù)器的時(shí)區(qū)如果不一致你存進(jìn)去的DATETIME看著沒問題但用JDBC連接時(shí)如果服務(wù)端時(shí)區(qū)跟客戶端不一致轉(zhuǎn)換出來的時(shí)間完全對(duì)不上。排查問題到最后發(fā)現(xiàn)是時(shí)區(qū)偏移引起的既浪費(fèi)精力又容易誤傷業(yè)務(wù)邏輯。我習(xí)慣在連接串里顯式指定時(shí)區(qū)同時(shí)在MySQL側(cè)把全局時(shí)區(qū)設(shè)為業(yè)務(wù)統(tǒng)一時(shí)區(qū)兩邊對(duì)死。會(huì)話級(jí)參數(shù)里最容易被高估的是sort_buffer_size。它表示每個(gè)連接做排序操作時(shí)能使用的內(nèi)存看上去大一點(diǎn)排序更快但這是每個(gè)連接都會(huì)分配的如果全局設(shè)置了8M200個(gè)連接同時(shí)排序就是1.6G內(nèi)存內(nèi)存瞬間被打爆。正確做法是保持默認(rèn)或小幅調(diào)整然后用狀態(tài)變量Sort_merge_passes觀察實(shí)際排序過程中是否有大量落盤操作如果這個(gè)計(jì)數(shù)很大再針對(duì)性放大排序緩沖。同理還有join_buffer_size別聽網(wǎng)上那些調(diào)大排序緩沖提升性能的配方就亂改會(huì)話級(jí)參數(shù)影響范圍大要謹(jǐn)慎。提示調(diào)參數(shù)最忌諱一次性改十幾個(gè)再重啟觀察出了問題根本不知道是哪個(gè)參數(shù)造成的。一次只改一兩個(gè)每次改完做一輪對(duì)比壓測(cè)或者看一段時(shí)間的運(yùn)行曲線確認(rèn)有效再改下一批。2. 索引優(yōu)化慢查詢的根源大多在索引設(shè)計(jì)配置是地基但真正讓查詢變慢的常見原因還是索引沒建好。MySQL 8.0的優(yōu)化器能力比老版本強(qiáng)了不少但它再聰明也需要一個(gè)合理設(shè)計(jì)的索引結(jié)構(gòu)。很多人遇到慢查詢第一反應(yīng)是機(jī)器性能不行或者SQL寫得爛但EXPLAIN一看全表掃描、沒有可用索引、回表次數(shù)爆炸問題清清楚楚。這一章我會(huì)從最典型的索引失效場(chǎng)景講到執(zhí)行計(jì)劃怎么看最后說聯(lián)合索引的實(shí)用設(shè)計(jì)原則?？炊@三塊你排查慢SQL的思路會(huì)清晰很多。2.1 最常見的索引失效場(chǎng)景你大概率踩過幾個(gè)先看一個(gè)典型例子在created_at字段上建了索引查詢條件是WHERE DATE(created_at) 2025-01-01。對(duì)索引列使用函數(shù)以后優(yōu)化器沒法直接利用B樹的有序性做查找只能把索引列全部取出來算一遍函數(shù)再過濾索引就白建了。正確做法是改成范圍查詢WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00。隱式類型轉(zhuǎn)換也是重災(zāi)區(qū)。假如user_id字段是varchar類型但傳入的是數(shù)字WHERE user_id 123456MySQL會(huì)先把索引列轉(zhuǎn)成數(shù)字再比較同樣導(dǎo)致索引失效。判斷方法很簡單看看表結(jié)構(gòu)里這個(gè)字段到底是什么類型然后確保傳入值跟字段類型嚴(yán)格一致。如果程序里傳參類型不統(tǒng)一建議在SQL層做好類型轉(zhuǎn)換讓參數(shù)先變成字符串再進(jìn)條件。前置通配符的LIKE查詢也是很常見的一個(gè)問題。LIKE %keyword這種寫法優(yōu)化器沒有辦法利用B樹順序掃描的特性只能全索引掃描。業(yè)務(wù)上確實(shí)需要模糊查詢時(shí)我一般先評(píng)估數(shù)據(jù)量幾千行的小表全表掃也就幾毫秒不用太糾結(jié)但百萬行以上就得考慮全文索引或者改造業(yè)務(wù)設(shè)計(jì)讓查詢條件盡量變成前綴匹配。最后提一下OR條件。WHERE a 1 OR b 2如果只在a上建了索引b沒有索引優(yōu)化器很可能放棄索引直接全表掃描。MySQL 8.0雖然支持索引合并但沒有索引的列始終是短板。要么給b也建索引要么把OR改寫成UNION兩條獨(dú)立查詢實(shí)測(cè)下來改寫后的執(zhí)行計(jì)劃往往更穩(wěn)定。2.2 學(xué)會(huì)看EXPLAIN優(yōu)化器想什么別猜EXPLAIN是排查索引問題時(shí)需要最先學(xué)會(huì)看的工具但很多人在MySQL 8.0里還是只盯著一列看實(shí)際上有幾個(gè)列的信息量很大。type列反應(yīng)了訪問方式從好到差大致是systemconsteq_refrefrangeindexALL?？吹紸LL基本意味著全表掃描是優(yōu)化的頭號(hào)目標(biāo)index雖然也是全索引掃描但至少比表掃描好一點(diǎn)也可能是因?yàn)椴樵冃枰牧卸荚谒饕铩?shí)用的判斷標(biāo)準(zhǔn)是能用range及以上級(jí)別就合格出現(xiàn)ALL就要處理。rows列是優(yōu)化器預(yù)估需要掃描的行數(shù)雖不是真實(shí)值但能看到大概量級(jí)優(yōu)化前后這個(gè)數(shù)字的變化能反映出索引是否生效。Extra列里重點(diǎn)留意Using filesort和Using temporary。出現(xiàn)Using filesort說明排序沒有用到索引順序需要額外排序操作出現(xiàn)Using temporary說明查詢過程創(chuàng)建了臨時(shí)表常見于GROUP BY、DISTINCT或者復(fù)雜的子查詢場(chǎng)景數(shù)據(jù)量大時(shí)性能下降非常明顯。如果看到的是Using index那說明查詢只訪問索引就能拿到全部需要的數(shù)據(jù)這就是最理想的覆蓋索引狀態(tài)。MySQL 8.0.18版本開始有EXPLAIN ANALYZE可以直接輸出真實(shí)的執(zhí)行耗時(shí)、掃描行數(shù)和各步驟耗時(shí)占比。用法很簡單EXPLAIN ANALYZE SELECT ...;結(jié)果比傳統(tǒng)EXPLAIN更直觀連 實(shí)際執(zhí)行了幾次循環(huán)、每次耗時(shí)多少 都給了。我在排查慢SQL時(shí)會(huì)先跑EXPLAIN看結(jié)構(gòu)再跑EXPLAIN ANALYZE看真實(shí)瓶頸兩步配合基本能把問題定位清楚。2.3 聯(lián)合索引與覆蓋索引的組合拳聯(lián)合索引設(shè)計(jì)的最核心原則是最左前綴。比如在(status, category_id, created_at)上建了聯(lián)合索引查詢條件里必須有status才能用上這個(gè)索引只查category_id或者只查created_at是不會(huì)命中這個(gè)聯(lián)合索引的。所以設(shè)計(jì)聯(lián)合索引時(shí)要把等值查詢的列放前面范圍查詢的列放后面。這里有一個(gè)比較容易忽略的細(xì)節(jié)聯(lián)合索引遇到范圍查詢后面的列就失效了。比如WHERE status 1 AND category_id 10 AND created_at 2025-01-01在(status, category_id, created_at)索引上category_id一旦用了范圍條件created_at就沒法用索引繼續(xù)過濾了。所以正確做法是把范圍條件涉及的列盡量放到聯(lián)合索引的最后。如果幾個(gè)條件都是范圍那就只能取舍選擇區(qū)分度最高、過濾效果最好的列放索引里。覆蓋索引是另一個(gè)提升效率的大殺器。簡單說就是讓查詢需要的所有列都包含在某一個(gè)索引中查詢時(shí)不用回表取數(shù)據(jù)行。舉一個(gè)常見的優(yōu)化案例業(yè)務(wù)請(qǐng)求是SELECT id, name FROM user WHERE status 1如果只在status上建了普通索引那么MySQL查到所有status1的主鍵后還得回表讀取整行數(shù)據(jù)再取出id和name但如果建的是(status, id, name)聯(lián)合索引數(shù)據(jù)在索引里就能全部拿到省掉了回表這趟I/O。對(duì)高并發(fā)讀多寫少的場(chǎng)景覆蓋索引帶來的提升非常明顯。索引也不是越多越好。每多一個(gè)索引寫入時(shí)就要多維護(hù)一份B樹結(jié)構(gòu)更新頻繁的表索引太多反而拖累寫性能還占用額外磁盤空間。我的取舍習(xí)慣是每個(gè)表的核心查詢路徑最多設(shè)計(jì)兩到三個(gè)聯(lián)合索引優(yōu)先滿足最重要的業(yè)務(wù)查詢次要查詢?nèi)绻麛?shù)據(jù)量不大走全表掃描也不是不能接受?？偸悄盟锌赡艿牟樵儣l件來建索引最終會(huì)把表逼到寫一次要更新十個(gè)索引的境地。3. SQL優(yōu)化實(shí)戰(zhàn)先開慢查詢?nèi)罩驹倬珳?zhǔn)打擊配置和索引都檢查過了接下來要做的是把真正耗時(shí)的SQL找出來。這一步靠猜是猜不出來的必須依賴慢查詢?nèi)罩竞托阅苤笜?biāo)。很多團(tuán)隊(duì)的MySQL默認(rèn)沒開慢查詢?nèi)罩镜扔陲w行員不看儀表盤在盲飛。開了日志你就知道哪些SQL在消耗大量時(shí)間然后再逐條分析、改寫。3.1 慢查詢?nèi)罩镜恼_打開方式MySQL 8.0里可以用下面幾條命令把慢查詢?nèi)罩敬蜷_同時(shí)設(shè)置閾值時(shí)間。默認(rèn)閾值是10秒對(duì)絕大多數(shù)在線業(yè)務(wù)來說太寬松了很多5秒、3秒的SQL可能天天在跑你都不知道。我一般直接設(shè)到1秒或者0.5秒先跑一段時(shí)間把高頻慢SQL暴露出來再說。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;有一個(gè)非常實(shí)用但容易忽略的參數(shù)是log_queries_not_using_indexes它會(huì)把沒有正確使用索引的SQL也記進(jìn)慢查詢?nèi)罩炯词箞?zhí)行時(shí)間沒超過閾值。這對(duì)早期發(fā)現(xiàn)還沒變慢、但全表掃描量大的隱患很有幫助。不過生產(chǎn)環(huán)境不要長期開著這個(gè)選項(xiàng)因?yàn)楹芏嘈”聿樵儽緛砭筒恍枰饕_著會(huì)導(dǎo)致日志量爆炸。我的習(xí)慣是開展優(yōu)化工作前的1到2周開啟收集完問題SQL就關(guān)掉。拿到慢查詢?nèi)罩疽院笙劝磮?zhí)行次數(shù)和平均耗時(shí)排序找出高頻率慢SQL。這里要區(qū)分兩種問題一種是單個(gè)SQL執(zhí)行時(shí)間特別長比如好幾秒另一種是每個(gè)SQL執(zhí)行時(shí)間幾十毫秒但每秒被調(diào)用成百上千次。前者靠優(yōu)化單條SQL解決后者往往要上升到應(yīng)用層加緩存或者限流去治理單靠數(shù)據(jù)庫層面調(diào)優(yōu)很快會(huì)達(dá)到上限。3.2 深翻頁與子查詢的典型優(yōu)化套路分頁查詢?cè)跇I(yè)務(wù)里太常見了但深翻頁問題直到今天依然在大量項(xiàng)目里存在。LIMIT 100000, 20這種寫法數(shù)據(jù)庫不是只取20條而是掃描完前面的100000條再丟掉掃描成本高得離譜。我一個(gè)朋友接手過一個(gè)千萬級(jí)數(shù)據(jù)的列表頁翻到第100頁之后接口就超時(shí)典型的深翻頁問題。優(yōu)化方案之一是延遲關(guān)聯(lián)先通過覆蓋索引查出目標(biāo)主鍵再用主鍵回表取完整數(shù)據(jù)。改造前是SELECT * FROM orders ORDER BY id LIMIT 100000, 20改造后類似下面這樣SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;這個(gè)寫法的思路是先讓子查詢用主鍵索引快速掃描出這一頁需要的20個(gè)主鍵然后再用連接去取完整數(shù)據(jù)避免了整個(gè)大表層面的掃描和回表。如果業(yè)務(wù)上有更明確的排序條件比如按created_at排序可以考慮基于游標(biāo)的方式記錄上一頁最后一條的created_at和id下一頁用條件過濾來拿數(shù)據(jù)這種寫法在千萬級(jí)數(shù)據(jù)下可以做到翻頁成本恒定。子查詢方面MySQL 8.0已經(jīng)從5.7時(shí)代的大部分需要搞成JOIN進(jìn)化到會(huì)用物化或者半連接策略來優(yōu)化但這不是說子查詢可以隨便寫。我的建議是業(yè)務(wù)邏輯簡單、數(shù)據(jù)量小的場(chǎng)景子查詢和JOIN差別不大按可讀性選數(shù)據(jù)量一大比如外層幾萬條、內(nèi)層掃描全表就要實(shí)際壓測(cè)驗(yàn)證。一個(gè)常用的排查方式是EXPLAIN ANALYZE看執(zhí)行計(jì)劃里子查詢被物化了幾次、掃描了多少行如果出現(xiàn)逐行循環(huán)執(zhí)行那就要改寫。3.3 統(tǒng)計(jì)數(shù)據(jù)、直方圖與優(yōu)化器提示MySQL優(yōu)化器選擇執(zhí)行計(jì)劃時(shí)依賴表的統(tǒng)計(jì)信息如果統(tǒng)計(jì)信息嚴(yán)重過期優(yōu)化器可能選擇一個(gè)明明有可用索引但是代價(jià)估算錯(cuò)誤而放棄索引的劣質(zhì)計(jì)劃。這種情況有個(gè)很直觀的表現(xiàn)EXPLAIN里type是ALL但你明明剛剛才確認(rèn)索引是存在的。解決辦法是執(zhí)行ANALYZE TABLE 表名;刷新統(tǒng)計(jì)信息再重新看EXPLAIN。MySQL 8.0新增了直方圖功能對(duì)數(shù)據(jù)分布嚴(yán)重不均勻的列非常實(shí)用。比如status字段99%的行是0、1%的行是1統(tǒng)計(jì)信息里可能只記錄大概平均分布優(yōu)化器如果因此選擇全表掃描去查那個(gè)只占1%的status1性能就很虧。給這類列建立直方圖之后優(yōu)化器能看到更真實(shí)的數(shù)據(jù)分布做出更合理的判斷。創(chuàng)建直方圖可以用ANALYZE TABLE 表名 UPDATE HISTOGRAM ON status;維護(hù)成本不高可以針對(duì)高頻查詢涉及的列做。至于優(yōu)化器提示如USE INDEX和FORCE INDEX我的態(tài)度比較謹(jǐn)慎。它們?cè)谝恍┚o急上線、臨時(shí)救火的時(shí)候確實(shí)有效能強(qiáng)制讓執(zhí)行計(jì)劃走你想走的索引。但如果把這種提示固化在業(yè)務(wù)代碼里將來表結(jié)構(gòu)調(diào)整、索引重命名或者數(shù)據(jù)量變大強(qiáng)制提示反而會(huì)阻止優(yōu)化器選出更優(yōu)計(jì)劃。除非有明確證據(jù)表明優(yōu)化器在可預(yù)見的未來都會(huì)選錯(cuò)否則我會(huì)優(yōu)先讓優(yōu)化器自主決策同時(shí)把統(tǒng)計(jì)信息維護(hù)好。注意排查慢SQL別只盯著執(zhí)行時(shí)間一條還要看平均掃描行數(shù)和返回行數(shù)的比例。掃描100萬行返回10行說明過濾條件沒用好這才是索引設(shè)計(jì)應(yīng)該優(yōu)化的點(diǎn)。4. 鎖與事務(wù)并發(fā)高時(shí)最隱蔽的性能殺手索引和SQL優(yōu)化好了之后單條查詢本身的速度可能已經(jīng)很可觀了但只要并發(fā)一上來數(shù)據(jù)庫就可能卡在另一個(gè)地方鎖等待和事務(wù)沖突。MySQL 8.0的InnoDB默認(rèn)使用行級(jí)鎖但行級(jí)鎖并不是說就不會(huì)鎖沖突了。長事務(wù)、間隙鎖、寫寫沖突、死鎖任何一個(gè)處理不好都會(huì)讓整個(gè)系統(tǒng)的吞吐量直線下降。這一章的很多問題在日常壓測(cè)中不輕易暴露往往人要等到線上并發(fā)真的高了才遇到。所以提前把排查路徑和應(yīng)對(duì)手段整理出來能幫你少走很多彎路。4.1 鎖等待排查先找到誰鎖了誰最常見的報(bào)錯(cuò)是ERROR 1205: Lock wait timeout exceeded; try restarting transaction。遇到這個(gè)錯(cuò)誤很多人的第一反應(yīng)是我把鎖等待時(shí)間調(diào)大一點(diǎn)。這個(gè)思路只能暫時(shí)緩解癥狀因?yàn)槿绻i沒釋放你等再久也一樣超時(shí)還會(huì)讓大量請(qǐng)求堆積在鎖等待隊(duì)列里把連接池打滿。正確做法是先查清楚誰持有鎖、誰在等待。MySQL 8.0的performance_schema和相關(guān)視圖提供了豐富的信息你可以直接查sys.innodb_lock_waits視圖它類似于一張鎖沖突快照包含了阻塞事務(wù)、被阻塞事務(wù)和對(duì)應(yīng)的SQL內(nèi)容。示例查詢SELECT * FROM sys.innodb_lock_waits\G如果嫌這個(gè)視圖信息太細(xì)也可以查information_schema.innodb_trx看當(dāng)前所有事務(wù)的運(yùn)行時(shí)間、狀態(tài)和SQL然后結(jié)合innodb_lock_waits找到阻塞鏈。定位到持有鎖的事務(wù)之后評(píng)估這個(gè)事務(wù)是否能盡快結(jié)束。如果是長事務(wù)誤鎖了大量行要從業(yè)務(wù)代碼層面找到它為什么遲遲不提交是邏輯里夾了慢查詢還是事務(wù)忘記提交。如果持鎖事務(wù)是合理的那就得考慮調(diào)整事務(wù)執(zhí)行順序讓短事務(wù)優(yōu)先拿到鎖。innodb_lock_wait_timeout這個(gè)參數(shù)我建議線上主動(dòng)調(diào)小一點(diǎn)默認(rèn)50秒太長。鎖等待超過5到10秒的業(yè)務(wù)大概率已經(jīng)積壓到用戶能感知的程度了不如讓請(qǐng)求快速失敗讓應(yīng)用層自動(dòng)重試比所有請(qǐng)求阻塞半分鐘最后一起報(bào)錯(cuò)健康得多。我見過的穩(wěn)定業(yè)務(wù)里這個(gè)值大多設(shè)在5到10秒之間。4.2 死鎖分析不是bug但必須管死鎖出現(xiàn)的經(jīng)典場(chǎng)景是兩個(gè)事務(wù)都以不同的順序加鎖。比如事務(wù)A先鎖了訂單表再鎖用戶表事務(wù)B先鎖了用戶表再鎖訂單表兩邊互相等對(duì)方釋放鎖就死鎖了。MySQL檢測(cè)到死鎖后會(huì)自動(dòng)回滾其中一個(gè)事務(wù)釋放資源并在客戶端報(bào)ERROR 1213: Deadlock found的錯(cuò)誤。死鎖發(fā)生后最重要的是拿到現(xiàn)場(chǎng)信息。執(zhí)行SHOW ENGINE INNODB STATUS;輸出內(nèi)容里找到LATEST DETECTED DEADLOCK那一大段里面會(huì)記錄死鎖涉及的兩個(gè)事務(wù)分別執(zhí)行了什么SQL、持有和等待哪些索引記錄。分析這段信息通常能直接看到加鎖順序的矛盾點(diǎn)。應(yīng)對(duì)手段有幾個(gè)方向一是讓所有相關(guān)事務(wù)統(tǒng)一加鎖順序比如始終先操作訂單表再操作用戶表二是縮小事務(wù)范圍事務(wù)里只保留必要的SQL減少持有鎖的時(shí)間三是在業(yè)務(wù)層對(duì)死鎖錯(cuò)誤碼做重試處理。死鎖在并發(fā)系統(tǒng)里不可能完全消除我見過很多團(tuán)隊(duì)把報(bào)錯(cuò)日志當(dāng)成事故來排查其實(shí)更重要的是把重試機(jī)制做好讓偶發(fā)死鎖對(duì)業(yè)務(wù)透明。4.3 隔離級(jí)別與長事務(wù)監(jiān)控InnoDB默認(rèn)隔離級(jí)別是REPEATABLE READ這個(gè)級(jí)別為了處理幻讀引入了Next-Key Lock也就是不僅鎖住某一行還會(huì)鎖住一個(gè)范圍。并發(fā)插入、更新時(shí)間隙鎖帶來的沖突要比想象的更多。如果業(yè)務(wù)對(duì)數(shù)據(jù)一致性要求沒那么嚴(yán)苛比如很多報(bào)表類、讀多寫少類的場(chǎng)景把隔離級(jí)別改成READ COMMITTED可以明顯減少鎖沖突。好處是只有真正匹配的行會(huì)被鎖住但要注意副作用如果業(yè)務(wù)依賴可重復(fù)讀的快照一致性切換之前必須做充分評(píng)估。長事務(wù)是另一個(gè)容易被忽視的問題。一個(gè)事務(wù)跑了幾分鐘甚至幾小時(shí)它的undo log會(huì)不斷膨脹形成大量歷史版本直接影響SELECT的多版本讀性能。同時(shí)長事務(wù)還會(huì)一直持有鎖擋住其他事務(wù)。排查方式很簡單SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id FROM information_schema.innodb_trx;重點(diǎn)看trx_started。如果發(fā)現(xiàn)某個(gè)事務(wù)啟動(dòng)時(shí)間已經(jīng)超過你設(shè)定的閾值比如5分鐘以上基本可以認(rèn)定它是異常長事務(wù)。再順著trx_mysql_thread_id找到對(duì)應(yīng)連接借助performance_schema查到它正在執(zhí)行的SQL就能定位到業(yè)務(wù)代碼里的問題。上線前把長事務(wù)監(jiān)控配置好比事后人工翻日志要省心太多。5. 性能監(jiān)控與日常巡檢別等問題爆發(fā)才動(dòng)手優(yōu)化做到這里配置、索引、SQL、鎖都過了一遍性能多半已經(jīng)很健康了。但數(shù)據(jù)庫是動(dòng)態(tài)系統(tǒng)業(yè)務(wù)量在漲數(shù)據(jù)量在漲訪問模式也會(huì)變。今天健康的索引三個(gè)月后可能因?yàn)樾聵I(yè)務(wù)查詢就變了。所以最后一件事是把監(jiān)控和巡檢機(jī)制建立起來讓問題在發(fā)酵之前就能被發(fā)現(xiàn)。5.1 要盯的指標(biāo)和開源監(jiān)控組合監(jiān)控不需要一開始就追求大而全先盯住跟性能強(qiáng)相關(guān)的幾個(gè)核心指標(biāo)就夠。我日常會(huì)固定關(guān)注以下幾項(xiàng)QPS/TPS數(shù)據(jù)庫整體負(fù)載水平趨勢(shì)異常上升或下降都要有數(shù)。慢查詢數(shù)量慢查詢?nèi)罩緱l數(shù)突增往往意味著某個(gè)索引失效或者某條SQL被調(diào)用量變大。Threads_connected連接數(shù)是否長期高位運(yùn)行接近max_connections時(shí)就要預(yù)警。Buffer Pool命中率長期低于99%要考慮調(diào)大緩沖池或優(yōu)化熱點(diǎn)查詢。臨時(shí)表與臨時(shí)文件說明查詢中出現(xiàn)大量排序或分組需要回到SQL優(yōu)化層面處理。鎖等待次數(shù)與等待時(shí)間判斷并發(fā)事務(wù)沖突是否在惡化。監(jiān)控實(shí)現(xiàn)方面常見的開源監(jiān)控方案是配合一個(gè)MySQL導(dǎo)出器采集狀態(tài)變量再用時(shí)序數(shù)據(jù)庫存指標(biāo)、用圖形面板展示。這套組合的優(yōu)勢(shì)在于社區(qū)成熟、指標(biāo)覆蓋廣而且不用額外購買商業(yè)產(chǎn)品。如果團(tuán)隊(duì)規(guī)模小只想快速看個(gè)概覽MySQL 8.0自帶的performance_schema和sys庫里已經(jīng)有非常豐富的等待事件和診斷信息不需要額外裝東西。5.2 基準(zhǔn)測(cè)試與容量規(guī)劃讓數(shù)據(jù)說話優(yōu)化到底有沒有效果不能靠感覺說好像快了一點(diǎn)要用數(shù)據(jù)說話。壓測(cè)工具上我經(jīng)常用的是sysbench它可以模擬OLTP讀寫混合負(fù)載在優(yōu)化前后分別跑一輪對(duì)比QPS和平均延遲。操作流程基本是幾步先準(zhǔn)備測(cè)試表和測(cè)試數(shù)據(jù)然后分別執(zhí)行只讀、只寫、讀寫混合的壓力測(cè)試記錄結(jié)果。壓測(cè)時(shí)要注意數(shù)據(jù)量不能只是幾百條至少上百萬行才有參考意義否則索引、緩沖池等機(jī)制根本來不及發(fā)揮作用。容量規(guī)劃這塊也能從壓測(cè)里找到線索。假設(shè)單機(jī)壓測(cè)出來能扛1500 QPS業(yè)務(wù)高峰期峰值是800 QPS那當(dāng)前余量充足如果已經(jīng)到1200 QPS了就該提前準(zhǔn)備擴(kuò)容方案或者優(yōu)化熱點(diǎn)。壓測(cè)結(jié)果建議記錄成表格每次調(diào)整完配置或索引都重新測(cè)一輪長期下來就知道系統(tǒng)的性能拐點(diǎn)在哪里了。把歷史壓測(cè)數(shù)據(jù)保存下來對(duì)后續(xù)做資源預(yù)算和新版本評(píng)估都很有價(jià)值。5.3 我自己的巡檢清單每周一查風(fēng)險(xiǎn)前置最后分享一份我的每周巡檢習(xí)慣幾分鐘能跑完但對(duì)穩(wěn)定性的幫助非常大。我會(huì)固定檢查幾項(xiàng)內(nèi)容一是配置參數(shù)和實(shí)際使用的偏差。線上機(jī)器跑了幾個(gè)月當(dāng)時(shí)設(shè)的innodb_buffer_pool_size可能已經(jīng)不夠了但沒人注意到。用狀態(tài)變量對(duì)比一下內(nèi)存和磁盤的使用趨勢(shì)就能發(fā)現(xiàn)該擴(kuò)容的擴(kuò)容、該調(diào)參的調(diào)參。二是慢查詢?nèi)罩镜内厔?shì)。對(duì)比這周和上周同期的慢查詢數(shù)量如果穩(wěn)步上升趕緊看新增的慢SQL是什么如果突然下降也要想想是不是業(yè)務(wù)量下降了或者高峰錯(cuò)開了。沒有任何波動(dòng)的慢查詢量反而可能是日志輪轉(zhuǎn)沒生效。三是鎖等待和死鎖計(jì)數(shù)。查SHOW GLOBAL STATUS LIKE Innodb_row_lock%;和SHOW GLOBAL STATUS LIKE Innodb_deadlocks;這些計(jì)數(shù)一直往上漲說明并發(fā)沖突在增加需要翻一眼最近的鎖等待視圖。四是磁盤空間和binlog大小。binlog增長過快通常意味著有大批量更新或者長時(shí)間未處理的事務(wù)磁盤如果被binlog塞滿MySQL會(huì)直接停止寫入這比慢查詢更致命。五是緩沖池的命中率和臟頁刷新情況。命中率低可以繼續(xù)調(diào)大緩存臟頁比例長期高說明寫入能力強(qiáng)但刷盤能力不足有可能I/O子系統(tǒng)已經(jīng)達(dá)到瓶頸。這一套巡檢做下來大多數(shù)問題都能在變成事故之前被攔截住。優(yōu)化MySQL 8.0不是一次性工作它更像一個(gè)跟業(yè)務(wù)共同演進(jìn)的循環(huán)壓測(cè)、監(jiān)控、發(fā)現(xiàn)問題、再優(yōu)化循環(huán)往復(fù)。最后再分享一個(gè)心得遇到性能問題我總會(huì)先看索引和SQL再看配置最后才懷疑硬件。很多看似配置不夠的場(chǎng)景其實(shí)是索引設(shè)計(jì)不合理導(dǎo)致的假象。你在配置上花再多精力也不如先把最耗時(shí)的那幾條SQL拎出來分析一遍。MySQL 8.0本身的優(yōu)化器已經(jīng)夠強(qiáng)了我們做優(yōu)化的主要工作就是給它提供正確的索引和干凈的數(shù)據(jù)環(huán)境剩下的它會(huì)自己搞定。 MySQL 8.0性能調(diào)優(yōu)實(shí)戰(zhàn)：從配置、索引到SQL與監(jiān)控
MySQL 8.0 基礎(chǔ)優(yōu)化實(shí)戰(zhàn)指南從配置到性能調(diào)優(yōu)很多人以為裝好MySQL 8.0、把數(shù)據(jù)導(dǎo)進(jìn)去數(shù)據(jù)庫就算能用了。直到某天業(yè)務(wù)慢得像蝸牛爬開發(fā)跑過來喊了一句數(shù)據(jù)庫卡了你才發(fā)現(xiàn)默認(rèn)配置和真實(shí)負(fù)載之間隔著一整座山。這篇指南就是要把MySQL 8.0從配置文件、索引設(shè)計(jì)、SQL寫法、鎖與事務(wù)、監(jiān)控巡檢這五條線串起來講清楚優(yōu)化時(shí)先動(dòng)哪里、后動(dòng)哪里以及每個(gè)操作背后的原因。不管你是正在接手一個(gè)MySQL 8.0項(xiàng)目的開發(fā)者還是已經(jīng)踩過幾個(gè)坑但想系統(tǒng)梳理一遍的運(yùn)維同學(xué)按這個(gè)順序走一遍基本能把數(shù)據(jù)庫從跑得動(dòng)推到跑得快。我這些年處理過的MySQL性能問題里至少有一半以上不是機(jī)器不行而是配置沒跟業(yè)務(wù)對(duì)齊、索引沒建對(duì)、SQL寫法太隨性。所以這篇指南不給那種裝完點(diǎn)下一步的流水賬只講真正影響性能的關(guān)鍵動(dòng)作。1. 基礎(chǔ)配置優(yōu)化先別急著動(dòng)參數(shù)先搞懂這幾個(gè)關(guān)鍵項(xiàng)MySQL 8.0安裝完用的是一套非常保守的默認(rèn)配置目的只是保證它能跑起來、兼容性最好生產(chǎn)環(huán)境的壓力和這套配置完全不匹配。舉一個(gè)最常見的例子innodb_buffer_pool_size默認(rèn)只有128M假如你機(jī)器有32G內(nèi)存不把這個(gè)參數(shù)調(diào)大InnoDB的緩存池只夠裝十分之一不到的熱點(diǎn)數(shù)據(jù)剩下的請(qǐng)求全部落到磁盤I/O上。磁盤再快也遠(yuǎn)不如內(nèi)存快性能自然上不去。所以我調(diào)優(yōu)的第一件事永遠(yuǎn)是結(jié)合業(yè)務(wù)特點(diǎn)把基礎(chǔ)參數(shù)先立起來然后再談索引和SQL。順序反了的話你后面再努力也可能是在給錯(cuò)誤的配置環(huán)境打補(bǔ)丁。1.1 連接數(shù)別亂調(diào)大要點(diǎn)是算峰值并發(fā)max_connections默認(rèn)是151開發(fā)環(huán)境甚至小網(wǎng)站都?jí)蛴玫坏交顒?dòng)大促或定時(shí)任務(wù)并發(fā)場(chǎng)景就特別容易打滿。打滿之后的表現(xiàn)很典型應(yīng)用日志里全是Too many connections新請(qǐng)求進(jìn)不來老請(qǐng)求又堵著不結(jié)束整個(gè)鏈路雪崩。最簡單的確認(rèn)方式是執(zhí)行下面這條SQL看當(dāng)前連接數(shù)和配置上限的差距SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;調(diào)優(yōu)時(shí)你可能會(huì)想那直接把max_connections設(shè)成2000不就完了還真不行。每個(gè)連接都會(huì)占用線程和內(nèi)存資源連接數(shù)設(shè)得太大CPU會(huì)大量浪費(fèi)在線程切換上請(qǐng)求響應(yīng)反而更慢。一般做法是先估算業(yè)務(wù)峰值時(shí)的并發(fā)連接數(shù)然后在這個(gè)基礎(chǔ)上預(yù)留30%-40%的余量。比如你用壓力測(cè)試得出500并發(fā)已經(jīng)能扛住業(yè)務(wù)峰值那么max_connections設(shè)在800左右就差不多了。另外程序側(cè)連接池的設(shè)置也很關(guān)鍵。很多應(yīng)用框架默認(rèn)的連接池是20到50個(gè)連接一個(gè)實(shí)例不夠就多開幾個(gè)實(shí)例這比在數(shù)據(jù)庫端放開幾千個(gè)連接要健康得多。我實(shí)測(cè)過把同樣請(qǐng)求壓力從連接池200個(gè)連接改成數(shù)據(jù)庫2000個(gè)連接連接池方案下的平均延遲小了一個(gè)數(shù)量級(jí)。別把數(shù)據(jù)庫當(dāng)成無狀態(tài)的服務(wù)去猛連連接是個(gè)昂貴的資源。1.2 InnoDB緩沖池與日志文件性能地基innodb_buffer_pool_size是MySQL里最重要的性能參數(shù)沒有之一。InnoDB會(huì)把數(shù)據(jù)頁、索引頁、變更緩沖等都放在這個(gè)內(nèi)存區(qū)域里所有讀寫操作先跟這塊內(nèi)存打交道內(nèi)存不夠了才落盤。經(jīng)驗(yàn)上這個(gè)值可以設(shè)置到物理內(nèi)存的60%到75%。舉個(gè)例子32G內(nèi)存的機(jī)器可以給到20G左右64G內(nèi)存給到40G到48G。如果你的服務(wù)器上不只跑MySQL還要跑應(yīng)用進(jìn)程那得按實(shí)際可用內(nèi)存重新算不要把機(jī)器搞到開始用swap。MySQL 8.0支持動(dòng)態(tài)調(diào)整緩沖池大小方便先看效果再固化到配置文件SET GLOBAL innodb_buffer_pool_size 20 * 1024 * 1024 * 1024;但注意這個(gè)動(dòng)態(tài)設(shè)置在重啟后會(huì)丟失調(diào)整到滿意的值之后記得同步修改my.cnf。緩沖池有沒有起作用看命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;命中的比例越高說明內(nèi)存緩存越有效。再來看innodb_log_file_size。MySQL 8.0默認(rèn)是48M這個(gè)值對(duì)寫密集的業(yè)務(wù)來說實(shí)在太小了。redo log太小意味著日志切換特別頻繁每一次切換都要做一次checkpoint把臟頁刷到磁盤磁盤持續(xù)高負(fù)載整個(gè)數(shù)據(jù)庫的寫性能就會(huì)明顯下滑。在寫多讀少的業(yè)務(wù)里我習(xí)慣把innodb_log_file_size調(diào)整到512M甚至1G同時(shí)也要觀察磁盤空間和恢復(fù)時(shí)間日志太大崩潰恢復(fù)時(shí)花費(fèi)的時(shí)間也會(huì)更長。正常運(yùn)維周期內(nèi)1G這個(gè)檔位對(duì)很多中等規(guī)模業(yè)務(wù)都算合理。innodb_flush_log_at_trx_commit這個(gè)參數(shù)建議你有意識(shí)地選一次。默認(rèn)值1表示每次事務(wù)提交都把日志刷到磁盤最安全但每次提交都要等一次磁盤fsync性能開銷大改成2表示每次提交只寫入操作系統(tǒng)緩存每秒再刷一次盤性能明顯好但MySQL進(jìn)程崩潰時(shí)可能丟最多1秒的事務(wù)。如果是金融交易類業(yè)務(wù)老老實(shí)實(shí)用1大多數(shù)互聯(lián)網(wǎng)業(yè)務(wù)能接受極小概率的秒級(jí)丟失可以用2換性能。很多團(tuán)隊(duì)完全沒有考慮過這個(gè)參數(shù)其實(shí)它和你的可靠性要求是直接相關(guān)的。1.3 字符集、時(shí)區(qū)與幾個(gè)會(huì)話級(jí)參數(shù)的坑MySQL 8.0默認(rèn)字符集已經(jīng)是utf8mb4這比5.7時(shí)代默認(rèn)latin1強(qiáng)很多但建庫時(shí)還是要顯式確認(rèn)一遍。尤其要注意默認(rèn)的排序規(guī)則utf8mb4_0900_ai_ci是不區(qū)分大小寫的如果業(yè)務(wù)要求用戶名、訂單號(hào)查詢時(shí)必須區(qū)分大小寫這個(gè)默認(rèn)值會(huì)讓你查出來的結(jié)果比預(yù)期更多而且還會(huì)讓LIKE查詢帶上額外的排序規(guī)則轉(zhuǎn)換成本。建表時(shí)按實(shí)際需要指定COLLATE utf8mb4_bin或者utf8mb4_0900_as_cs比事后改表省心得多。時(shí)區(qū)這個(gè)坑也很隱蔽。MySQL 8.0的系統(tǒng)時(shí)區(qū)和應(yīng)用服務(wù)器的時(shí)區(qū)如果不一致你存進(jìn)去的DATETIME看著沒問題但用JDBC連接時(shí)如果服務(wù)端時(shí)區(qū)跟客戶端不一致轉(zhuǎn)換出來的時(shí)間完全對(duì)不上。排查問題到最后發(fā)現(xiàn)是時(shí)區(qū)偏移引起的既浪費(fèi)精力又容易誤傷業(yè)務(wù)邏輯。我習(xí)慣在連接串里顯式指定時(shí)區(qū)同時(shí)在MySQL側(cè)把全局時(shí)區(qū)設(shè)為業(yè)務(wù)統(tǒng)一時(shí)區(qū)兩邊對(duì)死。會(huì)話級(jí)參數(shù)里最容易被高估的是sort_buffer_size。它表示每個(gè)連接做排序操作時(shí)能使用的內(nèi)存看上去大一點(diǎn)排序更快但這是每個(gè)連接都會(huì)分配的如果全局設(shè)置了8M200個(gè)連接同時(shí)排序就是1.6G內(nèi)存內(nèi)存瞬間被打爆。正確做法是保持默認(rèn)或小幅調(diào)整然后用狀態(tài)變量Sort_merge_passes觀察實(shí)際排序過程中是否有大量落盤操作如果這個(gè)計(jì)數(shù)很大再針對(duì)性放大排序緩沖。同理還有join_buffer_size別聽網(wǎng)上那些調(diào)大排序緩沖提升性能的配方就亂改會(huì)話級(jí)參數(shù)影響范圍大要謹(jǐn)慎。提示調(diào)參數(shù)最忌諱一次性改十幾個(gè)再重啟觀察出了問題根本不知道是哪個(gè)參數(shù)造成的。一次只改一兩個(gè)每次改完做一輪對(duì)比壓測(cè)或者看一段時(shí)間的運(yùn)行曲線確認(rèn)有效再改下一批。2. 索引優(yōu)化慢查詢的根源大多在索引設(shè)計(jì)配置是地基但真正讓查詢變慢的常見原因還是索引沒建好。MySQL 8.0的優(yōu)化器能力比老版本強(qiáng)了不少但它再聰明也需要一個(gè)合理設(shè)計(jì)的索引結(jié)構(gòu)。很多人遇到慢查詢第一反應(yīng)是機(jī)器性能不行或者SQL寫得爛但EXPLAIN一看全表掃描、沒有可用索引、回表次數(shù)爆炸問題清清楚楚。這一章我會(huì)從最典型的索引失效場(chǎng)景講到執(zhí)行計(jì)劃怎么看最后說聯(lián)合索引的實(shí)用設(shè)計(jì)原則?？炊@三塊你排查慢SQL的思路會(huì)清晰很多。2.1 最常見的索引失效場(chǎng)景你大概率踩過幾個(gè)先看一個(gè)典型例子在created_at字段上建了索引查詢條件是WHERE DATE(created_at) 2025-01-01。對(duì)索引列使用函數(shù)以后優(yōu)化器沒法直接利用B樹的有序性做查找只能把索引列全部取出來算一遍函數(shù)再過濾索引就白建了。正確做法是改成范圍查詢WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00。隱式類型轉(zhuǎn)換也是重災(zāi)區(qū)。假如user_id字段是varchar類型但傳入的是數(shù)字WHERE user_id 123456MySQL會(huì)先把索引列轉(zhuǎn)成數(shù)字再比較同樣導(dǎo)致索引失效。判斷方法很簡單看看表結(jié)構(gòu)里這個(gè)字段到底是什么類型然后確保傳入值跟字段類型嚴(yán)格一致。如果程序里傳參類型不統(tǒng)一建議在SQL層做好類型轉(zhuǎn)換讓參數(shù)先變成字符串再進(jìn)條件。前置通配符的LIKE查詢也是很常見的一個(gè)問題。LIKE %keyword這種寫法優(yōu)化器沒有辦法利用B樹順序掃描的特性只能全索引掃描。業(yè)務(wù)上確實(shí)需要模糊查詢時(shí)我一般先評(píng)估數(shù)據(jù)量幾千行的小表全表掃也就幾毫秒不用太糾結(jié)但百萬行以上就得考慮全文索引或者改造業(yè)務(wù)設(shè)計(jì)讓查詢條件盡量變成前綴匹配。最后提一下OR條件。WHERE a 1 OR b 2如果只在a上建了索引b沒有索引優(yōu)化器很可能放棄索引直接全表掃描。MySQL 8.0雖然支持索引合并但沒有索引的列始終是短板。要么給b也建索引要么把OR改寫成UNION兩條獨(dú)立查詢實(shí)測(cè)下來改寫后的執(zhí)行計(jì)劃往往更穩(wěn)定。2.2 學(xué)會(huì)看EXPLAIN優(yōu)化器想什么別猜EXPLAIN是排查索引問題時(shí)需要最先學(xué)會(huì)看的工具但很多人在MySQL 8.0里還是只盯著一列看實(shí)際上有幾個(gè)列的信息量很大。type列反應(yīng)了訪問方式從好到差大致是systemconsteq_refrefrangeindexALL?？吹紸LL基本意味著全表掃描是優(yōu)化的頭號(hào)目標(biāo)index雖然也是全索引掃描但至少比表掃描好一點(diǎn)也可能是因?yàn)椴樵冃枰牧卸荚谒饕铩?shí)用的判斷標(biāo)準(zhǔn)是能用range及以上級(jí)別就合格出現(xiàn)ALL就要處理。rows列是優(yōu)化器預(yù)估需要掃描的行數(shù)雖不是真實(shí)值但能看到大概量級(jí)優(yōu)化前后這個(gè)數(shù)字的變化能反映出索引是否生效。Extra列里重點(diǎn)留意Using filesort和Using temporary。出現(xiàn)Using filesort說明排序沒有用到索引順序需要額外排序操作出現(xiàn)Using temporary說明查詢過程創(chuàng)建了臨時(shí)表常見于GROUP BY、DISTINCT或者復(fù)雜的子查詢場(chǎng)景數(shù)據(jù)量大時(shí)性能下降非常明顯。如果看到的是Using index那說明查詢只訪問索引就能拿到全部需要的數(shù)據(jù)這就是最理想的覆蓋索引狀態(tài)。MySQL 8.0.18版本開始有EXPLAIN ANALYZE可以直接輸出真實(shí)的執(zhí)行耗時(shí)、掃描行數(shù)和各步驟耗時(shí)占比。用法很簡單EXPLAIN ANALYZE SELECT ...;結(jié)果比傳統(tǒng)EXPLAIN更直觀連 實(shí)際執(zhí)行了幾次循環(huán)、每次耗時(shí)多少 都給了。我在排查慢SQL時(shí)會(huì)先跑EXPLAIN看結(jié)構(gòu)再跑EXPLAIN ANALYZE看真實(shí)瓶頸兩步配合基本能把問題定位清楚。2.3 聯(lián)合索引與覆蓋索引的組合拳聯(lián)合索引設(shè)計(jì)的最核心原則是最左前綴。比如在(status, category_id, created_at)上建了聯(lián)合索引查詢條件里必須有status才能用上這個(gè)索引只查category_id或者只查created_at是不會(huì)命中這個(gè)聯(lián)合索引的。所以設(shè)計(jì)聯(lián)合索引時(shí)要把等值查詢的列放前面范圍查詢的列放后面。這里有一個(gè)比較容易忽略的細(xì)節(jié)聯(lián)合索引遇到范圍查詢后面的列就失效了。比如WHERE status 1 AND category_id 10 AND created_at 2025-01-01在(status, category_id, created_at)索引上category_id一旦用了范圍條件created_at就沒法用索引繼續(xù)過濾了。所以正確做法是把范圍條件涉及的列盡量放到聯(lián)合索引的最后。如果幾個(gè)條件都是范圍那就只能取舍選擇區(qū)分度最高、過濾效果最好的列放索引里。覆蓋索引是另一個(gè)提升效率的大殺器。簡單說就是讓查詢需要的所有列都包含在某一個(gè)索引中查詢時(shí)不用回表取數(shù)據(jù)行。舉一個(gè)常見的優(yōu)化案例業(yè)務(wù)請(qǐng)求是SELECT id, name FROM user WHERE status 1如果只在status上建了普通索引那么MySQL查到所有status1的主鍵后還得回表讀取整行數(shù)據(jù)再取出id和name但如果建的是(status, id, name)聯(lián)合索引數(shù)據(jù)在索引里就能全部拿到省掉了回表這趟I/O。對(duì)高并發(fā)讀多寫少的場(chǎng)景覆蓋索引帶來的提升非常明顯。索引也不是越多越好。每多一個(gè)索引寫入時(shí)就要多維護(hù)一份B樹結(jié)構(gòu)更新頻繁的表索引太多反而拖累寫性能還占用額外磁盤空間。我的取舍習(xí)慣是每個(gè)表的核心查詢路徑最多設(shè)計(jì)兩到三個(gè)聯(lián)合索引優(yōu)先滿足最重要的業(yè)務(wù)查詢次要查詢?nèi)绻麛?shù)據(jù)量不大走全表掃描也不是不能接受?？偸悄盟锌赡艿牟樵儣l件來建索引最終會(huì)把表逼到寫一次要更新十個(gè)索引的境地。3. SQL優(yōu)化實(shí)戰(zhàn)先開慢查詢?nèi)罩驹倬珳?zhǔn)打擊配置和索引都檢查過了接下來要做的是把真正耗時(shí)的SQL找出來。這一步靠猜是猜不出來的必須依賴慢查詢?nèi)罩竞托阅苤笜?biāo)。很多團(tuán)隊(duì)的MySQL默認(rèn)沒開慢查詢?nèi)罩镜扔陲w行員不看儀表盤在盲飛。開了日志你就知道哪些SQL在消耗大量時(shí)間然后再逐條分析、改寫。3.1 慢查詢?nèi)罩镜恼_打開方式MySQL 8.0里可以用下面幾條命令把慢查詢?nèi)罩敬蜷_同時(shí)設(shè)置閾值時(shí)間。默認(rèn)閾值是10秒對(duì)絕大多數(shù)在線業(yè)務(wù)來說太寬松了很多5秒、3秒的SQL可能天天在跑你都不知道。我一般直接設(shè)到1秒或者0.5秒先跑一段時(shí)間把高頻慢SQL暴露出來再說。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;有一個(gè)非常實(shí)用但容易忽略的參數(shù)是log_queries_not_using_indexes它會(huì)把沒有正確使用索引的SQL也記進(jìn)慢查詢?nèi)罩炯词箞?zhí)行時(shí)間沒超過閾值。這對(duì)早期發(fā)現(xiàn)還沒變慢、但全表掃描量大的隱患很有幫助。不過生產(chǎn)環(huán)境不要長期開著這個(gè)選項(xiàng)因?yàn)楹芏嘈”聿樵儽緛砭筒恍枰饕_著會(huì)導(dǎo)致日志量爆炸。我的習(xí)慣是開展優(yōu)化工作前的1到2周開啟收集完問題SQL就關(guān)掉。拿到慢查詢?nèi)罩疽院笙劝磮?zhí)行次數(shù)和平均耗時(shí)排序找出高頻率慢SQL。這里要區(qū)分兩種問題一種是單個(gè)SQL執(zhí)行時(shí)間特別長比如好幾秒另一種是每個(gè)SQL執(zhí)行時(shí)間幾十毫秒但每秒被調(diào)用成百上千次。前者靠優(yōu)化單條SQL解決后者往往要上升到應(yīng)用層加緩存或者限流去治理單靠數(shù)據(jù)庫層面調(diào)優(yōu)很快會(huì)達(dá)到上限。3.2 深翻頁與子查詢的典型優(yōu)化套路分頁查詢?cè)跇I(yè)務(wù)里太常見了但深翻頁問題直到今天依然在大量項(xiàng)目里存在。LIMIT 100000, 20這種寫法數(shù)據(jù)庫不是只取20條而是掃描完前面的100000條再丟掉掃描成本高得離譜。我一個(gè)朋友接手過一個(gè)千萬級(jí)數(shù)據(jù)的列表頁翻到第100頁之后接口就超時(shí)典型的深翻頁問題。優(yōu)化方案之一是延遲關(guān)聯(lián)先通過覆蓋索引查出目標(biāo)主鍵再用主鍵回表取完整數(shù)據(jù)。改造前是SELECT * FROM orders ORDER BY id LIMIT 100000, 20改造后類似下面這樣SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;這個(gè)寫法的思路是先讓子查詢用主鍵索引快速掃描出這一頁需要的20個(gè)主鍵然后再用連接去取完整數(shù)據(jù)避免了整個(gè)大表層面的掃描和回表。如果業(yè)務(wù)上有更明確的排序條件比如按created_at排序可以考慮基于游標(biāo)的方式記錄上一頁最后一條的created_at和id下一頁用條件過濾來拿數(shù)據(jù)這種寫法在千萬級(jí)數(shù)據(jù)下可以做到翻頁成本恒定。子查詢方面MySQL 8.0已經(jīng)從5.7時(shí)代的大部分需要搞成JOIN進(jìn)化到會(huì)用物化或者半連接策略來優(yōu)化但這不是說子查詢可以隨便寫。我的建議是業(yè)務(wù)邏輯簡單、數(shù)據(jù)量小的場(chǎng)景子查詢和JOIN差別不大按可讀性選數(shù)據(jù)量一大比如外層幾萬條、內(nèi)層掃描全表就要實(shí)際壓測(cè)驗(yàn)證。一個(gè)常用的排查方式是EXPLAIN ANALYZE看執(zhí)行計(jì)劃里子查詢被物化了幾次、掃描了多少行如果出現(xiàn)逐行循環(huán)執(zhí)行那就要改寫。3.3 統(tǒng)計(jì)數(shù)據(jù)、直方圖與優(yōu)化器提示MySQL優(yōu)化器選擇執(zhí)行計(jì)劃時(shí)依賴表的統(tǒng)計(jì)信息如果統(tǒng)計(jì)信息嚴(yán)重過期優(yōu)化器可能選擇一個(gè)明明有可用索引但是代價(jià)估算錯(cuò)誤而放棄索引的劣質(zhì)計(jì)劃。這種情況有個(gè)很直觀的表現(xiàn)EXPLAIN里type是ALL但你明明剛剛才確認(rèn)索引是存在的。解決辦法是執(zhí)行ANALYZE TABLE 表名;刷新統(tǒng)計(jì)信息再重新看EXPLAIN。MySQL 8.0新增了直方圖功能對(duì)數(shù)據(jù)分布嚴(yán)重不均勻的列非常實(shí)用。比如status字段99%的行是0、1%的行是1統(tǒng)計(jì)信息里可能只記錄大概平均分布優(yōu)化器如果因此選擇全表掃描去查那個(gè)只占1%的status1性能就很虧。給這類列建立直方圖之后優(yōu)化器能看到更真實(shí)的數(shù)據(jù)分布做出更合理的判斷。創(chuàng)建直方圖可以用ANALYZE TABLE 表名 UPDATE HISTOGRAM ON status;維護(hù)成本不高可以針對(duì)高頻查詢涉及的列做。至于優(yōu)化器提示如USE INDEX和FORCE INDEX我的態(tài)度比較謹(jǐn)慎。它們?cè)谝恍┚o急上線、臨時(shí)救火的時(shí)候確實(shí)有效能強(qiáng)制讓執(zhí)行計(jì)劃走你想走的索引。但如果把這種提示固化在業(yè)務(wù)代碼里將來表結(jié)構(gòu)調(diào)整、索引重命名或者數(shù)據(jù)量變大強(qiáng)制提示反而會(huì)阻止優(yōu)化器選出更優(yōu)計(jì)劃。除非有明確證據(jù)表明優(yōu)化器在可預(yù)見的未來都會(huì)選錯(cuò)否則我會(huì)優(yōu)先讓優(yōu)化器自主決策同時(shí)把統(tǒng)計(jì)信息維護(hù)好。注意排查慢SQL別只盯著執(zhí)行時(shí)間一條還要看平均掃描行數(shù)和返回行數(shù)的比例。掃描100萬行返回10行說明過濾條件沒用好這才是索引設(shè)計(jì)應(yīng)該優(yōu)化的點(diǎn)。4. 鎖與事務(wù)并發(fā)高時(shí)最隱蔽的性能殺手索引和SQL優(yōu)化好了之后單條查詢本身的速度可能已經(jīng)很可觀了但只要并發(fā)一上來數(shù)據(jù)庫就可能卡在另一個(gè)地方鎖等待和事務(wù)沖突。MySQL 8.0的InnoDB默認(rèn)使用行級(jí)鎖但行級(jí)鎖并不是說就不會(huì)鎖沖突了。長事務(wù)、間隙鎖、寫寫沖突、死鎖任何一個(gè)處理不好都會(huì)讓整個(gè)系統(tǒng)的吞吐量直線下降。這一章的很多問題在日常壓測(cè)中不輕易暴露往往人要等到線上并發(fā)真的高了才遇到。所以提前把排查路徑和應(yīng)對(duì)手段整理出來能幫你少走很多彎路。4.1 鎖等待排查先找到誰鎖了誰最常見的報(bào)錯(cuò)是ERROR 1205: Lock wait timeout exceeded; try restarting transaction。遇到這個(gè)錯(cuò)誤很多人的第一反應(yīng)是我把鎖等待時(shí)間調(diào)大一點(diǎn)。這個(gè)思路只能暫時(shí)緩解癥狀因?yàn)槿绻i沒釋放你等再久也一樣超時(shí)還會(huì)讓大量請(qǐng)求堆積在鎖等待隊(duì)列里把連接池打滿。正確做法是先查清楚誰持有鎖、誰在等待。MySQL 8.0的performance_schema和相關(guān)視圖提供了豐富的信息你可以直接查sys.innodb_lock_waits視圖它類似于一張鎖沖突快照包含了阻塞事務(wù)、被阻塞事務(wù)和對(duì)應(yīng)的SQL內(nèi)容。示例查詢SELECT * FROM sys.innodb_lock_waits\G如果嫌這個(gè)視圖信息太細(xì)也可以查information_schema.innodb_trx看當(dāng)前所有事務(wù)的運(yùn)行時(shí)間、狀態(tài)和SQL然后結(jié)合innodb_lock_waits找到阻塞鏈。定位到持有鎖的事務(wù)之后評(píng)估這個(gè)事務(wù)是否能盡快結(jié)束。如果是長事務(wù)誤鎖了大量行要從業(yè)務(wù)代碼層面找到它為什么遲遲不提交是邏輯里夾了慢查詢還是事務(wù)忘記提交。如果持鎖事務(wù)是合理的那就得考慮調(diào)整事務(wù)執(zhí)行順序讓短事務(wù)優(yōu)先拿到鎖。innodb_lock_wait_timeout這個(gè)參數(shù)我建議線上主動(dòng)調(diào)小一點(diǎn)默認(rèn)50秒太長。鎖等待超過5到10秒的業(yè)務(wù)大概率已經(jīng)積壓到用戶能感知的程度了不如讓請(qǐng)求快速失敗讓應(yīng)用層自動(dòng)重試比所有請(qǐng)求阻塞半分鐘最后一起報(bào)錯(cuò)健康得多。我見過的穩(wěn)定業(yè)務(wù)里這個(gè)值大多設(shè)在5到10秒之間。4.2 死鎖分析不是bug但必須管死鎖出現(xiàn)的經(jīng)典場(chǎng)景是兩個(gè)事務(wù)都以不同的順序加鎖。比如事務(wù)A先鎖了訂單表再鎖用戶表事務(wù)B先鎖了用戶表再鎖訂單表兩邊互相等對(duì)方釋放鎖就死鎖了。MySQL檢測(cè)到死鎖后會(huì)自動(dòng)回滾其中一個(gè)事務(wù)釋放資源并在客戶端報(bào)ERROR 1213: Deadlock found的錯(cuò)誤。死鎖發(fā)生后最重要的是拿到現(xiàn)場(chǎng)信息。執(zhí)行SHOW ENGINE INNODB STATUS;輸出內(nèi)容里找到LATEST DETECTED DEADLOCK那一大段里面會(huì)記錄死鎖涉及的兩個(gè)事務(wù)分別執(zhí)行了什么SQL、持有和等待哪些索引記錄。分析這段信息通常能直接看到加鎖順序的矛盾點(diǎn)。應(yīng)對(duì)手段有幾個(gè)方向一是讓所有相關(guān)事務(wù)統(tǒng)一加鎖順序比如始終先操作訂單表再操作用戶表二是縮小事務(wù)范圍事務(wù)里只保留必要的SQL減少持有鎖的時(shí)間三是在業(yè)務(wù)層對(duì)死鎖錯(cuò)誤碼做重試處理。死鎖在并發(fā)系統(tǒng)里不可能完全消除我見過很多團(tuán)隊(duì)把報(bào)錯(cuò)日志當(dāng)成事故來排查其實(shí)更重要的是把重試機(jī)制做好讓偶發(fā)死鎖對(duì)業(yè)務(wù)透明。4.3 隔離級(jí)別與長事務(wù)監(jiān)控InnoDB默認(rèn)隔離級(jí)別是REPEATABLE READ這個(gè)級(jí)別為了處理幻讀引入了Next-Key Lock也就是不僅鎖住某一行還會(huì)鎖住一個(gè)范圍。并發(fā)插入、更新時(shí)間隙鎖帶來的沖突要比想象的更多。如果業(yè)務(wù)對(duì)數(shù)據(jù)一致性要求沒那么嚴(yán)苛比如很多報(bào)表類、讀多寫少類的場(chǎng)景把隔離級(jí)別改成READ COMMITTED可以明顯減少鎖沖突。好處是只有真正匹配的行會(huì)被鎖住但要注意副作用如果業(yè)務(wù)依賴可重復(fù)讀的快照一致性切換之前必須做充分評(píng)估。長事務(wù)是另一個(gè)容易被忽視的問題。一個(gè)事務(wù)跑了幾分鐘甚至幾小時(shí)它的undo log會(huì)不斷膨脹形成大量歷史版本直接影響SELECT的多版本讀性能。同時(shí)長事務(wù)還會(huì)一直持有鎖擋住其他事務(wù)。排查方式很簡單SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id FROM information_schema.innodb_trx;重點(diǎn)看trx_started。如果發(fā)現(xiàn)某個(gè)事務(wù)啟動(dòng)時(shí)間已經(jīng)超過你設(shè)定的閾值比如5分鐘以上基本可以認(rèn)定它是異常長事務(wù)。再順著trx_mysql_thread_id找到對(duì)應(yīng)連接借助performance_schema查到它正在執(zhí)行的SQL就能定位到業(yè)務(wù)代碼里的問題。上線前把長事務(wù)監(jiān)控配置好比事后人工翻日志要省心太多。5. 性能監(jiān)控與日常巡檢別等問題爆發(fā)才動(dòng)手優(yōu)化做到這里配置、索引、SQL、鎖都過了一遍性能多半已經(jīng)很健康了。但數(shù)據(jù)庫是動(dòng)態(tài)系統(tǒng)業(yè)務(wù)量在漲數(shù)據(jù)量在漲訪問模式也會(huì)變。今天健康的索引三個(gè)月后可能因?yàn)樾聵I(yè)務(wù)查詢就變了。所以最后一件事是把監(jiān)控和巡檢機(jī)制建立起來讓問題在發(fā)酵之前就能被發(fā)現(xiàn)。5.1 要盯的指標(biāo)和開源監(jiān)控組合監(jiān)控不需要一開始就追求大而全先盯住跟性能強(qiáng)相關(guān)的幾個(gè)核心指標(biāo)就夠。我日常會(huì)固定關(guān)注以下幾項(xiàng)QPS/TPS數(shù)據(jù)庫整體負(fù)載水平趨勢(shì)異常上升或下降都要有數(shù)。慢查詢數(shù)量慢查詢?nèi)罩緱l數(shù)突增往往意味著某個(gè)索引失效或者某條SQL被調(diào)用量變大。Threads_connected連接數(shù)是否長期高位運(yùn)行接近max_connections時(shí)就要預(yù)警。Buffer Pool命中率長期低于99%要考慮調(diào)大緩沖池或優(yōu)化熱點(diǎn)查詢。臨時(shí)表與臨時(shí)文件說明查詢中出現(xiàn)大量排序或分組需要回到SQL優(yōu)化層面處理。鎖等待次數(shù)與等待時(shí)間判斷并發(fā)事務(wù)沖突是否在惡化。監(jiān)控實(shí)現(xiàn)方面常見的開源監(jiān)控方案是配合一個(gè)MySQL導(dǎo)出器采集狀態(tài)變量再用時(shí)序數(shù)據(jù)庫存指標(biāo)、用圖形面板展示。這套組合的優(yōu)勢(shì)在于社區(qū)成熟、指標(biāo)覆蓋廣而且不用額外購買商業(yè)產(chǎn)品。如果團(tuán)隊(duì)規(guī)模小只想快速看個(gè)概覽MySQL 8.0自帶的performance_schema和sys庫里已經(jīng)有非常豐富的等待事件和診斷信息不需要額外裝東西。5.2 基準(zhǔn)測(cè)試與容量規(guī)劃讓數(shù)據(jù)說話優(yōu)化到底有沒有效果不能靠感覺說好像快了一點(diǎn)要用數(shù)據(jù)說話。壓測(cè)工具上我經(jīng)常用的是sysbench它可以模擬OLTP讀寫混合負(fù)載在優(yōu)化前后分別跑一輪對(duì)比QPS和平均延遲。操作流程基本是幾步先準(zhǔn)備測(cè)試表和測(cè)試數(shù)據(jù)然后分別執(zhí)行只讀、只寫、讀寫混合的壓力測(cè)試記錄結(jié)果。壓測(cè)時(shí)要注意數(shù)據(jù)量不能只是幾百條至少上百萬行才有參考意義否則索引、緩沖池等機(jī)制根本來不及發(fā)揮作用。容量規(guī)劃這塊也能從壓測(cè)里找到線索。假設(shè)單機(jī)壓測(cè)出來能扛1500 QPS業(yè)務(wù)高峰期峰值是800 QPS那當(dāng)前余量充足如果已經(jīng)到1200 QPS了就該提前準(zhǔn)備擴(kuò)容方案或者優(yōu)化熱點(diǎn)。壓測(cè)結(jié)果建議記錄成表格每次調(diào)整完配置或索引都重新測(cè)一輪長期下來就知道系統(tǒng)的性能拐點(diǎn)在哪里了。把歷史壓測(cè)數(shù)據(jù)保存下來對(duì)后續(xù)做資源預(yù)算和新版本評(píng)估都很有價(jià)值。5.3 我自己的巡檢清單每周一查風(fēng)險(xiǎn)前置最后分享一份我的每周巡檢習(xí)慣幾分鐘能跑完但對(duì)穩(wěn)定性的幫助非常大。我會(huì)固定檢查幾項(xiàng)內(nèi)容一是配置參數(shù)和實(shí)際使用的偏差。線上機(jī)器跑了幾個(gè)月當(dāng)時(shí)設(shè)的innodb_buffer_pool_size可能已經(jīng)不夠了但沒人注意到。用狀態(tài)變量對(duì)比一下內(nèi)存和磁盤的使用趨勢(shì)就能發(fā)現(xiàn)該擴(kuò)容的擴(kuò)容、該調(diào)參的調(diào)參。二是慢查詢?nèi)罩镜内厔?shì)。對(duì)比這周和上周同期的慢查詢數(shù)量如果穩(wěn)步上升趕緊看新增的慢SQL是什么如果突然下降也要想想是不是業(yè)務(wù)量下降了或者高峰錯(cuò)開了。沒有任何波動(dòng)的慢查詢量反而可能是日志輪轉(zhuǎn)沒生效。三是鎖等待和死鎖計(jì)數(shù)。查SHOW GLOBAL STATUS LIKE Innodb_row_lock%;和SHOW GLOBAL STATUS LIKE Innodb_deadlocks;這些計(jì)數(shù)一直往上漲說明并發(fā)沖突在增加需要翻一眼最近的鎖等待視圖。四是磁盤空間和binlog大小。binlog增長過快通常意味著有大批量更新或者長時(shí)間未處理的事務(wù)磁盤如果被binlog塞滿MySQL會(huì)直接停止寫入這比慢查詢更致命。五是緩沖池的命中率和臟頁刷新情況。命中率低可以繼續(xù)調(diào)大緩存臟頁比例長期高說明寫入能力強(qiáng)但刷盤能力不足有可能I/O子系統(tǒng)已經(jīng)達(dá)到瓶頸。這一套巡檢做下來大多數(shù)問題都能在變成事故之前被攔截住。優(yōu)化MySQL 8.0不是一次性工作它更像一個(gè)跟業(yè)務(wù)共同演進(jìn)的循環(huán)壓測(cè)、監(jiān)控、發(fā)現(xiàn)問題、再優(yōu)化循環(huán)往復(fù)。最后再分享一個(gè)心得遇到性能問題我總會(huì)先看索引和SQL再看配置最后才懷疑硬件。很多看似配置不夠的場(chǎng)景其實(shí)是索引設(shè)計(jì)不合理導(dǎo)致的假象。你在配置上花再多精力也不如先把最耗時(shí)的那幾條SQL拎出來分析一遍。MySQL 8.0本身的優(yōu)化器已經(jīng)夠強(qiáng)了我們做優(yōu)化的主要工作就是給它提供正確的索引和干凈的數(shù)據(jù)環(huán)境剩下的它會(huì)自己搞定。