前言

這篇文章是補(bǔ)充一些基本概念和實(shí)戰(zhàn)的一些使用建議.

MySQL索引優(yōu)缺點(diǎn)分析

? 索引的優(yōu)勢(shì)

1?? 提升查詢性能：索引能夠顯著加快數(shù)據(jù)查詢速度，數(shù)據(jù)量越大，效果越明顯。
2?? 保證數(shù)據(jù)唯一性：唯一索引（UNIQUE）可以確保數(shù)據(jù)表中的某些字段不出現(xiàn)重復(fù)值，無(wú)需額外添加唯一性約束。
3?? 優(yōu)化分組與排序：索引可以加速 GROUP BY 和 ORDER BY 語(yǔ)句，減少分組與排序的計(jì)算開銷。
4?? 提升關(guān)聯(lián)查詢性能：在多表 JOIN 操作時(shí)，合理的索引（如主鍵索引、外鍵索引）能夠大幅提高查詢效率。
5?? 優(yōu)化范圍查詢：B+Tree 索引結(jié)構(gòu)天然有序，使 BETWEEN、>、<、>=、<= 這類范圍查詢更加高效。
6?? 提高數(shù)據(jù)庫(kù)吞吐量：優(yōu)化 SQL 執(zhí)行效率，減少查詢時(shí)間，從而提升數(shù)據(jù)庫(kù)整體的吞吐能力。

?? 索引的代價(jià)

1?? 占用額外存儲(chǔ)空間：索引會(huì)生成額外的磁盤文件，尤其是大數(shù)據(jù)量場(chǎng)景下，索引存儲(chǔ)空間可能遠(yuǎn)超數(shù)據(jù)本身。
2?? 影響寫入性能：數(shù)據(jù)的增、刪、改操作需要同步維護(hù)索引，導(dǎo)致寫入性能下降。
3?? 增加索引維護(hù)成本：每次 INSERT、DELETE 或 UPDATE 操作都可能引發(fā)索引的重構(gòu)或調(diào)整，影響整體執(zhí)行效率。
📌 結(jié)論：索引不是越多越好，而是要合理使用！
盡管索引帶來(lái)的優(yōu)勢(shì)遠(yuǎn)大于劣勢(shì)，但并不是索引越多越好。
過(guò)多的索引不僅會(huì)占用大量存儲(chǔ)，還可能影響寫入性能，因此合理規(guī)劃索引策略，結(jié)合業(yè)務(wù)場(chǎng)景進(jìn)行優(yōu)化，才是最佳實(shí)踐！ 🚀

如何合理建立索引?——關(guān)鍵原則總結(jié)

在設(shè)計(jì)索引時(shí)，僅僅考慮某個(gè)字段是否頻繁出現(xiàn)在查詢條件中是不夠的。
一個(gè)優(yōu)秀的索引策略需要綜合考慮查詢模式、數(shù)據(jù)特征以及索引類型，以實(shí)現(xiàn)最佳性能。以下是建立索引時(shí)需要遵循的重要原則：
1?? 針對(duì)查詢頻率高的字段建立索引
對(duì)于經(jīng)常用于 WHERE 條件的字段，應(yīng)考慮創(chuàng)建索引，以加速查詢。
2?? 關(guān)聯(lián)字段必須建立索引
主鍵（Primary Key）、外鍵（Foreign Key）及 JOIN 連接字段 應(yīng)創(chuàng)建索引，以提升多表查詢性能。
3?? 選擇區(qū)分度高的字段作為索引
索引字段的值應(yīng)該盡可能具有高區(qū)分度（Cardinality），即唯一值較多，能有效減少查詢掃描的行數(shù)。例如，索引 身份證號(hào) 是有效的，但索引 性別 作用不大。
4?? 避免索引過(guò)長(zhǎng)，可使用前綴索引
如果字段值較長(zhǎng)（如 VARCHAR(255)），應(yīng)避免全文索引，可以考慮前綴索引（PREFIX INDEX），這樣既能提高查詢效率，又能節(jié)省存儲(chǔ)空間。
5?? 聯(lián)合索引需遵循最左前綴原則
創(chuàng)建聯(lián)合索引時(shí)，應(yīng)按照查詢使用頻率 和 過(guò)濾效果 來(lái)確定字段順序。索引的匹配遵循最左前綴法則，即查詢條件必須從索引的最左字段開始，否則索引可能無(wú)法生效。
6?? 對(duì)于排序、分組字段建立索引
ORDER BY、GROUP BY 及范圍查詢（BETWEEN、>、<、>=、<=） 的字段適合建立索引，利用索引的有序性 可以加快查詢。
7?? 唯一索引不用于排序時(shí)，可考慮 Hash 結(jié)構(gòu)
如果某字段僅用于唯一性約束，且不會(huì)用于范圍查詢或排序，可以使用 Hash 索引（如 MEMORY 表中的 HASH INDEX），查詢性能更高。
8?? 聯(lián)合索引優(yōu)于多個(gè)單列索引
相較于多個(gè)獨(dú)立索引，聯(lián)合索引（Composite Index） 更具優(yōu)勢(shì)，能有效減少回表查詢（避免 Using filesort 和 Using temporary），提高查詢效率。
📌 結(jié)論：索引優(yōu)化是門技術(shù)活！
合理的索引策略不是盲目加索引，而是結(jié)合業(yè)務(wù)場(chǎng)景，選擇合適的索引字段和索引類型，以最大化查詢性能，同時(shí)避免過(guò)多索引帶來(lái)的存儲(chǔ)和維護(hù)開銷。

