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