本文總結(jié)數(shù)據(jù)庫索引的核心知識包括索引的作用、創(chuàng)建方式、索引的自動維護機制以及如何在查詢速度與空間/寫入開銷之間做權(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)字段有索引能大幅提速保證唯一性唯一索引可強制列值不重復(fù)覆蓋索引查詢字段全在索引里時無需回表讀數(shù)據(jù)行代價占用額外存儲空間寫操作INSERT/UPDATE/DELETE需同步維護索引會變慢。所以索引不是越多越好。二、如何創(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類比「電話簿」先按姓排、姓相同再按名排。知道姓能快速定位只知道名不知道姓還是得一頁頁翻。四、更新字段時索引由引擎自動維護對表做 INSERT / UPDATE / DELETE 時數(shù)據(jù)庫引擎會在同一個事務(wù)里自動把相關(guān)索引一起改掉保證數(shù)據(jù)和索引始終一致無需手動維護。以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)鍵點只維護「被改動的列」相關(guān)的索引。若只UPDATE name則idx_age不受影響。索引的寫入代價操作索引層面發(fā)生的事INSERT每個索引都要插入一條新索引項并維持有序DELETE每個索引都要刪除對應(yīng)索引項UPDATE若改的列在索引中 → 刪舊項 插新項可能引發(fā) B 樹的頁分裂/合并所以索引越多寫操作越慢——讀的時候爽寫的時候還債。認知要點順序會自動維持age 從 25 改成 26索引里位置會被自動挪到正確排序位。崩潰也不怕靠 redo log / WAL 等機制宕機重啟后數(shù)據(jù)和索引依然一致。可能變慢的場景頻繁更新索引列、或更新導(dǎo)致 B 樹頁分裂時寫入開銷更明顯。例外——全文索引某些搜索引擎類索引如 Elasticsearch可能是異步/近實時更新但普通 B 樹索引都是同步實時的。五、如何權(quán)衡查詢提速 vs 空間/寫入開銷這本質(zhì)是一個成本收益分析。1. 量化「收益」——查詢快了多少核心工具EXPLAIN/EXPLAIN ANALYZEEXPLAINANALYZESELECT*FROMusersWHEREnameBobANDage22;重點指標指標含義加索引前后對比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;索引總大小可能達到數(shù)據(jù)本身的 20%~50% 甚至更多。寫入放大方面表上每多一個索引寫操作就多維護一份。寫多讀少的表要克制讀多寫少的表可以多建。3. 平衡的決策框架場景建議高頻查詢 選擇性高區(qū)分度大值得建收益遠大于成本低頻查詢一天幾次通常不值得寫密集表嚴格控制索引數(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 分析瓶頸 → 確認是不是缺索引 3. 試建索引 → 在測試環(huán)境加上 4. 再次 EXPLAIN 壓測 → 量化查詢提速多少 5. 查索引占用空間 → 評估空間成本 6. 評估寫入影響 → 這張表寫頻繁嗎 7. 收益 成本 ? 保留 : 放棄 8. 上線后持續(xù)監(jiān)控 → 定期清理無用索引六、一句話總結(jié)對高頻、選擇性高的查詢建索引收益大用聯(lián)合索引和覆蓋索引減少索引數(shù)量成本低對寫密集表和低頻查詢保持克制上線后靠監(jiān)控持續(xù)做減法。