索引是什么？有什么好處？

索引類似與書籍的目錄，從全表掃描改成了根據(jù)索引查找，提高了查找效率

• 如果查詢的時候，沒有用到索引就會全表掃描，這時候查詢的時間復(fù)雜度是 O(N)
• 如果用到了索引，那么查詢的時候，可以基于二分查找算法，通過索引快速定位到目標(biāo)數(shù)據(jù)，MySQL 索引的數(shù)據(jù)結(jié)構(gòu)一般是 B+ 樹，其搜索復(fù)雜度為 O(logdN)，其中 d 表示節(jié)點允許的最大子節(jié)點個數(shù)。

講講索引的分類是什么？

MySQL的索引可以分為4類：

按數(shù)據(jù)結(jié)構(gòu)：B+Tree索引、Hash索引、Full-text索引

按物理存儲：聚簇索引(主鍵索引)、輔助索引(二級索引)

按字段特性：主鍵索引、唯一索引、前綴索引、普通索引

按字段個數(shù)：單列索引、復(fù)合索引(又叫聯(lián)合索引)

按數(shù)據(jù)結(jié)構(gòu)分

分為B+Tree索引、Hash索引、Full-text索引

B+Tree索引

. 對于 InnoDB 的聚簇索引 (Clustered Index)

• 非葉子節(jié)點：存儲索引鍵值（比如主鍵ID）和指向下一層節(jié)點的指針。
• 葉子節(jié)點：存儲完整的行數(shù)據(jù)（所有列的值）。
• 一句話：找到葉子節(jié)點，就找到了整行數(shù)據(jù)。

對于 InnoDB 的二級索引 (Secondary Index)

• 非葉子節(jié)點：存儲索引鍵值（比如 name 列的值）和指向下一層節(jié)點的指針。
• 葉子節(jié)點：存儲索引鍵值（name 的值）和對應(yīng)的主鍵值（不是完整行數(shù)據(jù)）。
• 一句話：找到葉子節(jié)點，只得到主鍵值，還需要回表查詢聚簇索引才能拿到完整行數(shù)據(jù)。

Hash索引底層是Hash表也就是一個一個鍵值對，在查找單個元素很快，接近O(1)，并且只能做精確查找

Full-text索底層是倒排索引，使用于搜索引擎，根據(jù)詞去定位文檔，根據(jù)值去定位id。

后面兩個索引都屬于二級索引也就是輔助索引

這里介紹一下什么是倒排索引，倒排索引就是“根據(jù)關(guān)鍵詞（詞）查找其所在位置（文檔ID/行）”的映射表，與“根據(jù)文檔找詞”的正排相反。

默認(rèn)引擎InnoDB在建表時會根據(jù)不同場景來選擇索引鍵

1.在建表時如果有主鍵，那么會選擇主鍵來作為聚簇索引

2.如果沒有主鍵，會選擇第一個不為NULL值的唯一列來作為聚簇索引

3.如果兩個都沒有，InnoDB會創(chuàng)建一個默認(rèn)的自增id來作為聚簇索引

注意：創(chuàng)建的主鍵索引和二級索引默認(rèn)使用的是 B+Tree 索引。

按物理存儲分

分為聚簇索引(主鍵索引)、輔助索引(二級索引)

這里的物理存儲是指存儲數(shù)據(jù)的方式：

• 主鍵索引的 B+Tree 的葉子節(jié)點存放的是實際數(shù)據(jù)，所有完整的用戶記錄都存放在主鍵索引的 B+Tree 的葉子節(jié)點里；
• 二級索引的 B+Tree 的葉子節(jié)點存放的是主鍵值，而不是實際數(shù)據(jù)。

在查詢時使用了二級索引，如果查詢的數(shù)據(jù)能在二級索引里查詢的到，那么就不需要回表，這個過程就是覆蓋索引。如果查詢的數(shù)據(jù)不在二級索引里，就會先檢索二級索引，找到對應(yīng)的葉子節(jié)點，獲取到主鍵值后，然后再檢索主鍵索引，就能查詢到數(shù)據(jù)了，這個過程就是回表。

按字段特性分

分為主鍵索引、唯一索引、前綴索引、普通索引

故名思意就是有主鍵的索引，唯一列需要的索引，只用前綴就可以建立的索引、普通沒有特點但需要快速查詢的列需要的索引

主鍵索引創(chuàng)建(PRIMARY KEY)：

主鍵索引就是建立在主鍵字段上的索引，通常在創(chuàng)建表的時候一起創(chuàng)建，一張表最多只有一個主鍵索引，索引列的值不允許有空值。

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引創(chuàng)建(UNIQUE KEY)

唯一索引建立在 UNIQUE 字段上的索引，一張表可以有多個唯一索引，索引列的值必須唯一，但是允許有空值。

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要創(chuàng)建唯一索引，可以使用這面這條命令：

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

• 普通索引

普通索引就是建立在普通字段上的索引，既不要求字段為主鍵，也不要求字段為 UNIQUE。

在創(chuàng)建表時，創(chuàng)建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要創(chuàng)建普通索引，可以使用這面這條命令：

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

• 前綴索引

前綴索引是指對字符類型字段的前幾個字符建立的索引，而不是在整個字段上建立的索引，前綴索引可以建立在字段類型為 char、 varchar、binary、varbinary 的列上。

