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