化實(shí)戰(zhàn)：從慢查詢定位到索引與分庫(kù)分表)
每次接手一個(gè)“MySQL慢得像蝸?！钡呐挪槿蝿?wù)我第一反應(yīng)不是去看服務(wù)器配置也不是去罵產(chǎn)品經(jīng)理又亂寫需求而是先打開慢查詢?nèi)罩竞虴XPLAIN。干這行十年經(jīng)手的MySQL優(yōu)化案例沒(méi)有一千也有八百不管是單表幾十萬(wàn)數(shù)據(jù)的初創(chuàng)項(xiàng)目還是分庫(kù)分表后單表仍上億的成熟業(yè)務(wù)系統(tǒng)最終的性能瓶頸往往都收束到三個(gè)方向索引是否設(shè)計(jì)合理、SQL是否寫得到位、數(shù)據(jù)量大到一定程度后架構(gòu)是否扛得住。這篇文章不會(huì)跟你聊那些“高大上”的底層原理名詞堆砌就實(shí)打?qū)嵉匕阉饕?、SQL調(diào)優(yōu)、分庫(kù)分表這三塊硬骨頭拆開揉碎講清楚每個(gè)方案背后的取舍邏輯和我在項(xiàng)目里驗(yàn)證過(guò)的真實(shí)做法。這篇攻略適合誰(shuí)看如果你剛把業(yè)務(wù)代碼寫完發(fā)現(xiàn)線上數(shù)據(jù)庫(kù)CPU經(jīng)常飆到100%或者查詢接口隨著數(shù)據(jù)量增長(zhǎng)從200ms一路滑到2秒開外又或者你正在為“要不要分庫(kù)分表”這個(gè)問(wèn)題糾結(jié)到失眠——那這篇文章就是寫給你的。我盡量把每個(gè)決策點(diǎn)都還原成“當(dāng)時(shí)我遇到的是什么場(chǎng)景、為什么選這個(gè)方案、踩了哪些坑”而不是給你一堆PPT式的理論框架。1. 先定位慢在哪90%的MySQL性能問(wèn)題都出在同一個(gè)地方我見過(guò)太多團(tuán)隊(duì)一上來(lái)就討論要不要換PostgreSQL、要不要上分布式數(shù)據(jù)庫(kù)結(jié)果我上去一查單表才三千萬(wàn)數(shù)據(jù)連接數(shù)才一兩百根本還沒(méi)到換數(shù)據(jù)庫(kù)的地步純粹是幾條核心SQL沒(méi)寫好索引走了全表掃描。定位慢查詢這件事是后續(xù)所有優(yōu)化的地基地基不穩(wěn)后面所有動(dòng)作都是瞎忙活。1.1 慢查詢?nèi)罩九cEXPLAIN的正確打開方式排查的第一步永遠(yuǎn)是打開慢查詢?nèi)罩?。很多開發(fā)者說(shuō)“我開了啊”結(jié)果一查long_query_time設(shè)置的是默認(rèn)的10秒等于沒(méi)開。這個(gè)閾值我一般建議從0.5秒開始設(shè)先抓出當(dāng)前消耗最高的SQL——晚上用pt-query-digest這類工具一匯總top 10的慢SQL基本就是你要優(yōu)化的大頭。拿到慢SQL之后先別急著看業(yè)務(wù)邏輯直接EXPLAIN看執(zhí)行計(jì)劃。這里有個(gè)新手特別容易忽略的點(diǎn)EXPLAIN看的是預(yù)估執(zhí)行計(jì)劃它告訴你的是優(yōu)化器“打算怎么干”不是“實(shí)際什么表現(xiàn)”。真正要看你得開EXPLAIN ANALYZEMySQL 8.0.18支持它能給出實(shí)際執(zhí)行時(shí)間和掃描行數(shù)做完索引改動(dòng)之后也必須用這個(gè)命令確認(rèn)優(yōu)化效果而不是只看type是不是從ALL變成了ref就以為萬(wàn)事大吉。看執(zhí)行計(jì)劃時(shí)我有一套自己的快速判斷順序type字段從好到差依次是 system const eq_ref ref range index ALL。只要看到ALL基本可以確定這條SQL要全表掃除非表就幾百行否則一定要優(yōu)化。key字段看看實(shí)際用了哪個(gè)索引。經(jīng)常出現(xiàn)的坑是possible_keys里有候選索引但key是NULL——就是優(yōu)化器壓根沒(méi)選你的索引。rows字段預(yù)估掃描行數(shù)。這個(gè)數(shù)值和實(shí)際行數(shù)相差太大通常意味著統(tǒng)計(jì)信息過(guò)期或者WHERE條件的寫法讓優(yōu)化器無(wú)法做準(zhǔn)確估算。Extra字段看到Using filesort和Using temporary這是兩個(gè)危險(xiǎn)信號(hào)一個(gè)說(shuō)明排序沒(méi)有走索引一個(gè)說(shuō)明臨時(shí)表被用到了后面第六節(jié)詳細(xì)講。1.2 從執(zhí)行計(jì)劃到根因判斷一張排查對(duì)照表為了讓你排查起來(lái)更有方向感我把日常最常遇到的執(zhí)行計(jì)劃特點(diǎn)和對(duì)應(yīng)根因整理成一張表。這個(gè)話單是多年實(shí)戰(zhàn)積累下來(lái)的濃縮版排查時(shí)直接對(duì)照著看就行執(zhí)行計(jì)劃特征可能根因優(yōu)先處理建議typeALL, rows 很大沒(méi)建索引或索引失效檢查WHERE和JOIN條件字段建立合適的單列或聯(lián)合索引keyNULL, possible_keys 有值優(yōu)化器放棄索引可能是數(shù)據(jù)分布問(wèn)題或函數(shù)運(yùn)算導(dǎo)致重寫SQL去掉函數(shù)包裹如果實(shí)在無(wú)法避免考慮強(qiáng)制索引Using filesort排序字段和WHERE條件不在同一個(gè)聯(lián)合索引里調(diào)整聯(lián)合索引字段順序讓排序走索引而不是再開一道排序Using temporaryGROUP BY 或 DISTINCT 觸發(fā)了臨時(shí)表重寫查詢或者調(diào)整索引讓分組字段有序rows 誤差超過(guò)10倍統(tǒng)計(jì)信息嚴(yán)重過(guò)期執(zhí)行 ANALYZE TABLE 刷新統(tǒng)計(jì)信息索引選擇性極低重復(fù)率過(guò)高建了索引但區(qū)分度不夠優(yōu)化器不想用換區(qū)分度更高的字段或者用覆蓋索引繞開回表還有一種坑比較隱蔽——索引選擇性誤判。比如性別字段你建了索引結(jié)果優(yōu)化器發(fā)現(xiàn)全表一半是男一半是女它覺(jué)得用這個(gè)索引還不如直接掃全表劃算于是直接放棄索引。遇到這種情況別硬剛優(yōu)化器要么用覆蓋索引帶上查詢需要的其他列讓掃描成本降低要么直接改業(yè)務(wù)查詢邏輯去掉這個(gè)條件。2. 索引為什么失效回表、最左前綴與函數(shù)運(yùn)算的連鎖反應(yīng)索引優(yōu)化是MySQL性能優(yōu)化的第一顆子彈。但很多人建完索引就以為完事了結(jié)果上線后該慢還是慢。索引失效的那幾類典型場(chǎng)景我閉著眼都能背出來(lái)——不是因?yàn)槲矣洃浟枚敲總€(gè)坑我都親自掉進(jìn)去過(guò)又親自爬出來(lái)。2.1 函數(shù)包裹、隱式轉(zhuǎn)換和區(qū)分度問(wèn)題一個(gè)聯(lián)合索引的設(shè)計(jì)案例先看一個(gè)我最近處理的真實(shí)案例。一張訂單表查詢條件是WHERE pay_time BETWEEN ... AND ...我建議開發(fā)在上面建索引他說(shuō)建了但你猜怎么著他是這么寫的WHERE DATE(pay_time) BETWEEN 2024-01-01 AND 2024-01-31。索引是建在pay_time上的但DATE()函數(shù)一包索引直接失效——因?yàn)镸ySQL要先算出DATE(pay_time)的結(jié)果才能去和區(qū)間比較這等于把全部行先計(jì)算一遍那建索引還有什么意義正確的寫法應(yīng)該是WHERE pay_time 2024-01-01 AND pay_time 2024-02-01既覆蓋了整個(gè)一月份的區(qū)間又沒(méi)做任何函數(shù)運(yùn)算索引能完美走range掃描。這條規(guī)則幾乎適用于所有日期范圍查詢也是我審查SQL時(shí)一眼就能掃出來(lái)的問(wèn)題。同樣的道理字段類型不匹配的隱式轉(zhuǎn)換也是索引殺手。比如user_id字段是varchar(32)但你在代碼里傳入的是整數(shù)類型的IDMySQL會(huì)自動(dòng)把字符串轉(zhuǎn)成數(shù)字再比對(duì)。這本來(lái)不算什么大問(wèn)題壞就壞在當(dāng)你拿一個(gè)數(shù)字去和varchar字段比較的時(shí)候MySQL會(huì)先對(duì)所有行的user_id做一次類型轉(zhuǎn)換于是索引全部失效。這類問(wèn)題很難排查因?yàn)樗粓?bào)錯(cuò)執(zhí)行計(jì)劃也不崩但rows行數(shù)就是下不來(lái)。真正設(shè)計(jì)聯(lián)合索引的時(shí)候我養(yǎng)成了三個(gè)習(xí)慣分享出來(lái)供你參考把等值條件放最前面然后才是范圍條件。比如查詢條件是WHERE status1 AND create_time 2024-01-01聯(lián)合索引就應(yīng)該設(shè)計(jì)為(status, create_time)。因?yàn)榈戎禇l件可以精確定位范圍條件只需要在等值定位后的范圍里掃一小段。能用覆蓋索引解決的盡量用覆蓋索引。比如查詢只需要id和status建一個(gè)(status, id)的索引MySQL就能直接從索引里拿數(shù)據(jù)連回表都省了這是最快的一種查詢路徑。控制索引數(shù)量。這塊我必須多說(shuō)兩句。我見過(guò)一個(gè)項(xiàng)目為了一張表建了12個(gè)索引寫入性能慘不忍睹。索引不是越多越好每多一個(gè)索引INSERT和UPDATE就要多維護(hù)一個(gè)B樹。通常單表索引控制在5個(gè)以內(nèi)能復(fù)用聯(lián)合索引的左前綴就絕不重復(fù)建索引。2.2 復(fù)合索引的最左前綴原則一個(gè)字段順序引發(fā)的性能災(zāi)難復(fù)合索引聯(lián)合索引最核心的規(guī)則就是最左前綴原則——MySQL 8.0之前優(yōu)化器只能從左往右匹配索引列你不能跳過(guò)第一列直接用到第二列。這個(gè)我建議你牢記在腦子里因?yàn)閷?shí)際開發(fā)中被它坑過(guò)的案例我至少親手處理過(guò)十次。具體來(lái)說(shuō)假設(shè)你建了復(fù)合索引(a, b, c)WHERE a1能走索引WHERE a1 AND b2能走索引WHERE a1 AND c3只能用到索引的a列c列用不上WHERE b2完全不走這個(gè)索引最經(jīng)典的反例是業(yè)務(wù)需求經(jīng)常按name和create_time查開發(fā)就建了(name, create_time)的索引。但后來(lái)新增了一個(gè)需求只按create_time范圍查詢發(fā)現(xiàn)這條新SQL慢得離譜查看執(zhí)行計(jì)劃居然走了全表掃描。原因就是這個(gè)復(fù)合索引根本幫不上忙——因?yàn)閏reate_time不是第一列只按它查的時(shí)候索引直接報(bào)廢。這時(shí)候正確的做法有三種。第一種是再建一個(gè)(create_time)的單列索引第二種是如果你頻繁需要這個(gè)組合直接正面剛改查詢讓name條件不缺席比如業(yè)務(wù)上允許默認(rèn)帶一個(gè)空串或通配但要注意通配符走不了索引這里不做推薦第三種最優(yōu)雅——把復(fù)合索引設(shè)計(jì)成(create_time, name)既支持按時(shí)間查又支持按時(shí)間姓名查。我強(qiáng)烈推薦第三種因?yàn)閏reate_time通常是范圍條件放在左邊之后右邊接等值條件大多數(shù)情況下都能滿足新老需求。索引失效的場(chǎng)景還有很多比如WHERE name LIKE %張這種前置通配符會(huì)導(dǎo)致索引失效這個(gè)好理解B樹是按前綴有序的你硬要在中間挖一段它沒(méi)法定位還有OR連接的非索引列場(chǎng)景以及SQL里出現(xiàn)!或IS NOT NULL的情況。但歸根結(jié)底你要理解B樹的排序存儲(chǔ)特性就能自己推導(dǎo)出索引什么時(shí)候能用只要是破壞了有序性或者需要全量計(jì)算的場(chǎng)景索引大概率就廢了。3. SQL層面最容易忽略的性能殺手分頁(yè)、JOIN與排序的隱形代價(jià)索引設(shè)計(jì)得再合理SQL寫得不靠譜照樣卡死。這一節(jié)我從最常見的三個(gè)業(yè)務(wù)場(chǎng)景入手分頁(yè)、多表關(guān)聯(lián)和排序分組把隱藏在業(yè)務(wù)代碼背后的性能殺手挨個(gè)揪出來(lái)。3.1 分頁(yè)翻得越深越慢limit深翻頁(yè)問(wèn)題的三種解法做后臺(tái)管理系統(tǒng)的同學(xué)應(yīng)該有切身體會(huì)——導(dǎo)數(shù)據(jù)列表的時(shí)候越往后面翻頁(yè)越慢。比如運(yùn)營(yíng)人員在列表頁(yè)一頁(yè)頁(yè)翻到第200頁(yè)每頁(yè)50條這頁(yè)只顯示第9951到10000條但MySQL干的事是把從101到10000行的所有行先掃出來(lái)然后全部丟掉只把最后50條返回給你。數(shù)據(jù)量小的時(shí)候感覺(jué)不出來(lái)數(shù)據(jù)量上到9000萬(wàn)這種寫法的延遲你根本沒(méi)法接受。排查的時(shí)候往往發(fā)現(xiàn)開發(fā)同學(xué)的SQL是這種形態(tài)SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;這條基本就是深翻頁(yè)臭名昭著的寫法。優(yōu)化方案有三種按優(yōu)先級(jí)推薦方案一延遲關(guān)聯(lián)所有版本通用。先只查id覆蓋索引能快速定位再用這些id去關(guān)聯(lián)查詢完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) t ON o.id t.id;方案二基于游標(biāo)分頁(yè)推薦用在Web端。不是翻頁(yè)跳轉(zhuǎn)而是通過(guò)上一頁(yè)最后一條記錄的ID來(lái)進(jìn)行下一頁(yè)定位SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;用where條件過(guò)濾掉上一頁(yè)已知的最后一條走索引只在數(shù)據(jù)里順序取20條不丟數(shù)據(jù)不重排。缺點(diǎn)是用戶不能直接跳到任意頁(yè)只能在相鄰頁(yè)之間點(diǎn)擊“加載更多”這種交互形式移動(dòng)端和多數(shù)信息流都這么玩因?yàn)樗鼔簻y(cè)下來(lái)性能最穩(wěn)。方案三如果產(chǎn)品必須跳頁(yè)提前算好ID集合一次性查詢。用前端的countoffset換算成已知的主鍵集合然后WHERE id IN (...) LIMIT 20。根據(jù)我的實(shí)測(cè)經(jīng)驗(yàn)方案二在沒(méi)有深翻頁(yè)訴求時(shí)性能最佳方案一在必須保留跳頁(yè)時(shí)性價(jià)比最高。極限情況下同樣的深翻頁(yè)SQL從800ms優(yōu)化到80ms以內(nèi)的都是常規(guī)操作。3.2 JOIN不是不能用但ON條件沒(méi)索引等于災(zāi)難再來(lái)說(shuō)JOIN。很多規(guī)范文檔都會(huì)告訴你“盡量少用JOIN”這句話讓不少新手走入了另一個(gè)極端——把原本一條JOIN拆成三五條SQL在代碼里挨個(gè)查結(jié)果網(wǎng)絡(luò)來(lái)回比以前更慢。我的觀點(diǎn)是JOIN本身不是洪水猛獸關(guān)鍵在JOIN字段是否走索引以及小表驅(qū)動(dòng)大表的原則是否被遵守。MySQL的JOIN執(zhí)行邏輯是這樣的外層驅(qū)動(dòng)表每一行都需要去匹配內(nèi)層表。如果內(nèi)層表的連接字段沒(méi)有索引那就是一次全表掃描乘以驅(qū)動(dòng)表的行數(shù)——這個(gè)代價(jià)是幾何級(jí)增長(zhǎng)的。以一個(gè)業(yè)務(wù)系統(tǒng)為例用戶表幾十萬(wàn)條訂單表上千萬(wàn)條要查某時(shí)間段內(nèi)有訂單的用戶信息。如果JOIN條件是u.id o.user_id但訂單表的user_id沒(méi)建索引每次匹配都要去掃訂單表的一千多萬(wàn)行這條查詢基本就是災(zāi)難現(xiàn)場(chǎng)。建上user_id索引一切迎刃而解。所以我審查SQL的第一原則不是“見了JOIN就皺眉”而是看EXPLAIN里的type只要被驅(qū)動(dòng)表的連接字段是ref或eq_ref級(jí)別JOIN完全可以放心用。另一個(gè)重點(diǎn)是小表驅(qū)動(dòng)大表。MySQL的優(yōu)化器一般會(huì)自動(dòng)選擇小表做驅(qū)動(dòng)表但如果你用什么子查詢包裹的方式去打亂它的統(tǒng)計(jì)幻覺(jué)優(yōu)化器也有可能做出錯(cuò)誤選擇。這時(shí)候STRAIGHT_JOIN可以直接指定驅(qū)動(dòng)順序把大表放到被驅(qū)動(dòng)位置。這個(gè)技巧不常用但當(dāng)你在EXPLAIN里看到驅(qū)動(dòng)表選錯(cuò)的時(shí)候它比硬改SQL簡(jiǎn)單得多。3.3 filesort和臨時(shí)表ORDER BY與GROUP BY為什么會(huì)拖垮查詢ORDER BY最怕的不是排序本身而是排序字段沒(méi)法走索引迫使MySQL把所有結(jié)果集撈出來(lái)再額外執(zhí)行一次內(nèi)存排序或者磁盤排序。EXPLAIN里的Using filesort就是它。在這個(gè)地方聯(lián)合索引的字段順序又體現(xiàn)出關(guān)鍵作用。比如聯(lián)合索引是(status, create_time)當(dāng)查詢條件是WHERE status1 ORDER BY create_time DESCMySQL可以直接按索引順序來(lái)讀不需要額外排序。但如果你把ORDER BY create_time中的create_time放在索引非末尾位置或者排序條件和查詢條件的順序不匹配filesort就出現(xiàn)了。同樣GROUP BY 之所以效率低是因?yàn)樗ǔ０殡S著臨時(shí)表的創(chuàng)建MySQL 8.0之前還會(huì)專門建個(gè)內(nèi)部臨時(shí)表來(lái)存分組中間結(jié)果量大還可能落到磁盤。遇到大表上的GROUP BY我的優(yōu)化順序是先看能否通過(guò)聯(lián)合索引設(shè)計(jì)讓分組字段有序這樣MySQL就可以直接順序掃索引然后順便分組不需要建臨時(shí)表。如果分組字段的基數(shù)很小比如就幾個(gè)枚舉狀態(tài)改用COUNT(DISTINCT ...)配合業(yè)務(wù)側(cè)的Map匯總有時(shí)反而更利落。非要在SQL里GROUP BY把返回字段做得盡量窄只取需要分組的字段和聚合函數(shù)字段減少臨時(shí)表的內(nèi)存壓力。4. 寫入性能與并發(fā)控制的隱形瓶頸不只是慢查詢才需要關(guān)注慢查詢折磨人寫入抖動(dòng)同樣是事故隱患。很多后臺(tái)系統(tǒng)的MySQL優(yōu)化文章通篇講SELECT但一到每天零點(diǎn)定時(shí)任務(wù)跑批、或者業(yè)務(wù)高峰導(dǎo)入數(shù)據(jù)的場(chǎng)景INSERT和UPDATE的性能問(wèn)題能把線上打掛。這一節(jié)聊聊我在處理寫入瓶頸和并發(fā)控制時(shí)常用的兩招批量寫的正確姿勢(shì)和鎖競(jìng)爭(zhēng)的有效規(guī)避。4.1 批量提交與事務(wù)大小的黃金分割點(diǎn)剛工作那會(huì)兒我寫過(guò)一個(gè)批量導(dǎo)入腳本循環(huán)一萬(wàn)條數(shù)據(jù)一條一條INSERT跑了一個(gè)多小時(shí)還沒(méi)跑完。后來(lái)改成一次事務(wù)批量INSERT一千條十分鐘跑完。差異就在于INSERT本身一條條執(zhí)行時(shí)每次都有網(wǎng)絡(luò)往返、事務(wù)提交刷盤的開銷而批量提交可以把這些固定開銷攤薄。但也不是批量越大越好。我有過(guò)一次性INSERT十萬(wàn)條的“翻車”經(jīng)歷——事務(wù)過(guò)大導(dǎo)致undo日志膨脹MVCC機(jī)制要保留舊版本數(shù)據(jù)binlog同步延遲飆升主庫(kù)事務(wù)把從庫(kù)備庫(kù)拖得追不上差點(diǎn)引發(fā)主從切換事故。后來(lái)總結(jié)出的經(jīng)驗(yàn)是單個(gè)事務(wù)控制在1000~5000條范圍或者事務(wù)執(zhí)行時(shí)間控制在1秒以內(nèi)。這個(gè)數(shù)字不是拍腦袋定的而是權(quán)衡了網(wǎng)絡(luò)往返、鎖持有時(shí)長(zhǎng)、binlog體積和回滾成本之后的可接受區(qū)間你可以根據(jù)自己業(yè)務(wù)的寫入量做微調(diào)。預(yù)處理語(yǔ)句綁定參數(shù)值得強(qiáng)烈推薦INSERT INTO orders (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)使用prepared statement批量綁定參數(shù)能避免每條INSERT都做一次SQL解析。在大批量導(dǎo)數(shù)據(jù)場(chǎng)景這個(gè)優(yōu)化效果非常明顯一千萬(wàn)行的導(dǎo)數(shù)任務(wù)從小時(shí)級(jí)縮到十幾分鐘。4.2 行鎖、間隙鎖與死鎖為什么長(zhǎng)事務(wù)比慢SQL更可怕慢SQL至少還能在慢查詢?nèi)罩纠镲@式看到長(zhǎng)事務(wù)才是真正的隱形殺手。事務(wù)不提交它持有的鎖就一直不釋放后續(xù)所有要更新同一條記錄、甚至插入相鄰記錄的事務(wù)都會(huì)排隊(duì)堵住。更隱蔽的是MVCC下的undo日志膨脹長(zhǎng)事務(wù)意味著大量舊版本數(shù)據(jù)不能被purge線程清理這會(huì)導(dǎo)致表空間無(wú)限膨脹查詢還要在版本鏈上往前找正確的版本性能越來(lái)越差。這類長(zhǎng)事務(wù)的來(lái)源十有八九是業(yè)務(wù)代碼在同一個(gè)事務(wù)里調(diào)用了外部接口。例如一個(gè)“創(chuàng)建訂單并扣庫(kù)存”的接口你在Spring的Transactional事務(wù)里調(diào)用支付回調(diào)接口等外部響應(yīng)等了3秒。這三秒里整個(gè)訂單表相關(guān)范圍的鎖都捏在你手里所有同類請(qǐng)求全部排隊(duì)阻塞線上就炸鍋了。死鎖同樣值得反復(fù)排查。我處理過(guò)最典型的死鎖場(chǎng)景是兩個(gè)事務(wù)都執(zhí)行“先更新主表再更新明細(xì)表”但順序恰好反過(guò)來(lái)兩邊互相等鎖MySQL檢測(cè)到死鎖后就隨機(jī)選一個(gè)事務(wù)回滾。這種死鎖在高并發(fā)寫入下特別常見。規(guī)避方法不復(fù)雜所有事務(wù)按照固定順序訪問(wèn)資源比如先主表后明細(xì)表或者把相關(guān)行一次性用SELECT ... FOR UPDATE鎖住。經(jīng)驗(yàn)之談線上環(huán)境一定要監(jiān)控長(zhǎng)事務(wù)和鎖等待事件。information_schema.innodb_trx這張表可以直接查出來(lái)哪些事務(wù)超過(guò)3秒還沒(méi)提交配套sys.innodb_lock_waits能定位鎖等待的根因。趁事務(wù)還沒(méi)膨脹到釀成大禍之前把它殺在萌芽階段是DBA和資深后端必須建立的夜間值班基本功。5. 分庫(kù)分表的前提判斷數(shù)據(jù)量大不等于必須要分講到分庫(kù)分表我先潑一盆冷水分庫(kù)分表是最后的手段不是第一選擇。很多人看數(shù)據(jù)量到了幾千萬(wàn)就喊著要分庫(kù)分表結(jié)果分完以后跨庫(kù)JOIN、分布式事務(wù)、分布式ID、擴(kuò)容數(shù)據(jù)遷移樣樣都是新坑問(wèn)題沒(méi)解決反而更多了。我的經(jīng)驗(yàn)是在動(dòng)手分庫(kù)分表之前你先把下面這些事做扎實(shí)了再說(shuō)。5.1 什么數(shù)據(jù)量級(jí)才需要分庫(kù)分表三大前置判斷標(biāo)準(zhǔn)判斷要不要分庫(kù)分表不是一個(gè)數(shù)字閾值就拍板的我通常會(huì)看三個(gè)維度同時(shí)亮紅燈才啟動(dòng)方案單表數(shù)據(jù)量超過(guò)MySQL的舒適區(qū)。InnoDB的B樹在單表數(shù)據(jù)量一億以內(nèi)理論上都能扛住但實(shí)際運(yùn)維中發(fā)現(xiàn)當(dāng)表超過(guò)2000萬(wàn)行且索引深度超過(guò)3層時(shí)查詢延遲開始明顯可見地退化。當(dāng)然這和數(shù)據(jù)寬度、磁盤IO性能都有關(guān)系不是絕對(duì)判斷標(biāo)準(zhǔn)。單庫(kù)的并發(fā)寫入能力成為瓶頸。比如數(shù)據(jù)庫(kù)連接數(shù)打滿每秒寫入行數(shù)受制于磁盤IOPS即使拆索引、優(yōu)化SQL也提不上去。數(shù)據(jù)增長(zhǎng)趨勢(shì)是持續(xù)且陡峭的。如果只是短期活動(dòng)帶來(lái)的一波數(shù)據(jù)量活動(dòng)結(jié)束就平穩(wěn)了那沒(méi)必要為了峰值去做永久性的分庫(kù)分表改造。我見過(guò)一個(gè)日志表三個(gè)月時(shí)間從2億漲到15億這種就屬于必須分另一種是用戶表全年就漲個(gè)2000萬(wàn)說(shuō)實(shí)話單表再撐幾年問(wèn)題不大。如果只占了第一點(diǎn)單表數(shù)據(jù)量大但讀多寫少優(yōu)先考慮冷熱分離歸檔。比如訂單表把一年前的訂單遷移到一個(gè)獨(dú)立的歸檔庫(kù)或者只讀歷史表主表數(shù)據(jù)量直接砍掉70%查詢性能秒回。占有便宜又見效快完全不需要分庫(kù)分表。如果第二、第三點(diǎn)同時(shí)出現(xiàn)比如訂單系統(tǒng)日訂單量突破500萬(wàn)單、流水表半年就過(guò)10億行那才是分庫(kù)分表的啟動(dòng)時(shí)機(jī)可以進(jìn)入下一節(jié)。5.2 分片鍵選錯(cuò)是最大的災(zāi)難訂單表案例的教訓(xùn)分庫(kù)分表的第一步不是選中間件而是選分片鍵。這里有一條血的教訓(xùn)分片鍵必須滿足80%以上的核心查詢能帶著它走否則就是給自己埋雷。以電商訂單系統(tǒng)為例主流分片鍵是user_id或order_id。選user_id的好處是用戶能查到自己的全部訂單天然落在同一個(gè)分片上壞處是通過(guò)order_id直接查詢?cè)斍榫吐闊┝恕唵翁?hào)不知道屬于哪個(gè)用戶得路由到所有分片去查一次點(diǎn)開詳情頁(yè)所有分片都得掃一遍。選order_id作為分片鍵則剛好反過(guò)來(lái)通過(guò)訂單號(hào)查詳情很快但查用戶某個(gè)時(shí)間段的訂單列表就麻煩了。工程上的常見解法是維護(hù)一張映射表或者在生成order_id時(shí)內(nèi)嵌用戶ID的分片信息讓order_id自身就攜帶分片路由信息。舉個(gè)例子生成訂單號(hào)的時(shí)候把用戶ID的后4位嵌入到訂單號(hào)中這樣拿到order_id算一下就知道去哪個(gè)分片查不需要額外建立映射關(guān)系。分布式ID這塊我強(qiáng)烈建議不要再用數(shù)據(jù)庫(kù)自增ID作為分片表的全局主鍵。因?yàn)榉制竺總€(gè)實(shí)例的自增ID會(huì)重復(fù)必須要引入全局唯一ID生成方案。常見的方案有雪花算法Snowflake、各個(gè)中間件自帶的ID生成器或者用Redis的原子INCR。雪花算法是目前使用最廣泛的方案它的核心是時(shí)間戳機(jī)器ID序列號(hào)每秒能生成幾百萬(wàn)個(gè)不重復(fù)ID而且趨勢(shì)遞增對(duì)數(shù)據(jù)庫(kù)索引友好。5.3 分庫(kù)分表之后這些SQL全都不能用了分庫(kù)分表帶來(lái)的功能損失如果你沒(méi)有足夠的心理和技術(shù)準(zhǔn)備上線后會(huì)讓業(yè)務(wù)方罵娘。我先列一份“黑名單”給你做好準(zhǔn)備全局表JOIN消失了。不能做跨分片的JOIN查詢。以前一條SQL搞定的事現(xiàn)在得代碼里先在各個(gè)分片上分別查完然后在內(nèi)存里做map合并和關(guān)聯(lián)。數(shù)據(jù)量小時(shí)還能接受數(shù)據(jù)量大了這種聚合層邏輯也是性能瓶頸。全局排序分頁(yè)非常痛苦?？绶制鯫RDER BY create_time LIMIT 0, 20你得把所有分片的top N都拉出來(lái)然后在代碼層重新排序截取。更惡心的深翻頁(yè)——你根本沒(méi)法像單表那樣直接跳到第100頁(yè)因?yàn)槊總€(gè)分片都不知道全局的第990到第1000行是哪幾條。這也是為什么分庫(kù)分表之后的產(chǎn)品設(shè)計(jì)大都改成“下拉加載更多”而不是跳頁(yè)。分布式事務(wù)成本高。分片之后跨分片的事務(wù)已經(jīng)超越了MySQL本地事務(wù)的能力你需要引入分布式事務(wù)方案兩階段提交、最終一致性的消息事務(wù)、或者TCC這套復(fù)雜度不是小團(tuán)隊(duì)能輕易駕馭的。所以分庫(kù)分表后第一原則是盡可能把需要事務(wù)的數(shù)據(jù)放到同一個(gè)分片也就是通過(guò)合理選分片鍵來(lái)避免跨分片事務(wù)。這些問(wèn)題沒(méi)辦法完全規(guī)避只能通過(guò)合理的表結(jié)構(gòu)設(shè)計(jì)來(lái)緩解。我見過(guò)的高可用設(shè)計(jì)是把訂單主表按user_id分片訂單明細(xì)表也按user_id分片這樣同一個(gè)用戶的主表記錄和明細(xì)表記錄落在同一個(gè)物理庫(kù)還是可以走本地事務(wù)這是把跨分片事務(wù)扼殺在搖籃里的標(biāo)準(zhǔn)做法。5.4 中間件選型ShardingSphere、MyCat還是自研路由分庫(kù)分表的落地中間件選型決定了你后續(xù)半年的運(yùn)維成本。當(dāng)前主流有三個(gè)方向ShardingSphere推薦首選Apache頂級(jí)項(xiàng)目支持分片、讀寫分離、數(shù)據(jù)加密等多種能力。它的JDBC模式是一個(gè)輕量級(jí)jar包嵌入到應(yīng)用里代碼侵入小、性能損耗低適合Java技術(shù)棧是我們團(tuán)隊(duì)目前的主力方案。MyCat適合做數(shù)據(jù)庫(kù)代理層獨(dú)立部署的中間件服務(wù)對(duì)應(yīng)用透明應(yīng)用不需要改連接方式但它需要額外部署和運(yùn)維一套集群鏈路更長(zhǎng)性能損耗也更大。如果非Java技術(shù)?；蛘呦胍霐?shù)據(jù)庫(kù)中間件這個(gè)方向可以評(píng)估。自研路由適合極簡(jiǎn)場(chǎng)景只在代碼層做一個(gè)簡(jiǎn)單的分片規(guī)則算法比如通過(guò)用戶ID取模路由到不同的庫(kù)。這個(gè)方案性能最好、最可控但只適合分片規(guī)則簡(jiǎn)單、分片數(shù)量固定、不要求彈性擴(kuò)縮容的場(chǎng)景。再?gòu)?fù)雜一點(diǎn)你就得從頭自己解決分布式事務(wù)、平滑擴(kuò)容這些問(wèn)題代價(jià)極高。我在這塊的取舍原則是沒(méi)有完美的中間件適合當(dāng)前團(tuán)隊(duì)規(guī)模和技術(shù)棧的才是最好的。如果團(tuán)隊(duì)只有兩三個(gè)人我建議直接用ShardingSphere-JDBC學(xué)習(xí)成本低出了問(wèn)題社區(qū)答案也多如果公司數(shù)據(jù)團(tuán)隊(duì)規(guī)范成熟、對(duì)數(shù)據(jù)庫(kù)有強(qiáng)管控需求MyCat的代理模式會(huì)讓DBA更安心。5.5 數(shù)據(jù)遷移與擴(kuò)容從停機(jī)遷移到平滑擴(kuò)容的演進(jìn)分庫(kù)分表之后遲早要面對(duì)擴(kuò)容的問(wèn)題。假設(shè)初始分成了16個(gè)分片兩年后數(shù)據(jù)量漲到需要32個(gè)分片怎么辦這時(shí)候最忌諱的是用hash(user_id) % 32這種規(guī)則因?yàn)橐坏┌讶∧；鶖?shù)從16改成32幾乎所有數(shù)據(jù)都要重新分布遷移成本讓人崩潰。我建議在設(shè)計(jì)分片規(guī)則時(shí)就直接避免這個(gè)問(wèn)題。兩個(gè)常用方案一致性哈希。它把哈希值空間組織成環(huán)數(shù)據(jù)落在哪個(gè)分片由它沿環(huán)順時(shí)針找到的第一個(gè)節(jié)點(diǎn)決定。擴(kuò)容的時(shí)候只需要遷移部分分片上的數(shù)據(jù)到新節(jié)點(diǎn)不用全部重排。運(yùn)維成本要低很多。按時(shí)間分片歷史數(shù)據(jù)冷處理。對(duì)于日志、流水類數(shù)據(jù)按月份或季度分片。過(guò)了當(dāng)前月份的數(shù)據(jù)不會(huì)再有寫入完全不需要遷移。查詢的時(shí)候帶上月份條件就能定位到對(duì)應(yīng)分片讀取性能和擴(kuò)容便利性都有保障。這兩個(gè)方案選哪個(gè)取決于業(yè)務(wù)特性核心還是要前置考慮別等數(shù)據(jù)量大了再改路由規(guī)則那真是所有DBA的噩夢(mèng)。6. 優(yōu)化效果的一次完整復(fù)盤一個(gè)日訂單量百萬(wàn)級(jí)系統(tǒng)的改造路徑理論講了這么多還是給你看一個(gè)我印象比較深的完整改造案例。這套系統(tǒng)我接手時(shí)已經(jīng)線上運(yùn)行了兩年每天新增訂單量在100萬(wàn)行左右數(shù)據(jù)庫(kù)單表已經(jīng)突破1.8億行高峰期核心查詢接口平均響應(yīng)時(shí)間從最初的300ms惡化到3秒左右時(shí)不時(shí)還會(huì)因?yàn)槁樵兺峡暹B接池。6.1 從200ms到20ms索引重建與SQL重寫的組合拳接手第一階段我根本沒(méi)考慮分庫(kù)分表而是先針對(duì)現(xiàn)有單表做極限優(yōu)化目標(biāo)是確認(rèn)在不動(dòng)架構(gòu)的前提下能壓榨出多少性能。先抓了top慢SQL發(fā)現(xiàn)80%的請(qǐng)求落在兩個(gè)查詢上一是訂單列表查詢按user_id和order_status過(guò)濾按create_time倒序分頁(yè)二是訂單數(shù)統(tǒng)計(jì)按seller_id和日期范圍聚合。訂單列表查詢的問(wèn)題是原來(lái)的單列索引(user_id)和(create_time)單獨(dú)存在導(dǎo)致WHERE條件走了user_id索引但ORDER BY create_time依然要filesort。我用覆蓋索引方案把聯(lián)合索引直接設(shè)計(jì)為(user_id, order_status, create_time, id)查詢條件直接從索引里全部取出連回表都省了。這個(gè)索引上線后列表查詢從450ms降到25ms。訂單數(shù)統(tǒng)計(jì)的問(wèn)題在GROUP BY。原來(lái)的SQL是SELECT seller_id, COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY seller_id沒(méi)有合適的聯(lián)合索引觸發(fā)了臨時(shí)表和全表掃描。我調(diào)整成了(create_time, seller_id)聯(lián)合索引讓時(shí)間過(guò)濾和分組字段都能走索引。日期范圍檢索后的分組從全表掃描變成了索引順序掃描加分組統(tǒng)計(jì)耗時(shí)從2.8秒降到180ms。這輪改造做完線上整體慢查詢數(shù)量直接下降了一個(gè)數(shù)量級(jí)因?yàn)?0%的核心查詢都通過(guò)新索引跑進(jìn)了幾十毫秒的區(qū)間。6.2 什么觸發(fā)了最終的分庫(kù)分表以及落地后的效果對(duì)比索引和SQL優(yōu)化到極限之后系統(tǒng)穩(wěn)定運(yùn)行了相當(dāng)長(zhǎng)一段時(shí)間。但隨著業(yè)務(wù)繼續(xù)擴(kuò)張單日訂單量從100萬(wàn)翻到500萬(wàn)單表數(shù)據(jù)量爆炸式增長(zhǎng)到7億行以上索引深度已經(jīng)達(dá)到4層即使覆蓋索引也開始出現(xiàn)明顯延遲數(shù)據(jù)庫(kù)磁盤占用率長(zhǎng)期接近90%。更致命的是核心表的寫入并發(fā)量大增單庫(kù)的寫入性能開始成為瓶頸連續(xù)兩次因?yàn)樽x寫互鎖導(dǎo)致高峰期訂單寫入延遲超過(guò)10秒。到了這個(gè)節(jié)點(diǎn)單表優(yōu)化的路已經(jīng)走到頭了我們才正式啟動(dòng)了分庫(kù)分表方案。按順序挑了ShardingSphere-JDBC作為中間件分片鍵選了user_id分成16個(gè)物理庫(kù)每個(gè)庫(kù)內(nèi)按月份做子表這樣既能根據(jù)用戶很快定位又能利用時(shí)間維度做冷熱分離這個(gè)方案兼顧了業(yè)務(wù)查詢規(guī)律和后續(xù)數(shù)據(jù)歸檔的便利性。因?yàn)榉制I選對(duì)了大部分按用戶視角的查詢只需要路由到唯一一個(gè)分片響應(yīng)時(shí)間從優(yōu)化前的平均500ms進(jìn)一步降到30ms以內(nèi)。以前那些跨月查詢訂單列表的SQL現(xiàn)在通過(guò)ShardingSphere的綁定表功能自動(dòng)路由到各個(gè)月表再合并結(jié)果整體體驗(yàn)也沒(méi)差太多。這輪改造落地的關(guān)鍵其實(shí)還是前面的鋪墊因?yàn)樗饕蚐QL優(yōu)化已經(jīng)把單表性能壓到了極致我們才敢在數(shù)據(jù)量確實(shí)起來(lái)以后有條不紊地分庫(kù)分表而不是在性能問(wèn)題一出現(xiàn)就倉(cāng)促上馬。很多團(tuán)隊(duì)把順序搞反了單表索引還沒(méi)優(yōu)化好就慌忙分庫(kù)分表后果基本都是雪上加霜。7. 全局優(yōu)化思路的更高層面索引優(yōu)化與架構(gòu)演進(jìn)的金線在哪里最后想聊聊我在每一次優(yōu)化項(xiàng)目中都會(huì)問(wèn)自己的一個(gè)問(wèn)題優(yōu)化的金線到底畫在哪里這個(gè)話題可能有點(diǎn)“務(wù)虛”但它決定了你在一個(gè)項(xiàng)目里投入多少精力和資源值得單獨(dú)拿一小節(jié)來(lái)說(shuō)。7.1 優(yōu)化決策的兩條鐵律不要過(guò)度設(shè)計(jì)也不要放棄治療第一條鐵律是先拿數(shù)據(jù)說(shuō)話再談方案取舍。每個(gè)優(yōu)化的起點(diǎn)都是監(jiān)控?cái)?shù)據(jù)而不是直覺(jué)。慢查詢?nèi)罩?、監(jiān)控面板上的CPU、IOPS、連接數(shù)曲線能告訴你系統(tǒng)真實(shí)的壓力點(diǎn)在哪個(gè)維度。數(shù)據(jù)還沒(méi)確認(rèn)瓶頸先優(yōu)化了一堆業(yè)務(wù)代碼那是白費(fèi)勁。第二條鐵律是不在錯(cuò)誤層次上優(yōu)化。有些團(tuán)隊(duì)遇到慢查詢第一反應(yīng)是換數(shù)據(jù)庫(kù)結(jié)果換完發(fā)現(xiàn)還是慢——因?yàn)槁母驹蚴遣樵円祷貛装僬讛?shù)據(jù)給應(yīng)用層。反過(guò)來(lái)有些團(tuán)隊(duì)一遇到寫入慢就硬著頭皮分析SQL結(jié)果根因是磁盤IOPS不夠加一塊SSD或者把RAID級(jí)別換一下就好了。怎么判斷正確的優(yōu)化層次我的經(jīng)驗(yàn)是如果CPU高通常優(yōu)先考慮SQL和索引優(yōu)化如果磁盤IO高往往要考慮減少回表冷讀量、增加內(nèi)存緩沖池或者升級(jí)硬件如果連接數(shù)滿一般查慢查詢和鎖等待如果以上都排除了還是扛不住才輪到架構(gòu)層——緩存、讀寫分離、分庫(kù)分表逐層升級(jí)。7.2 當(dāng)分庫(kù)分表也不夠用的時(shí)候冷熱分離與數(shù)據(jù)歸檔的最后一公里有一種情況即使做了分庫(kù)分表你仍然會(huì)發(fā)現(xiàn)熱數(shù)據(jù)被冷數(shù)據(jù)拖累。因?yàn)榉制前从脩鬒D或者訂單號(hào)分某個(gè)分片上的用戶可能恰好一年了還有大量歷史訂單查詢需求和另一個(gè)活躍度低的分片負(fù)載極不均衡。這時(shí)候冷熱分離就非常重要了。以訂單系統(tǒng)為例我們上線了數(shù)據(jù)歸檔任務(wù)把超過(guò)一年的訂單從在線訂單庫(kù)定期遷移到歷史歸檔庫(kù)。在線庫(kù)里只保留近一年的熱數(shù)據(jù)一年前的訂單需要查詢時(shí)走獨(dú)立的歸檔查詢接口。由于在線庫(kù)的數(shù)據(jù)量直接砍掉一半以上索引深度降低查詢和寫入都變輕了。這一步和分庫(kù)分表是能疊加的。很多團(tuán)隊(duì)分庫(kù)分表后就停在原地其實(shí)還可以再往前走一步在分片內(nèi)部再按時(shí)間滾動(dòng)切割表或者定期對(duì)冷數(shù)據(jù)做壓縮歸檔。這一套組合拳打下來(lái)系統(tǒng)的橫向擴(kuò)展能力和縱向數(shù)據(jù)治理能力才算是真正建立起來(lái)了。7.3 緩存系統(tǒng)與MySQL的分工協(xié)作別把緩存當(dāng)成萬(wàn)能藥最后想提醒一個(gè)特別容易被誤用的點(diǎn)很多人把Redis緩存當(dāng)成MySQL問(wèn)題的萬(wàn)能藥有什么性能問(wèn)題先加一層緩存。緩存確實(shí)能分擔(dān)讀壓力但如果你把緩存當(dāng)成覆蓋一切查詢的手段等到緩存穿透、緩存雪崩和緩存一致性這些問(wèn)題一起來(lái)的時(shí)候你才會(huì)意識(shí)到緩存治理的復(fù)雜度完全不亞于數(shù)據(jù)庫(kù)優(yōu)化。更合理的方式是先讓MySQL本身健健康康地跑再用緩存去解決MySQL“不該承擔(dān)”的讀壓力。什么算不該承擔(dān)熱點(diǎn)數(shù)據(jù)讀多寫少、一致性要求不高的場(chǎng)景比如商品詳情、配置信息這些完全可以扔到Redis里。但強(qiáng)一致性的庫(kù)存扣減、訂單狀態(tài)流轉(zhuǎn)這些數(shù)據(jù)最好留在MySQL中用數(shù)據(jù)庫(kù)事務(wù)能力來(lái)保證而不是把一致性邏輯搬到Redis的Lua腳本里去任何一個(gè)環(huán)節(jié)失誤都會(huì)造成資損。我見過(guò)最“嚇人”的架構(gòu)是把訂單都放到Redis里MySQL只做持久化備份結(jié)果Redis宕機(jī)一次就永久丟了一部分未落庫(kù)的訂單。這種災(zāi)難完全可以避免——緩存回歸緩存數(shù)據(jù)庫(kù)承擔(dān)它該承擔(dān)的一致性保障角色。這里不是否認(rèn)緩存的價(jià)值而是強(qiáng)調(diào)分層清楚、職責(zé)分明。做了這么多年MySQL優(yōu)化我最深的體會(huì)是優(yōu)化不是一錘子買賣也不是一招鮮吃遍天。索引、SQL、分庫(kù)分表乃至緩存與冷熱分離是一個(gè)層層遞進(jìn)、互相配合的體系。每個(gè)階段都有它該做的功課和不該越過(guò)的紅線而判斷那條金線在哪里才是區(qū)分一個(gè)普通增刪改查工程師和資深性能優(yōu)化專家的根本差異。如果你現(xiàn)在正被MySQL性能問(wèn)題困擾我建議你按這篇文章的層次逐一排查先打開慢查詢?nèi)罩咀?wèn)題SQL再用EXPLAIN分析執(zhí)行計(jì)劃然后用索引設(shè)計(jì)和SQL重寫去解決85%以上的性能問(wèn)題當(dāng)單表優(yōu)化到頭且數(shù)據(jù)增長(zhǎng)趨勢(shì)確實(shí)不可逆時(shí)再理性評(píng)估分庫(kù)分表并且一定要記住分片鍵大于一切路由規(guī)則要預(yù)留擴(kuò)展空間。順著這個(gè)思路走你至少能少踩我當(dāng)年踩過(guò)的那一連串大坑。 MySQL性能優(yōu)化實(shí)戰(zhàn)：從慢查詢定位到索引與分庫(kù)分表
每次接手一個(gè)“MySQL慢得像蝸?！钡呐挪槿蝿?wù)我第一反應(yīng)不是去看服務(wù)器配置也不是去罵產(chǎn)品經(jīng)理又亂寫需求而是先打開慢查詢?nèi)罩竞虴XPLAIN。干這行十年經(jīng)手的MySQL優(yōu)化案例沒(méi)有一千也有八百不管是單表幾十萬(wàn)數(shù)據(jù)的初創(chuàng)項(xiàng)目還是分庫(kù)分表后單表仍上億的成熟業(yè)務(wù)系統(tǒng)最終的性能瓶頸往往都收束到三個(gè)方向索引是否設(shè)計(jì)合理、SQL是否寫得到位、數(shù)據(jù)量大到一定程度后架構(gòu)是否扛得住。這篇文章不會(huì)跟你聊那些“高大上”的底層原理名詞堆砌就實(shí)打?qū)嵉匕阉饕?、SQL調(diào)優(yōu)、分庫(kù)分表這三塊硬骨頭拆開揉碎講清楚每個(gè)方案背后的取舍邏輯和我在項(xiàng)目里驗(yàn)證過(guò)的真實(shí)做法。這篇攻略適合誰(shuí)看如果你剛把業(yè)務(wù)代碼寫完發(fā)現(xiàn)線上數(shù)據(jù)庫(kù)CPU經(jīng)常飆到100%或者查詢接口隨著數(shù)據(jù)量增長(zhǎng)從200ms一路滑到2秒開外又或者你正在為“要不要分庫(kù)分表”這個(gè)問(wèn)題糾結(jié)到失眠——那這篇文章就是寫給你的。我盡量把每個(gè)決策點(diǎn)都還原成“當(dāng)時(shí)我遇到的是什么場(chǎng)景、為什么選這個(gè)方案、踩了哪些坑”而不是給你一堆PPT式的理論框架。1. 先定位慢在哪90%的MySQL性能問(wèn)題都出在同一個(gè)地方我見過(guò)太多團(tuán)隊(duì)一上來(lái)就討論要不要換PostgreSQL、要不要上分布式數(shù)據(jù)庫(kù)結(jié)果我上去一查單表才三千萬(wàn)數(shù)據(jù)連接數(shù)才一兩百根本還沒(méi)到換數(shù)據(jù)庫(kù)的地步純粹是幾條核心SQL沒(méi)寫好索引走了全表掃描。定位慢查詢這件事是后續(xù)所有優(yōu)化的地基地基不穩(wěn)后面所有動(dòng)作都是瞎忙活。1.1 慢查詢?nèi)罩九cEXPLAIN的正確打開方式排查的第一步永遠(yuǎn)是打開慢查詢?nèi)罩?。很多開發(fā)者說(shuō)“我開了啊”結(jié)果一查long_query_time設(shè)置的是默認(rèn)的10秒等于沒(méi)開。這個(gè)閾值我一般建議從0.5秒開始設(shè)先抓出當(dāng)前消耗最高的SQL——晚上用pt-query-digest這類工具一匯總top 10的慢SQL基本就是你要優(yōu)化的大頭。拿到慢SQL之后先別急著看業(yè)務(wù)邏輯直接EXPLAIN看執(zhí)行計(jì)劃。這里有個(gè)新手特別容易忽略的點(diǎn)EXPLAIN看的是預(yù)估執(zhí)行計(jì)劃它告訴你的是優(yōu)化器“打算怎么干”不是“實(shí)際什么表現(xiàn)”。真正要看你得開EXPLAIN ANALYZEMySQL 8.0.18支持它能給出實(shí)際執(zhí)行時(shí)間和掃描行數(shù)做完索引改動(dòng)之后也必須用這個(gè)命令確認(rèn)優(yōu)化效果而不是只看type是不是從ALL變成了ref就以為萬(wàn)事大吉。看執(zhí)行計(jì)劃時(shí)我有一套自己的快速判斷順序type字段從好到差依次是 system const eq_ref ref range index ALL。只要看到ALL基本可以確定這條SQL要全表掃除非表就幾百行否則一定要優(yōu)化。key字段看看實(shí)際用了哪個(gè)索引。經(jīng)常出現(xiàn)的坑是possible_keys里有候選索引但key是NULL——就是優(yōu)化器壓根沒(méi)選你的索引。rows字段預(yù)估掃描行數(shù)。這個(gè)數(shù)值和實(shí)際行數(shù)相差太大通常意味著統(tǒng)計(jì)信息過(guò)期或者WHERE條件的寫法讓優(yōu)化器無(wú)法做準(zhǔn)確估算。Extra字段看到Using filesort和Using temporary這是兩個(gè)危險(xiǎn)信號(hào)一個(gè)說(shuō)明排序沒(méi)有走索引一個(gè)說(shuō)明臨時(shí)表被用到了后面第六節(jié)詳細(xì)講。1.2 從執(zhí)行計(jì)劃到根因判斷一張排查對(duì)照表為了讓你排查起來(lái)更有方向感我把日常最常遇到的執(zhí)行計(jì)劃特點(diǎn)和對(duì)應(yīng)根因整理成一張表。這個(gè)話單是多年實(shí)戰(zhàn)積累下來(lái)的濃縮版排查時(shí)直接對(duì)照著看就行執(zhí)行計(jì)劃特征可能根因優(yōu)先處理建議typeALL, rows 很大沒(méi)建索引或索引失效檢查WHERE和JOIN條件字段建立合適的單列或聯(lián)合索引keyNULL, possible_keys 有值優(yōu)化器放棄索引可能是數(shù)據(jù)分布問(wèn)題或函數(shù)運(yùn)算導(dǎo)致重寫SQL去掉函數(shù)包裹如果實(shí)在無(wú)法避免考慮強(qiáng)制索引Using filesort排序字段和WHERE條件不在同一個(gè)聯(lián)合索引里調(diào)整聯(lián)合索引字段順序讓排序走索引而不是再開一道排序Using temporaryGROUP BY 或 DISTINCT 觸發(fā)了臨時(shí)表重寫查詢或者調(diào)整索引讓分組字段有序rows 誤差超過(guò)10倍統(tǒng)計(jì)信息嚴(yán)重過(guò)期執(zhí)行 ANALYZE TABLE 刷新統(tǒng)計(jì)信息索引選擇性極低重復(fù)率過(guò)高建了索引但區(qū)分度不夠優(yōu)化器不想用換區(qū)分度更高的字段或者用覆蓋索引繞開回表還有一種坑比較隱蔽——索引選擇性誤判。比如性別字段你建了索引結(jié)果優(yōu)化器發(fā)現(xiàn)全表一半是男一半是女它覺(jué)得用這個(gè)索引還不如直接掃全表劃算于是直接放棄索引。遇到這種情況別硬剛優(yōu)化器要么用覆蓋索引帶上查詢需要的其他列讓掃描成本降低要么直接改業(yè)務(wù)查詢邏輯去掉這個(gè)條件。2. 索引為什么失效回表、最左前綴與函數(shù)運(yùn)算的連鎖反應(yīng)索引優(yōu)化是MySQL性能優(yōu)化的第一顆子彈。但很多人建完索引就以為完事了結(jié)果上線后該慢還是慢。索引失效的那幾類典型場(chǎng)景我閉著眼都能背出來(lái)——不是因?yàn)槲矣洃浟枚敲總€(gè)坑我都親自掉進(jìn)去過(guò)又親自爬出來(lái)。2.1 函數(shù)包裹、隱式轉(zhuǎn)換和區(qū)分度問(wèn)題一個(gè)聯(lián)合索引的設(shè)計(jì)案例先看一個(gè)我最近處理的真實(shí)案例。一張訂單表查詢條件是WHERE pay_time BETWEEN ... AND ...我建議開發(fā)在上面建索引他說(shuō)建了但你猜怎么著他是這么寫的WHERE DATE(pay_time) BETWEEN 2024-01-01 AND 2024-01-31。索引是建在pay_time上的但DATE()函數(shù)一包索引直接失效——因?yàn)镸ySQL要先算出DATE(pay_time)的結(jié)果才能去和區(qū)間比較這等于把全部行先計(jì)算一遍那建索引還有什么意義正確的寫法應(yīng)該是WHERE pay_time 2024-01-01 AND pay_time 2024-02-01既覆蓋了整個(gè)一月份的區(qū)間又沒(méi)做任何函數(shù)運(yùn)算索引能完美走range掃描。這條規(guī)則幾乎適用于所有日期范圍查詢也是我審查SQL時(shí)一眼就能掃出來(lái)的問(wèn)題。同樣的道理字段類型不匹配的隱式轉(zhuǎn)換也是索引殺手。比如user_id字段是varchar(32)但你在代碼里傳入的是整數(shù)類型的IDMySQL會(huì)自動(dòng)把字符串轉(zhuǎn)成數(shù)字再比對(duì)。這本來(lái)不算什么大問(wèn)題壞就壞在當(dāng)你拿一個(gè)數(shù)字去和varchar字段比較的時(shí)候MySQL會(huì)先對(duì)所有行的user_id做一次類型轉(zhuǎn)換于是索引全部失效。這類問(wèn)題很難排查因?yàn)樗粓?bào)錯(cuò)執(zhí)行計(jì)劃也不崩但rows行數(shù)就是下不來(lái)。真正設(shè)計(jì)聯(lián)合索引的時(shí)候我養(yǎng)成了三個(gè)習(xí)慣分享出來(lái)供你參考把等值條件放最前面然后才是范圍條件。比如查詢條件是WHERE status1 AND create_time 2024-01-01聯(lián)合索引就應(yīng)該設(shè)計(jì)為(status, create_time)。因?yàn)榈戎禇l件可以精確定位范圍條件只需要在等值定位后的范圍里掃一小段。能用覆蓋索引解決的盡量用覆蓋索引。比如查詢只需要id和status建一個(gè)(status, id)的索引MySQL就能直接從索引里拿數(shù)據(jù)連回表都省了這是最快的一種查詢路徑。控制索引數(shù)量。這塊我必須多說(shuō)兩句。我見過(guò)一個(gè)項(xiàng)目為了一張表建了12個(gè)索引寫入性能慘不忍睹。索引不是越多越好每多一個(gè)索引INSERT和UPDATE就要多維護(hù)一個(gè)B樹。通常單表索引控制在5個(gè)以內(nèi)能復(fù)用聯(lián)合索引的左前綴就絕不重復(fù)建索引。2.2 復(fù)合索引的最左前綴原則一個(gè)字段順序引發(fā)的性能災(zāi)難復(fù)合索引聯(lián)合索引最核心的規(guī)則就是最左前綴原則——MySQL 8.0之前優(yōu)化器只能從左往右匹配索引列你不能跳過(guò)第一列直接用到第二列。這個(gè)我建議你牢記在腦子里因?yàn)閷?shí)際開發(fā)中被它坑過(guò)的案例我至少親手處理過(guò)十次。具體來(lái)說(shuō)假設(shè)你建了復(fù)合索引(a, b, c)WHERE a1能走索引WHERE a1 AND b2能走索引WHERE a1 AND c3只能用到索引的a列c列用不上WHERE b2完全不走這個(gè)索引最經(jīng)典的反例是業(yè)務(wù)需求經(jīng)常按name和create_time查開發(fā)就建了(name, create_time)的索引。但后來(lái)新增了一個(gè)需求只按create_time范圍查詢發(fā)現(xiàn)這條新SQL慢得離譜查看執(zhí)行計(jì)劃居然走了全表掃描。原因就是這個(gè)復(fù)合索引根本幫不上忙——因?yàn)閏reate_time不是第一列只按它查的時(shí)候索引直接報(bào)廢。這時(shí)候正確的做法有三種。第一種是再建一個(gè)(create_time)的單列索引第二種是如果你頻繁需要這個(gè)組合直接正面剛改查詢讓name條件不缺席比如業(yè)務(wù)上允許默認(rèn)帶一個(gè)空串或通配但要注意通配符走不了索引這里不做推薦第三種最優(yōu)雅——把復(fù)合索引設(shè)計(jì)成(create_time, name)既支持按時(shí)間查又支持按時(shí)間姓名查。我強(qiáng)烈推薦第三種因?yàn)閏reate_time通常是范圍條件放在左邊之后右邊接等值條件大多數(shù)情況下都能滿足新老需求。索引失效的場(chǎng)景還有很多比如WHERE name LIKE %張這種前置通配符會(huì)導(dǎo)致索引失效這個(gè)好理解B樹是按前綴有序的你硬要在中間挖一段它沒(méi)法定位還有OR連接的非索引列場(chǎng)景以及SQL里出現(xiàn)!或IS NOT NULL的情況。但歸根結(jié)底你要理解B樹的排序存儲(chǔ)特性就能自己推導(dǎo)出索引什么時(shí)候能用只要是破壞了有序性或者需要全量計(jì)算的場(chǎng)景索引大概率就廢了。3. SQL層面最容易忽略的性能殺手分頁(yè)、JOIN與排序的隱形代價(jià)索引設(shè)計(jì)得再合理SQL寫得不靠譜照樣卡死。這一節(jié)我從最常見的三個(gè)業(yè)務(wù)場(chǎng)景入手分頁(yè)、多表關(guān)聯(lián)和排序分組把隱藏在業(yè)務(wù)代碼背后的性能殺手挨個(gè)揪出來(lái)。3.1 分頁(yè)翻得越深越慢limit深翻頁(yè)問(wèn)題的三種解法做后臺(tái)管理系統(tǒng)的同學(xué)應(yīng)該有切身體會(huì)——導(dǎo)數(shù)據(jù)列表的時(shí)候越往后面翻頁(yè)越慢。比如運(yùn)營(yíng)人員在列表頁(yè)一頁(yè)頁(yè)翻到第200頁(yè)每頁(yè)50條這頁(yè)只顯示第9951到10000條但MySQL干的事是把從101到10000行的所有行先掃出來(lái)然后全部丟掉只把最后50條返回給你。數(shù)據(jù)量小的時(shí)候感覺(jué)不出來(lái)數(shù)據(jù)量上到9000萬(wàn)這種寫法的延遲你根本沒(méi)法接受。排查的時(shí)候往往發(fā)現(xiàn)開發(fā)同學(xué)的SQL是這種形態(tài)SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;這條基本就是深翻頁(yè)臭名昭著的寫法。優(yōu)化方案有三種按優(yōu)先級(jí)推薦方案一延遲關(guān)聯(lián)所有版本通用。先只查id覆蓋索引能快速定位再用這些id去關(guān)聯(lián)查詢完整行SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) t ON o.id t.id;方案二基于游標(biāo)分頁(yè)推薦用在Web端。不是翻頁(yè)跳轉(zhuǎn)而是通過(guò)上一頁(yè)最后一條記錄的ID來(lái)進(jìn)行下一頁(yè)定位SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;用where條件過(guò)濾掉上一頁(yè)已知的最后一條走索引只在數(shù)據(jù)里順序取20條不丟數(shù)據(jù)不重排。缺點(diǎn)是用戶不能直接跳到任意頁(yè)只能在相鄰頁(yè)之間點(diǎn)擊“加載更多”這種交互形式移動(dòng)端和多數(shù)信息流都這么玩因?yàn)樗鼔簻y(cè)下來(lái)性能最穩(wěn)。方案三如果產(chǎn)品必須跳頁(yè)提前算好ID集合一次性查詢。用前端的countoffset換算成已知的主鍵集合然后WHERE id IN (...) LIMIT 20。根據(jù)我的實(shí)測(cè)經(jīng)驗(yàn)方案二在沒(méi)有深翻頁(yè)訴求時(shí)性能最佳方案一在必須保留跳頁(yè)時(shí)性價(jià)比最高。極限情況下同樣的深翻頁(yè)SQL從800ms優(yōu)化到80ms以內(nèi)的都是常規(guī)操作。3.2 JOIN不是不能用但ON條件沒(méi)索引等于災(zāi)難再來(lái)說(shuō)JOIN。很多規(guī)范文檔都會(huì)告訴你“盡量少用JOIN”這句話讓不少新手走入了另一個(gè)極端——把原本一條JOIN拆成三五條SQL在代碼里挨個(gè)查結(jié)果網(wǎng)絡(luò)來(lái)回比以前更慢。我的觀點(diǎn)是JOIN本身不是洪水猛獸關(guān)鍵在JOIN字段是否走索引以及小表驅(qū)動(dòng)大表的原則是否被遵守。MySQL的JOIN執(zhí)行邏輯是這樣的外層驅(qū)動(dòng)表每一行都需要去匹配內(nèi)層表。如果內(nèi)層表的連接字段沒(méi)有索引那就是一次全表掃描乘以驅(qū)動(dòng)表的行數(shù)——這個(gè)代價(jià)是幾何級(jí)增長(zhǎng)的。以一個(gè)業(yè)務(wù)系統(tǒng)為例用戶表幾十萬(wàn)條訂單表上千萬(wàn)條要查某時(shí)間段內(nèi)有訂單的用戶信息。如果JOIN條件是u.id o.user_id但訂單表的user_id沒(méi)建索引每次匹配都要去掃訂單表的一千多萬(wàn)行這條查詢基本就是災(zāi)難現(xiàn)場(chǎng)。建上user_id索引一切迎刃而解。所以我審查SQL的第一原則不是“見了JOIN就皺眉”而是看EXPLAIN里的type只要被驅(qū)動(dòng)表的連接字段是ref或eq_ref級(jí)別JOIN完全可以放心用。另一個(gè)重點(diǎn)是小表驅(qū)動(dòng)大表。MySQL的優(yōu)化器一般會(huì)自動(dòng)選擇小表做驅(qū)動(dòng)表但如果你用什么子查詢包裹的方式去打亂它的統(tǒng)計(jì)幻覺(jué)優(yōu)化器也有可能做出錯(cuò)誤選擇。這時(shí)候STRAIGHT_JOIN可以直接指定驅(qū)動(dòng)順序把大表放到被驅(qū)動(dòng)位置。這個(gè)技巧不常用但當(dāng)你在EXPLAIN里看到驅(qū)動(dòng)表選錯(cuò)的時(shí)候它比硬改SQL簡(jiǎn)單得多。3.3 filesort和臨時(shí)表ORDER BY與GROUP BY為什么會(huì)拖垮查詢ORDER BY最怕的不是排序本身而是排序字段沒(méi)法走索引迫使MySQL把所有結(jié)果集撈出來(lái)再額外執(zhí)行一次內(nèi)存排序或者磁盤排序。EXPLAIN里的Using filesort就是它。在這個(gè)地方聯(lián)合索引的字段順序又體現(xiàn)出關(guān)鍵作用。比如聯(lián)合索引是(status, create_time)當(dāng)查詢條件是WHERE status1 ORDER BY create_time DESCMySQL可以直接按索引順序來(lái)讀不需要額外排序。但如果你把ORDER BY create_time中的create_time放在索引非末尾位置或者排序條件和查詢條件的順序不匹配filesort就出現(xiàn)了。同樣GROUP BY 之所以效率低是因?yàn)樗ǔ０殡S著臨時(shí)表的創(chuàng)建MySQL 8.0之前還會(huì)專門建個(gè)內(nèi)部臨時(shí)表來(lái)存分組中間結(jié)果量大還可能落到磁盤。遇到大表上的GROUP BY我的優(yōu)化順序是先看能否通過(guò)聯(lián)合索引設(shè)計(jì)讓分組字段有序這樣MySQL就可以直接順序掃索引然后順便分組不需要建臨時(shí)表。如果分組字段的基數(shù)很小比如就幾個(gè)枚舉狀態(tài)改用COUNT(DISTINCT ...)配合業(yè)務(wù)側(cè)的Map匯總有時(shí)反而更利落。非要在SQL里GROUP BY把返回字段做得盡量窄只取需要分組的字段和聚合函數(shù)字段減少臨時(shí)表的內(nèi)存壓力。4. 寫入性能與并發(fā)控制的隱形瓶頸不只是慢查詢才需要關(guān)注慢查詢折磨人寫入抖動(dòng)同樣是事故隱患。很多后臺(tái)系統(tǒng)的MySQL優(yōu)化文章通篇講SELECT但一到每天零點(diǎn)定時(shí)任務(wù)跑批、或者業(yè)務(wù)高峰導(dǎo)入數(shù)據(jù)的場(chǎng)景INSERT和UPDATE的性能問(wèn)題能把線上打掛。這一節(jié)聊聊我在處理寫入瓶頸和并發(fā)控制時(shí)常用的兩招批量寫的正確姿勢(shì)和鎖競(jìng)爭(zhēng)的有效規(guī)避。4.1 批量提交與事務(wù)大小的黃金分割點(diǎn)剛工作那會(huì)兒我寫過(guò)一個(gè)批量導(dǎo)入腳本循環(huán)一萬(wàn)條數(shù)據(jù)一條一條INSERT跑了一個(gè)多小時(shí)還沒(méi)跑完。后來(lái)改成一次事務(wù)批量INSERT一千條十分鐘跑完。差異就在于INSERT本身一條條執(zhí)行時(shí)每次都有網(wǎng)絡(luò)往返、事務(wù)提交刷盤的開銷而批量提交可以把這些固定開銷攤薄。但也不是批量越大越好。我有過(guò)一次性INSERT十萬(wàn)條的“翻車”經(jīng)歷——事務(wù)過(guò)大導(dǎo)致undo日志膨脹MVCC機(jī)制要保留舊版本數(shù)據(jù)binlog同步延遲飆升主庫(kù)事務(wù)把從庫(kù)備庫(kù)拖得追不上差點(diǎn)引發(fā)主從切換事故。后來(lái)總結(jié)出的經(jīng)驗(yàn)是單個(gè)事務(wù)控制在1000~5000條范圍或者事務(wù)執(zhí)行時(shí)間控制在1秒以內(nèi)。這個(gè)數(shù)字不是拍腦袋定的而是權(quán)衡了網(wǎng)絡(luò)往返、鎖持有時(shí)長(zhǎng)、binlog體積和回滾成本之后的可接受區(qū)間你可以根據(jù)自己業(yè)務(wù)的寫入量做微調(diào)。預(yù)處理語(yǔ)句綁定參數(shù)值得強(qiáng)烈推薦INSERT INTO orders (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)使用prepared statement批量綁定參數(shù)能避免每條INSERT都做一次SQL解析。在大批量導(dǎo)數(shù)據(jù)場(chǎng)景這個(gè)優(yōu)化效果非常明顯一千萬(wàn)行的導(dǎo)數(shù)任務(wù)從小時(shí)級(jí)縮到十幾分鐘。4.2 行鎖、間隙鎖與死鎖為什么長(zhǎng)事務(wù)比慢SQL更可怕慢SQL至少還能在慢查詢?nèi)罩纠镲@式看到長(zhǎng)事務(wù)才是真正的隱形殺手。事務(wù)不提交它持有的鎖就一直不釋放后續(xù)所有要更新同一條記錄、甚至插入相鄰記錄的事務(wù)都會(huì)排隊(duì)堵住。更隱蔽的是MVCC下的undo日志膨脹長(zhǎng)事務(wù)意味著大量舊版本數(shù)據(jù)不能被purge線程清理這會(huì)導(dǎo)致表空間無(wú)限膨脹查詢還要在版本鏈上往前找正確的版本性能越來(lái)越差。這類長(zhǎng)事務(wù)的來(lái)源十有八九是業(yè)務(wù)代碼在同一個(gè)事務(wù)里調(diào)用了外部接口。例如一個(gè)“創(chuàng)建訂單并扣庫(kù)存”的接口你在Spring的Transactional事務(wù)里調(diào)用支付回調(diào)接口等外部響應(yīng)等了3秒。這三秒里整個(gè)訂單表相關(guān)范圍的鎖都捏在你手里所有同類請(qǐng)求全部排隊(duì)阻塞線上就炸鍋了。死鎖同樣值得反復(fù)排查。我處理過(guò)最典型的死鎖場(chǎng)景是兩個(gè)事務(wù)都執(zhí)行“先更新主表再更新明細(xì)表”但順序恰好反過(guò)來(lái)兩邊互相等鎖MySQL檢測(cè)到死鎖后就隨機(jī)選一個(gè)事務(wù)回滾。這種死鎖在高并發(fā)寫入下特別常見。規(guī)避方法不復(fù)雜所有事務(wù)按照固定順序訪問(wèn)資源比如先主表后明細(xì)表或者把相關(guān)行一次性用SELECT ... FOR UPDATE鎖住。經(jīng)驗(yàn)之談線上環(huán)境一定要監(jiān)控長(zhǎng)事務(wù)和鎖等待事件。information_schema.innodb_trx這張表可以直接查出來(lái)哪些事務(wù)超過(guò)3秒還沒(méi)提交配套sys.innodb_lock_waits能定位鎖等待的根因。趁事務(wù)還沒(méi)膨脹到釀成大禍之前把它殺在萌芽階段是DBA和資深后端必須建立的夜間值班基本功。5. 分庫(kù)分表的前提判斷數(shù)據(jù)量大不等于必須要分講到分庫(kù)分表我先潑一盆冷水分庫(kù)分表是最后的手段不是第一選擇。很多人看數(shù)據(jù)量到了幾千萬(wàn)就喊著要分庫(kù)分表結(jié)果分完以后跨庫(kù)JOIN、分布式事務(wù)、分布式ID、擴(kuò)容數(shù)據(jù)遷移樣樣都是新坑問(wèn)題沒(méi)解決反而更多了。我的經(jīng)驗(yàn)是在動(dòng)手分庫(kù)分表之前你先把下面這些事做扎實(shí)了再說(shuō)。5.1 什么數(shù)據(jù)量級(jí)才需要分庫(kù)分表三大前置判斷標(biāo)準(zhǔn)判斷要不要分庫(kù)分表不是一個(gè)數(shù)字閾值就拍板的我通常會(huì)看三個(gè)維度同時(shí)亮紅燈才啟動(dòng)方案單表數(shù)據(jù)量超過(guò)MySQL的舒適區(qū)。InnoDB的B樹在單表數(shù)據(jù)量一億以內(nèi)理論上都能扛住但實(shí)際運(yùn)維中發(fā)現(xiàn)當(dāng)表超過(guò)2000萬(wàn)行且索引深度超過(guò)3層時(shí)查詢延遲開始明顯可見地退化。當(dāng)然這和數(shù)據(jù)寬度、磁盤IO性能都有關(guān)系不是絕對(duì)判斷標(biāo)準(zhǔn)。單庫(kù)的并發(fā)寫入能力成為瓶頸。比如數(shù)據(jù)庫(kù)連接數(shù)打滿每秒寫入行數(shù)受制于磁盤IOPS即使拆索引、優(yōu)化SQL也提不上去。數(shù)據(jù)增長(zhǎng)趨勢(shì)是持續(xù)且陡峭的。如果只是短期活動(dòng)帶來(lái)的一波數(shù)據(jù)量活動(dòng)結(jié)束就平穩(wěn)了那沒(méi)必要為了峰值去做永久性的分庫(kù)分表改造。我見過(guò)一個(gè)日志表三個(gè)月時(shí)間從2億漲到15億這種就屬于必須分另一種是用戶表全年就漲個(gè)2000萬(wàn)說(shuō)實(shí)話單表再撐幾年問(wèn)題不大。如果只占了第一點(diǎn)單表數(shù)據(jù)量大但讀多寫少優(yōu)先考慮冷熱分離歸檔。比如訂單表把一年前的訂單遷移到一個(gè)獨(dú)立的歸檔庫(kù)或者只讀歷史表主表數(shù)據(jù)量直接砍掉70%查詢性能秒回。占有便宜又見效快完全不需要分庫(kù)分表。如果第二、第三點(diǎn)同時(shí)出現(xiàn)比如訂單系統(tǒng)日訂單量突破500萬(wàn)單、流水表半年就過(guò)10億行那才是分庫(kù)分表的啟動(dòng)時(shí)機(jī)可以進(jìn)入下一節(jié)。5.2 分片鍵選錯(cuò)是最大的災(zāi)難訂單表案例的教訓(xùn)分庫(kù)分表的第一步不是選中間件而是選分片鍵。這里有一條血的教訓(xùn)分片鍵必須滿足80%以上的核心查詢能帶著它走否則就是給自己埋雷。以電商訂單系統(tǒng)為例主流分片鍵是user_id或order_id。選user_id的好處是用戶能查到自己的全部訂單天然落在同一個(gè)分片上壞處是通過(guò)order_id直接查詢?cè)斍榫吐闊┝恕唵翁?hào)不知道屬于哪個(gè)用戶得路由到所有分片去查一次點(diǎn)開詳情頁(yè)所有分片都得掃一遍。選order_id作為分片鍵則剛好反過(guò)來(lái)通過(guò)訂單號(hào)查詳情很快但查用戶某個(gè)時(shí)間段的訂單列表就麻煩了。工程上的常見解法是維護(hù)一張映射表或者在生成order_id時(shí)內(nèi)嵌用戶ID的分片信息讓order_id自身就攜帶分片路由信息。舉個(gè)例子生成訂單號(hào)的時(shí)候把用戶ID的后4位嵌入到訂單號(hào)中這樣拿到order_id算一下就知道去哪個(gè)分片查不需要額外建立映射關(guān)系。分布式ID這塊我強(qiáng)烈建議不要再用數(shù)據(jù)庫(kù)自增ID作為分片表的全局主鍵。因?yàn)榉制竺總€(gè)實(shí)例的自增ID會(huì)重復(fù)必須要引入全局唯一ID生成方案。常見的方案有雪花算法Snowflake、各個(gè)中間件自帶的ID生成器或者用Redis的原子INCR。雪花算法是目前使用最廣泛的方案它的核心是時(shí)間戳機(jī)器ID序列號(hào)每秒能生成幾百萬(wàn)個(gè)不重復(fù)ID而且趨勢(shì)遞增對(duì)數(shù)據(jù)庫(kù)索引友好。5.3 分庫(kù)分表之后這些SQL全都不能用了分庫(kù)分表帶來(lái)的功能損失如果你沒(méi)有足夠的心理和技術(shù)準(zhǔn)備上線后會(huì)讓業(yè)務(wù)方罵娘。我先列一份“黑名單”給你做好準(zhǔn)備全局表JOIN消失了。不能做跨分片的JOIN查詢。以前一條SQL搞定的事現(xiàn)在得代碼里先在各個(gè)分片上分別查完然后在內(nèi)存里做map合并和關(guān)聯(lián)。數(shù)據(jù)量小時(shí)還能接受數(shù)據(jù)量大了這種聚合層邏輯也是性能瓶頸。全局排序分頁(yè)非常痛苦?？绶制鯫RDER BY create_time LIMIT 0, 20你得把所有分片的top N都拉出來(lái)然后在代碼層重新排序截取。更惡心的深翻頁(yè)——你根本沒(méi)法像單表那樣直接跳到第100頁(yè)因?yàn)槊總€(gè)分片都不知道全局的第990到第1000行是哪幾條。這也是為什么分庫(kù)分表之后的產(chǎn)品設(shè)計(jì)大都改成“下拉加載更多”而不是跳頁(yè)。分布式事務(wù)成本高。分片之后跨分片的事務(wù)已經(jīng)超越了MySQL本地事務(wù)的能力你需要引入分布式事務(wù)方案兩階段提交、最終一致性的消息事務(wù)、或者TCC這套復(fù)雜度不是小團(tuán)隊(duì)能輕易駕馭的。所以分庫(kù)分表后第一原則是盡可能把需要事務(wù)的數(shù)據(jù)放到同一個(gè)分片也就是通過(guò)合理選分片鍵來(lái)避免跨分片事務(wù)。這些問(wèn)題沒(méi)辦法完全規(guī)避只能通過(guò)合理的表結(jié)構(gòu)設(shè)計(jì)來(lái)緩解。我見過(guò)的高可用設(shè)計(jì)是把訂單主表按user_id分片訂單明細(xì)表也按user_id分片這樣同一個(gè)用戶的主表記錄和明細(xì)表記錄落在同一個(gè)物理庫(kù)還是可以走本地事務(wù)這是把跨分片事務(wù)扼殺在搖籃里的標(biāo)準(zhǔn)做法。5.4 中間件選型ShardingSphere、MyCat還是自研路由分庫(kù)分表的落地中間件選型決定了你后續(xù)半年的運(yùn)維成本。當(dāng)前主流有三個(gè)方向ShardingSphere推薦首選Apache頂級(jí)項(xiàng)目支持分片、讀寫分離、數(shù)據(jù)加密等多種能力。它的JDBC模式是一個(gè)輕量級(jí)jar包嵌入到應(yīng)用里代碼侵入小、性能損耗低適合Java技術(shù)棧是我們團(tuán)隊(duì)目前的主力方案。MyCat適合做數(shù)據(jù)庫(kù)代理層獨(dú)立部署的中間件服務(wù)對(duì)應(yīng)用透明應(yīng)用不需要改連接方式但它需要額外部署和運(yùn)維一套集群鏈路更長(zhǎng)性能損耗也更大。如果非Java技術(shù)?；蛘呦胍霐?shù)據(jù)庫(kù)中間件這個(gè)方向可以評(píng)估。自研路由適合極簡(jiǎn)場(chǎng)景只在代碼層做一個(gè)簡(jiǎn)單的分片規(guī)則算法比如通過(guò)用戶ID取模路由到不同的庫(kù)。這個(gè)方案性能最好、最可控但只適合分片規(guī)則簡(jiǎn)單、分片數(shù)量固定、不要求彈性擴(kuò)縮容的場(chǎng)景。再?gòu)?fù)雜一點(diǎn)你就得從頭自己解決分布式事務(wù)、平滑擴(kuò)容這些問(wèn)題代價(jià)極高。我在這塊的取舍原則是沒(méi)有完美的中間件適合當(dāng)前團(tuán)隊(duì)規(guī)模和技術(shù)棧的才是最好的。如果團(tuán)隊(duì)只有兩三個(gè)人我建議直接用ShardingSphere-JDBC學(xué)習(xí)成本低出了問(wèn)題社區(qū)答案也多如果公司數(shù)據(jù)團(tuán)隊(duì)規(guī)范成熟、對(duì)數(shù)據(jù)庫(kù)有強(qiáng)管控需求MyCat的代理模式會(huì)讓DBA更安心。5.5 數(shù)據(jù)遷移與擴(kuò)容從停機(jī)遷移到平滑擴(kuò)容的演進(jìn)分庫(kù)分表之后遲早要面對(duì)擴(kuò)容的問(wèn)題。假設(shè)初始分成了16個(gè)分片兩年后數(shù)據(jù)量漲到需要32個(gè)分片怎么辦這時(shí)候最忌諱的是用hash(user_id) % 32這種規(guī)則因?yàn)橐坏┌讶∧；鶖?shù)從16改成32幾乎所有數(shù)據(jù)都要重新分布遷移成本讓人崩潰。我建議在設(shè)計(jì)分片規(guī)則時(shí)就直接避免這個(gè)問(wèn)題。兩個(gè)常用方案一致性哈希。它把哈希值空間組織成環(huán)數(shù)據(jù)落在哪個(gè)分片由它沿環(huán)順時(shí)針找到的第一個(gè)節(jié)點(diǎn)決定。擴(kuò)容的時(shí)候只需要遷移部分分片上的數(shù)據(jù)到新節(jié)點(diǎn)不用全部重排。運(yùn)維成本要低很多。按時(shí)間分片歷史數(shù)據(jù)冷處理。對(duì)于日志、流水類數(shù)據(jù)按月份或季度分片。過(guò)了當(dāng)前月份的數(shù)據(jù)不會(huì)再有寫入完全不需要遷移。查詢的時(shí)候帶上月份條件就能定位到對(duì)應(yīng)分片讀取性能和擴(kuò)容便利性都有保障。這兩個(gè)方案選哪個(gè)取決于業(yè)務(wù)特性核心還是要前置考慮別等數(shù)據(jù)量大了再改路由規(guī)則那真是所有DBA的噩夢(mèng)。6. 優(yōu)化效果的一次完整復(fù)盤一個(gè)日訂單量百萬(wàn)級(jí)系統(tǒng)的改造路徑理論講了這么多還是給你看一個(gè)我印象比較深的完整改造案例。這套系統(tǒng)我接手時(shí)已經(jīng)線上運(yùn)行了兩年每天新增訂單量在100萬(wàn)行左右數(shù)據(jù)庫(kù)單表已經(jīng)突破1.8億行高峰期核心查詢接口平均響應(yīng)時(shí)間從最初的300ms惡化到3秒左右時(shí)不時(shí)還會(huì)因?yàn)槁樵兺峡暹B接池。6.1 從200ms到20ms索引重建與SQL重寫的組合拳接手第一階段我根本沒(méi)考慮分庫(kù)分表而是先針對(duì)現(xiàn)有單表做極限優(yōu)化目標(biāo)是確認(rèn)在不動(dòng)架構(gòu)的前提下能壓榨出多少性能。先抓了top慢SQL發(fā)現(xiàn)80%的請(qǐng)求落在兩個(gè)查詢上一是訂單列表查詢按user_id和order_status過(guò)濾按create_time倒序分頁(yè)二是訂單數(shù)統(tǒng)計(jì)按seller_id和日期范圍聚合。訂單列表查詢的問(wèn)題是原來(lái)的單列索引(user_id)和(create_time)單獨(dú)存在導(dǎo)致WHERE條件走了user_id索引但ORDER BY create_time依然要filesort。我用覆蓋索引方案把聯(lián)合索引直接設(shè)計(jì)為(user_id, order_status, create_time, id)查詢條件直接從索引里全部取出連回表都省了。這個(gè)索引上線后列表查詢從450ms降到25ms。訂單數(shù)統(tǒng)計(jì)的問(wèn)題在GROUP BY。原來(lái)的SQL是SELECT seller_id, COUNT(*) FROM orders WHERE create_time BETWEEN ? AND ? GROUP BY seller_id沒(méi)有合適的聯(lián)合索引觸發(fā)了臨時(shí)表和全表掃描。我調(diào)整成了(create_time, seller_id)聯(lián)合索引讓時(shí)間過(guò)濾和分組字段都能走索引。日期范圍檢索后的分組從全表掃描變成了索引順序掃描加分組統(tǒng)計(jì)耗時(shí)從2.8秒降到180ms。這輪改造做完線上整體慢查詢數(shù)量直接下降了一個(gè)數(shù)量級(jí)因?yàn)?0%的核心查詢都通過(guò)新索引跑進(jìn)了幾十毫秒的區(qū)間。6.2 什么觸發(fā)了最終的分庫(kù)分表以及落地后的效果對(duì)比索引和SQL優(yōu)化到極限之后系統(tǒng)穩(wěn)定運(yùn)行了相當(dāng)長(zhǎng)一段時(shí)間。但隨著業(yè)務(wù)繼續(xù)擴(kuò)張單日訂單量從100萬(wàn)翻到500萬(wàn)單表數(shù)據(jù)量爆炸式增長(zhǎng)到7億行以上索引深度已經(jīng)達(dá)到4層即使覆蓋索引也開始出現(xiàn)明顯延遲數(shù)據(jù)庫(kù)磁盤占用率長(zhǎng)期接近90%。更致命的是核心表的寫入并發(fā)量大增單庫(kù)的寫入性能開始成為瓶頸連續(xù)兩次因?yàn)樽x寫互鎖導(dǎo)致高峰期訂單寫入延遲超過(guò)10秒。到了這個(gè)節(jié)點(diǎn)單表優(yōu)化的路已經(jīng)走到頭了我們才正式啟動(dòng)了分庫(kù)分表方案。按順序挑了ShardingSphere-JDBC作為中間件分片鍵選了user_id分成16個(gè)物理庫(kù)每個(gè)庫(kù)內(nèi)按月份做子表這樣既能根據(jù)用戶很快定位又能利用時(shí)間維度做冷熱分離這個(gè)方案兼顧了業(yè)務(wù)查詢規(guī)律和后續(xù)數(shù)據(jù)歸檔的便利性。因?yàn)榉制I選對(duì)了大部分按用戶視角的查詢只需要路由到唯一一個(gè)分片響應(yīng)時(shí)間從優(yōu)化前的平均500ms進(jìn)一步降到30ms以內(nèi)。以前那些跨月查詢訂單列表的SQL現(xiàn)在通過(guò)ShardingSphere的綁定表功能自動(dòng)路由到各個(gè)月表再合并結(jié)果整體體驗(yàn)也沒(méi)差太多。這輪改造落地的關(guān)鍵其實(shí)還是前面的鋪墊因?yàn)樗饕蚐QL優(yōu)化已經(jīng)把單表性能壓到了極致我們才敢在數(shù)據(jù)量確實(shí)起來(lái)以后有條不紊地分庫(kù)分表而不是在性能問(wèn)題一出現(xiàn)就倉(cāng)促上馬。很多團(tuán)隊(duì)把順序搞反了單表索引還沒(méi)優(yōu)化好就慌忙分庫(kù)分表后果基本都是雪上加霜。7. 全局優(yōu)化思路的更高層面索引優(yōu)化與架構(gòu)演進(jìn)的金線在哪里最后想聊聊我在每一次優(yōu)化項(xiàng)目中都會(huì)問(wèn)自己的一個(gè)問(wèn)題優(yōu)化的金線到底畫在哪里這個(gè)話題可能有點(diǎn)“務(wù)虛”但它決定了你在一個(gè)項(xiàng)目里投入多少精力和資源值得單獨(dú)拿一小節(jié)來(lái)說(shuō)。7.1 優(yōu)化決策的兩條鐵律不要過(guò)度設(shè)計(jì)也不要放棄治療第一條鐵律是先拿數(shù)據(jù)說(shuō)話再談方案取舍。每個(gè)優(yōu)化的起點(diǎn)都是監(jiān)控?cái)?shù)據(jù)而不是直覺(jué)。慢查詢?nèi)罩?、監(jiān)控面板上的CPU、IOPS、連接數(shù)曲線能告訴你系統(tǒng)真實(shí)的壓力點(diǎn)在哪個(gè)維度。數(shù)據(jù)還沒(méi)確認(rèn)瓶頸先優(yōu)化了一堆業(yè)務(wù)代碼那是白費(fèi)勁。第二條鐵律是不在錯(cuò)誤層次上優(yōu)化。有些團(tuán)隊(duì)遇到慢查詢第一反應(yīng)是換數(shù)據(jù)庫(kù)結(jié)果換完發(fā)現(xiàn)還是慢——因?yàn)槁母驹蚴遣樵円祷貛装僬讛?shù)據(jù)給應(yīng)用層。反過(guò)來(lái)有些團(tuán)隊(duì)一遇到寫入慢就硬著頭皮分析SQL結(jié)果根因是磁盤IOPS不夠加一塊SSD或者把RAID級(jí)別換一下就好了。怎么判斷正確的優(yōu)化層次我的經(jīng)驗(yàn)是如果CPU高通常優(yōu)先考慮SQL和索引優(yōu)化如果磁盤IO高往往要考慮減少回表冷讀量、增加內(nèi)存緩沖池或者升級(jí)硬件如果連接數(shù)滿一般查慢查詢和鎖等待如果以上都排除了還是扛不住才輪到架構(gòu)層——緩存、讀寫分離、分庫(kù)分表逐層升級(jí)。7.2 當(dāng)分庫(kù)分表也不夠用的時(shí)候冷熱分離與數(shù)據(jù)歸檔的最后一公里有一種情況即使做了分庫(kù)分表你仍然會(huì)發(fā)現(xiàn)熱數(shù)據(jù)被冷數(shù)據(jù)拖累。因?yàn)榉制前从脩鬒D或者訂單號(hào)分某個(gè)分片上的用戶可能恰好一年了還有大量歷史訂單查詢需求和另一個(gè)活躍度低的分片負(fù)載極不均衡。這時(shí)候冷熱分離就非常重要了。以訂單系統(tǒng)為例我們上線了數(shù)據(jù)歸檔任務(wù)把超過(guò)一年的訂單從在線訂單庫(kù)定期遷移到歷史歸檔庫(kù)。在線庫(kù)里只保留近一年的熱數(shù)據(jù)一年前的訂單需要查詢時(shí)走獨(dú)立的歸檔查詢接口。由于在線庫(kù)的數(shù)據(jù)量直接砍掉一半以上索引深度降低查詢和寫入都變輕了。這一步和分庫(kù)分表是能疊加的。很多團(tuán)隊(duì)分庫(kù)分表后就停在原地其實(shí)還可以再往前走一步在分片內(nèi)部再按時(shí)間滾動(dòng)切割表或者定期對(duì)冷數(shù)據(jù)做壓縮歸檔。這一套組合拳打下來(lái)系統(tǒng)的橫向擴(kuò)展能力和縱向數(shù)據(jù)治理能力才算是真正建立起來(lái)了。7.3 緩存系統(tǒng)與MySQL的分工協(xié)作別把緩存當(dāng)成萬(wàn)能藥最后想提醒一個(gè)特別容易被誤用的點(diǎn)很多人把Redis緩存當(dāng)成MySQL問(wèn)題的萬(wàn)能藥有什么性能問(wèn)題先加一層緩存。緩存確實(shí)能分擔(dān)讀壓力但如果你把緩存當(dāng)成覆蓋一切查詢的手段等到緩存穿透、緩存雪崩和緩存一致性這些問(wèn)題一起來(lái)的時(shí)候你才會(huì)意識(shí)到緩存治理的復(fù)雜度完全不亞于數(shù)據(jù)庫(kù)優(yōu)化。更合理的方式是先讓MySQL本身健健康康地跑再用緩存去解決MySQL“不該承擔(dān)”的讀壓力。什么算不該承擔(dān)熱點(diǎn)數(shù)據(jù)讀多寫少、一致性要求不高的場(chǎng)景比如商品詳情、配置信息這些完全可以扔到Redis里。但強(qiáng)一致性的庫(kù)存扣減、訂單狀態(tài)流轉(zhuǎn)這些數(shù)據(jù)最好留在MySQL中用數(shù)據(jù)庫(kù)事務(wù)能力來(lái)保證而不是把一致性邏輯搬到Redis的Lua腳本里去任何一個(gè)環(huán)節(jié)失誤都會(huì)造成資損。我見過(guò)最“嚇人”的架構(gòu)是把訂單都放到Redis里MySQL只做持久化備份結(jié)果Redis宕機(jī)一次就永久丟了一部分未落庫(kù)的訂單。這種災(zāi)難完全可以避免——緩存回歸緩存數(shù)據(jù)庫(kù)承擔(dān)它該承擔(dān)的一致性保障角色。這里不是否認(rèn)緩存的價(jià)值而是強(qiáng)調(diào)分層清楚、職責(zé)分明。做了這么多年MySQL優(yōu)化我最深的體會(huì)是優(yōu)化不是一錘子買賣也不是一招鮮吃遍天。索引、SQL、分庫(kù)分表乃至緩存與冷熱分離是一個(gè)層層遞進(jìn)、互相配合的體系。每個(gè)階段都有它該做的功課和不該越過(guò)的紅線而判斷那條金線在哪里才是區(qū)分一個(gè)普通增刪改查工程師和資深性能優(yōu)化專家的根本差異。如果你現(xiàn)在正被MySQL性能問(wèn)題困擾我建議你按這篇文章的層次逐一排查先打開慢查詢?nèi)罩咀?wèn)題SQL再用EXPLAIN分析執(zhí)行計(jì)劃然后用索引設(shè)計(jì)和SQL重寫去解決85%以上的性能問(wèn)題當(dāng)單表優(yōu)化到頭且數(shù)據(jù)增長(zhǎng)趨勢(shì)確實(shí)不可逆時(shí)再理性評(píng)估分庫(kù)分表并且一定要記住分片鍵大于一切路由規(guī)則要預(yù)留擴(kuò)展空間。順著這個(gè)思路走你至少能少踩我當(dāng)年踩過(guò)的那一連串大坑。