柚子快報邀請碼778899分享：MySQL 聯(lián)合索引

http://yzkb.51969.com/

文章目錄

1.簡介2.最左匹配3.最左匹配原理4.如何建立聯(lián)合索引?5.覆蓋索引參考文獻

1.簡介

聯(lián)合索引指建立在多個列上的索引。

MySQL 可以創(chuàng)建聯(lián)合索引（即多列上的索引）。一個索引最多可以包含 16 列。

聯(lián)合索引可以測試包含索引中所有列的查詢，或僅測試第一列、前兩列、前三列等等的查詢。如果在索引定義中以正確的順序指定列，則復(fù)合索引可以加快對同一表的多種查詢的速度。

下面是一個聯(lián)合索引的例子。

CREATE TABLE test (

id INT NOT NULL,

last_name CHAR(30) NOT NULL,

first_name CHAR(30) NOT NULL,

PRIMARY KEY (id),

INDEX name (last_name,first_name)

);

name 索引是針對 last_name 和 first_name 列的索引。該索引可加速查詢。這些查詢?yōu)?last_name 和 first_name 值的組合。或僅指定 last_name 值的查詢，因為該列是索引的最左側(cè)前綴，即聯(lián)合索引支持最左匹配。

2.最左匹配

如果 SQL 語句用到了聯(lián)合索引中最左邊的字段，那么這條 SQL 語句就可以利用這個聯(lián)合索引進行匹配，這便是最左匹配。

值得注意的是，當遇到范圍查詢 (>、<、between、like) 就會停止匹配。

假設(shè)，我們對 (a,b) 字段建立一個索引，也就是說，如果 WHERE 條件為下面的則可以匹配索引。

a = 1

a = 1 AND b = 2

// 可以匹配索引，優(yōu)化器會自動調(diào)整 a,b 的順序與索引順序一致。

b = 2 AND a = 1

相反的，下面的條件將無法匹配索引了。

b = 2

而你對 (a,b,c,d) 建立索引，如果條件為：

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

那么 a,b,c 三個字段能用到索引，而 d 就匹配不到，因為遇到了范圍查詢。

3.最左匹配原理

最左匹配是針對聯(lián)合索引來說的，所以我們可以從聯(lián)合索引的原理來了解最左匹配。

我們都知道索引的底層是一顆 B+ 樹，那么聯(lián)合索引當然也是一顆 B+ 樹，只不過聯(lián)合索引的鍵值不是一個，而是多個。構(gòu)建一顆 B+ 樹只能根據(jù)一個鍵值來構(gòu)建，因此數(shù)據(jù)庫依據(jù)聯(lián)合索引最左邊的字段來構(gòu)建 B+ 樹。

假設(shè)我們對 (a,b) 字段建立索引：

如圖所示是按照 a 來進行排序，在 a 相等的情況下，才按 b 來排序。

所以這就能夠解釋為什么條件 a=1 AND b=2 可以利用索引而 b=2 不能利用索引，因為 b 在全局是無序的，只有 a 確定的情況下，b 才是有序。

4.如何建立聯(lián)合索引?

有了上面的基礎(chǔ)，我們可以看一下關(guān)于聯(lián)合索引常見的面試問題。

（1）如果 SQL 為：

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

如果此題回答為對 (a,b,c) 建立索引，那就可以回去等通知了。

此題正確答案是任意順序都可以, 如 (a,b,c) 或 (b,a,c) 或 (c,a,b) 都可以，重點是要將區(qū)分度高的字段放在前面，區(qū)分度低的字段放后面。像性別、狀態(tài)這種字段區(qū)分度就很低，我們一般放后面。

例如假設(shè)區(qū)分度由大到小為 b,a,c。那么我們就對 (b,a,c) 建立索引。在執(zhí)行 SQL 的時候，優(yōu)化器會幫我們調(diào)整 WHERE 后 a,b,c 的順序，讓我們用上索引。

（2）如果 SQL 為：

SELECT * FROM table WHERE a > 1 AND b = 2;

如果此題回答為對 (a,b) 建立索引，那就可以回去等通知了。

正確答案對 (b,a) 建立索引。如果你建立的是 (a,b) 索引，那么只有 a 字段能用得上索引，畢竟最左匹配遇到范圍查詢就停止匹配。

如果對 (b,a) 建立索引那么兩個字段都能用上，優(yōu)化器會幫我們調(diào)整 WHERE 后 a,b 的順序，讓我們用上索引。

（3）如果 SQL 為：

SELECT * FROM table WHERE a > 1 and b = 2 and c > 3;

此題回答是 (b,a) 或 (b,c) 都可以，要結(jié)合具體情況具體分析。

拓展一下：

SELECT * FROM table WHERE a = 1 AND b = 2 AND c > 3;

根據(jù)最左匹配，因為字段 c 是范圍查詢應(yīng)該放到最后，所以應(yīng)該建立 (a,b,c) 或 (b,a,c)。

（4）如果 SQL 為：

SELECT * FROM table WHERE a = 1 ORDER BY b;

對 (a,b) 建索引，當 a = 1 的時候，b 相對有序，可以避免再次排序。

拓展以下，如果 SQL 為：

SELECT * FROM table WHERE a > 1 ORDER BY b;

對 (a) 建立索引，因為 a 的值是一個范圍，這個范圍內(nèi) b 值是無序的，沒有必要對 (a,b) 建立索引。

（5）如果 SQL 為：

SELECT * FROM table WHERE a IN (1,2,3) AND b > 1;

還是對 (a, b) 建立索引，因為 IN 在這里可以視為等值引用，不會中止索引匹配，所以還是 (a,b)。

5.覆蓋索引

覆蓋索引（Covering Index）指的是一個索引包含了所有需要查詢的字段，而不必回到實際的數(shù)據(jù)行中查找。當一個查詢可以直接從索引中獲取所有需要的信息時，就稱之為覆蓋索引。

-- 創(chuàng)建表

CREATE TABLE mytable (

col1 INT,

col2 INT,

col3 VARCHAR(255),

INDEX idx_covering (col1, col2)

);

-- 覆蓋索引查詢

-- 因為 idx_covering 包含了查詢所需的所有列，所以是一個覆蓋索引查詢

SELECT col1, col2 FROM mytable WHERE col1 = 1 AND col2 = 2;

覆蓋索引（Covering Index）是一種索引優(yōu)化技術(shù)，旨在最小化查詢開銷。

聯(lián)合索引有一個作用就是實現(xiàn)覆蓋索引，如果聯(lián)合索引包含了查詢所需的所有列，那么查詢可以直接從索引中獲取所需的數(shù)據(jù)，避免了額外的表訪問，這可以減少 I/O 操作，提高查詢性能。

當然單列索引也可以實現(xiàn)覆蓋索引，即查詢的列是索引列。

參考文獻

8.3.1 How MySQL Uses Indexes - MySQL 8.3.6 Multiple-Column Indexes - MySQL 面試官:談?wù)勀銓ysql聯(lián)合索引的認識？

柚子快報邀請碼778899分享：MySQL 聯(lián)合索引

http://yzkb.51969.com/

參考鏈接