本文總結(jié)數(shù)據(jù)庫(kù)索引的核心知識(shí)包括索引的作用、創(chuàng)建方式、索引的自動(dòng)維護(hù)機(jī)制以及如何在查詢速度與空間/寫(xiě)入開(kāi)銷之間做權(quán)衡。以 MySQLInnoDB / B 樹(shù)索引為主要示例。一、索引的作用索引本質(zhì)是一種排好序的數(shù)據(jù)結(jié)構(gòu)多數(shù)用 B 樹(shù)核心作用如下作用說(shuō)明加速查詢把全表掃描O(n)變成樹(shù)查找O(log n)這是索引最主要的價(jià)值加速排序/分組ORDER BY、GROUP BY命中索引可省去額外排序加速連接JOIN 時(shí)關(guān)聯(lián)字段有索引能大幅提速保證唯一性唯一索引可強(qiáng)制列值不重復(fù)覆蓋索引查詢字段全在索引里時(shí)無(wú)需回表讀數(shù)據(jù)行代價(jià)占用額外存儲(chǔ)空間寫(xiě)操作INSERT/UPDATE/DELETE需同步維護(hù)索引會(huì)變慢。所以索引不是越多越好。二、如何創(chuàng)建索引以 MySQL 為例1. 建表時(shí)創(chuàng)建CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,-- 主鍵索引emailVARCHAR(100),nameVARCHAR(50),ageINT,UNIQUEKEYuk_email(email),-- 唯一索引KEYidx_name(name),-- 普通索引KEYidx_name_age(name,age)-- 聯(lián)合索引);2. 對(duì)已有表創(chuàng)建-- 普通索引CREATEINDEXidx_nameONusers(name);-- 唯一索引CREATEUNIQUEINDEXuk_emailONusers(email);-- 聯(lián)合索引多列CREATEINDEXidx_name_ageONusers(name,age);-- 或用 ALTER TABLEALTERTABLEusersADDINDEXidx_age(age);3. 查看 / 刪除SHOWINDEXFROMusers;-- 查看索引DROPINDEXidx_nameONusers;-- 刪除索引三、語(yǔ)法解讀表名(列1, 列2, ...)CREATEINDEXidx_name_ageONusers(name,age);│ │ └──┬───┘ 索引名稱 表名 索引列users—— 表名表示這個(gè)索引建在users表上(name, age)—— 列名列表表示用name和age這兩列的值來(lái)構(gòu)建索引聯(lián)合索引復(fù)合索引當(dāng)括號(hào)里有多個(gè)列時(shí)就是聯(lián)合索引。它會(huì)先按name排序name相同時(shí)再按age排序nameageAmy18Amy25Bob20Bob22最左前綴原則聯(lián)合索引(name, age)的列順序很重要查詢能否用上索引取決于是否從最左列開(kāi)始WHEREnameBob-- ? 用上索引命中最左列 nameWHEREnameBobANDage22-- ? 用上索引name age 都命中WHEREage22-- ? 用不上跳過(guò)了最左列 name類比「電話簿」先按姓排、姓相同再按名排。知道姓能快速定位只知道名不知道姓還是得一頁(yè)頁(yè)翻。四、更新字段時(shí)索引由引擎自動(dòng)維護(hù)對(duì)表做 INSERT / UPDATE / DELETE 時(shí)數(shù)據(jù)庫(kù)引擎會(huì)在同一個(gè)事務(wù)里自動(dòng)把相關(guān)索引一起改掉保證數(shù)據(jù)和索引始終一致無(wú)需手動(dòng)維護(hù)。以UPDATE users SET age 26 WHERE id 1存在索引idx_age(age)為例1. 修改數(shù)據(jù)行聚簇索引 / 主鍵那份真實(shí)數(shù)據(jù) 2. 從 idx_age 索引里刪掉舊值 age25 的索引項(xiàng) 3. 往 idx_age 索引里插入新值 age26 的索引項(xiàng)并重新排到正確位置 ↑ 這些都在一個(gè)事務(wù)里原子完成要么全成功要么全回滾關(guān)鍵點(diǎn)只維護(hù)「被改動(dòng)的列」相關(guān)的索引。若只UPDATE name則idx_age不受影響。索引的寫(xiě)入代價(jià)操作索引層面發(fā)生的事INSERT每個(gè)索引都要插入一條新索引項(xiàng)并維持有序DELETE每個(gè)索引都要?jiǎng)h除對(duì)應(yīng)索引項(xiàng)UPDATE若改的列在索引中 → 刪舊項(xiàng) 插新項(xiàng)可能引發(fā) B 樹(shù)的頁(yè)分裂/合并所以索引越多寫(xiě)操作越慢——讀的時(shí)候爽寫(xiě)的時(shí)候還債。認(rèn)知要點(diǎn)順序會(huì)自動(dòng)維持age 從 25 改成 26索引里位置會(huì)被自動(dòng)挪到正確排序位。崩潰也不怕靠 redo log / WAL 等機(jī)制宕機(jī)重啟后數(shù)據(jù)和索引依然一致?？赡茏兟膱?chǎng)景頻繁更新索引列、或更新導(dǎo)致 B 樹(shù)頁(yè)分裂時(shí)寫(xiě)入開(kāi)銷更明顯。例外——全文索引某些搜索引擎類索引如 Elasticsearch可能是異步/近實(shí)時(shí)更新但普通 B 樹(shù)索引都是同步實(shí)時(shí)的。五、如何權(quán)衡查詢提速 vs 空間/寫(xiě)入開(kāi)銷這本質(zhì)是一個(gè)成本收益分析。1. 量化「收益」——查詢快了多少核心工具EXPLAIN/EXPLAIN ANALYZEEXPLAINANALYZESELECT*FROMusersWHEREnameBobANDage22;重點(diǎn)指標(biāo)指標(biāo)含義加索引前后對(duì)比type訪問(wèn)類型ALL全表掃描→ref/range走索引就是收益rows預(yù)估掃描行數(shù)從「幾百萬(wàn)」降到「幾十」就是巨大收益key實(shí)際用的索引從NULL變成索引名 生效了實(shí)際執(zhí)行耗時(shí)ANALYZE 給出真實(shí)時(shí)間前后各跑一次直接對(duì)比判斷原則rows大幅下降如 100萬(wàn) → 100說(shuō)明索引價(jià)值高值得加。2. 量化「成本」——空間和寫(xiě)入開(kāi)銷查看索引占用空間SELECTindex_name,ROUND(stat_value*innodb_page_size/1024/1024,2)ASsize_mbFROMmysql.innodb_index_statsWHEREtable_nameusersANDstat_namesize;索引總大小可能達(dá)到數(shù)據(jù)本身的 20%~50% 甚至更多。寫(xiě)入放大方面表上每多一個(gè)索引寫(xiě)操作就多維護(hù)一份。寫(xiě)多讀少的表要克制讀多寫(xiě)少的表可以多建。3. 平衡的決策框架場(chǎng)景建議高頻查詢 選擇性高區(qū)分度大值得建收益遠(yuǎn)大于成本低頻查詢一天幾次通常不值得寫(xiě)密集表嚴(yán)格控制索引數(shù)量只留最關(guān)鍵的選擇性低如性別、狀態(tài)只有幾個(gè)值別建掃描比例太高索引意義不大多個(gè)查詢條件優(yōu)先用聯(lián)合索引覆蓋多個(gè)查詢4. 用更少索引拿更多收益的技巧聯(lián)合索引 多個(gè)單列索引一個(gè)(a, b, c)聯(lián)合索引能同時(shí)服務(wù)a、a,b、a,b,c三類查詢。覆蓋索引讓索引直接包含查詢要的列避免回表。CREATEINDEXidx_coverONusers(name,age);SELECTname,ageFROMusersWHEREnameBob;-- 無(wú)需回表定期清理無(wú)用索引-- MySQL 8.0SELECT*FROMsys.schema_unused_indexes;控制單表索引數(shù)量經(jīng)驗(yàn)值單表一般不超過(guò) 5 個(gè)。5. 完整評(píng)估流程1. 找出慢查詢 → 開(kāi)慢查詢?nèi)罩?/ 監(jiān)控 2. EXPLAIN 分析瓶頸 → 確認(rèn)是不是缺索引 3. 試建索引 → 在測(cè)試環(huán)境加上 4. 再次 EXPLAIN 壓測(cè) → 量化查詢提速多少 5. 查索引占用空間 → 評(píng)估空間成本 6. 評(píng)估寫(xiě)入影響 → 這張表寫(xiě)頻繁嗎 7. 收益 成本 ? 保留 : 放棄 8. 上線后持續(xù)監(jiān)控 → 定期清理無(wú)用索引六、一句話總結(jié)對(duì)高頻、選擇性高的查詢建索引收益大用聯(lián)合索引和覆蓋索引減少索引數(shù)量成本低對(duì)寫(xiě)密集表和低頻查詢保持克制上線后靠監(jiān)控持續(xù)做減法。