據(jù)庫(kù)索引：B+樹原理與優(yōu)化實(shí)踐)
1. 數(shù)據(jù)庫(kù)索引的本質(zhì)與B樹結(jié)構(gòu)數(shù)據(jù)庫(kù)索引就像一本字典的目錄它能讓我們快速找到需要的數(shù)據(jù)而不必逐頁翻閱整本書。在關(guān)系型數(shù)據(jù)庫(kù)中B樹是最常見的索引數(shù)據(jù)結(jié)構(gòu)它完美平衡了查詢效率和存儲(chǔ)成本。B樹是一種多路平衡查找樹與二叉查找樹不同每個(gè)節(jié)點(diǎn)可以有多個(gè)子節(jié)點(diǎn)。一個(gè)典型的B樹包含兩種節(jié)點(diǎn)內(nèi)部節(jié)點(diǎn)枝節(jié)點(diǎn)和葉子節(jié)點(diǎn)。內(nèi)部節(jié)點(diǎn)不存儲(chǔ)實(shí)際數(shù)據(jù)只存儲(chǔ)鍵值和指向子節(jié)點(diǎn)的指針而葉子節(jié)點(diǎn)存儲(chǔ)了完整的索引條目包含鍵值和對(duì)應(yīng)的數(shù)據(jù)位置信息通常是主鍵值或行指針。B樹與B樹的關(guān)鍵區(qū)別在于B樹的所有數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)內(nèi)部節(jié)點(diǎn)只作為導(dǎo)航使用B樹的葉子節(jié)點(diǎn)通過指針相互連接形成有序鏈表B樹的內(nèi)部節(jié)點(diǎn)鍵值不重復(fù)存儲(chǔ)節(jié)省空間這種結(jié)構(gòu)設(shè)計(jì)帶來了幾個(gè)重要優(yōu)勢(shì)查詢穩(wěn)定性任何查詢都需要從根節(jié)點(diǎn)走到葉子節(jié)點(diǎn)時(shí)間復(fù)雜度穩(wěn)定為O(log n)范圍查詢高效葉子節(jié)點(diǎn)的鏈表結(jié)構(gòu)使得范圍查詢只需找到起始點(diǎn)后順序遍歷更高的扇出內(nèi)部節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)可以容納更多鍵值降低樹的高度實(shí)際工程中數(shù)據(jù)庫(kù)會(huì)根據(jù)硬件特性如磁盤塊大小優(yōu)化B樹節(jié)點(diǎn)大小。例如MySQL的InnoDB引擎默認(rèn)頁大小為16KB這通常就是一個(gè)B樹節(jié)點(diǎn)的大小。2. 索引的兩種訪問方式覆蓋與回表當(dāng)SQL查詢使用索引時(shí)根據(jù)查詢所需的字段是否全部包含在索引中會(huì)產(chǎn)生兩種不同的數(shù)據(jù)訪問路徑2.1 覆蓋索引Covering Index覆蓋索引是指查詢的所有字段都包含在索引中引擎只需訪問索引即可完成查詢無需回表。例如-- 假設(shè)在user表上有聯(lián)合索引(name, age) SELECT name, age FROM user WHERE name LIKE 張%;這種情況下查詢只需要掃描name-age索引樹找到匹配的索引條目后直接返回結(jié)果不需要再去主鍵索引查找完整行數(shù)據(jù)。覆蓋索引的優(yōu)勢(shì)減少IO操作避免額外的磁盤尋道提高緩存效率索引通常比完整行數(shù)據(jù)小緩沖池能緩存更多索引避免回表開銷省去了通過主鍵二次查找的過程2.2 回表查詢Bookmark Lookup當(dāng)查詢的字段不全部包含在索引中時(shí)數(shù)據(jù)庫(kù)引擎需要先通過索引找到主鍵再通過主鍵去聚簇索引中查找完整行數(shù)據(jù)。這個(gè)過程稱為回表。例如-- 同樣的name-age索引 SELECT name, age, address FROM user WHERE name LIKE 張%;這里address字段不在索引中引擎需要通過name-age索引找到匹配記錄的主鍵用這些主鍵去聚簇索引中查找完整的行記錄從行記錄中提取address字段返回回表操作的主要問題額外的IO開銷特別是當(dāng)回表次數(shù)多時(shí)如范圍查詢匹配大量行隨機(jī)IO主鍵查找通常是隨機(jī)訪問比順序掃描慢得多緩存污染大量回表可能擠出緩沖池中的有用數(shù)據(jù)3. 索引穿透問題與優(yōu)化策略索引穿透是指索引失去了過濾作用導(dǎo)致查詢效率低下的情況。常見場(chǎng)景包括3.1 低選擇性索引當(dāng)索引列的值區(qū)分度很低時(shí)如性別字段只有男/女兩種值使用該索引可能不如全表掃描高效。優(yōu)化方法避免對(duì)低選擇性列單獨(dú)建索引將低選擇性列作為聯(lián)合索引的后綴列3.2 索引失效的查詢條件某些查詢寫法會(huì)導(dǎo)致索引失效-- 對(duì)索引列使用函數(shù) SELECT * FROM user WHERE YEAR(create_time) 2023; -- 隱式類型轉(zhuǎn)換 SELECT * FROM user WHERE phone 13800138000; -- phone是varchar類型 -- 前導(dǎo)模糊查詢 SELECT * FROM user WHERE name LIKE %張%;優(yōu)化建議避免對(duì)索引列使用函數(shù)或計(jì)算確保查詢類型與列類型匹配盡量使用前綴匹配LIKE 張%而非全模糊3.3 大量回表操作即使索引選擇性高如果需要回表的行數(shù)太多性能也會(huì)下降。解決方案使用覆蓋索引只查詢索引包含的字段使用索引條件下推ICP在存儲(chǔ)引擎層過濾數(shù)據(jù)限制查詢范圍添加更嚴(yán)格的WHERE條件4. 實(shí)戰(zhàn)中的索引設(shè)計(jì)與優(yōu)化4.1 聯(lián)合索引設(shè)計(jì)原則聯(lián)合索引的列順序至關(guān)重要應(yīng)遵循以下原則高選擇性列在前等值查詢列在前范圍查詢列在后常用查詢條件優(yōu)先考慮覆蓋索引的可能性例如對(duì)于查詢SELECT * FROM orders WHERE user_id 123 AND status paid ORDER BY create_time DESC;最優(yōu)索引可能是(user_id, status, create_time)4.2 索引維護(hù)與監(jiān)控即使設(shè)計(jì)良好的索引也可能隨著數(shù)據(jù)變化而失效需要定期分析索引使用情況-- MySQL中查看索引使用統(tǒng)計(jì) SELECT * FROM sys.schema_index_statistics;重建碎片化嚴(yán)重的索引-- InnoDB重建表索引 ALTER TABLE orders ENGINEInnoDB;刪除冗余索引-- 查找可能冗余的索引 SELECT * FROM sys.schema_redundant_indexes;4.3 特殊場(chǎng)景優(yōu)化分頁查詢優(yōu)化-- 低效寫法偏移量大時(shí) SELECT * FROM user ORDER BY name LIMIT 10000, 20; -- 優(yōu)化寫法使用覆蓋索引主鍵回表 SELECT * FROM user WHERE id (SELECT id FROM user ORDER BY name LIMIT 10000, 1) ORDER BY name LIMIT 20;COUNT查詢優(yōu)化-- 低效 SELECT COUNT(*) FROM user WHERE status active; -- 高效使用覆蓋索引 SELECT COUNT(id) FROM user WHERE status active;5. 不同數(shù)據(jù)庫(kù)的索引實(shí)現(xiàn)差異雖然B樹是主流索引結(jié)構(gòu)但不同數(shù)據(jù)庫(kù)的實(shí)現(xiàn)有所差異5.1 MySQL的InnoDB索引聚簇索引主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)完整行數(shù)據(jù)二級(jí)索引存儲(chǔ)主鍵值而非行指針回表需要兩次查找自適應(yīng)哈希索引自動(dòng)為熱點(diǎn)頁建立哈希索引5.2 PostgreSQL的索引堆表結(jié)構(gòu)數(shù)據(jù)無序存儲(chǔ)所有索引都是二級(jí)索引支持多種索引類型B-tree、Hash、GiST、SP-GiST、GIN、BRIN索引包含額外信息如可見性映射減少回表次數(shù)5.3 SQL Server的索引聚簇索引與非聚簇索引結(jié)構(gòu)包含列索引可以在非聚簇索引中包含非鍵列實(shí)現(xiàn)偽覆蓋索引篩選索引只為部分?jǐn)?shù)據(jù)創(chuàng)建索引減少索引大小6. 新型存儲(chǔ)引擎的索引創(chuàng)新隨著硬件發(fā)展一些新型存儲(chǔ)引擎采用了不同的索引設(shè)計(jì)6.1 LSM-Tree結(jié)構(gòu)LevelDB、RocksDB等使用LSM-Tree日志結(jié)構(gòu)合并樹寫優(yōu)化先將寫入放到內(nèi)存表MemTable再異步合并到磁盤讀需要檢查多個(gè)層次MemTable SST文件布隆過濾器加速查找6.2 內(nèi)存數(shù)據(jù)庫(kù)索引Redis等內(nèi)存數(shù)據(jù)庫(kù)采用特殊索引哈希表O(1)時(shí)間復(fù)雜度查找跳表有序數(shù)據(jù)結(jié)構(gòu)支持范圍查詢字典樹用于前綴匹配6.3 列式存儲(chǔ)索引ClickHouse等列式數(shù)據(jù)庫(kù)的索引特點(diǎn)每個(gè)列單獨(dú)存儲(chǔ)可以只為查詢列讀取數(shù)據(jù)稀疏索引每N行記錄一個(gè)標(biāo)記減少索引大小數(shù)據(jù)局部性相同值壓縮存儲(chǔ)減少IO7. 索引優(yōu)化的度量與驗(yàn)證優(yōu)化索引后需要驗(yàn)證效果主要方法包括7.1 執(zhí)行計(jì)劃分析-- MySQL EXPLAIN SELECT * FROM user WHERE name 張三; -- PostgreSQL EXPLAIN ANALYZE SELECT * FROM user WHERE name 張三;關(guān)鍵指標(biāo)訪問類型type/indexALL表示全表掃描ref/range表示索引訪問預(yù)估行數(shù)rows越小越好額外操作ExtraUsing index表示覆蓋索引7.2 性能基準(zhǔn)測(cè)試使用sysbench等工具模擬真實(shí)負(fù)載sysbench oltp_read_only --db-drivermysql \ --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-password \ --mysql-dbsbtest --tables10 \ --table-size1000000 --threads32 --time300 \ --report-interval10 run7.3 監(jiān)控系統(tǒng)指標(biāo)QPS/TPS變化磁盤IO利用率緩存命中率CPU使用率8. 索引設(shè)計(jì)的反模式與陷阱即使經(jīng)驗(yàn)豐富的開發(fā)者也可能陷入這些索引陷阱8.1 過度索引每個(gè)額外的索引都會(huì)帶來寫入開銷每次INSERT/UPDATE/DELETE需要更新所有相關(guān)索引存儲(chǔ)開銷索引可能比數(shù)據(jù)本身占用更多空間優(yōu)化器負(fù)擔(dān)更多索引可能導(dǎo)致優(yōu)化器選擇困難8.2 盲目添加索引常見誤區(qū)為所有查詢條件都創(chuàng)建索引為所有列創(chuàng)建單列索引索引越多查詢?cè)娇鞂?shí)際上應(yīng)該先分析查詢模式監(jiān)控現(xiàn)有索引使用情況優(yōu)先優(yōu)化高頻、關(guān)鍵查詢8.3 忽視索引維護(hù)成本索引不是一勞永逸的需要定期重建碎片化索引隨著數(shù)據(jù)分布變化調(diào)整索引刪除不再使用的索引9. 索引與查詢優(yōu)化器的交互現(xiàn)代數(shù)據(jù)庫(kù)的查詢優(yōu)化器非常復(fù)雜了解其工作原理有助于寫出更好的查詢9.1 統(tǒng)計(jì)信息的重要性優(yōu)化器依賴統(tǒng)計(jì)信息做出決策表的行數(shù)索引的基數(shù)不同值的數(shù)量數(shù)據(jù)分布直方圖統(tǒng)計(jì)信息不準(zhǔn)確會(huì)導(dǎo)致選擇低效的執(zhí)行計(jì)劃索引未被使用錯(cuò)誤的JOIN順序9.2 優(yōu)化器提示當(dāng)優(yōu)化器選擇不理想時(shí)可以使用提示-- MySQL強(qiáng)制使用特定索引 SELECT * FROM user FORCE INDEX(idx_name) WHERE name LIKE 張%; -- PostgreSQL禁用特定索引 SET enable_indexscan off;但應(yīng)謹(jǐn)慎使用因?yàn)閿?shù)據(jù)變化后可能不再最優(yōu)不同數(shù)據(jù)庫(kù)版本行為可能變化使SQL難以維護(hù)9.3 參數(shù)調(diào)優(yōu)影響索引選擇的重要參數(shù)MySQLoptimizer_switch、range_optimizer_max_mem_sizePostgreSQLrandom_page_cost、effective_cache_sizeSQL Servercost threshold for parallelism10. 未來索引技術(shù)發(fā)展趨勢(shì)數(shù)據(jù)庫(kù)索引技術(shù)仍在不斷發(fā)展幾個(gè)值得關(guān)注的趨勢(shì)10.1 機(jī)器學(xué)習(xí)優(yōu)化自動(dòng)索引推薦基于查詢歷史自動(dòng)建議創(chuàng)建/刪除索引代價(jià)模型優(yōu)化使用機(jī)器學(xué)習(xí)改進(jìn)執(zhí)行計(jì)劃成本估算自適應(yīng)索引根據(jù)負(fù)載動(dòng)態(tài)調(diào)整索引結(jié)構(gòu)10.2 硬件感知索引針對(duì)新型硬件設(shè)計(jì)持久內(nèi)存PMEM友好的索引結(jié)構(gòu)GPU加速的索引掃描利用SIMD指令加速索引查找10.3 多模索引支持復(fù)雜查詢?nèi)臋z索與結(jié)構(gòu)化查詢的統(tǒng)一索引時(shí)空數(shù)據(jù)索引圖數(shù)據(jù)索引在實(shí)際工作中我經(jīng)常發(fā)現(xiàn)開發(fā)者在索引使用上存在兩個(gè)極端要么過度依賴索引要么完全忽視索引。正確的做法應(yīng)該是基于具體的查詢模式和數(shù)據(jù)特征設(shè)計(jì)恰到好處的索引策略。一個(gè)好的經(jīng)驗(yàn)法則是先讓查詢跑起來然后通過執(zhí)行計(jì)劃分析和性能測(cè)試有針對(duì)性地添加或調(diào)整索引而不是一開始就試圖創(chuàng)建完美的索引結(jié)構(gòu)。 深入解析數(shù)據(jù)庫(kù)索引：B+樹原理與優(yōu)化實(shí)踐
1. 數(shù)據(jù)庫(kù)索引的本質(zhì)與B樹結(jié)構(gòu)數(shù)據(jù)庫(kù)索引就像一本字典的目錄它能讓我們快速找到需要的數(shù)據(jù)而不必逐頁翻閱整本書。在關(guān)系型數(shù)據(jù)庫(kù)中B樹是最常見的索引數(shù)據(jù)結(jié)構(gòu)它完美平衡了查詢效率和存儲(chǔ)成本。B樹是一種多路平衡查找樹與二叉查找樹不同每個(gè)節(jié)點(diǎn)可以有多個(gè)子節(jié)點(diǎn)。一個(gè)典型的B樹包含兩種節(jié)點(diǎn)內(nèi)部節(jié)點(diǎn)枝節(jié)點(diǎn)和葉子節(jié)點(diǎn)。內(nèi)部節(jié)點(diǎn)不存儲(chǔ)實(shí)際數(shù)據(jù)只存儲(chǔ)鍵值和指向子節(jié)點(diǎn)的指針而葉子節(jié)點(diǎn)存儲(chǔ)了完整的索引條目包含鍵值和對(duì)應(yīng)的數(shù)據(jù)位置信息通常是主鍵值或行指針。B樹與B樹的關(guān)鍵區(qū)別在于B樹的所有數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)內(nèi)部節(jié)點(diǎn)只作為導(dǎo)航使用B樹的葉子節(jié)點(diǎn)通過指針相互連接形成有序鏈表B樹的內(nèi)部節(jié)點(diǎn)鍵值不重復(fù)存儲(chǔ)節(jié)省空間這種結(jié)構(gòu)設(shè)計(jì)帶來了幾個(gè)重要優(yōu)勢(shì)查詢穩(wěn)定性任何查詢都需要從根節(jié)點(diǎn)走到葉子節(jié)點(diǎn)時(shí)間復(fù)雜度穩(wěn)定為O(log n)范圍查詢高效葉子節(jié)點(diǎn)的鏈表結(jié)構(gòu)使得范圍查詢只需找到起始點(diǎn)后順序遍歷更高的扇出內(nèi)部節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)可以容納更多鍵值降低樹的高度實(shí)際工程中數(shù)據(jù)庫(kù)會(huì)根據(jù)硬件特性如磁盤塊大小優(yōu)化B樹節(jié)點(diǎn)大小。例如MySQL的InnoDB引擎默認(rèn)頁大小為16KB這通常就是一個(gè)B樹節(jié)點(diǎn)的大小。2. 索引的兩種訪問方式覆蓋與回表當(dāng)SQL查詢使用索引時(shí)根據(jù)查詢所需的字段是否全部包含在索引中會(huì)產(chǎn)生兩種不同的數(shù)據(jù)訪問路徑2.1 覆蓋索引Covering Index覆蓋索引是指查詢的所有字段都包含在索引中引擎只需訪問索引即可完成查詢無需回表。例如-- 假設(shè)在user表上有聯(lián)合索引(name, age) SELECT name, age FROM user WHERE name LIKE 張%;這種情況下查詢只需要掃描name-age索引樹找到匹配的索引條目后直接返回結(jié)果不需要再去主鍵索引查找完整行數(shù)據(jù)。覆蓋索引的優(yōu)勢(shì)減少IO操作避免額外的磁盤尋道提高緩存效率索引通常比完整行數(shù)據(jù)小緩沖池能緩存更多索引避免回表開銷省去了通過主鍵二次查找的過程2.2 回表查詢Bookmark Lookup當(dāng)查詢的字段不全部包含在索引中時(shí)數(shù)據(jù)庫(kù)引擎需要先通過索引找到主鍵再通過主鍵去聚簇索引中查找完整行數(shù)據(jù)。這個(gè)過程稱為回表。例如-- 同樣的name-age索引 SELECT name, age, address FROM user WHERE name LIKE 張%;這里address字段不在索引中引擎需要通過name-age索引找到匹配記錄的主鍵用這些主鍵去聚簇索引中查找完整的行記錄從行記錄中提取address字段返回回表操作的主要問題額外的IO開銷特別是當(dāng)回表次數(shù)多時(shí)如范圍查詢匹配大量行隨機(jī)IO主鍵查找通常是隨機(jī)訪問比順序掃描慢得多緩存污染大量回表可能擠出緩沖池中的有用數(shù)據(jù)3. 索引穿透問題與優(yōu)化策略索引穿透是指索引失去了過濾作用導(dǎo)致查詢效率低下的情況。常見場(chǎng)景包括3.1 低選擇性索引當(dāng)索引列的值區(qū)分度很低時(shí)如性別字段只有男/女兩種值使用該索引可能不如全表掃描高效。優(yōu)化方法避免對(duì)低選擇性列單獨(dú)建索引將低選擇性列作為聯(lián)合索引的后綴列3.2 索引失效的查詢條件某些查詢寫法會(huì)導(dǎo)致索引失效-- 對(duì)索引列使用函數(shù) SELECT * FROM user WHERE YEAR(create_time) 2023; -- 隱式類型轉(zhuǎn)換 SELECT * FROM user WHERE phone 13800138000; -- phone是varchar類型 -- 前導(dǎo)模糊查詢 SELECT * FROM user WHERE name LIKE %張%;優(yōu)化建議避免對(duì)索引列使用函數(shù)或計(jì)算確保查詢類型與列類型匹配盡量使用前綴匹配LIKE 張%而非全模糊3.3 大量回表操作即使索引選擇性高如果需要回表的行數(shù)太多性能也會(huì)下降。解決方案使用覆蓋索引只查詢索引包含的字段使用索引條件下推ICP在存儲(chǔ)引擎層過濾數(shù)據(jù)限制查詢范圍添加更嚴(yán)格的WHERE條件4. 實(shí)戰(zhàn)中的索引設(shè)計(jì)與優(yōu)化4.1 聯(lián)合索引設(shè)計(jì)原則聯(lián)合索引的列順序至關(guān)重要應(yīng)遵循以下原則高選擇性列在前等值查詢列在前范圍查詢列在后常用查詢條件優(yōu)先考慮覆蓋索引的可能性例如對(duì)于查詢SELECT * FROM orders WHERE user_id 123 AND status paid ORDER BY create_time DESC;最優(yōu)索引可能是(user_id, status, create_time)4.2 索引維護(hù)與監(jiān)控即使設(shè)計(jì)良好的索引也可能隨著數(shù)據(jù)變化而失效需要定期分析索引使用情況-- MySQL中查看索引使用統(tǒng)計(jì) SELECT * FROM sys.schema_index_statistics;重建碎片化嚴(yán)重的索引-- InnoDB重建表索引 ALTER TABLE orders ENGINEInnoDB;刪除冗余索引-- 查找可能冗余的索引 SELECT * FROM sys.schema_redundant_indexes;4.3 特殊場(chǎng)景優(yōu)化分頁查詢優(yōu)化-- 低效寫法偏移量大時(shí) SELECT * FROM user ORDER BY name LIMIT 10000, 20; -- 優(yōu)化寫法使用覆蓋索引主鍵回表 SELECT * FROM user WHERE id (SELECT id FROM user ORDER BY name LIMIT 10000, 1) ORDER BY name LIMIT 20;COUNT查詢優(yōu)化-- 低效 SELECT COUNT(*) FROM user WHERE status active; -- 高效使用覆蓋索引 SELECT COUNT(id) FROM user WHERE status active;5. 不同數(shù)據(jù)庫(kù)的索引實(shí)現(xiàn)差異雖然B樹是主流索引結(jié)構(gòu)但不同數(shù)據(jù)庫(kù)的實(shí)現(xiàn)有所差異5.1 MySQL的InnoDB索引聚簇索引主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)完整行數(shù)據(jù)二級(jí)索引存儲(chǔ)主鍵值而非行指針回表需要兩次查找自適應(yīng)哈希索引自動(dòng)為熱點(diǎn)頁建立哈希索引5.2 PostgreSQL的索引堆表結(jié)構(gòu)數(shù)據(jù)無序存儲(chǔ)所有索引都是二級(jí)索引支持多種索引類型B-tree、Hash、GiST、SP-GiST、GIN、BRIN索引包含額外信息如可見性映射減少回表次數(shù)5.3 SQL Server的索引聚簇索引與非聚簇索引結(jié)構(gòu)包含列索引可以在非聚簇索引中包含非鍵列實(shí)現(xiàn)偽覆蓋索引篩選索引只為部分?jǐn)?shù)據(jù)創(chuàng)建索引減少索引大小6. 新型存儲(chǔ)引擎的索引創(chuàng)新隨著硬件發(fā)展一些新型存儲(chǔ)引擎采用了不同的索引設(shè)計(jì)6.1 LSM-Tree結(jié)構(gòu)LevelDB、RocksDB等使用LSM-Tree日志結(jié)構(gòu)合并樹寫優(yōu)化先將寫入放到內(nèi)存表MemTable再異步合并到磁盤讀需要檢查多個(gè)層次MemTable SST文件布隆過濾器加速查找6.2 內(nèi)存數(shù)據(jù)庫(kù)索引Redis等內(nèi)存數(shù)據(jù)庫(kù)采用特殊索引哈希表O(1)時(shí)間復(fù)雜度查找跳表有序數(shù)據(jù)結(jié)構(gòu)支持范圍查詢字典樹用于前綴匹配6.3 列式存儲(chǔ)索引ClickHouse等列式數(shù)據(jù)庫(kù)的索引特點(diǎn)每個(gè)列單獨(dú)存儲(chǔ)可以只為查詢列讀取數(shù)據(jù)稀疏索引每N行記錄一個(gè)標(biāo)記減少索引大小數(shù)據(jù)局部性相同值壓縮存儲(chǔ)減少IO7. 索引優(yōu)化的度量與驗(yàn)證優(yōu)化索引后需要驗(yàn)證效果主要方法包括7.1 執(zhí)行計(jì)劃分析-- MySQL EXPLAIN SELECT * FROM user WHERE name 張三; -- PostgreSQL EXPLAIN ANALYZE SELECT * FROM user WHERE name 張三;關(guān)鍵指標(biāo)訪問類型type/indexALL表示全表掃描ref/range表示索引訪問預(yù)估行數(shù)rows越小越好額外操作ExtraUsing index表示覆蓋索引7.2 性能基準(zhǔn)測(cè)試使用sysbench等工具模擬真實(shí)負(fù)載sysbench oltp_read_only --db-drivermysql \ --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-password \ --mysql-dbsbtest --tables10 \ --table-size1000000 --threads32 --time300 \ --report-interval10 run7.3 監(jiān)控系統(tǒng)指標(biāo)QPS/TPS變化磁盤IO利用率緩存命中率CPU使用率8. 索引設(shè)計(jì)的反模式與陷阱即使經(jīng)驗(yàn)豐富的開發(fā)者也可能陷入這些索引陷阱8.1 過度索引每個(gè)額外的索引都會(huì)帶來寫入開銷每次INSERT/UPDATE/DELETE需要更新所有相關(guān)索引存儲(chǔ)開銷索引可能比數(shù)據(jù)本身占用更多空間優(yōu)化器負(fù)擔(dān)更多索引可能導(dǎo)致優(yōu)化器選擇困難8.2 盲目添加索引常見誤區(qū)為所有查詢條件都創(chuàng)建索引為所有列創(chuàng)建單列索引索引越多查詢?cè)娇鞂?shí)際上應(yīng)該先分析查詢模式監(jiān)控現(xiàn)有索引使用情況優(yōu)先優(yōu)化高頻、關(guān)鍵查詢8.3 忽視索引維護(hù)成本索引不是一勞永逸的需要定期重建碎片化索引隨著數(shù)據(jù)分布變化調(diào)整索引刪除不再使用的索引9. 索引與查詢優(yōu)化器的交互現(xiàn)代數(shù)據(jù)庫(kù)的查詢優(yōu)化器非常復(fù)雜了解其工作原理有助于寫出更好的查詢9.1 統(tǒng)計(jì)信息的重要性優(yōu)化器依賴統(tǒng)計(jì)信息做出決策表的行數(shù)索引的基數(shù)不同值的數(shù)量數(shù)據(jù)分布直方圖統(tǒng)計(jì)信息不準(zhǔn)確會(huì)導(dǎo)致選擇低效的執(zhí)行計(jì)劃索引未被使用錯(cuò)誤的JOIN順序9.2 優(yōu)化器提示當(dāng)優(yōu)化器選擇不理想時(shí)可以使用提示-- MySQL強(qiáng)制使用特定索引 SELECT * FROM user FORCE INDEX(idx_name) WHERE name LIKE 張%; -- PostgreSQL禁用特定索引 SET enable_indexscan off;但應(yīng)謹(jǐn)慎使用因?yàn)閿?shù)據(jù)變化后可能不再最優(yōu)不同數(shù)據(jù)庫(kù)版本行為可能變化使SQL難以維護(hù)9.3 參數(shù)調(diào)優(yōu)影響索引選擇的重要參數(shù)MySQLoptimizer_switch、range_optimizer_max_mem_sizePostgreSQLrandom_page_cost、effective_cache_sizeSQL Servercost threshold for parallelism10. 未來索引技術(shù)發(fā)展趨勢(shì)數(shù)據(jù)庫(kù)索引技術(shù)仍在不斷發(fā)展幾個(gè)值得關(guān)注的趨勢(shì)10.1 機(jī)器學(xué)習(xí)優(yōu)化自動(dòng)索引推薦基于查詢歷史自動(dòng)建議創(chuàng)建/刪除索引代價(jià)模型優(yōu)化使用機(jī)器學(xué)習(xí)改進(jìn)執(zhí)行計(jì)劃成本估算自適應(yīng)索引根據(jù)負(fù)載動(dòng)態(tài)調(diào)整索引結(jié)構(gòu)10.2 硬件感知索引針對(duì)新型硬件設(shè)計(jì)持久內(nèi)存PMEM友好的索引結(jié)構(gòu)GPU加速的索引掃描利用SIMD指令加速索引查找10.3 多模索引支持復(fù)雜查詢?nèi)臋z索與結(jié)構(gòu)化查詢的統(tǒng)一索引時(shí)空數(shù)據(jù)索引圖數(shù)據(jù)索引在實(shí)際工作中我經(jīng)常發(fā)現(xiàn)開發(fā)者在索引使用上存在兩個(gè)極端要么過度依賴索引要么完全忽視索引。正確的做法應(yīng)該是基于具體的查詢模式和數(shù)據(jù)特征設(shè)計(jì)恰到好處的索引策略。一個(gè)好的經(jīng)驗(yàn)法則是先讓查詢跑起來然后通過執(zhí)行計(jì)劃分析和性能測(cè)試有針對(duì)性地添加或調(diào)整索引而不是一開始就試圖創(chuàng)建完美的索引結(jié)構(gòu)。