重要的優(yōu)化機(jī)制

索引覆蓋——通俗的方式講解

我們先從回表查詢 說(shuō)起——想象一下，你去圖書館查一本書的內(nèi)容。
回表查詢的情況：
你想知道 房間號(hào)、房型、價(jià)格、入住人姓名，但是前臺(tái)的客房查詢系統(tǒng)（索引） 只存了 房間號(hào)和房型，而入住人姓名和價(jià)格在紙質(zhì)登記表（主鍵索引數(shù)據(jù)）里。
你先從 索引 里查到房間號(hào)，再去 紙質(zhì)登記表 里翻找到對(duì)應(yīng)的信息，才能拿到最終結(jié)果。
這個(gè)過(guò)程就類似 MySQL 先用索引查 ID，再回表查完整數(shù)據(jù)，也就是 回表查詢。

索引覆蓋的情況：
如果你 只想查房間號(hào)和房型，那么前臺(tái)系統(tǒng)（索引） 里已經(jīng)包含了這些信息，你直接就能得到結(jié)果，不用再翻紙質(zhì)登記表（回表）。

• 這個(gè)時(shí)候，你查的信息 完全被索引覆蓋，數(shù)據(jù)庫(kù)不需要再去表里查完整數(shù)據(jù)，查詢效率更高

舉個(gè)例子,假設(shè)有個(gè) hotel_rooms 表，字段如下：

room_id（主鍵）	room_type	price	guest_name
101	豪華大床房	500	張三
102	標(biāo)準(zhǔn)雙床房	300	李四

🚨 回表查詢

SELECT * FROM hotel_rooms WHERE room_type = '豪華大床房';

🔹 MySQL 先通過(guò)索引找到符合條件的 room_id，然后還要回表查 price 和 guest_name，才能返回完整數(shù)據(jù)。

? 使用索引覆蓋

SELECT room_id, room_type FROM hotel_rooms WHERE room_type = '豪華大床房';

🔹 這次查詢的 room_id 和 room_type 都在索引里，不用回表，直接返回結(jié)果！ 🔹 索引覆蓋成功，查詢更快！

📌 總結(jié)
索引覆蓋就像 前臺(tái)查詢系統(tǒng)，如果你查的信息已經(jīng)在索引里，直接返回；如果你查的信息不全，就得去翻紙質(zhì)檔案（回表）。
所以，合理設(shè)計(jì)索引結(jié)構(gòu)，可以大大減少回表，提高查詢速度！🚀

索引下推

我們用 酒店前臺(tái)查詢 的例子，和索引覆蓋的方式類似.
📚 先來(lái)看普通查詢（不使用索引下推）
假設(shè)你是 酒店前臺(tái)查詢?nèi)胱〉目腿?#xff0c;你說(shuō)：“我想查 住在標(biāo)準(zhǔn)雙床房，且價(jià)格低于 400 元 的客人信息。”
前臺(tái)（數(shù)據(jù)庫(kù)）是這樣做的：

• 先查索引：找到所有 “標(biāo)準(zhǔn)雙床房” 的 room_id。
• 回表查詢：去登記表（主鍵索引）里 一個(gè)個(gè)查 price，篩選出價(jià)格 < 400 的房間。
  ? 問(wèn)題：索引本來(lái)能篩選部分?jǐn)?shù)據(jù)，但 price 這個(gè)條件要等回表后才能判斷，多了一步，效率低！

