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