1. 一條 UPDATE 語句的“全景路線圖”——先建立整體認知1.1 為什么值得把一個 UPDATE 的執(zhí)行過程單獨拉出來聊先說個我踩過的坑。早年維護一個訂單系統(tǒng)某天線上突然出現(xiàn)大量Lock wait timeout exceeded一查全是同一條 UPDATE 語句。當(dāng)時的第一反應(yīng)是“是不是索引沒建”但 explain 看下來走了索引于是開始懷疑參數(shù)、懷疑連接池折騰了大半天最后才發(fā)現(xiàn)問題出在“這條 UPDATE 自己寫的子查詢里有一個全表掃描”把整張表的行都鎖住了。從那以后我就意識到如果你僅僅把 UPDATE 當(dāng)成“改一行數(shù)據(jù)的語法”遇到線上問題就會非常被動。MySQL 8.0 是目前生產(chǎn)環(huán)境使用最廣的版本之一很多細節(jié)和 5.7 相比有調(diào)整比如默認字符集變了、WITH語法更成熟、優(yōu)化器成本模型更細膩、undo 和 redo 的機制也有重構(gòu)。但 UPDATE 的骨架邏輯是穩(wěn)定且經(jīng)典的先定位要改的行再加鎖然后修改最后提交或回滾。這四個階段聽上去簡單實際執(zhí)行過程中牽扯到 SQL 解析、權(quán)限校驗、優(yōu)化器選路、存儲引擎加鎖、binlog 與 redo log 配合、主從同步等一長串環(huán)節(jié)。任何一個環(huán)節(jié)出問題表象都可能只是“Update 很慢”或者“Update 報錯”但根因可能千差萬別。這篇文章不打算堆砌抽象的架構(gòu)名詞而是帶著一條具體的 UPDATE 語句從客戶端發(fā)起到最終落盤走一遍 MySQL 8.0 的完整旅程。每一步都會講清楚MySQL 在這個階段做了什么為什么要這么做以及生產(chǎn)環(huán)境中常見的坑在哪里。1.2 先給整條執(zhí)行鏈路畫個輪廓如果只說“執(zhí)行一條 UPDATE”很多人腦子里只有一句話“UPDATE t SET namexx WHERE id1然后行數(shù)據(jù)變了?！闭鎸嵡闆r當(dāng)然沒這么簡單。我在排查問題時習(xí)慣把整個過程切分成幾個階段連接與通信階段客戶端把 SQL 文本發(fā)給 MySQL ServerMySQL 分配線程、初始化上下文。解析與預(yù)處理階段把 SQL 字符串變成 MySQL 認識的內(nèi)部結(jié)構(gòu)檢查表、列是否存在權(quán)限是否足夠。優(yōu)化階段決定用哪個索引、按什么順序掃描、如何做連接。執(zhí)行階段調(diào)用存儲引擎接口定位記錄加鎖讀取舊值寫入新值生成 undo 和 redo 日志。提交階段完成 binlog 和 redo log 的兩階段提交釋放鎖返回客戶端影響行數(shù)。后面的內(nèi)容就按這個順序展開。這樣即便以后遇到 UPDATE 相關(guān)問題也能先在腦子里定位“問題可能出在第幾步”再針對性去查。2. 執(zhí)行前的第一道坎SQL 解析與預(yù)處理2.1 詞法分析、語法分析到底在做什么當(dāng)客戶端把“UPDATE t SET namexx WHERE id1”這段文本發(fā)給 MySQL 時服務(wù)端首先不是急著找數(shù)據(jù)而是先“讀題”。MySQL 的解析器會把字符串拆成一個個 Token比如 UPDATE、t、SET、name、、xx、WHERE、id、、1。這一步叫詞法分析。然后進入語法分析MySQL 會根據(jù)預(yù)定義的語法規(guī)則把這些 Token 組裝成語法樹。我在教學(xué)時經(jīng)常用一句話概括解析器只關(guān)心“這句話符不符合 SQL 語法”完全不關(guān)心表里有沒有數(shù)據(jù)。比如你把條件寫成WHERE id1x并且這一列是整數(shù)類型解析階段不會報錯真正執(zhí)行時才會報類型轉(zhuǎn)換或數(shù)據(jù)轉(zhuǎn)換的問題。再比如你寫成UPDATE t SET namexx WHERE id1 AND這里語法都不完整解析階段就會被直接攔住報You have an error in your SQL syntax。這類錯誤定位最簡單看錯誤信息里提示的“near”關(guān)鍵字就能找到問題位置。MySQL 8.0 在解析階段一個值得提的改動是對WITH子句公共表表達式的支持更完善了。以前 5.7 及更早版本里UPDATE配合子查詢寫法受限較多8.0 里WITH ... UPDATE是合法寫法這讓復(fù)雜的關(guān)聯(lián)更新語句表達能力更強。但有一點要注意WITH子句可以被優(yōu)化器物化也可以被合并到主查詢中這取決于成本估算。如果物化后的臨時表非常大反而可能導(dǎo)致 UPDATE 變慢。后面優(yōu)化器部分會細說。2.2 預(yù)處理檢查表、列和權(quán)限語法樹生成后MySQL 會進入預(yù)處理階段resolve 階段。這個階段的工作包括解析表名和列名確認這些對象在數(shù)據(jù)庫中真實存在。對星號*進行展開。雖然 UPDATE 一般不直接寫SELECT *但如果 SET 或子查詢里出現(xiàn)*這里會展開成具體列。校驗權(quán)限。用戶是否有這張表的 UPDATE 權(quán)限是否對 SET 涉及的列有更新權(quán)限是否對 WHERE 條件里涉及的列有 SELECT 權(quán)限。很多人對權(quán)限校驗不敏感覺得“反正我是 root不會碰到”。但在生產(chǎn)環(huán)境里業(yè)務(wù)賬號通常是最小權(quán)限。我見過一個真實案例某個報表賬號能查數(shù)據(jù)也能執(zhí)行 UPDATE但 UPDATE 語句里帶了一個子查詢而子查詢引用了另一張業(yè)務(wù)表該賬號對這張表沒有 SELECT 權(quán)限結(jié)果報錯SELECT command denied to user。從報錯信息看明明是在執(zhí)行 UPDATE卻被拒絕在 SELECT 權(quán)限上很多人會懵。理解了預(yù)處理階段在解析時就會校驗子查詢涉及的所有對象權(quán)限這個問題就很容易解釋了。預(yù)處理階段還有一個容易忽略的細節(jié)列的可見性。MySQL 8.0 里如果表上建了不可見列INVISIBLE普通的 SELECT 不會顯示該列但 UPDATE 如果顯式指定列名去更新它是可以的。如果你用的是UPDATE t SET col ...這種寫法MySQL 在預(yù)處理階段就會對列名做精確解析列不存在會直接報Unknown column。這一類錯誤通常不會拖到執(zhí)行階段才暴露。2.3 預(yù)處理階段容易踩的隱式類型轉(zhuǎn)換坑預(yù)處理階段除了檢查對象和權(quán)限還會做一部分類型推導(dǎo)和隱式轉(zhuǎn)換的準(zhǔn)備。舉個例子執(zhí)行UPDATE t SET namexx WHERE id1如果id是整數(shù)類型字符串1會轉(zhuǎn)換為數(shù)字 1。這本身沒問題但如果你寫的是WHERE id1abcMySQL 在比較時會把1abc轉(zhuǎn)換成 1行為可能和你預(yù)期的完全不一樣。我碰到過一個典型事故某張表的 user_id 是 varchar 類型但存的內(nèi)容是純數(shù)字比如1001、1002。有人 UPDATE 時條件寫成WHERE user_id1001MySQL 會把字段值轉(zhuǎn)成數(shù)字做比較由于字符串轉(zhuǎn)數(shù)字時會忽略后面的非數(shù)字字符看似能匹配到但一旦表中存在類似1001abc這樣的臟數(shù)據(jù)也會被誤匹配導(dǎo)致更新行數(shù)超出預(yù)期。這類問題在預(yù)處理階段不會暴露但在執(zhí)行階段會造成“影響行數(shù)異?！迸挪槠饋肀日Z法錯誤痛苦得多。所以在寫 UPDATE 時條件列的類型一定要和字段類型完全對齊能用字符串就用字符串能用數(shù)字就用數(shù)字盡量不要依賴隱式轉(zhuǎn)換。優(yōu)化器在做隱式轉(zhuǎn)換時通常也會放棄索引這一點放在優(yōu)化器部分再展開。3. 優(yōu)化器你的 UPDATE 為什么慢在這里就決定了3.1 從 SQL 文本變成執(zhí)行計劃如果說解析階段是“讀題”優(yōu)化器階段就是“決定用哪種方式做題”。MySQL 的優(yōu)化器是一個基于成本的優(yōu)化器CBOCost-Based Optimizer它的核心思路是根據(jù)表的統(tǒng)計信息估算各種執(zhí)行路徑的成本選擇成本最低的路徑。對 UPDATE 語句來說優(yōu)化器要考慮的事情比 SELECT 多一些因為 UPDATE 最終需要定位到具體記錄并修改如果走全表掃描就是逐行判斷 WHERE 條件如果走索引就是先根據(jù)索引找到目標(biāo)記錄再回表讀取完整行。優(yōu)化器的核心決策點包括選擇哪個索引。WHERE 條件里有多個字段是走單列索引還是走聯(lián)合索引還是干脆全表掃描。連接順序。UPDATE 如果帶有子查詢或關(guān)聯(lián)表比如UPDATE t1 JOIN t2 ON ... SET t1.at2.b WHERE ...優(yōu)化器要決定先驅(qū)動哪張表。子查詢的處理方式。是物化成臨時表還是改寫成 semi-join還是直接嵌套執(zhí)行。我平時排查 UPDATE 性能問題第一步永遠是EXPLAIN UPDATE ...看 type 列和 key 列。如果在 type 列看到ALL而表數(shù)據(jù)量又很大基本可以斷定這條 UPDATE 會掃描全表不僅慢而且會鎖住大量行。注意MySQL 8.0 中EXPLAIN UPDATE是支持的但EXPLAIN ANALYZE只支持 SELECT這一點別搞混了。如果你想分析 UPDATE 的真實執(zhí)行耗時和行數(shù)一般做法是先把 WHERE 條件拿出來改成SELECT COUNT(*)去看掃描行數(shù)或者用 performance_schema 里的事件統(tǒng)計。3.2 優(yōu)化器選擇索引時的一個隱性成本回表舉個簡單例子。表結(jié)構(gòu)如下CREATE TABLE orders ( id bigint unsigned NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_order_no (order_no) ) ENGINEInnoDB;執(zhí)行UPDATE orders SET status1 WHERE user_id10086 AND status0。優(yōu)化器面前有兩條路走idx_user_id索引找到所有user_id10086的記錄回表讀完整行再判斷status0匹配成功則更新?；蛘咧苯尤頀呙柚鹦信袛?。如果user_id10086的訂單只有 3 條而全表有 1000 萬行走索引顯然劃算成本模型會給索引路徑一個低得多的成本值最終選擇索引。但這里有個細節(jié)如果user_id10086的訂單有 50 萬行而全表 1000 萬行且status0的比例很低比如只有 1%優(yōu)化器估算時如果把二級索引回表的成本算得很高可能會選擇全表掃描。全表掃描意味著 InnoDB 要掃 1000 萬行每行都判斷條件雖然只更新 5000 行但加鎖范圍幾乎是全表這是生產(chǎn)環(huán)境最怕看到的場景。優(yōu)化器走idx_user_id時還會做一個“回表數(shù)量”的估算。這個估算依賴兩個統(tǒng)計信息索引的區(qū)分度和表的行數(shù)。如果統(tǒng)計信息不準(zhǔn)確優(yōu)化器就可能做出錯誤選擇。MySQL 8.0 中可以通過ANALYZE TABLE更新統(tǒng)計信息也可以調(diào)整innodb_stats_persistent和innodb_stats_auto_recalc參數(shù)來控制自動更新策略。遇到“明明有索引卻走了全表”的情況先別急著罵優(yōu)化器跑一次ANALYZE TABLE再看執(zhí)行計劃大概率能解決。3.3 關(guān)聯(lián)更新語句的執(zhí)行計劃比單表更新更容易翻車生產(chǎn)環(huán)境里真正麻煩的 UPDATE往往是多表關(guān)聯(lián)更新比如UPDATE orders o JOIN users u ON o.user_id u.id SET o.status 1, o.receiver_name u.name WHERE u.level 3;優(yōu)化器要決定先用users表過濾出 level3 的用戶再關(guān)聯(lián)orders還是反過來。這個決策直接影響性能。通常的經(jīng)驗是先用小表作為驅(qū)動表再去大表里查匹配行。但優(yōu)化器是否真的這么做取決于統(tǒng)計信息。我在實際排障中遇到過一種情況users表只有 5000 行orders表有 3000 萬行按常理應(yīng)該先掃 users再走 orders 的 user_id 索引。但因為 users 表某次批量導(dǎo)入后沒有更新統(tǒng)計信息MySQL 以為 users 表有 500 萬行優(yōu)化器一算成本決定反過來先掃 orders 表結(jié)果一條 UPDATE 跑了十幾分鐘鎖了一堆行。當(dāng)時就是用ANALYZE TABLE users解決了問題。從這個案例可以得出一個結(jié)論多表關(guān)聯(lián) UPDATE 的執(zhí)行計劃不穩(wěn)定因為它依賴多個表的統(tǒng)計信息。避免這種不確定性的最佳方式是盡量改成“先 SELECT 出主鍵列表再逐批 UPDATE”的寫法或者在業(yè)務(wù)層分步執(zhí)行。雖然代碼會多一點但執(zhí)行路徑完全可控鎖粒度也更小。3.4 優(yōu)化器對“影響行數(shù)”的估算與真實數(shù)據(jù)的偏差還有一個影響優(yōu)化器判斷的因素是“影響行數(shù)”。如果優(yōu)化器認為某條 UPDATE 會影響 90% 的行它可能選擇全表掃描而不是索引因為全表掃描在這種情況下反而更高效。但優(yōu)化器估算的前提是列的數(shù)據(jù)分布均勻。如果表里有一條 SQL 的 WHERE 條件WHERE statusa而status字段 99% 的行都是a另有 1% 是b但統(tǒng)計信息很久沒更新直方圖信息沒有優(yōu)化器可能不知道a占大頭就會誤判。MySQL 8.0 從 8.0.2 開始支持直方圖Histogram這是一個重要的能力。通過直方圖優(yōu)化器可以更準(zhǔn)確地估算不同值的選擇性尤其是在沒有索引的列上。如果更新條件經(jīng)常落在某些非索引列上可以給這些列建立直方圖ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;直方圖不是索引不參與索引選擇但可以幫助優(yōu)化器在估算掃描行數(shù)時更準(zhǔn)確避免因統(tǒng)計偏差導(dǎo)致執(zhí)行計劃劣化。4. 執(zhí)行器與存儲引擎UPDATE 真正“動手”的階段4.1 執(zhí)行器如何與 InnoDB 協(xié)作優(yōu)化器生成執(zhí)行計劃后就把控制權(quán)交給執(zhí)行器。執(zhí)行器負責(zé)調(diào)用存儲引擎的接口逐條讀取記錄判斷條件發(fā)出修改指令。InnoDB 在收到指令后真正承擔(dān)了存儲層面的工作讀頁、定位記錄、加鎖、寫 undo、寫 redo。這里要先說一個容易誤解的點UPDATE 并不是先執(zhí)行 DELETE 再執(zhí)行 INSERT而是原地更新記錄。InnoDB 在更新時會先找到目標(biāo)記錄的聚簇索引記錄嘗試在原有位置上進行更新。如果更新導(dǎo)致記錄大小變化超過頁內(nèi)可用空間InnoDB 可能會把記錄遷移到新位置這時會留下舊記錄的“刪除標(biāo)記”并插入新記錄。從宏觀表現(xiàn)看類似 deleteinsert但內(nèi)部機制不同。當(dāng) WHERE 條件命中的是二級索引時執(zhí)行器會先通過二級索引找到主鍵值再回到聚簇索引上讀取完整記錄。這就是“回表”?；乇砹鞒淘?UPDATE 里比 SELECT 更敏感因為回表過程不僅要讀還要對目標(biāo)記錄加鎖。如果條件命中的二級索引區(qū)分度很低比如status0命中了 50 萬行InnoDB 會逐行回表并逐行加鎖鎖的范圍展開非常大并發(fā)環(huán)境下很容易造成鎖等待。4.2 InnoDB 的鎖機制這條 UPDATE 會鎖住哪些行鎖是 UPDATE 執(zhí)行中最關(guān)鍵的機制也是 DBA 排障時最難受的部分。InnoDB 支持多種鎖我通常把它們分成幾個維度來記按粒度行鎖Record Lock、間隙鎖Gap Lock、臨鍵鎖Next-Key Lock、表鎖意向鎖等。按模式共享鎖S、排他鎖X、意向共享鎖IS、意向排他鎖IX。對 UPDATE 來說InnoDB 會在匹配到的記錄上加排他鎖。但問題來了InnoDB 在 RR可重復(fù)讀隔離級別下為了防止幻讀會在掃描到的范圍上額外加間隙鎖或臨鍵鎖。舉個例子表里id有 1、5、10 三條記錄執(zhí)行UPDATE t SET namexx WHERE id6;在 RR 隔離級別下InnoDB 會鎖住(5, 10)這個區(qū)間也就是所謂的“間隙鎖”。哪怕沒有任何id6的記錄其他事務(wù)想插入id7的記錄也會被阻塞。很多人不理解明明 UPDATE 沒更新任何行為什么還會鎖等待答案就是間隙鎖在起作用。再比如最常見的條件UPDATE t SET namexx WHERE id5;此時 InnoDB 會加 Next-Key Lock鎖住的范圍是(1, 5]這個左開右閉區(qū)間。也就是說其他事務(wù)想插入id2的記錄會被阻塞因為id2落在(1,5]區(qū)間內(nèi)。具體表現(xiàn)和索引上有哪些記錄有關(guān)系但原理就是如此。在 RC讀已提交隔離級別下InnoDB 只加 Record Lock不加 Gap Lock所以鎖粒度小很多這也是為什么很多高并發(fā)系統(tǒng)會主動把隔離級別設(shè)為 RC。代價是 binlog 必須使用 ROW 格式并且無法依賴數(shù)據(jù)庫層面的間隙鎖來防止幻讀。生產(chǎn)環(huán)境里如果業(yè)務(wù)場景允許將隔離級別從 RR 調(diào)整為 RC是緩解 UPDATE 鎖競爭的一個常見手段。4.3 加鎖與 SQL 執(zhí)行順序的細節(jié)還有一點很值得注意InnoDB 加鎖的順序和更新數(shù)據(jù)的順序并不完全一致。InnoDB 在執(zhí)行 UPDATE 時先根據(jù)二級索引找到主鍵再回聚簇索引讀取記錄加鎖是在聚簇索引記錄上完成的。這意味著如果一條 UPDATE 走了二級索引它可能先對二級索引記錄加鎖準(zhǔn)確說是對索引讀路徑加鎖再回表對聚簇索引記錄加鎖。如果二級索引的鍵值本身也要被更新比如UPDATE t SET status1 WHERE status0status是二級索引列InnoDB 會采用“先插入新記錄、再刪除舊記錄”的方式來維護索引這個過程中新插入的索引記錄會加鎖舊記錄的刪除標(biāo)記也會持有鎖邏輯。這帶來一個實際經(jīng)驗如果你 UPDATE 的列恰好是二級索引列鎖競爭往往比更新非索引列更嚴(yán)重因為索引維護涉及更多鎖操作。高并發(fā)更新場景下盡量把 WHERE 條件設(shè)計成主鍵或唯一索引來定位記錄避免通過二級索引大范圍掃描后更新同樣的二級索引列。網(wǎng)上關(guān)于SELECT ... FOR UPDATE、FOR UPDATE SKIP LOCKED的討論很多核心就是鎖粒度的問題。比如LIMIT 1 FOR UPDATE SKIP LOCKED這個組合語義是“跳過已經(jīng)被其他事務(wù)鎖住的行取一條可以鎖定的記錄”。它到底鎖住一條還是整個 WHERE 條件范圍答案是InnoDB 會掃描滿足 WHERE 條件的記錄逐個跳過已被鎖的行直到找到第一條可用的記錄并加鎖然后因為LIMIT 1停止繼續(xù)掃描。所以最終只鎖住一條記錄但掃描過程中可能讀取并跳過大量被鎖的行掃描路徑上的某些鎖判斷會產(chǎn)生額外成本。4.4 redo log、undo log 和兩階段提交UPDATE 修改數(shù)據(jù)后數(shù)據(jù)頁并不會立即刷到磁盤。為了崩潰恢復(fù)和事務(wù)回滾InnoDB 會同時生成兩類日志。undo log記錄“如何撤銷這個修改”用于事務(wù)回滾和 MVCC。比如把name從a改成bundo log 會記錄“原來的值是a”。如果事務(wù)回滾InnoDB 根據(jù) undo log 恢復(fù)舊值。redo log記錄“這個修改做了哪些物理變更”用于崩潰恢復(fù)。比如“把某個數(shù)據(jù)頁的某個偏移量處的字節(jié)從某個值改成某個值”。數(shù)據(jù)庫異常宕機后重啟時通過 redo log 重放未落盤的修改。MySQL 8.0 中redo log 的實現(xiàn)相比 5.7 有改動比如innodb_log_writer_threads等參數(shù)但兩階段提交的框架仍然穩(wěn)定。具體流程是事務(wù)中執(zhí)行 UPDATEInnoDB 將修改寫入 redo log buffer此時狀態(tài)是 Prepare。事務(wù)提交時MySQL Server 將事務(wù)產(chǎn)生的 binlog 事件寫入 binlog 文件并調(diào)用fsync取決于sync_binlog參數(shù)。兩階段提交的第二種InnoDB 將 redo log 從 Prepare 狀態(tài)變?yōu)?Commit 狀態(tài)再次fsync取決于innodb_flush_log_at_trx_commit。這套“先寫 redoPrepare再寫 binlog再提交 redoCommit”的邏輯是為了保證 binlog 和 redo log 的一致性。如果崩潰發(fā)生在 binlog 寫入前事務(wù)回滾如果崩潰發(fā)生在 binlog 寫入后、redo 提交前MySQL 重啟時會根據(jù) binlog 和 redo 的狀態(tài)做判斷保證主從一致。這里經(jīng)常被忽略的是sync_binlog1和innodb_flush_log_at_trx_commit1都是安全配置但每條提交都有兩次fsync小事務(wù)密集寫入的場景下性能會明顯受限。如果業(yè)務(wù)允許丟失少量最近事務(wù)可以適當(dāng)調(diào)整參數(shù)換取性能但這是安全性和性能的權(quán)衡不要在不理解后果的情況下盲目調(diào)參。4.5 影響行數(shù)與返回結(jié)果UPDATE 執(zhí)行完成后MySQL 會給客戶端返回“影響行數(shù)”。默認情況下如果新舊值完全一樣InnoDB 也會報告影響行數(shù)為 0即使匹配到了行。這個行為和 MySQL 的CLIENT_FOUND_ROWS標(biāo)志位有關(guān)如果連接設(shè)置了CLIENT_FOUND_ROWS返回的是“匹配到的行數(shù)”默認則是“實際修改的行數(shù)”。線上遇到過排查問題的人問“為什么 UPDATE 說影響 0 行binlog 里卻能看到這條 UPDATE”這其實是正常的因為 binlog 默認記錄的是整條 UPDATE 語句及其匹配范圍不一定代表實際修改了數(shù)據(jù)。如果在 binlog 里看到大量影響行數(shù)為 0 的 UPDATE反而值得關(guān)注是不是業(yè)務(wù)代碼在重復(fù)執(zhí)行無意義的更新這類“空更新”也會走完整的加鎖、日志流程白白消耗數(shù)據(jù)庫資源。5. 實操一次 UPDATE 執(zhí)行過程中的故障排查實錄5.1 場景一更新不走索引導(dǎo)致鎖等待飆升現(xiàn)象某天監(jiān)控告警information_schema.INNODB_TRX里大量事務(wù)處于LOCK WAIT狀態(tài)等待時間持續(xù)上漲。查看sys.schema_table_lock_waits發(fā)現(xiàn)多條 UPDATE 語句都在等待同一張表的行鎖。排查過程首先抓出阻塞源頭SELECT * FROM performance_schema.data_lock_waits\G;然后根據(jù)BLOCKING_ENGINE_TRANSACTION_ID找到持有鎖的事務(wù)。再把持有鎖的事務(wù)完整 SQL 拿出來和等待中的 SQL 對比發(fā)現(xiàn)持有鎖的事務(wù)執(zhí)行的是UPDATE payment_orders SET status2 WHERE merchant_id333 AND status1;這條語句語義沒問題但merchant_id列上沒有索引而業(yè)務(wù)表已經(jīng) 2000 萬行。執(zhí)行EXPLAIN UPDATE后 type 是ALLrows 估算接近全表。InnoDB 在掃描過程中會把所有已掃描的行都加上鎖所以這條 UPDATE 等于把整張表的寫能力都“凍結(jié)”了。解決方案分兩步先通知業(yè)務(wù)暫停該批量更新然后給merchant_id建索引ALTER TABLE payment_orders ADD INDEX idx_merchant_id (merchant_id);索引建好后同樣的 UPDATE 只命中幾百條記錄鎖范圍大幅縮小。這個案例給我的教訓(xùn)是批量 UPDATE 上線前必須做 EXPLAIN 驗證尤其是 WHERE 條件里的列是否有合適索引。不要假設(shè)“數(shù)據(jù)量小就沒事”生產(chǎn)環(huán)境的數(shù)據(jù)量和你本地測試完全不是一個量級。5.2 場景二并發(fā)更新相同行導(dǎo)致死鎖現(xiàn)象應(yīng)用日志頻繁報Deadlock found when trying to get lock; try restarting transaction且發(fā)生在同一個訂單號的更新上。排查過程死鎖日志查看方式有兩種SHOW ENGINE INNODB STATUS\G里看LATEST DETECTED DEADLOCK部分或者打開innodb_print_all_deadlocks1把所有死鎖打印到錯誤日志。日志里通常包含兩個事務(wù)的 SQL以及每個事務(wù)持有的鎖和等待的鎖。典型場景是事務(wù) A 先更新訂單 1001再更新訂單 1002事務(wù) B 先更新訂單 1002再更新訂單 1001。兩個事務(wù)并發(fā)時各自持有一半的鎖又互相等待對方釋放鎖就形成死鎖。InnoDB 檢測到死鎖后會選擇回滾其中一個事務(wù)讓另一個繼續(xù)。業(yè)務(wù)側(cè)如果沒有完整的事務(wù)重試機制就會看到報錯。這個問題的根治手段并不是去調(diào)數(shù)據(jù)庫參數(shù)而是統(tǒng)一應(yīng)用層獲取鎖的順序。比如所有涉及多行更新的操作都先按主鍵排序再執(zhí)行UPDATE orders SET status1 WHERE id IN (1001, 1002) ORDER BY id;或者業(yè)務(wù)代碼里在事務(wù)開始前先對要操作的訂單號集合做排序保證所有事務(wù)以相同順序加鎖就能有效規(guī)避死鎖。更多時候死鎖發(fā)生的根因是應(yīng)用層邏輯問題而不是數(shù)據(jù)庫本身的 bug。數(shù)據(jù)庫只是把問題暴露了出來。5.3 場景三主從延遲的罪魁禍?zhǔn)资且粭l超大 UPDATE現(xiàn)象從庫延遲持續(xù)增大SHOW REPLICA STATUS里Seconds_Behind_Source不斷上升從庫 CPU 使用率也偏高。在主庫執(zhí)行SHOW PROCESSLIST發(fā)現(xiàn)當(dāng)前有一條 UPDATE 正在執(zhí)行已經(jīng)跑了很久。排查過程這條 UPDATE 本身在主庫也耗時較長但主庫因為并行能力、硬件資源充足業(yè)務(wù)還能忍受到了從庫SQL 線程是單線程回放大事務(wù)延遲就會迅速累積。查看該 UPDATE 的條件和涉及行數(shù)發(fā)現(xiàn)是對一張大表的全量更新比如UPDATE t SET flag1 WHERE flag0涉及 3000 萬行單事務(wù)執(zhí)行整個 redo log 和 binlog 都非常大。這類問題的解決思路業(yè)務(wù)上進行分批更新比如按主鍵范圍每 5 萬行提交一次避免單一大事務(wù)。如果無法改業(yè)務(wù)可以使用pt-osc等工具做在線表結(jié)構(gòu)變更但實際上這種全表 UPDATE 不太適合用工具自動處理還是得改邏輯。從庫并行復(fù)制參數(shù)要合理設(shè)置比如replica_parallel_workers。MySQL 8.0 的 MTS多線程復(fù)制能力比 5.7 更好但大事務(wù)在從庫仍然無法拆分成并行回放因為屬于同一個事務(wù)的事件必須按順序執(zhí)行。這個案例的核心啟示是大批量 UPDATE 看起來只是改數(shù)據(jù)但它產(chǎn)生的日志量、鎖持有時間、從庫回放壓力都可能成為更大范圍事故的導(dǎo)火索。對生產(chǎn)環(huán)境來說控制單條 UPDATE 的影響行數(shù)比追求“一條 SQL 搞定一切”要重要得多。5.4 常見問題速查表現(xiàn)象可能原因快速排查手段解決方向UPDATE 執(zhí)行極慢WHERE 條件無索引、統(tǒng)計信息不準(zhǔn)、鎖等待EXPLAIN 看 type/key/rows查 INNODB_TRX建索引、ANALYZE TABLE、拆分事務(wù)報 Lock wait timeout exceeded其他事務(wù)持鎖未釋放查 performance_schema.data_lock_waits優(yōu)化持鎖事務(wù)、減小事務(wù)范圍、縮短事務(wù)時間報 Deadlock found多事務(wù)加鎖順序不一致SHOW ENGINE INNODB STATUS統(tǒng)一加鎖順序、增加重試機制UPDATE 報權(quán)限錯誤子查詢涉及其他表無 SELECT 權(quán)限查看錯誤信息中表名給賬號授權(quán)或改寫 SQL影響行數(shù)為 0新舊值相同無需處理如需匹配行數(shù)設(shè)置 CLIENT_FOUND_ROWS檢查業(yè)務(wù)邏輯是否存在無意義更新從庫延遲迅速增大大事務(wù)、大 UPDATESHOW REPLICA STATUS 查看耗時分批更新、優(yōu)化單事務(wù)大小修改后數(shù)據(jù)不對隱式類型轉(zhuǎn)換檢查列類型與條件值類型顯式類型匹配避免依賴轉(zhuǎn)換6. 關(guān)于參數(shù)調(diào)優(yōu)與 UPDATE 性能的幾個補充經(jīng)驗6.1 先看業(yè)務(wù)設(shè)計再談參數(shù)調(diào)優(yōu)很多人在優(yōu)化 UPDATE 性能時第一反應(yīng)就是調(diào)innodb_buffer_pool_size或者innodb_flush_log_at_trx_commit。這些參數(shù)當(dāng)然重要但優(yōu)先級一定要放在業(yè)務(wù)設(shè)計之后。我在實際項目中總結(jié)出的順序是先確認 WHERE 條件是否走索引這是性價比最高的優(yōu)化手段。一個合適的索引能讓 UPDATE 從全表掃描變成點查性能提升可能是幾個數(shù)量級。再審視事務(wù)大小。一次 UPDATE 更新的行數(shù)越少鎖持有時間越短沖突概率越低。如果批量更新無法避免就拆分多批次每批加LIMIT或按主鍵范圍限定。然后檢查并發(fā)沖突。如果多個事務(wù)頻繁競爭同一批行即使每條 UPDATE 都很快也會因為等待導(dǎo)致整體吞吐量上不去。最后才輪到參數(shù)調(diào)優(yōu)。盲目調(diào)參可能帶來副作用比如調(diào)大 buffer pool 會占用更多內(nèi)存調(diào)低刷新頻率會提高崩潰丟失數(shù)據(jù)的風(fēng)險。6.2 與 UPDATE 強相關(guān)的幾個關(guān)鍵參數(shù)innodb_lock_wait_timeout默認 50 秒控制事務(wù)等待行鎖的超時時間。調(diào)小可以讓問題更早暴露但業(yè)務(wù)會更容易報錯調(diào)大則可能讓等待堆積到不可控的程度。不建議隨意調(diào)大。binlog_formatMySQL 8.0 默認是 ROW。ROW 格式下binlog 記錄的是每一行變更前后的完整鏡像雖然日志量比 STATEMENT 大但主從數(shù)據(jù)一致性更好。UPDATE 大批量修改時ROW 格式的 binlog 膨脹會非常明顯需要提前規(guī)劃磁盤空間和主從帶寬。innodb_flush_log_at_trx_commit默認 1每次提交都刷 redo log。這個參數(shù)的調(diào)優(yōu)空間一直存在但要想清楚安全性和性能的取舍。tx_isolation8.0 里是transaction_isolation默認 REPEATABLE-READ。如果業(yè)務(wù)可以接受 RC 隔離級別UPDATE 的間隙鎖問題會大大減少。多提一句innodb_buffer_pool_size雖然不直接控制 UPDATE 執(zhí)行速度但 UPDATE 需要讀取目標(biāo)數(shù)據(jù)頁到 buffer pool 中才能修改。如果頁已經(jīng)在內(nèi)存里速度會快很多如果 buffer pool 太小每次都要從磁盤讀取性能自然上不去。通常建議把 buffer pool 設(shè)置為物理內(nèi)存的 60%~75%但也要考慮機器上還有操作系統(tǒng)和其他進程。6.3 一條 UPDATE 語句的“最小化鎖范圍”實踐模板如果你需要更新一批訂單狀態(tài)建議用下面這種可控的批處理方式而不是一條 SQL 掃全表-- 假設(shè)每次更新 1000 條按主鍵順序取 UPDATE orders SET status 2 WHERE status 1 AND id :last_max_id ORDER BY id LIMIT 1000;每次執(zhí)行后記錄:last_max_id為本次更新的最大主鍵值循環(huán)執(zhí)行直到影響行數(shù)為 0。這樣做的好處是單事務(wù)鎖定的行數(shù)有限不會長時間占用大量鎖每批事務(wù)完成后立即提交釋放鎖即使中途出錯也不會因為回滾超大事務(wù)導(dǎo)致長時間不可用。這種寫法在批量清理、批量標(biāo)記、歷史數(shù)據(jù)歸檔等場景中非常實用。6.4 別忽略連接層面的小問題有些 UPDATE 性能問題其實不是 MySQL 本身造成的而是連接層。比如長事務(wù)一直持有事務(wù)未提交連接池里的連接把事務(wù)邊界搞錯了導(dǎo)致一條 UPDATE 在執(zhí)行時事務(wù)還持有之前其他操作留下的鎖。這類問題從 SQL 本身看不出毛病必須檢查應(yīng)用層的事務(wù)管理。我遇到過的最典型情況是Spring 事務(wù)切面配置錯誤導(dǎo)致一個本不該開啟事務(wù)的查詢操作和后面的 UPDATE 被放在同一個事務(wù)里前面的查詢雖然已結(jié)束但事務(wù)一直沒提交持有的一批鎖也一直沒釋放后面的 UPDATE 自然就卡住了。這種問題在代碼 review 時很難發(fā)現(xiàn)但一旦出現(xiàn)會讓人懷疑人生。所以排查 UPDATE 性能問題時除了看數(shù)據(jù)庫側(cè)的執(zhí)行計劃、鎖等待、日志也別忘了檢查應(yīng)用的事務(wù)邊界是否正確。數(shù)據(jù)庫和代碼是配合的關(guān)系任何一端出了問題另一端都會表現(xiàn)異常。最后分享一個小技巧。如果你經(jīng)常需要分析 UPDATE 的加鎖行為可以在測試環(huán)境開啟innodb_status_output_locks1和performance_schemaON然后通過SELECT * FROM performance_schema.data_locks\G查看具體鎖信息這比猜要高效得多。數(shù)據(jù)量越大、并發(fā)越高越要養(yǎng)成“用數(shù)據(jù)說話、用日志定位”的習(xí)慣。SQL 優(yōu)化沒有銀彈但只要把執(zhí)行旅程的每一步都想清楚再奇怪的問題也會變得有跡可循。