MySQL聯(lián)合索引的使用解讀

更新時(shí)間：2025年06月25日 09:54:41 作者：在成都搬磚的鴨鴨

這篇文章主要介紹了MySQL聯(lián)合索引的使用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

1、背景

聯(lián)合索引就是給多個(gè)列建一個(gè)索引，使用聯(lián)合索引時(shí)要滿足最左匹配原則，不然會(huì)索引失效，本篇文章就通過(guò)explain執(zhí)行計(jì)劃研究一下聯(lián)合索引，能讓我們避免使用聯(lián)合索引的一些坑。

2、數(shù)據(jù)示例

創(chuàng)建一張表，有三個(gè)索引：主鍵索引、普通索引、聯(lián)合索引，我們重點(diǎn)關(guān)注聯(lián)合索引：

CREATE TABLE test1
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    str1 VARCHAR(255) NOT NULL DEFAULT '',
    str2 VARCHAR(255),
    str3 CHAR(5),
    str4 VARCHAR(255),
    str5 CHAR(10),
    INDEX idx_str1 (str1),
    INDEX idx_str4_str5 (str4, str5)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

插入100條數(shù)據(jù)：

INSERT INTO test1 (str1, str2, str3, str4, str5) VALUES
                                                     ('value1', 'data1', 'abc', 'value4_1', 'value5_1'),
                                                     ('value2', 'data2', 'def', 'value4_2', 'value5_2'),
                                                     ('value3', 'data3', 'ghi', 'value4_3', 'value5_3'),
                                                     ('value4', 'data4', 'jkl', 'value4_4', 'value5_4'),
                                                     ('value5', 'data5', 'mno', 'value4_5', 'value5_5'),
                                                     ('value6', 'data6', 'pqr', 'value4_6', 'value5_6'),
                                                     ('value7', 'data7', 'stu', 'value4_7', 'value5_7'),
                                                     ('value8', 'data8', 'vwx', 'value4_8', 'value5_8'),
                                                     ('value9', 'data9', 'yz1', 'value4_9', 'value5_9'),
                                                     ('value10', 'data10', 'yz2', 'value4_10', 'value5_10'),
                                                     ('value11', 'data11', 'yz3', 'value4_11', 'value5_11'),
                                                     ('value12', 'data12', 'yz4', 'value4_12', 'value5_12'),
                                                     ....
                                                     ('value98', 'data98', 'yz90', 'value4_98', 'value5_98'),
                                                     ('value99', 'data99', 'yz91', 'value4_99', 'value5_99'),
                                                     ('value100', 'data100', 'yz92', 'value4_100', 'value5_100');

3、聯(lián)合索引B+樹(shù)結(jié)構(gòu)

可以用如下圖來(lái)表示聯(lián)合索引的B+樹(shù)結(jié)構(gòu)：

在這里插入圖片描述

解釋一下上面的圖：

1、藍(lán)色部分表示InnoDB的基本存儲(chǔ)單位"頁(yè)"，頁(yè)上的綠色部分代表目錄項(xiàng)記錄或者用戶記錄。

2、從上往下，第一層是非葉子節(jié)點(diǎn)，每個(gè)頁(yè)上存儲(chǔ)目錄項(xiàng)記錄，第二層是葉子節(jié)點(diǎn)，每個(gè)頁(yè)上存儲(chǔ)的是目錄項(xiàng)記錄。

3、目錄項(xiàng)記錄或用戶記錄會(huì)存儲(chǔ)聯(lián)合索引的str4列、str5列、主鍵id列。

4、同一個(gè)頁(yè)上記錄根據(jù)str4列和str5列的大小從左往右順序存儲(chǔ)，同一層葉子節(jié)點(diǎn)或非葉子節(jié)點(diǎn)的所有頁(yè)也是根據(jù)str4列和str5列的大小從左往右順序存儲(chǔ)，并且這些頁(yè)組成一個(gè)雙向鏈表。

5、聯(lián)合索引的大小規(guī)則為先按照最左邊的列str4的大小排列，在列str4相同的情況下再根據(jù)列str5進(jìn)行排列。

