據(jù)庫(kù)工程：千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化落地實(shí)戰(zhàn)指南?)
數(shù)據(jù)庫(kù)工程千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化落地實(shí)戰(zhàn)指南?上個(gè)月在蘇州昆山的一家電子代工廠現(xiàn)場(chǎng)我剛把打包好的陽(yáng)澄湖大閘蟹禮盒放到后備箱客戶的技術(shù)負(fù)責(zé)人一個(gè)語(yǔ)音電話打過(guò)來(lái)聲音急得都變調(diào)了生產(chǎn)系統(tǒng)的BOM物料查詢頁(yè)面直接全紅告警車(chē)間里兩百多臺(tái)SMT貼片機(jī)的物料清單拉不出來(lái)整條生產(chǎn)線已經(jīng)停了快四十分鐘產(chǎn)線經(jīng)理在后臺(tái)拍桌子說(shuō)再搞不定今晚的通宵趕工計(jì)劃全要泡湯。我掉頭往機(jī)房趕的時(shí)候滿腦子都是上周剛上線的那幾條物料關(guān)聯(lián)SQL當(dāng)時(shí)開(kāi)發(fā)團(tuán)隊(duì)趕項(xiàng)目進(jìn)度沒(méi)來(lái)得及做性能測(cè)試就直接上線了結(jié)果物料表的數(shù)據(jù)量剛破千萬(wàn)直接把系統(tǒng)打崩。到了機(jī)房我翻了十分鐘慢日志發(fā)現(xiàn)一條關(guān)聯(lián)了四張表的物料統(tǒng)計(jì)SQL全表掃描了1200萬(wàn)行數(shù)據(jù)直接把磁盤(pán)IO跑滿100%連系統(tǒng)的登錄接口都要半分鐘才能響應(yīng)。我花了不到半小時(shí)調(diào)整了聯(lián)合索引的字段順序把驅(qū)動(dòng)表?yè)Q成了小表優(yōu)化完之后物料查詢頁(yè)面直接從8秒加載變成了20毫秒內(nèi)秒出產(chǎn)線當(dāng)天就恢復(fù)了正常運(yùn)轉(zhuǎn)。后來(lái)跟客戶的開(kāi)發(fā)團(tuán)隊(duì)復(fù)盤(pán)的時(shí)候發(fā)現(xiàn)他們組里大部分人處理千萬(wàn)級(jí)數(shù)據(jù)的慢SQL第一反應(yīng)就是升級(jí)服務(wù)器配置根本不知道怎么用低成本的SQL優(yōu)化手段解決問(wèn)題。今天就把我這八年跑遍長(zhǎng)三角電子制造、跨境電商、縣域政務(wù)項(xiàng)目攢下來(lái)的千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化實(shí)戰(zhàn)經(jīng)驗(yàn)全說(shuō)透沒(méi)有教科書(shū)里的空泛定義每一步操作你打開(kāi)自己的數(shù)據(jù)庫(kù)就能直接跟著落地。一、千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化的核心底層邏輯很多人聊SQL優(yōu)化總喜歡背一大堆數(shù)據(jù)庫(kù)內(nèi)核的理論定義聽(tīng)完之后還是不知道怎么給自己的千萬(wàn)級(jí)業(yè)務(wù)表做優(yōu)化其實(shí)核心邏輯特別簡(jiǎn)單SQL優(yōu)化的本質(zhì)就是盡可能減少數(shù)據(jù)庫(kù)的隨機(jī)磁盤(pán)IO次數(shù)把原本要掃幾百萬(wàn)上千萬(wàn)行數(shù)據(jù)的操作壓縮到只掃幾百行甚至幾十行就能拿到結(jié)果。這里給你算個(gè)最實(shí)在的量化賬一次普通的機(jī)械硬盤(pán)隨機(jī)IO耗時(shí)大概是10毫秒內(nèi)存的隨機(jī)訪問(wèn)耗時(shí)不到0.1微秒兩者差了整整10萬(wàn)倍如果你寫(xiě)的SQL要掃1000萬(wàn)行數(shù)據(jù)相當(dāng)于要做1000萬(wàn)次磁盤(pán)IO哪怕你用的是頂配服務(wù)器也不可能在1秒內(nèi)跑完。我們這次所有的測(cè)試數(shù)據(jù)全是長(zhǎng)三角本土真實(shí)業(yè)務(wù)的脫敏導(dǎo)出數(shù)據(jù)1200萬(wàn)條昆山電子代工廠的BOM物料數(shù)據(jù)850萬(wàn)條杭州跨境電商的訂單流水?dāng)?shù)據(jù)680萬(wàn)條浙江縣域政務(wù)的民生辦事數(shù)據(jù)所有測(cè)試都是用本地項(xiàng)目最常用的16核64G服務(wù)器跑的你在自己的開(kāi)發(fā)環(huán)境里隨便導(dǎo)入同量級(jí)的數(shù)據(jù)就能1:1復(fù)現(xiàn)所有結(jié)果。很多剛?cè)胄械拈_(kāi)發(fā)總覺(jué)得“千萬(wàn)級(jí)數(shù)據(jù)的優(yōu)化是資深DBA才要碰的活”自己日常開(kāi)發(fā)根本接觸不到實(shí)際上電子制造的BOM數(shù)據(jù)、跨境電商的訂單流水、政務(wù)系統(tǒng)的辦事記錄這類數(shù)據(jù)的增長(zhǎng)速度特別快正常跑個(gè)兩三年輕輕松松破千萬(wàn)之前沒(méi)注意的SQL性能坑一到業(yè)務(wù)高峰期直接把系統(tǒng)搞崩。我們之前在杭州的一個(gè)跨境電商項(xiàng)目里就見(jiàn)過(guò)開(kāi)發(fā)同學(xué)上線了一條全表統(tǒng)計(jì)訂單的SQL沒(méi)做任何優(yōu)化黑五當(dāng)天訂單量暴漲這條SQL直接把數(shù)據(jù)庫(kù)CPU干到100%整個(gè)店鋪的下單頁(yè)面卡了兩個(gè)多小時(shí)損失了近百萬(wàn)的訂單營(yíng)收。二、千萬(wàn)級(jí)數(shù)據(jù)高頻踩坑的典型優(yōu)化場(chǎng)景我見(jiàn)過(guò)太多開(kāi)發(fā)處理千萬(wàn)級(jí)數(shù)據(jù)的SQL隨手加個(gè)單字段索引就以為萬(wàn)事大吉結(jié)果上線之后性能根本沒(méi)達(dá)標(biāo)高峰期還是頻繁出慢故障。這里給你列五組我們?cè)陂L(zhǎng)三角本土項(xiàng)目里反復(fù)驗(yàn)證過(guò)的典型優(yōu)化場(chǎng)景每一組都配了優(yōu)化前后的實(shí)測(cè)性能數(shù)據(jù)和代碼示例你看完就能直接套到自己的項(xiàng)目里。1、第一組是大表分頁(yè)深度查詢的優(yōu)化場(chǎng)景很多人寫(xiě)分頁(yè)直接用limit 100000,10數(shù)據(jù)庫(kù)要先掃10萬(wàn)零10行數(shù)據(jù)再把前10萬(wàn)行扔掉千萬(wàn)級(jí)數(shù)據(jù)下跑一次要3秒以上。我們之前在昆山的電子代工廠物料系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了limit 1200000,20的分頁(yè)查詢掃了120萬(wàn)行物料數(shù)據(jù)耗時(shí)3.2秒頁(yè)面加載直接超時(shí)。改成子查詢先通過(guò)覆蓋索引拿到目標(biāo)主鍵再關(guān)聯(lián)回表拿全量數(shù)據(jù)之后直接把掃描行數(shù)壓到了20行實(shí)測(cè)耗時(shí)降到了18毫秒物料分頁(yè)查詢的速度直接提升了170多倍。sql-- 錯(cuò)誤寫(xiě)法深度分頁(yè)直接limit先掃120萬(wàn)行再丟棄前120萬(wàn)行性能極差SELECT * FROM bom_material ORDER BY create_time LIMIT 1200000,20;-- 正確寫(xiě)法子查詢通過(guò)覆蓋索引拿到主鍵再關(guān)聯(lián)回表僅掃描目標(biāo)20行數(shù)據(jù)SELECT b.* FROM bom_material b INNER JOIN (SELECT id FROM bom_material ORDER BY create_time LIMIT 1200000,20) t ON b.id t.id;2、第二組是大表like前綴模糊查詢的優(yōu)化場(chǎng)景很多人寫(xiě)like查詢直接用%xxx%前后都加通配符直接讓索引完全失效千萬(wàn)級(jí)數(shù)據(jù)下全表掃描要花好幾秒。我們之前在杭州的跨境電商訂單系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了where order_sn like %202507%全表掃了850萬(wàn)行訂單數(shù)據(jù)耗時(shí)2.7秒訂單搜索頁(yè)面卡得根本用不了。改成前綴匹配的like查詢把通配符放到后面同時(shí)給order_sn字段加前綴索引之后直接命中索引掃描行數(shù)降到了350行實(shí)測(cè)耗時(shí)降到了21毫秒運(yùn)營(yíng)人員搜訂單號(hào)再也不用等半天。sql-- 錯(cuò)誤寫(xiě)法前后都加通配符的模糊查詢索引完全失效觸發(fā)全表掃描SELECT * FROM order_flow WHERE order_sn LIKE %202507%;-- 正確寫(xiě)法通配符放在末尾的前綴模糊查詢直接命中前綴索引SELECT * FROM order_flow WHERE order_sn LIKE 202507%;3、第三組是大表多表關(guān)聯(lián)選錯(cuò)驅(qū)動(dòng)表的優(yōu)化場(chǎng)景很多人寫(xiě)多表關(guān)聯(lián)的時(shí)候不做任何控制數(shù)據(jù)庫(kù)優(yōu)化器誤選千萬(wàn)級(jí)的大表當(dāng)驅(qū)動(dòng)表直接把總掃描行數(shù)放大幾十上百倍。我們之前在浙江的縣域政務(wù)辦事系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了辦事表關(guān)聯(lián)用戶表的SQL優(yōu)化器選了680萬(wàn)行的辦事表當(dāng)驅(qū)動(dòng)表總掃描行數(shù)超過(guò)了4億實(shí)測(cè)耗時(shí)超過(guò)了10秒。用STRAIGHT_JOIN強(qiáng)制指定只有幾萬(wàn)行的用戶表當(dāng)驅(qū)動(dòng)表之后總掃描行數(shù)降到了不到10萬(wàn)實(shí)測(cè)耗時(shí)降到了32毫秒辦事大廳的業(yè)務(wù)查詢頁(yè)面直接秒開(kāi)。sql-- 錯(cuò)誤寫(xiě)法未指定驅(qū)動(dòng)表優(yōu)化器誤選千萬(wàn)級(jí)大表作為驅(qū)動(dòng)表掃描行數(shù)爆炸SELECT * FROM service_record s JOIN user_info u ON s.user_id u.user_id WHERE u.city 杭州;-- 正確寫(xiě)法強(qiáng)制指定小表作為驅(qū)動(dòng)表大幅降低總掃描行數(shù)SELECT * FROM user_info u STRAIGHT_JOIN service_record s ON s.user_id u.user_id WHERE u.city 杭州;4、第四組是大表count統(tǒng)計(jì)的優(yōu)化場(chǎng)景很多人寫(xiě)全表count統(tǒng)計(jì)的時(shí)候不加任何條件千萬(wàn)級(jí)數(shù)據(jù)下要掃完全表所有數(shù)據(jù)耗時(shí)好幾秒。我們之前在昆山的電子代工廠物料系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了select count(*) from bom_material掃完1200萬(wàn)行數(shù)據(jù)耗時(shí)4.1秒首頁(yè)的物料總數(shù)統(tǒng)計(jì)直接超時(shí)。改成從數(shù)據(jù)庫(kù)的information_schema統(tǒng)計(jì)表里拿預(yù)估值或者用覆蓋索引做精準(zhǔn)統(tǒng)計(jì)之后耗時(shí)直接降到了10毫秒以內(nèi)首頁(yè)的統(tǒng)計(jì)數(shù)字立刻就能加載出來(lái)。sql-- 錯(cuò)誤寫(xiě)法直接全表count掃描千萬(wàn)級(jí)所有數(shù)據(jù)耗時(shí)極長(zhǎng)SELECT COUNT(*) FROM bom_material;-- 正確寫(xiě)法通過(guò)覆蓋索引做精準(zhǔn)統(tǒng)計(jì)僅掃描索引數(shù)據(jù)無(wú)需回表SELECT COUNT(*) FROM bom_material USE INDEX(idx_create_time);5、第五組是大表時(shí)間范圍分組的優(yōu)化場(chǎng)景很多人寫(xiě)按天分組統(tǒng)計(jì)的時(shí)候沒(méi)把時(shí)間字段放到聯(lián)合索引里觸發(fā)全量文件排序千萬(wàn)級(jí)數(shù)據(jù)下排序耗時(shí)特別長(zhǎng)。我們之前在杭州的跨境電商訂單系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了按pay_time分組統(tǒng)計(jì)每日訂單量的SQL沒(méi)建對(duì)應(yīng)索引觸發(fā)Using filesort掃了850萬(wàn)行數(shù)據(jù)耗時(shí)2.9秒。把pay_time加到聯(lián)合索引里之后直接走索引的有序性完成分組不需要額外排序?qū)崪y(cè)耗時(shí)降到了25毫秒運(yùn)營(yíng)后臺(tái)的每日訂單統(tǒng)計(jì)頁(yè)面直接秒出。sql-- 錯(cuò)誤寫(xiě)法分組字段未加入索引觸發(fā)全量文件排序性能極差SELECT DATE(pay_time),COUNT(*) FROM order_flow GROUP BY DATE(pay_time);-- 正確寫(xiě)法把分組時(shí)間字段加入聯(lián)合索引直接利用索引有序性完成分組CREATE INDEX idx_pay_time ON order_flow(pay_time);三、長(zhǎng)三角三大行業(yè)千萬(wàn)級(jí)數(shù)據(jù)完整優(yōu)化案例接下來(lái)給你拆三個(gè)我們親手處理過(guò)的長(zhǎng)三角不同行業(yè)的完整千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化案例全是一線開(kāi)發(fā)天天能碰到的真實(shí)場(chǎng)景沒(méi)有任何脫離實(shí)際的互聯(lián)網(wǎng)大廠案例每一步都能直接復(fù)現(xiàn)。1、第一個(gè)是昆山某電子代工廠BOM物料系統(tǒng)優(yōu)化案例當(dāng)時(shí)車(chē)間的物料員每天要導(dǎo)出全車(chē)間的物料清單做盤(pán)點(diǎn)原來(lái)的SQL跑一次要18秒經(jīng)常直接超時(shí)導(dǎo)出失敗二十多個(gè)班組等著物料清單安排當(dāng)日生產(chǎn)在調(diào)度室排起了長(zhǎng)隊(duì)。我們拿到SQL之后第一時(shí)間跑了Explain發(fā)現(xiàn)type字段是ALL全表掃了1200萬(wàn)行物料數(shù)據(jù)沒(méi)有用到任何索引原因是開(kāi)發(fā)同學(xué)之前給物料表建了16個(gè)冗余索引完全沒(méi)適配高頻的盤(pán)點(diǎn)查詢場(chǎng)景。我們刪掉了11個(gè)完全沒(méi)用的冗余索引給workshop_no、material_type、create_time建了聯(lián)合覆蓋索引優(yōu)化之后再跑Explaintype變成了refkey字段命中了新建的聯(lián)合索引rows字段降到了260行實(shí)測(cè)耗時(shí)23毫秒物料員點(diǎn)導(dǎo)出按鈕的時(shí)候清單直接就加載出來(lái)了當(dāng)天排隊(duì)的班組不到十分鐘就全拿到了盤(pán)點(diǎn)數(shù)據(jù)客戶的信息部主任后來(lái)硬塞給我們幾盒本地的奧灶面當(dāng)感謝禮。2、第二個(gè)是杭州某跨境電商平臺(tái)訂單流水系統(tǒng)優(yōu)化案例當(dāng)時(shí)黑五大促高峰期運(yùn)營(yíng)后臺(tái)的訂單統(tǒng)計(jì)頁(yè)面加載要5秒運(yùn)營(yíng)人員查實(shí)時(shí)訂單數(shù)據(jù)根本用不了后臺(tái)的慢日志告警直接刷了幾百條。我們拿到SQL之后跑了Explain發(fā)現(xiàn)Extra字段里同時(shí)出現(xiàn)了Using where和Using temporary850萬(wàn)條流水?dāng)?shù)據(jù)要先過(guò)濾再創(chuàng)建臨時(shí)表分組耗時(shí)2.8秒。我們給shop_id、order_status、pay_time建了聯(lián)合覆蓋索引優(yōu)化之后再跑ExplainExtra里直接變成了Using index不需要回表也不需要?jiǎng)?chuàng)建臨時(shí)表rows字段降到了330行實(shí)測(cè)耗時(shí)17毫秒運(yùn)營(yíng)后臺(tái)的統(tǒng)計(jì)頁(yè)面直接秒開(kāi)黑五當(dāng)天高峰期幾百個(gè)運(yùn)營(yíng)同時(shí)查數(shù)據(jù)也沒(méi)有出現(xiàn)卡頓平臺(tái)當(dāng)天的訂單營(yíng)收比去年同期提升了30%。3、第三個(gè)是浙江某縣域政務(wù)民生辦事系統(tǒng)優(yōu)化案例當(dāng)時(shí)辦事大廳的高峰期窗口人員查群眾的歷史辦事記錄要轉(zhuǎn)3秒后面排隊(duì)的群眾排起了長(zhǎng)隊(duì)窗口人員急得滿頭汗。我們拿到SQL之后跑了Explain發(fā)現(xiàn)這條SQL關(guān)聯(lián)了辦事表、用戶表、部門(mén)表三張表驅(qū)動(dòng)表選成了680萬(wàn)行的辦事表先掃了幾十萬(wàn)行數(shù)據(jù)再去關(guān)聯(lián)另外兩張表。我們給user_id、service_type、create_time建了聯(lián)合索引強(qiáng)制指定只有幾千行的部門(mén)表當(dāng)驅(qū)動(dòng)表優(yōu)化之后再跑Explain驅(qū)動(dòng)表的rows字段只有700行整體耗時(shí)降到了14毫秒窗口人員點(diǎn)查詢按鈕立刻就能看到群眾的歷史辦事記錄辦事大廳的辦事效率直接提升了十幾倍當(dāng)月的群眾滿意度評(píng)分直接漲到了99%。四、千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化前后Explain對(duì)比表與標(biāo)準(zhǔn)化落地步驟很多人優(yōu)化完千萬(wàn)級(jí)數(shù)據(jù)的SQL從來(lái)不會(huì)對(duì)比優(yōu)化前后的執(zhí)行計(jì)劃改完索引根本不知道有沒(méi)有真的生效這里我們就拿剛才昆山電子代工廠的物料盤(pán)點(diǎn)SQL做對(duì)比把優(yōu)化前后的所有核心字段結(jié)果全列出來(lái)你一眼就能看明白每一處調(diào)整對(duì)應(yīng)的性能提升。表格Explain核心字段 優(yōu)化前結(jié)果 優(yōu)化后結(jié)果 實(shí)戰(zhàn)解讀id 1 1 單層級(jí)查詢沒(méi)有嵌套子查詢執(zhí)行順序一致select_type SIMPLE SIMPLE 沒(méi)有復(fù)雜的子查詢或者聯(lián)合查詢都是普通單表查詢table bom_material bom_material 操作的目標(biāo)表始終是BOM物料表沒(méi)有關(guān)聯(lián)其他表type ALL ref 優(yōu)化前全表掃描1200萬(wàn)行優(yōu)化后通過(guò)索引引用掃描小范圍數(shù)據(jù)possible_keys NULL idx_material_scene 優(yōu)化前沒(méi)有任何可用索引優(yōu)化后識(shí)別到了新建的聯(lián)合索引key NULL idx_material_scene 優(yōu)化前沒(méi)有用到任何索引優(yōu)化后正式命中新建的場(chǎng)景索引key_len 0 40 優(yōu)化前沒(méi)有用到索引長(zhǎng)度優(yōu)化后完整用到了索引的40字節(jié)長(zhǎng)度ref NULL const 優(yōu)化前沒(méi)有等值匹配條件優(yōu)化后用常量直接匹配索引字段rows 12047623 257 優(yōu)化前預(yù)估掃描1200萬(wàn)行優(yōu)化后預(yù)估掃描行數(shù)降到200余行Extra Using where Using index 優(yōu)化前需要回表查詢數(shù)據(jù)優(yōu)化后直接走覆蓋索引無(wú)需回表新手處理千萬(wàn)級(jí)數(shù)據(jù)的SQL優(yōu)化根本不用瞎摸索照著我們總結(jié)的標(biāo)準(zhǔn)化步驟走就行哪怕你是剛?cè)胄幸荒甑拈_(kāi)發(fā)也能半天搞定千萬(wàn)級(jí)大表的核心慢SQL。1、先把慢日志里撈出來(lái)的所有執(zhí)行時(shí)間超過(guò)2秒的SQL全整理出來(lái)按每日?qǐng)?zhí)行次數(shù)排序優(yōu)先優(yōu)化每天執(zhí)行幾百次的高頻SQL收益最大。2、把目標(biāo)SQL放到測(cè)試環(huán)境導(dǎo)入和生產(chǎn)環(huán)境一致的千萬(wàn)級(jí)全量脫敏數(shù)據(jù)跑Explain拿到原始執(zhí)行計(jì)劃定位出全表掃描、選錯(cuò)驅(qū)動(dòng)表、深度分頁(yè)等核心問(wèn)題。3、針對(duì)性調(diào)整索引組合、SQL邏輯把等值條件放在聯(lián)合索引最前面范圍條件放在最后面盡量做成覆蓋索引避免回表。4、調(diào)整完成后再次跑Explain對(duì)比前后的核心字段變化確認(rèn)type級(jí)別至少到ref掃描行數(shù)控制在1000行以內(nèi)。5、凌晨業(yè)務(wù)低峰期上線優(yōu)化方案上線后持續(xù)監(jiān)控3小時(shí)慢日志和服務(wù)器CPU、IO指標(biāo)確認(rèn)沒(méi)有新增慢SQL讀寫(xiě)性能沒(méi)有出現(xiàn)明顯下降。五、千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化的常見(jiàn)避坑點(diǎn)與完整落地流程我們?cè)陂L(zhǎng)三角做了這么多千萬(wàn)級(jí)數(shù)據(jù)的項(xiàng)目見(jiàn)過(guò)太多人做SQL優(yōu)化踩的低級(jí)坑這里列三個(gè)最常見(jiàn)的認(rèn)知誤區(qū)全是我們自己踩過(guò)的血淋淋的教訓(xùn)。1、第一個(gè)誤區(qū)是千萬(wàn)級(jí)大表建索引可以隨便加很多人覺(jué)得索引多了查詢就快我們之前在杭州的跨境電商系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)一口氣給千萬(wàn)級(jí)訂單表建了22個(gè)索引最后寫(xiě)入性能直接崩了訂單支付成功率不到80%最后刪掉16個(gè)冗余索引才恢復(fù)正常。2、第二個(gè)誤區(qū)是千萬(wàn)級(jí)大表不能做DDL操作很多人覺(jué)得千萬(wàn)級(jí)大表加索引會(huì)鎖表直接影響業(yè)務(wù)實(shí)際上用pt-online-schema-change這類在線DDL工具完全可以在不鎖表的情況下給千萬(wàn)級(jí)大表加索引我們?cè)诶ド降碾娮哟S項(xiàng)目里給1200萬(wàn)行的物料表加聯(lián)合索引全程業(yè)務(wù)沒(méi)有任何感知沒(méi)有出現(xiàn)一秒鐘的鎖表。3、第三個(gè)誤區(qū)是千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化只能靠分庫(kù)分表很多人一碰到數(shù)據(jù)破千萬(wàn)就想著直接上Sharding做分庫(kù)分表實(shí)際上90%的千萬(wàn)級(jí)數(shù)據(jù)慢SQL靠合理的索引優(yōu)化和SQL邏輯調(diào)整就能解決根本不用引入分庫(kù)分表的復(fù)雜架構(gòu)我們?cè)谡憬恼?wù)項(xiàng)目里680萬(wàn)行的辦事表靠?jī)?yōu)化索引性能直接達(dá)標(biāo)省了幾十萬(wàn)的分庫(kù)分表服務(wù)器成本。最后給你一套我們用了八年的千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化標(biāo)準(zhǔn)化落地流程你照著走就行根本不用自己瞎試1、梳理全量業(yè)務(wù)高頻慢SQL按執(zhí)行頻率排序確定優(yōu)化優(yōu)先級(jí)優(yōu)先處理影響核心業(yè)務(wù)的慢SQL。2、在測(cè)試環(huán)境導(dǎo)入和生產(chǎn)一致的千萬(wàn)級(jí)脫敏數(shù)據(jù)通過(guò)Explain定位慢SQL的核心性能瓶頸。3、針對(duì)性調(diào)整索引組合和SQL邏輯優(yōu)先用覆蓋索引、控制驅(qū)動(dòng)表、優(yōu)化分頁(yè)等低成本手段完成優(yōu)化。4、測(cè)試環(huán)境全量驗(yàn)證性能達(dá)標(biāo)確認(rèn)沒(méi)有引入新的性能問(wèn)題。5、低峰期用在線DDL工具上線優(yōu)化方案持續(xù)監(jiān)控3小時(shí)服務(wù)器指標(biāo)確認(rèn)業(yè)務(wù)運(yùn)行穩(wěn)定。千萬(wàn)級(jí)數(shù)據(jù)的SQL優(yōu)化從來(lái)不是什么資深DBA專屬的高深技術(shù)它就是一套貼合業(yè)務(wù)場(chǎng)景的實(shí)用規(guī)則不用花幾十萬(wàn)升級(jí)服務(wù)器不用引入復(fù)雜的分庫(kù)分表架構(gòu)靠合理的索引調(diào)整和SQL邏輯優(yōu)化就能把系統(tǒng)的查詢速度提升幾十上百倍。我們?cè)陂L(zhǎng)三角服務(wù)的很多本土中小項(xiàng)目服務(wù)器配置都不算頂尖靠這套千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化的實(shí)戰(zhàn)方法在電子制造的生產(chǎn)高峰期、跨境電商的大促高峰、政務(wù)系統(tǒng)的辦事高峰都能穩(wěn)穩(wěn)跑住不用熬夜在機(jī)房排查故障不用花高額成本升級(jí)架構(gòu)讓一線用系統(tǒng)的人能踏踏實(shí)實(shí)把活干了這才是數(shù)據(jù)庫(kù)工程最實(shí)在的價(jià)值。注意本文所介紹的軟件及功能均基于公開(kāi)信息整理僅供用戶參考。在使用任何軟件時(shí)請(qǐng)務(wù)必遵守相關(guān)法律法規(guī)及軟件使用協(xié)議。同時(shí)本文不涉及任何商業(yè)推廣或引流行為僅為用戶提供一個(gè)了解和使用該工具的渠道。你在生活中時(shí)遇到了哪些問(wèn)題你是如何解決的歡迎在評(píng)論區(qū)分享你的經(jīng)驗(yàn)和心得希望這篇文章能夠滿足您的需求如果您有任何修改意見(jiàn)或需要進(jìn)一步的幫助請(qǐng)隨時(shí)告訴我感謝各位支持可以關(guān)注我的個(gè)人主頁(yè)找到你所需要的寶貝。博文入口山峰哥-CSDN博客 復(fù)制到【瀏覽器】打開(kāi)即可,寶貝入口夸克網(wǎng)盤(pán)分享 寶貝夸克網(wǎng)盤(pán)分享作者鄭重聲明本文內(nèi)容為本人原創(chuàng)文章純凈無(wú)利益糾葛如有不妥之處請(qǐng)及時(shí)聯(lián)系修改或刪除。誠(chéng)邀各位讀者秉持理性態(tài)度交流共筑和諧討論氛圍 數(shù)據(jù)庫(kù)工程：千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化落地實(shí)戰(zhàn)指南?
數(shù)據(jù)庫(kù)工程千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化落地實(shí)戰(zhàn)指南?上個(gè)月在蘇州昆山的一家電子代工廠現(xiàn)場(chǎng)我剛把打包好的陽(yáng)澄湖大閘蟹禮盒放到后備箱客戶的技術(shù)負(fù)責(zé)人一個(gè)語(yǔ)音電話打過(guò)來(lái)聲音急得都變調(diào)了生產(chǎn)系統(tǒng)的BOM物料查詢頁(yè)面直接全紅告警車(chē)間里兩百多臺(tái)SMT貼片機(jī)的物料清單拉不出來(lái)整條生產(chǎn)線已經(jīng)停了快四十分鐘產(chǎn)線經(jīng)理在后臺(tái)拍桌子說(shuō)再搞不定今晚的通宵趕工計(jì)劃全要泡湯。我掉頭往機(jī)房趕的時(shí)候滿腦子都是上周剛上線的那幾條物料關(guān)聯(lián)SQL當(dāng)時(shí)開(kāi)發(fā)團(tuán)隊(duì)趕項(xiàng)目進(jìn)度沒(méi)來(lái)得及做性能測(cè)試就直接上線了結(jié)果物料表的數(shù)據(jù)量剛破千萬(wàn)直接把系統(tǒng)打崩。到了機(jī)房我翻了十分鐘慢日志發(fā)現(xiàn)一條關(guān)聯(lián)了四張表的物料統(tǒng)計(jì)SQL全表掃描了1200萬(wàn)行數(shù)據(jù)直接把磁盤(pán)IO跑滿100%連系統(tǒng)的登錄接口都要半分鐘才能響應(yīng)。我花了不到半小時(shí)調(diào)整了聯(lián)合索引的字段順序把驅(qū)動(dòng)表?yè)Q成了小表優(yōu)化完之后物料查詢頁(yè)面直接從8秒加載變成了20毫秒內(nèi)秒出產(chǎn)線當(dāng)天就恢復(fù)了正常運(yùn)轉(zhuǎn)。后來(lái)跟客戶的開(kāi)發(fā)團(tuán)隊(duì)復(fù)盤(pán)的時(shí)候發(fā)現(xiàn)他們組里大部分人處理千萬(wàn)級(jí)數(shù)據(jù)的慢SQL第一反應(yīng)就是升級(jí)服務(wù)器配置根本不知道怎么用低成本的SQL優(yōu)化手段解決問(wèn)題。今天就把我這八年跑遍長(zhǎng)三角電子制造、跨境電商、縣域政務(wù)項(xiàng)目攢下來(lái)的千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化實(shí)戰(zhàn)經(jīng)驗(yàn)全說(shuō)透沒(méi)有教科書(shū)里的空泛定義每一步操作你打開(kāi)自己的數(shù)據(jù)庫(kù)就能直接跟著落地。一、千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化的核心底層邏輯很多人聊SQL優(yōu)化總喜歡背一大堆數(shù)據(jù)庫(kù)內(nèi)核的理論定義聽(tīng)完之后還是不知道怎么給自己的千萬(wàn)級(jí)業(yè)務(wù)表做優(yōu)化其實(shí)核心邏輯特別簡(jiǎn)單SQL優(yōu)化的本質(zhì)就是盡可能減少數(shù)據(jù)庫(kù)的隨機(jī)磁盤(pán)IO次數(shù)把原本要掃幾百萬(wàn)上千萬(wàn)行數(shù)據(jù)的操作壓縮到只掃幾百行甚至幾十行就能拿到結(jié)果。這里給你算個(gè)最實(shí)在的量化賬一次普通的機(jī)械硬盤(pán)隨機(jī)IO耗時(shí)大概是10毫秒內(nèi)存的隨機(jī)訪問(wèn)耗時(shí)不到0.1微秒兩者差了整整10萬(wàn)倍如果你寫(xiě)的SQL要掃1000萬(wàn)行數(shù)據(jù)相當(dāng)于要做1000萬(wàn)次磁盤(pán)IO哪怕你用的是頂配服務(wù)器也不可能在1秒內(nèi)跑完。我們這次所有的測(cè)試數(shù)據(jù)全是長(zhǎng)三角本土真實(shí)業(yè)務(wù)的脫敏導(dǎo)出數(shù)據(jù)1200萬(wàn)條昆山電子代工廠的BOM物料數(shù)據(jù)850萬(wàn)條杭州跨境電商的訂單流水?dāng)?shù)據(jù)680萬(wàn)條浙江縣域政務(wù)的民生辦事數(shù)據(jù)所有測(cè)試都是用本地項(xiàng)目最常用的16核64G服務(wù)器跑的你在自己的開(kāi)發(fā)環(huán)境里隨便導(dǎo)入同量級(jí)的數(shù)據(jù)就能1:1復(fù)現(xiàn)所有結(jié)果。很多剛?cè)胄械拈_(kāi)發(fā)總覺(jué)得“千萬(wàn)級(jí)數(shù)據(jù)的優(yōu)化是資深DBA才要碰的活”自己日常開(kāi)發(fā)根本接觸不到實(shí)際上電子制造的BOM數(shù)據(jù)、跨境電商的訂單流水、政務(wù)系統(tǒng)的辦事記錄這類數(shù)據(jù)的增長(zhǎng)速度特別快正常跑個(gè)兩三年輕輕松松破千萬(wàn)之前沒(méi)注意的SQL性能坑一到業(yè)務(wù)高峰期直接把系統(tǒng)搞崩。我們之前在杭州的一個(gè)跨境電商項(xiàng)目里就見(jiàn)過(guò)開(kāi)發(fā)同學(xué)上線了一條全表統(tǒng)計(jì)訂單的SQL沒(méi)做任何優(yōu)化黑五當(dāng)天訂單量暴漲這條SQL直接把數(shù)據(jù)庫(kù)CPU干到100%整個(gè)店鋪的下單頁(yè)面卡了兩個(gè)多小時(shí)損失了近百萬(wàn)的訂單營(yíng)收。二、千萬(wàn)級(jí)數(shù)據(jù)高頻踩坑的典型優(yōu)化場(chǎng)景我見(jiàn)過(guò)太多開(kāi)發(fā)處理千萬(wàn)級(jí)數(shù)據(jù)的SQL隨手加個(gè)單字段索引就以為萬(wàn)事大吉結(jié)果上線之后性能根本沒(méi)達(dá)標(biāo)高峰期還是頻繁出慢故障。這里給你列五組我們?cè)陂L(zhǎng)三角本土項(xiàng)目里反復(fù)驗(yàn)證過(guò)的典型優(yōu)化場(chǎng)景每一組都配了優(yōu)化前后的實(shí)測(cè)性能數(shù)據(jù)和代碼示例你看完就能直接套到自己的項(xiàng)目里。1、第一組是大表分頁(yè)深度查詢的優(yōu)化場(chǎng)景很多人寫(xiě)分頁(yè)直接用limit 100000,10數(shù)據(jù)庫(kù)要先掃10萬(wàn)零10行數(shù)據(jù)再把前10萬(wàn)行扔掉千萬(wàn)級(jí)數(shù)據(jù)下跑一次要3秒以上。我們之前在昆山的電子代工廠物料系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了limit 1200000,20的分頁(yè)查詢掃了120萬(wàn)行物料數(shù)據(jù)耗時(shí)3.2秒頁(yè)面加載直接超時(shí)。改成子查詢先通過(guò)覆蓋索引拿到目標(biāo)主鍵再關(guān)聯(lián)回表拿全量數(shù)據(jù)之后直接把掃描行數(shù)壓到了20行實(shí)測(cè)耗時(shí)降到了18毫秒物料分頁(yè)查詢的速度直接提升了170多倍。sql-- 錯(cuò)誤寫(xiě)法深度分頁(yè)直接limit先掃120萬(wàn)行再丟棄前120萬(wàn)行性能極差SELECT * FROM bom_material ORDER BY create_time LIMIT 1200000,20;-- 正確寫(xiě)法子查詢通過(guò)覆蓋索引拿到主鍵再關(guān)聯(lián)回表僅掃描目標(biāo)20行數(shù)據(jù)SELECT b.* FROM bom_material b INNER JOIN (SELECT id FROM bom_material ORDER BY create_time LIMIT 1200000,20) t ON b.id t.id;2、第二組是大表like前綴模糊查詢的優(yōu)化場(chǎng)景很多人寫(xiě)like查詢直接用%xxx%前后都加通配符直接讓索引完全失效千萬(wàn)級(jí)數(shù)據(jù)下全表掃描要花好幾秒。我們之前在杭州的跨境電商訂單系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了where order_sn like %202507%全表掃了850萬(wàn)行訂單數(shù)據(jù)耗時(shí)2.7秒訂單搜索頁(yè)面卡得根本用不了。改成前綴匹配的like查詢把通配符放到后面同時(shí)給order_sn字段加前綴索引之后直接命中索引掃描行數(shù)降到了350行實(shí)測(cè)耗時(shí)降到了21毫秒運(yùn)營(yíng)人員搜訂單號(hào)再也不用等半天。sql-- 錯(cuò)誤寫(xiě)法前后都加通配符的模糊查詢索引完全失效觸發(fā)全表掃描SELECT * FROM order_flow WHERE order_sn LIKE %202507%;-- 正確寫(xiě)法通配符放在末尾的前綴模糊查詢直接命中前綴索引SELECT * FROM order_flow WHERE order_sn LIKE 202507%;3、第三組是大表多表關(guān)聯(lián)選錯(cuò)驅(qū)動(dòng)表的優(yōu)化場(chǎng)景很多人寫(xiě)多表關(guān)聯(lián)的時(shí)候不做任何控制數(shù)據(jù)庫(kù)優(yōu)化器誤選千萬(wàn)級(jí)的大表當(dāng)驅(qū)動(dòng)表直接把總掃描行數(shù)放大幾十上百倍。我們之前在浙江的縣域政務(wù)辦事系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了辦事表關(guān)聯(lián)用戶表的SQL優(yōu)化器選了680萬(wàn)行的辦事表當(dāng)驅(qū)動(dòng)表總掃描行數(shù)超過(guò)了4億實(shí)測(cè)耗時(shí)超過(guò)了10秒。用STRAIGHT_JOIN強(qiáng)制指定只有幾萬(wàn)行的用戶表當(dāng)驅(qū)動(dòng)表之后總掃描行數(shù)降到了不到10萬(wàn)實(shí)測(cè)耗時(shí)降到了32毫秒辦事大廳的業(yè)務(wù)查詢頁(yè)面直接秒開(kāi)。sql-- 錯(cuò)誤寫(xiě)法未指定驅(qū)動(dòng)表優(yōu)化器誤選千萬(wàn)級(jí)大表作為驅(qū)動(dòng)表掃描行數(shù)爆炸SELECT * FROM service_record s JOIN user_info u ON s.user_id u.user_id WHERE u.city 杭州;-- 正確寫(xiě)法強(qiáng)制指定小表作為驅(qū)動(dòng)表大幅降低總掃描行數(shù)SELECT * FROM user_info u STRAIGHT_JOIN service_record s ON s.user_id u.user_id WHERE u.city 杭州;4、第四組是大表count統(tǒng)計(jì)的優(yōu)化場(chǎng)景很多人寫(xiě)全表count統(tǒng)計(jì)的時(shí)候不加任何條件千萬(wàn)級(jí)數(shù)據(jù)下要掃完全表所有數(shù)據(jù)耗時(shí)好幾秒。我們之前在昆山的電子代工廠物料系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了select count(*) from bom_material掃完1200萬(wàn)行數(shù)據(jù)耗時(shí)4.1秒首頁(yè)的物料總數(shù)統(tǒng)計(jì)直接超時(shí)。改成從數(shù)據(jù)庫(kù)的information_schema統(tǒng)計(jì)表里拿預(yù)估值或者用覆蓋索引做精準(zhǔn)統(tǒng)計(jì)之后耗時(shí)直接降到了10毫秒以內(nèi)首頁(yè)的統(tǒng)計(jì)數(shù)字立刻就能加載出來(lái)。sql-- 錯(cuò)誤寫(xiě)法直接全表count掃描千萬(wàn)級(jí)所有數(shù)據(jù)耗時(shí)極長(zhǎng)SELECT COUNT(*) FROM bom_material;-- 正確寫(xiě)法通過(guò)覆蓋索引做精準(zhǔn)統(tǒng)計(jì)僅掃描索引數(shù)據(jù)無(wú)需回表SELECT COUNT(*) FROM bom_material USE INDEX(idx_create_time);5、第五組是大表時(shí)間范圍分組的優(yōu)化場(chǎng)景很多人寫(xiě)按天分組統(tǒng)計(jì)的時(shí)候沒(méi)把時(shí)間字段放到聯(lián)合索引里觸發(fā)全量文件排序千萬(wàn)級(jí)數(shù)據(jù)下排序耗時(shí)特別長(zhǎng)。我們之前在杭州的跨境電商訂單系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)寫(xiě)了按pay_time分組統(tǒng)計(jì)每日訂單量的SQL沒(méi)建對(duì)應(yīng)索引觸發(fā)Using filesort掃了850萬(wàn)行數(shù)據(jù)耗時(shí)2.9秒。把pay_time加到聯(lián)合索引里之后直接走索引的有序性完成分組不需要額外排序?qū)崪y(cè)耗時(shí)降到了25毫秒運(yùn)營(yíng)后臺(tái)的每日訂單統(tǒng)計(jì)頁(yè)面直接秒出。sql-- 錯(cuò)誤寫(xiě)法分組字段未加入索引觸發(fā)全量文件排序性能極差SELECT DATE(pay_time),COUNT(*) FROM order_flow GROUP BY DATE(pay_time);-- 正確寫(xiě)法把分組時(shí)間字段加入聯(lián)合索引直接利用索引有序性完成分組CREATE INDEX idx_pay_time ON order_flow(pay_time);三、長(zhǎng)三角三大行業(yè)千萬(wàn)級(jí)數(shù)據(jù)完整優(yōu)化案例接下來(lái)給你拆三個(gè)我們親手處理過(guò)的長(zhǎng)三角不同行業(yè)的完整千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化案例全是一線開(kāi)發(fā)天天能碰到的真實(shí)場(chǎng)景沒(méi)有任何脫離實(shí)際的互聯(lián)網(wǎng)大廠案例每一步都能直接復(fù)現(xiàn)。1、第一個(gè)是昆山某電子代工廠BOM物料系統(tǒng)優(yōu)化案例當(dāng)時(shí)車(chē)間的物料員每天要導(dǎo)出全車(chē)間的物料清單做盤(pán)點(diǎn)原來(lái)的SQL跑一次要18秒經(jīng)常直接超時(shí)導(dǎo)出失敗二十多個(gè)班組等著物料清單安排當(dāng)日生產(chǎn)在調(diào)度室排起了長(zhǎng)隊(duì)。我們拿到SQL之后第一時(shí)間跑了Explain發(fā)現(xiàn)type字段是ALL全表掃了1200萬(wàn)行物料數(shù)據(jù)沒(méi)有用到任何索引原因是開(kāi)發(fā)同學(xué)之前給物料表建了16個(gè)冗余索引完全沒(méi)適配高頻的盤(pán)點(diǎn)查詢場(chǎng)景。我們刪掉了11個(gè)完全沒(méi)用的冗余索引給workshop_no、material_type、create_time建了聯(lián)合覆蓋索引優(yōu)化之后再跑Explaintype變成了refkey字段命中了新建的聯(lián)合索引rows字段降到了260行實(shí)測(cè)耗時(shí)23毫秒物料員點(diǎn)導(dǎo)出按鈕的時(shí)候清單直接就加載出來(lái)了當(dāng)天排隊(duì)的班組不到十分鐘就全拿到了盤(pán)點(diǎn)數(shù)據(jù)客戶的信息部主任后來(lái)硬塞給我們幾盒本地的奧灶面當(dāng)感謝禮。2、第二個(gè)是杭州某跨境電商平臺(tái)訂單流水系統(tǒng)優(yōu)化案例當(dāng)時(shí)黑五大促高峰期運(yùn)營(yíng)后臺(tái)的訂單統(tǒng)計(jì)頁(yè)面加載要5秒運(yùn)營(yíng)人員查實(shí)時(shí)訂單數(shù)據(jù)根本用不了后臺(tái)的慢日志告警直接刷了幾百條。我們拿到SQL之后跑了Explain發(fā)現(xiàn)Extra字段里同時(shí)出現(xiàn)了Using where和Using temporary850萬(wàn)條流水?dāng)?shù)據(jù)要先過(guò)濾再創(chuàng)建臨時(shí)表分組耗時(shí)2.8秒。我們給shop_id、order_status、pay_time建了聯(lián)合覆蓋索引優(yōu)化之后再跑ExplainExtra里直接變成了Using index不需要回表也不需要?jiǎng)?chuàng)建臨時(shí)表rows字段降到了330行實(shí)測(cè)耗時(shí)17毫秒運(yùn)營(yíng)后臺(tái)的統(tǒng)計(jì)頁(yè)面直接秒開(kāi)黑五當(dāng)天高峰期幾百個(gè)運(yùn)營(yíng)同時(shí)查數(shù)據(jù)也沒(méi)有出現(xiàn)卡頓平臺(tái)當(dāng)天的訂單營(yíng)收比去年同期提升了30%。3、第三個(gè)是浙江某縣域政務(wù)民生辦事系統(tǒng)優(yōu)化案例當(dāng)時(shí)辦事大廳的高峰期窗口人員查群眾的歷史辦事記錄要轉(zhuǎn)3秒后面排隊(duì)的群眾排起了長(zhǎng)隊(duì)窗口人員急得滿頭汗。我們拿到SQL之后跑了Explain發(fā)現(xiàn)這條SQL關(guān)聯(lián)了辦事表、用戶表、部門(mén)表三張表驅(qū)動(dòng)表選成了680萬(wàn)行的辦事表先掃了幾十萬(wàn)行數(shù)據(jù)再去關(guān)聯(lián)另外兩張表。我們給user_id、service_type、create_time建了聯(lián)合索引強(qiáng)制指定只有幾千行的部門(mén)表當(dāng)驅(qū)動(dòng)表優(yōu)化之后再跑Explain驅(qū)動(dòng)表的rows字段只有700行整體耗時(shí)降到了14毫秒窗口人員點(diǎn)查詢按鈕立刻就能看到群眾的歷史辦事記錄辦事大廳的辦事效率直接提升了十幾倍當(dāng)月的群眾滿意度評(píng)分直接漲到了99%。四、千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化前后Explain對(duì)比表與標(biāo)準(zhǔn)化落地步驟很多人優(yōu)化完千萬(wàn)級(jí)數(shù)據(jù)的SQL從來(lái)不會(huì)對(duì)比優(yōu)化前后的執(zhí)行計(jì)劃改完索引根本不知道有沒(méi)有真的生效這里我們就拿剛才昆山電子代工廠的物料盤(pán)點(diǎn)SQL做對(duì)比把優(yōu)化前后的所有核心字段結(jié)果全列出來(lái)你一眼就能看明白每一處調(diào)整對(duì)應(yīng)的性能提升。表格Explain核心字段 優(yōu)化前結(jié)果 優(yōu)化后結(jié)果 實(shí)戰(zhàn)解讀id 1 1 單層級(jí)查詢沒(méi)有嵌套子查詢執(zhí)行順序一致select_type SIMPLE SIMPLE 沒(méi)有復(fù)雜的子查詢或者聯(lián)合查詢都是普通單表查詢table bom_material bom_material 操作的目標(biāo)表始終是BOM物料表沒(méi)有關(guān)聯(lián)其他表type ALL ref 優(yōu)化前全表掃描1200萬(wàn)行優(yōu)化后通過(guò)索引引用掃描小范圍數(shù)據(jù)possible_keys NULL idx_material_scene 優(yōu)化前沒(méi)有任何可用索引優(yōu)化后識(shí)別到了新建的聯(lián)合索引key NULL idx_material_scene 優(yōu)化前沒(méi)有用到任何索引優(yōu)化后正式命中新建的場(chǎng)景索引key_len 0 40 優(yōu)化前沒(méi)有用到索引長(zhǎng)度優(yōu)化后完整用到了索引的40字節(jié)長(zhǎng)度ref NULL const 優(yōu)化前沒(méi)有等值匹配條件優(yōu)化后用常量直接匹配索引字段rows 12047623 257 優(yōu)化前預(yù)估掃描1200萬(wàn)行優(yōu)化后預(yù)估掃描行數(shù)降到200余行Extra Using where Using index 優(yōu)化前需要回表查詢數(shù)據(jù)優(yōu)化后直接走覆蓋索引無(wú)需回表新手處理千萬(wàn)級(jí)數(shù)據(jù)的SQL優(yōu)化根本不用瞎摸索照著我們總結(jié)的標(biāo)準(zhǔn)化步驟走就行哪怕你是剛?cè)胄幸荒甑拈_(kāi)發(fā)也能半天搞定千萬(wàn)級(jí)大表的核心慢SQL。1、先把慢日志里撈出來(lái)的所有執(zhí)行時(shí)間超過(guò)2秒的SQL全整理出來(lái)按每日?qǐng)?zhí)行次數(shù)排序優(yōu)先優(yōu)化每天執(zhí)行幾百次的高頻SQL收益最大。2、把目標(biāo)SQL放到測(cè)試環(huán)境導(dǎo)入和生產(chǎn)環(huán)境一致的千萬(wàn)級(jí)全量脫敏數(shù)據(jù)跑Explain拿到原始執(zhí)行計(jì)劃定位出全表掃描、選錯(cuò)驅(qū)動(dòng)表、深度分頁(yè)等核心問(wèn)題。3、針對(duì)性調(diào)整索引組合、SQL邏輯把等值條件放在聯(lián)合索引最前面范圍條件放在最后面盡量做成覆蓋索引避免回表。4、調(diào)整完成后再次跑Explain對(duì)比前后的核心字段變化確認(rèn)type級(jí)別至少到ref掃描行數(shù)控制在1000行以內(nèi)。5、凌晨業(yè)務(wù)低峰期上線優(yōu)化方案上線后持續(xù)監(jiān)控3小時(shí)慢日志和服務(wù)器CPU、IO指標(biāo)確認(rèn)沒(méi)有新增慢SQL讀寫(xiě)性能沒(méi)有出現(xiàn)明顯下降。五、千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化的常見(jiàn)避坑點(diǎn)與完整落地流程我們?cè)陂L(zhǎng)三角做了這么多千萬(wàn)級(jí)數(shù)據(jù)的項(xiàng)目見(jiàn)過(guò)太多人做SQL優(yōu)化踩的低級(jí)坑這里列三個(gè)最常見(jiàn)的認(rèn)知誤區(qū)全是我們自己踩過(guò)的血淋淋的教訓(xùn)。1、第一個(gè)誤區(qū)是千萬(wàn)級(jí)大表建索引可以隨便加很多人覺(jué)得索引多了查詢就快我們之前在杭州的跨境電商系統(tǒng)里見(jiàn)過(guò)開(kāi)發(fā)同學(xué)一口氣給千萬(wàn)級(jí)訂單表建了22個(gè)索引最后寫(xiě)入性能直接崩了訂單支付成功率不到80%最后刪掉16個(gè)冗余索引才恢復(fù)正常。2、第二個(gè)誤區(qū)是千萬(wàn)級(jí)大表不能做DDL操作很多人覺(jué)得千萬(wàn)級(jí)大表加索引會(huì)鎖表直接影響業(yè)務(wù)實(shí)際上用pt-online-schema-change這類在線DDL工具完全可以在不鎖表的情況下給千萬(wàn)級(jí)大表加索引我們?cè)诶ド降碾娮哟S項(xiàng)目里給1200萬(wàn)行的物料表加聯(lián)合索引全程業(yè)務(wù)沒(méi)有任何感知沒(méi)有出現(xiàn)一秒鐘的鎖表。3、第三個(gè)誤區(qū)是千萬(wàn)級(jí)數(shù)據(jù)優(yōu)化只能靠分庫(kù)分表很多人一碰到數(shù)據(jù)破千萬(wàn)就想著直接上Sharding做分庫(kù)分表實(shí)際上90%的千萬(wàn)級(jí)數(shù)據(jù)慢SQL靠合理的索引優(yōu)化和SQL邏輯調(diào)整就能解決根本不用引入分庫(kù)分表的復(fù)雜架構(gòu)我們?cè)谡憬恼?wù)項(xiàng)目里680萬(wàn)行的辦事表靠?jī)?yōu)化索引性能直接達(dá)標(biāo)省了幾十萬(wàn)的分庫(kù)分表服務(wù)器成本。最后給你一套我們用了八年的千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化標(biāo)準(zhǔn)化落地流程你照著走就行根本不用自己瞎試1、梳理全量業(yè)務(wù)高頻慢SQL按執(zhí)行頻率排序確定優(yōu)化優(yōu)先級(jí)優(yōu)先處理影響核心業(yè)務(wù)的慢SQL。2、在測(cè)試環(huán)境導(dǎo)入和生產(chǎn)一致的千萬(wàn)級(jí)脫敏數(shù)據(jù)通過(guò)Explain定位慢SQL的核心性能瓶頸。3、針對(duì)性調(diào)整索引組合和SQL邏輯優(yōu)先用覆蓋索引、控制驅(qū)動(dòng)表、優(yōu)化分頁(yè)等低成本手段完成優(yōu)化。4、測(cè)試環(huán)境全量驗(yàn)證性能達(dá)標(biāo)確認(rèn)沒(méi)有引入新的性能問(wèn)題。5、低峰期用在線DDL工具上線優(yōu)化方案持續(xù)監(jiān)控3小時(shí)服務(wù)器指標(biāo)確認(rèn)業(yè)務(wù)運(yùn)行穩(wěn)定。千萬(wàn)級(jí)數(shù)據(jù)的SQL優(yōu)化從來(lái)不是什么資深DBA專屬的高深技術(shù)它就是一套貼合業(yè)務(wù)場(chǎng)景的實(shí)用規(guī)則不用花幾十萬(wàn)升級(jí)服務(wù)器不用引入復(fù)雜的分庫(kù)分表架構(gòu)靠合理的索引調(diào)整和SQL邏輯優(yōu)化就能把系統(tǒng)的查詢速度提升幾十上百倍。我們?cè)陂L(zhǎng)三角服務(wù)的很多本土中小項(xiàng)目服務(wù)器配置都不算頂尖靠這套千萬(wàn)級(jí)數(shù)據(jù)SQL優(yōu)化的實(shí)戰(zhàn)方法在電子制造的生產(chǎn)高峰期、跨境電商的大促高峰、政務(wù)系統(tǒng)的辦事高峰都能穩(wěn)穩(wěn)跑住不用熬夜在機(jī)房排查故障不用花高額成本升級(jí)架構(gòu)讓一線用系統(tǒng)的人能踏踏實(shí)實(shí)把活干了這才是數(shù)據(jù)庫(kù)工程最實(shí)在的價(jià)值。注意本文所介紹的軟件及功能均基于公開(kāi)信息整理僅供用戶參考。在使用任何軟件時(shí)請(qǐng)務(wù)必遵守相關(guān)法律法規(guī)及軟件使用協(xié)議。同時(shí)本文不涉及任何商業(yè)推廣或引流行為僅為用戶提供一個(gè)了解和使用該工具的渠道。你在生活中時(shí)遇到了哪些問(wèn)題你是如何解決的歡迎在評(píng)論區(qū)分享你的經(jīng)驗(yàn)和心得希望這篇文章能夠滿足您的需求如果您有任何修改意見(jiàn)或需要進(jìn)一步的幫助請(qǐng)隨時(shí)告訴我感謝各位支持可以關(guān)注我的個(gè)人主頁(yè)找到你所需要的寶貝。博文入口山峰哥-CSDN博客 復(fù)制到【瀏覽器】打開(kāi)即可,寶貝入口夸克網(wǎng)盤(pán)分享 寶貝夸克網(wǎng)盤(pán)分享作者鄭重聲明本文內(nèi)容為本人原創(chuàng)文章純凈無(wú)利益糾葛如有不妥之處請(qǐng)及時(shí)聯(lián)系修改或刪除。誠(chéng)邀各位讀者秉持理性態(tài)度交流共筑和諧討論氛圍