使用前綴索引的目的是為了減少索引占用的存儲空間，提升查詢效率。

在創(chuàng)建表時，創(chuàng)建前綴索引的方式如下：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

建表后，如果要創(chuàng)建前綴索引，可以使用這面這條命令：

CREATE INDEX index_name
ON table_name(column_name(length));

這里可以舉例說明

-- 你的查詢

SELECT * FROM articles WHERE content = 'apple';

數(shù)據(jù)庫使用前綴索引的查找步驟：

• 計算查詢值的前綴：取 'apple' 的前 N 個字符 → 'app'（假設(shè)前綴長度是3）
• 在索引中找 'app'：定位到索引中 'app' 這個鍵
• 拿到對應(yīng)的主鍵列表：上面例子中 'app' 對應(yīng) ID 1, 2, 3
• 回表查完整數(shù)據(jù)：去聚簇索引拿 ID 1, 2, 3 的完整行
• 再過濾一遍：只返回 content 完整值 真正等于 'apple' 的那一行（ID 1）

按字段個數(shù)分類

分為單列索引、聯(lián)合索引（復(fù)合索引）。

• 建立在單列上的索引稱為單列索引，比如主鍵索引；
• 建立在多列上的索引稱為聯(lián)合索引；

通過將多個字段組合成一個索引，該索引就被稱為聯(lián)合索引。

比如，將商品表中的 product_no 和 name 字段組合成聯(lián)合索引(product_no, name)，創(chuàng)建聯(lián)合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

這里重點講解一下復(fù)合索引，底層在葉子節(jié)點是雙向鏈表

在查找時可以看到，聯(lián)合索引的非葉子節(jié)點用兩個字段的值作為 B+Tree 的 key 值。當(dāng)在聯(lián)合索引查詢數(shù)據(jù)時，先按 product_no 字段比較，在 product_no 相同的情況下再按 name 字段比較。

也就是說，聯(lián)合索引查詢的 B+Tree 是先按 product_no 進(jìn)行排序，然后再 product_no 相同的情況再按 name 字段排序。

最左匹配原則

即先按照索引最左邊的列進(jìn)行排序，再按后面的排序，如果不遵循這個原則，就無法使用聯(lián)合索引

比如，如果創(chuàng)建了一個 (a, b, c) 聯(lián)合索引，如果查詢條件是以下這幾種，就可以匹配上聯(lián)合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；
• where b=2 and a=1 and c=3；
• 需要注意的是，因為有查詢優(yōu)化器，所以 a 字段在 where 子句的順序并不重要。

但是，如果查詢條件是以下這幾種，因為不符合最左匹配原則，所以就無法匹配上聯(lián)合索引，聯(lián)合索引就會失效:

• where b=2；
• where c=3；
• where b=2 and c=3；
• 上面這些查詢條件之所以會失效，是因為(a, b, c) 聯(lián)合索引，是先按 a 排序，在 a 相同的情況再按 b 排序，在 b 相同的情況再按 c 排序。所以，b 和 c 是全局無序，局部相對有序的，這樣在沒有遵循最左匹配原則的情況下，是無法利用到索引的。

還有一種是通過a,b,c三列建造的索引，where a = 1,b = 2,c = 3,d = 4時只有前面三個能用

• a=1 → 用到索引（最左前綴，精準(zhǔn)定位）
• b=2 → 用到索引（等值查詢，在 a 相同的基礎(chǔ)上 b 有序）
• c=3 → 用到索引（等值查詢，在 a、b 都相同的基礎(chǔ)上 c 有序）
• d=4 → 用不到索引，因為索引里根本沒有 d 這一列

失效情況

聯(lián)合索引的最左匹配原則在從左到右的查詢?nèi)绻龅搅朔秶樵?，在范圍查詢之后就會停止?/p>

這里舉例說明一下

假設(shè)聯(lián)合索引是 (a, b, c, d)，查詢條件是：

WHERE a = 1 AND b > 2 AND c = 3 AND d = 4

此時的匹配情況是：

• a = 1 → 用到索引（等值，精準(zhǔn)定位）
• b > 2 → 用到索引（范圍查詢，走索引確定范圍的起止邊界）
• c = 3 → 用不到索引的 B+Tree 有序性，因為 b 是范圍查詢，b 相同值的內(nèi)部 c 才有序，跨 b 范圍后 c 就無序了
• d = 4 → 同樣用不到

結(jié)論：a 和 b 兩個字段用到了索引，c 和 d 沒用到。b = 2這種條件才能保證后續(xù)有序，再b > 2就不一定有序了

聯(lián)合索引 (a, b, c, d) 的排序邏輯是：

• 先按 a 排序
• a 相同的情況下，按 b 排序
• b 相同的情況下，按 c 排序
• c 相同的情況下，按 d 排序

舉個例子：

(a=1, b=3, c=1)
(a=1, b=3, c=5)
(a=1, b=4, c=2)  <-- 注意這里，b值變了
(a=1, b=4, c=8)
(a=1, b=5, c=3)  <-- c=3 在這里

到此這篇關(guān)于MySQL 索引分類、最左匹配與失效場景問題分析的文章就介紹到這了,更多相關(guān)mysql索引分類最左匹配內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論