4、聯(lián)合索引的幾種使用方式

【1】全值匹配

查詢條件將聯(lián)合索引中所有列都用到了就叫全值匹配，例如：

select * from test1 where str4=‘value4_32' and str5=‘value5_32';

mysql [xxx]> explain select * from test1 where str4='value4_32' and str5='value5_32';
+------+-------------+-------+------+---------------+---------------+---------+-------------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key           | key_len | ref         | rows | Extra                 |
+------+-------------+-------+------+---------------+---------------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | test1 | ref  | idx_str4_str5 | idx_str4_str5 | 1064    | const,const | 1    | Using index condition |
+------+-------------+-------+------+---------------+---------------+---------+-------------+------+-----------------------+
1 row in set (0.001 sec)

可以看到組合索引被使用了，我們將查詢條件的列str4和列str5調(diào)換位置，組合索引是否可以用到呢：

select * from test1 where str5=‘value4_32' and str4=‘value5_32';

mysql [xxx]> explain select * from test1 where str5='value4_32' and str4='value5_32';
+------+-------------+-------+------+---------------+---------------+---------+-------------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key           | key_len | ref         | rows | Extra                 |
+------+-------------+-------+------+---------------+---------------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | test1 | ref  | idx_str4_str5 | idx_str4_str5 | 1064    | const,const | 1    | Using index condition |
+------+-------------+-------+------+---------------+---------------+---------+-------------+------+-----------------------+
1 row in set (0.001 sec)

可以看到組合索引依然被使用，這是因?yàn)閟ql執(zhí)行過(guò)程中的優(yōu)化器會(huì)將sql進(jìn)行優(yōu)化，優(yōu)化之后就會(huì)優(yōu)先使用列str4去查詢記錄。

【2】部分列匹配

只使用聯(lián)合索引中的部分列作為查詢條件，例如：

select * from test1 where str4=‘value5_32';

mysql [xxx]> explain select * from test1 where str4='value4_32';
+------+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | test1 | ref  | idx_str4_str5 | idx_str4_str5 | 1023    | const | 1    | Using index condition |
+------+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+
1 row in set (0.001 sec)

可以看到使用最左邊的列str4用到了聯(lián)合索引，再試試只使用右邊的列str5作為查詢條件：

select * from test1 where str5=‘value5_32';

mysql [xxx]> explain select * from test1 where str5='value5_32';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 100  | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.001 sec)

使用右邊的列就沒(méi)用到聯(lián)合索引，并且rows為100，會(huì)進(jìn)行全表掃描。

所以在使用聯(lián)合索引的列作為查詢條件時(shí)，使用最左邊連續(xù)的列作為查詢條件才能用到聯(lián)合索引。

【3】列前綴匹配

前綴匹配可以分為：左前綴、右前綴、中間部分。模糊查詢只有左前綴會(huì)生效，我們依次看看這三種情況是否使用到索引。先看左前綴：

select * from test1 where str4 like ‘xxx%';

mysql [xxx]> explain select * from test1 where str4 like 'xxx%';
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | test1 | range | idx_str4_str5 | idx_str4_str5 | 1023    | NULL | 1    | Using index condition |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
1 row in set (0.001 sec)

可以看到左前綴用到了聯(lián)合索引，再看右前綴：

select * from test1 where str4 like ‘%xxx';

mysql [xxx]> explain select * from test1 where str4 like '%xxx';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 100  | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

可以看到全表掃描了，再看中間匹配：

explain select * from test1 where str4 like ‘%xxx%';

mysql [xxx]> explain select * from test1 where str4 like '%xxx%';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 100  | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

也是全表掃描了。

左前綴匹配不一定會(huì)使用到索引，也有可能會(huì)全表掃描，這是因?yàn)橛袝r(shí)候優(yōu)化器認(rèn)為通過(guò)索引查詢的開(kāi)銷比全表掃描開(kāi)銷還要大，因?yàn)樗饕檎业降臄?shù)據(jù)回表的也是有開(kāi)銷的，具體細(xì)節(jié)后面再講。

【4】范圍匹配

