化：數(shù)據(jù)庫索引的七個(gè)關(guān)鍵點(diǎn))
索引不是銀彈但絕大多數(shù)慢查詢的根源恰恰是索引沒建對(duì)。開發(fā)十年我見過太多系統(tǒng)在流量沖擊下崩塌DBA拉出慢日志滿屏的typeALL和rows134562。而修復(fù)方式往往簡單到令人羞愧加一個(gè)復(fù)合索引查詢從12秒降到30毫秒。這七千字我把數(shù)據(jù)庫索引的七個(gè)關(guān)鍵點(diǎn)拆開揉碎每一處都是血淚換來的經(jīng)驗(yàn)。1. 選擇性是索引的靈魂但別被唯一值騙了很多人建索引第一反應(yīng)是找區(qū)分度高的列比如訂單號(hào)、身份證號(hào)。這沒錯(cuò)但選擇性不是越高越好而是夠用就好。一個(gè)只有0和1的布爾列選擇性極低可如果業(yè)務(wù)上90%的查詢都帶WHERE is_deleted0那這個(gè)低選擇性列放在索引最左端反而能過濾掉海量已刪數(shù)據(jù)——前提是剩余10%的歷史數(shù)據(jù)不常被訪問。真正的選擇性問題出現(xiàn)在前綴重復(fù)度上比如電話號(hào)碼前三位是運(yùn)營商號(hào)段前七位是地區(qū)碼如果你只對(duì)前六位建索引那區(qū)分度可能只有幾十完全無法限制掃描范圍。我建議用COUNT(DISTINCT LEFT(col,N)) / COUNT()去實(shí)際測(cè)算曲線的拐點(diǎn)找到那個(gè)增加前綴長度幾乎不再提升區(qū)分度的位置。別用直覺判斷選擇性用SQL跑一遍基數(shù)統(tǒng)計(jì)。2. 復(fù)合索引的最左前綴原則恰恰是最常被違反的MySQL、PostgreSQL都遵循最左前綴匹配。但這帶來一個(gè)隱蔽陷阱把等值查詢的列放在前面范圍查詢的列放在后面。比如WHERE status1 AND create_time 2024-01-01復(fù)合索引應(yīng)該建(status, create_time)因?yàn)閟tatus是精確匹配create_time是范圍?？梢坏┱{(diào)換順序MySQL只能用到索引里status那一截create_time的范圍條件會(huì)退化成文件排序。更微妙的是多個(gè)范圍條件同時(shí)存在時(shí)只能有一個(gè)范圍列用到索引剩下的全靠回表。所以建復(fù)合索引前先問自己這條SQL里哪些列是哪些是或或BETWEEN把等值列全部前置范圍列放最后且一個(gè)索引最多容忍一個(gè)范圍列。3. 覆蓋索引是最廉價(jià)的性能核彈為什么覆蓋索引效果炸裂因?yàn)樗尣樵兺耆@開回表操作。普通二級(jí)索引找到主鍵后還得再去聚簇索引里撈整行數(shù)據(jù)這中間是隨機(jī)I/O。而覆蓋索引的葉子節(jié)點(diǎn)已經(jīng)包含了查詢需要的所有列InnoDB直接返回索引數(shù)據(jù)。設(shè)計(jì)時(shí)要刻意讓索引覆蓋高頻查詢的字段。比如列表頁通常只顯示id, title, create_time那你建(status, create_time, title)時(shí)如果查詢只挑這三列這個(gè)索引直接變成覆蓋索引。加一個(gè)看似多余的列能省掉每次查詢幾千次隨機(jī)I/O。但也要克制索引不是越寬越好每個(gè)索引都會(huì)拖慢寫入速度并占據(jù)內(nèi)存緩沖池。覆蓋索引只對(duì)高頻且固定的查詢值得堆字段低頻寬表查詢不如直接建普通索引。4. 前綴索引能救大字段但有三個(gè)致命前提遇到VARCHAR(255)的URL、郵箱、備注這種超長列直接建完整索引會(huì)吃掉大量空間緩沖池?zé)釘?shù)據(jù)被擠占。前綴索引把前N個(gè)字符提取出來做索引鍵體積驟降。但前綴索引無法用于ORDER BY和GROUP BY也無法覆蓋查詢因?yàn)樗饕锎娴牟皇峭暾怠８拥氖乔熬Y索引讓選擇性計(jì)算變得不可見你看著t.猜了10個(gè)字符實(shí)際可能基數(shù)和5個(gè)字符沒區(qū)別。我見踩坑最多的場(chǎng)景是把email列建了前綴索引但查詢條件是WHERE email abcexample.comMySQL會(huì)先按前綴匹配到幾百條再回表逐行比對(duì)——這其實(shí)比全表掃描快了但遠(yuǎn)沒有完整索引快。只有在列長度極大、且該列基本只做等值查詢、且不做排序分組時(shí)才值得用前綴索引否則寧可把列改成冗余計(jì)算字段直接存哈希值。5. 索引失效的六個(gè)真兇每一個(gè)都讓人頭皮發(fā)麻很多開發(fā)者背熟了不要對(duì)索引列做函數(shù)操作、不要隱式類型轉(zhuǎn)換但實(shí)踐中還有更陰險(xiǎn)的場(chǎng)景。最隱蔽的是字符集不一致表A的user_id是utf8mb4表B的user_id是utf8JOIN時(shí)MySQL只能把其中一個(gè)轉(zhuǎn)成另一個(gè)索引直接失效掃描行數(shù)從幾千飆到幾十萬。其次是IS NULL不等于IS NOT NULLInnoDB對(duì)WHERE col IS NULL其實(shí)可以利用索引但I(xiàn)S NOT NULL通常不走因?yàn)樗饕锊淮鎯?chǔ)NULL值相反方向掃描代價(jià)更高。還有LIKE %keyword這個(gè)經(jīng)典的左模糊任何索引都救不了除非你改成全文索引或者反轉(zhuǎn)存儲(chǔ)。別忘了數(shù)據(jù)分布會(huì)讓優(yōu)化器放棄索引當(dāng)索引列有大量重復(fù)值比如status1占了全表90%優(yōu)化器一算發(fā)現(xiàn)走全表掃描比讀索引快于是執(zhí)行計(jì)劃無視索引。這種情況的正確解法是分區(qū)表或partial index而不是抱怨優(yōu)化器傻。最后IN加子查詢的坑當(dāng)IN列表超過某個(gè)閾值MySQL 8.0之前是200左右優(yōu)化器會(huì)從索引掃描退化成全表掃描。6. 索引不是越多越好寫入放大是隱形殺手每個(gè)索引在InnoDB里都是一棵獨(dú)立的B樹。插入一行數(shù)據(jù)聚簇索引寫一次每一棵二級(jí)索引樹都要各寫一次。如果表上有5個(gè)索引單條INSERT的磁盤寫入次數(shù)就從1變成6。高并發(fā)寫入場(chǎng)景下索引數(shù)量直接決定TPS上限。更糟的是二級(jí)索引樹的葉子節(jié)點(diǎn)是有序的隨機(jī)插入會(huì)引發(fā)大量頁分裂和頁合并產(chǎn)生寫放大。讀多寫少的表可以堆索引寫多讀少的表必須砍索引。我見過一個(gè)訂單表開發(fā)為了應(yīng)對(duì)各種運(yùn)營報(bào)表建了12個(gè)索引結(jié)果高峰期每次插入要更新12棵B樹磁盤I/O直接飽和。后來砍到4個(gè)核心索引把報(bào)表需求改成走只讀從庫系統(tǒng)立刻穩(wěn)了。索引管理要像代碼評(píng)審一樣每個(gè)索引都必須有明確的業(yè)務(wù)SQL背書。7. 監(jiān)控與維護(hù)索引的健康度比建索引更關(guān)鍵索引建好就能高枕無憂大錯(cuò)特錯(cuò)。統(tǒng)計(jì)信息會(huì)過期MySQL通過采樣統(tǒng)計(jì)的基數(shù)會(huì)隨數(shù)據(jù)增刪而變化舊的統(tǒng)計(jì)信息會(huì)讓優(yōu)化器做出錯(cuò)誤選擇。定期ANALYZE TABLE是必需品但別做太勤每天一次即可。索引碎片化發(fā)生在高頻更新場(chǎng)景頁分裂留下太多空洞邏輯順序和物理順序偏離嚴(yán)重導(dǎo)致范圍掃描時(shí)隨機(jī)I/O激增。ALTER TABLE ... ENGINEInnoDB可以重建表消除碎片但鎖表時(shí)間長得用gh-ost這類在線工具。還有冗余索引檢測(cè)idx_a_b_c和idx_a_b是重復(fù)的后者完全沒必要卻白白承擔(dān)寫入成本。用sys.schema_unused_indexes查看從未被使用的索引果斷刪除。別忘了索引監(jiān)控要關(guān)聯(lián)慢日志每一條慢SQL背后都可能藏著一個(gè)沒走索引的查詢。七個(gè)關(guān)鍵點(diǎn)講完你會(huì)發(fā)現(xiàn)索引設(shè)計(jì)的本質(zhì)是權(quán)衡——用存儲(chǔ)空間換查詢速度用寫入延遲換讀延遲用維護(hù)復(fù)雜度換運(yùn)行時(shí)簡單。沒有放之四海而皆準(zhǔn)的規(guī)則只有針對(duì)具體業(yè)務(wù)負(fù)載的取舍。下次再遇到慢查詢別急著在索引上堆砌先看執(zhí)行計(jì)劃再用PROCEDURE ANALYSE()分析字段分布最后模擬最壞情況的寫入壓力。真正高手的索引設(shè)計(jì)永遠(yuǎn)是對(duì)業(yè)務(wù)查詢模式的深刻理解而不是對(duì)索引語法的機(jī)械套用?；仡^再看那些被DBA罵SQL都不會(huì)寫的夜晚其實(shí)每一條慢SQL都在教你認(rèn)識(shí)數(shù)據(jù)的形狀。數(shù)據(jù)庫優(yōu)化沒有終點(diǎn)只有下一場(chǎng)流量高峰。索引是你與磁盤之間的談判籌碼——你多建一個(gè)索引磁盤就得多流一滴汗水。學(xué)會(huì)用最少的結(jié)構(gòu)支撐最鋒利的查詢這才是后端性能優(yōu)化的藝術(shù)。 后端性能優(yōu)化：數(shù)據(jù)庫索引的七個(gè)關(guān)鍵點(diǎn)
索引不是銀彈但絕大多數(shù)慢查詢的根源恰恰是索引沒建對(duì)。開發(fā)十年我見過太多系統(tǒng)在流量沖擊下崩塌DBA拉出慢日志滿屏的typeALL和rows134562。而修復(fù)方式往往簡單到令人羞愧加一個(gè)復(fù)合索引查詢從12秒降到30毫秒。這七千字我把數(shù)據(jù)庫索引的七個(gè)關(guān)鍵點(diǎn)拆開揉碎每一處都是血淚換來的經(jīng)驗(yàn)。1. 選擇性是索引的靈魂但別被唯一值騙了很多人建索引第一反應(yīng)是找區(qū)分度高的列比如訂單號(hào)、身份證號(hào)。這沒錯(cuò)但選擇性不是越高越好而是夠用就好。一個(gè)只有0和1的布爾列選擇性極低可如果業(yè)務(wù)上90%的查詢都帶WHERE is_deleted0那這個(gè)低選擇性列放在索引最左端反而能過濾掉海量已刪數(shù)據(jù)——前提是剩余10%的歷史數(shù)據(jù)不常被訪問。真正的選擇性問題出現(xiàn)在前綴重復(fù)度上比如電話號(hào)碼前三位是運(yùn)營商號(hào)段前七位是地區(qū)碼如果你只對(duì)前六位建索引那區(qū)分度可能只有幾十完全無法限制掃描范圍。我建議用COUNT(DISTINCT LEFT(col,N)) / COUNT()去實(shí)際測(cè)算曲線的拐點(diǎn)找到那個(gè)增加前綴長度幾乎不再提升區(qū)分度的位置。別用直覺判斷選擇性用SQL跑一遍基數(shù)統(tǒng)計(jì)。2. 復(fù)合索引的最左前綴原則恰恰是最常被違反的MySQL、PostgreSQL都遵循最左前綴匹配。但這帶來一個(gè)隱蔽陷阱把等值查詢的列放在前面范圍查詢的列放在后面。比如WHERE status1 AND create_time 2024-01-01復(fù)合索引應(yīng)該建(status, create_time)因?yàn)閟tatus是精確匹配create_time是范圍?？梢坏┱{(diào)換順序MySQL只能用到索引里status那一截create_time的范圍條件會(huì)退化成文件排序。更微妙的是多個(gè)范圍條件同時(shí)存在時(shí)只能有一個(gè)范圍列用到索引剩下的全靠回表。所以建復(fù)合索引前先問自己這條SQL里哪些列是哪些是或或BETWEEN把等值列全部前置范圍列放最后且一個(gè)索引最多容忍一個(gè)范圍列。3. 覆蓋索引是最廉價(jià)的性能核彈為什么覆蓋索引效果炸裂因?yàn)樗尣樵兺耆@開回表操作。普通二級(jí)索引找到主鍵后還得再去聚簇索引里撈整行數(shù)據(jù)這中間是隨機(jī)I/O。而覆蓋索引的葉子節(jié)點(diǎn)已經(jīng)包含了查詢需要的所有列InnoDB直接返回索引數(shù)據(jù)。設(shè)計(jì)時(shí)要刻意讓索引覆蓋高頻查詢的字段。比如列表頁通常只顯示id, title, create_time那你建(status, create_time, title)時(shí)如果查詢只挑這三列這個(gè)索引直接變成覆蓋索引。加一個(gè)看似多余的列能省掉每次查詢幾千次隨機(jī)I/O。但也要克制索引不是越寬越好每個(gè)索引都會(huì)拖慢寫入速度并占據(jù)內(nèi)存緩沖池。覆蓋索引只對(duì)高頻且固定的查詢值得堆字段低頻寬表查詢不如直接建普通索引。4. 前綴索引能救大字段但有三個(gè)致命前提遇到VARCHAR(255)的URL、郵箱、備注這種超長列直接建完整索引會(huì)吃掉大量空間緩沖池?zé)釘?shù)據(jù)被擠占。前綴索引把前N個(gè)字符提取出來做索引鍵體積驟降。但前綴索引無法用于ORDER BY和GROUP BY也無法覆蓋查詢因?yàn)樗饕锎娴牟皇峭暾怠８拥氖乔熬Y索引讓選擇性計(jì)算變得不可見你看著t.猜了10個(gè)字符實(shí)際可能基數(shù)和5個(gè)字符沒區(qū)別。我見踩坑最多的場(chǎng)景是把email列建了前綴索引但查詢條件是WHERE email abcexample.comMySQL會(huì)先按前綴匹配到幾百條再回表逐行比對(duì)——這其實(shí)比全表掃描快了但遠(yuǎn)沒有完整索引快。只有在列長度極大、且該列基本只做等值查詢、且不做排序分組時(shí)才值得用前綴索引否則寧可把列改成冗余計(jì)算字段直接存哈希值。5. 索引失效的六個(gè)真兇每一個(gè)都讓人頭皮發(fā)麻很多開發(fā)者背熟了不要對(duì)索引列做函數(shù)操作、不要隱式類型轉(zhuǎn)換但實(shí)踐中還有更陰險(xiǎn)的場(chǎng)景。最隱蔽的是字符集不一致表A的user_id是utf8mb4表B的user_id是utf8JOIN時(shí)MySQL只能把其中一個(gè)轉(zhuǎn)成另一個(gè)索引直接失效掃描行數(shù)從幾千飆到幾十萬。其次是IS NULL不等于IS NOT NULLInnoDB對(duì)WHERE col IS NULL其實(shí)可以利用索引但I(xiàn)S NOT NULL通常不走因?yàn)樗饕锊淮鎯?chǔ)NULL值相反方向掃描代價(jià)更高。還有LIKE %keyword這個(gè)經(jīng)典的左模糊任何索引都救不了除非你改成全文索引或者反轉(zhuǎn)存儲(chǔ)。別忘了數(shù)據(jù)分布會(huì)讓優(yōu)化器放棄索引當(dāng)索引列有大量重復(fù)值比如status1占了全表90%優(yōu)化器一算發(fā)現(xiàn)走全表掃描比讀索引快于是執(zhí)行計(jì)劃無視索引。這種情況的正確解法是分區(qū)表或partial index而不是抱怨優(yōu)化器傻。最后IN加子查詢的坑當(dāng)IN列表超過某個(gè)閾值MySQL 8.0之前是200左右優(yōu)化器會(huì)從索引掃描退化成全表掃描。6. 索引不是越多越好寫入放大是隱形殺手每個(gè)索引在InnoDB里都是一棵獨(dú)立的B樹。插入一行數(shù)據(jù)聚簇索引寫一次每一棵二級(jí)索引樹都要各寫一次。如果表上有5個(gè)索引單條INSERT的磁盤寫入次數(shù)就從1變成6。高并發(fā)寫入場(chǎng)景下索引數(shù)量直接決定TPS上限。更糟的是二級(jí)索引樹的葉子節(jié)點(diǎn)是有序的隨機(jī)插入會(huì)引發(fā)大量頁分裂和頁合并產(chǎn)生寫放大。讀多寫少的表可以堆索引寫多讀少的表必須砍索引。我見過一個(gè)訂單表開發(fā)為了應(yīng)對(duì)各種運(yùn)營報(bào)表建了12個(gè)索引結(jié)果高峰期每次插入要更新12棵B樹磁盤I/O直接飽和。后來砍到4個(gè)核心索引把報(bào)表需求改成走只讀從庫系統(tǒng)立刻穩(wěn)了。索引管理要像代碼評(píng)審一樣每個(gè)索引都必須有明確的業(yè)務(wù)SQL背書。7. 監(jiān)控與維護(hù)索引的健康度比建索引更關(guān)鍵索引建好就能高枕無憂大錯(cuò)特錯(cuò)。統(tǒng)計(jì)信息會(huì)過期MySQL通過采樣統(tǒng)計(jì)的基數(shù)會(huì)隨數(shù)據(jù)增刪而變化舊的統(tǒng)計(jì)信息會(huì)讓優(yōu)化器做出錯(cuò)誤選擇。定期ANALYZE TABLE是必需品但別做太勤每天一次即可。索引碎片化發(fā)生在高頻更新場(chǎng)景頁分裂留下太多空洞邏輯順序和物理順序偏離嚴(yán)重導(dǎo)致范圍掃描時(shí)隨機(jī)I/O激增。ALTER TABLE ... ENGINEInnoDB可以重建表消除碎片但鎖表時(shí)間長得用gh-ost這類在線工具。還有冗余索引檢測(cè)idx_a_b_c和idx_a_b是重復(fù)的后者完全沒必要卻白白承擔(dān)寫入成本。用sys.schema_unused_indexes查看從未被使用的索引果斷刪除。別忘了索引監(jiān)控要關(guān)聯(lián)慢日志每一條慢SQL背后都可能藏著一個(gè)沒走索引的查詢。七個(gè)關(guān)鍵點(diǎn)講完你會(huì)發(fā)現(xiàn)索引設(shè)計(jì)的本質(zhì)是權(quán)衡——用存儲(chǔ)空間換查詢速度用寫入延遲換讀延遲用維護(hù)復(fù)雜度換運(yùn)行時(shí)簡單。沒有放之四海而皆準(zhǔn)的規(guī)則只有針對(duì)具體業(yè)務(wù)負(fù)載的取舍。下次再遇到慢查詢別急著在索引上堆砌先看執(zhí)行計(jì)劃再用PROCEDURE ANALYSE()分析字段分布最后模擬最壞情況的寫入壓力。真正高手的索引設(shè)計(jì)永遠(yuǎn)是對(duì)業(yè)務(wù)查詢模式的深刻理解而不是對(duì)索引語法的機(jī)械套用?；仡^再看那些被DBA罵SQL都不會(huì)寫的夜晚其實(shí)每一條慢SQL都在教你認(rèn)識(shí)數(shù)據(jù)的形狀。數(shù)據(jù)庫優(yōu)化沒有終點(diǎn)只有下一場(chǎng)流量高峰。索引是你與磁盤之間的談判籌碼——你多建一個(gè)索引磁盤就得多流一滴汗水。學(xué)會(huì)用最少的結(jié)構(gòu)支撐最鋒利的查詢這才是后端性能優(yōu)化的藝術(shù)。