? 使用索引下推優(yōu)化查詢
索引下推 就像前臺(tái)自己變聰明了，能直接用索引來(lái)篩選一部分?jǐn)?shù)據(jù)！
“標(biāo)準(zhǔn)雙床房 & 價(jià)格 < 400” 這兩個(gè)條件，前臺(tái)能直接處理一部分，不用都去翻登記表！"

1. 先查索引，不僅找 room_type = “標(biāo)準(zhǔn)雙床房”，還在索引層先篩選 price < 400 的記錄！
2. 只對(duì)符合條件的 room_id 才回表查詢，減少不必要的回表操作。
   🚀 優(yōu)化點(diǎn)：減少了回表次數(shù)，提高查詢速度！

🛠 結(jié)合 SQL 代碼

SELECT guest_name FROM hotel_rooms 
WHERE room_type = '標(biāo)準(zhǔn)雙床房' AND price < 400;

如果 room_type 和 price 都建了索引，MySQL 會(huì)使用索引下推：

• 先在索引中篩選：找到 room_type = ‘標(biāo)準(zhǔn)雙床房’ 的記錄，并且 過(guò)濾掉 price >= 400 的行！
• 只對(duì)符合條件的記錄回表，查 guest_name。

📌 總結(jié)

優(yōu)化方式	是否先用索引篩選 price	回表次數(shù)	查詢速度
? 沒(méi)有索引下推	? 否（先找 room_type，再回表篩選 price）	? 回表次數(shù)多	? 慢
? 使用索引下推	? 是（索引層先篩選一部分 price）	? 回表次數(shù)減少	🚀 快

索引跳躍式掃描

索引跳躍式掃描 是 MySQL 在查詢時(shí)的一種優(yōu)化策略，即使沒(méi)有使用索引的最左列，它仍然可以部分利用索引來(lái)加速查詢，而不必完全放棄索引。

📚 直觀類比：查找書籍時(shí)的跳躍式翻找
假設(shè)你去圖書館找一本書，圖書館的書架是按照 類別（Category）+ 書名（Title） 的方式排序的，比如這樣：

類別（Category）	書名（Title）
計(jì)算機(jī)	Java入門
計(jì)算機(jī)	Python進(jìn)階
計(jì)算機(jī)	數(shù)據(jù)結(jié)構(gòu)與算法
歷史	中國(guó)古代史
歷史	世界歷史
文學(xué)	紅樓夢(mèng)
文學(xué)	哈利波特

🎯 現(xiàn)實(shí)場(chǎng)景：你要找所有書名包含“歷史”的書
但問(wèn)題是：書架是按照類別 + 書名排序的，而你沒(méi)有指定類別！！！
? 傳統(tǒng)索引掃描（最左匹配失敗，無(wú)法利用索引）
如果索引是按 (類別, 書名) 排序的，通常你得先指定類別才能用索引查找。但你沒(méi)指定類別，所以數(shù)據(jù)庫(kù)可能會(huì)直接全表掃描，一本一本地檢查書名里有沒(méi)有“歷史”兩字。
? 索引跳躍式掃描（Index Skip Scan）
數(shù)據(jù)庫(kù)的優(yōu)化策略是：
雖然你沒(méi)指定類別，但系統(tǒng)可以按類別分組，一個(gè)類別一個(gè)類別地跳躍查找書名：

1. 先在“計(jì)算機(jī)”類別里查找，發(fā)現(xiàn)沒(méi)有“歷史”相關(guān)書籍，跳過(guò)。
2. 再到“歷史”類別里查找，發(fā)現(xiàn)有《中國(guó)古代史》《世界歷史》，記下來(lái)。
3. 最后查“文學(xué)”類別，發(fā)現(xiàn)沒(méi)有匹配的書，跳過(guò)。
   這樣就不用掃描所有的書，而是按類別跳躍式掃描索引，提高查詢效率！ 🚀

🔍 代碼示例
假設(shè)數(shù)據(jù)庫(kù)表 t_books:

CREATE TABLE t_books (category VARCHAR(50),   -- 書籍類別title VARCHAR(100),     -- 書名PRIMARY KEY (category, title)  -- 聯(lián)合索引（按類別+書名排序）
);

你想查所有書名是 “歷史” 的書:

SELECT title FROM t_books WHERE title LIKE '%歷史%';

🔥 MySQL 可能使用索引跳躍式掃描：

1. 先按 category 一組一組地跳躍掃描
2. 然后在每組里查 title 是否包含“歷史”
   這樣比全表掃描快很多！