只有使用最左邊的列進(jìn)行范圍匹配才會(huì)生效，例如：

select * from test1 where str4 > ‘value4_56' and str4 < ‘value4_78';

mysql [xxx]> explain select * from test1 where str4 > 'value4_56' and str4 < 'value4_78';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | ALL  | idx_str4_str5 | NULL | NULL    | NULL | 100  | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.002 sec)

可以看到可能會(huì)使用到聯(lián)合索引，但是最后使用了全表掃描，因?yàn)閮?yōu)化器認(rèn)為全表掃描更快，這個(gè)時(shí)候聯(lián)合索引就失效了，我們修改一下select *為select str4，也就是不需要回表，再看看是否使用到索引：

select str4 from test1 where str4 > ‘value4_56' and str4 < ‘value4_78';

mysql [xxx]> explain select str4 from test1 where str4 > 'value4_56' and str4 < 'value4_78';
+------+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | test1 | range | idx_str4_str5 | idx_str4_str5 | 1023    | NULL | 23   | Using where; Using index |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.001 sec)

可以看到成功使用了聯(lián)合索引，當(dāng)左邊的列精準(zhǔn)匹配，右邊的列范圍匹配時(shí)也可能用到聯(lián)合索引，例如：

select * from test1 where str4 = ‘value4_56' and str5 < ‘value5_80';

mysql [xxx]> explain select * from test1 where str4 = 'value4_56' and str5 < 'value5_80';
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | test1 | range | idx_str4_str5 | idx_str4_str5 | 1064    | NULL | 1    | Using index condition |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
1 row in set (0.001 sec)

可以看到聯(lián)合索引被使用了。

【5】排序

根據(jù)索引列進(jìn)行排序也是有可能用到索引的，例如：

select * from test1 order by str4,str5;

mysql [xxx]> explain select * from test1 order by str4,str5;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 100  | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.000 sec)

可以看到根據(jù)str4和str5排序進(jìn)行了全表掃描，我們限制一下數(shù)量：

select * from test1 order by str4,str5 limit 1;

MariaDB [mng]> explain select * from test1 order by str4,str5 limit 1;
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
| id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
|    1 | SIMPLE      | test1 | index | NULL          | idx_str4_str5 | 1064    | NULL | 1    |       |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
1 row in set (0.000 sec)

可以看到使用到了聯(lián)合索引。

還有幾種情況會(huì)用不到聯(lián)合索引，寫(xiě)個(gè)sql帶上explain驗(yàn)證一下就行了：

1、AESC和DESC混用。
2、非聯(lián)合索引精確匹配，聯(lián)合索引列范圍查詢。
3、排序的多個(gè)列包含非聯(lián)合索引的列。
4、使用了表達(dá)式。

【6】分組

分組也可能會(huì)用到聯(lián)合索引，例如:

select str4,str5,count(*) from test1 group by str4,str5;

mysql [xxx]> explain select str4,str5,count(*) from test1 group by str4,str5;
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | idx_str4_str5 | 1064    | NULL | 100  | Using index |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.001 sec)

5、總結(jié)

熟悉聯(lián)合索引的使用，其它二級(jí)索引也很好理解，至于索引最終是否一定會(huì)用到，可以通過(guò)explain去查看執(zhí)行計(jì)劃。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL聯(lián)合索引的使用解讀

目錄

1、背景

2、數(shù)據(jù)示例

3、聯(lián)合索引B+樹(shù)結(jié)構(gòu)

4、聯(lián)合索引的幾種使用方式

【1】全值匹配

【2】部分列匹配

【3】列前綴匹配

【4】范圍匹配

【5】排序

【6】分組

5、總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL聯(lián)合索引的使用解讀

目錄

1、背景

2、數(shù)據(jù)示例

3、聯(lián)合索引B+樹(shù)結(jié)構(gòu)

4、聯(lián)合索引的幾種使用方式

【1】全值匹配

【2】部分列匹配

【3】列前綴匹配

【4】范圍匹配

【5】排序

【6】分組

5、總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

1、背景

3、聯(lián)合索引B+樹(shù)結(jié)構(gòu)

5、總結(jié)