MySQL聯(lián)合索引的使用解讀
更新時間：2025年06月25日 09:54:41 作者：在成都搬磚的鴨鴨
這篇文章主要介紹了MySQL聯(lián)合索引的使用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
1、背景
聯(lián)合索引就是給多個列建一個索引，使用聯(lián)合索引時要滿足最左匹配原則，不然會索引失效，本篇文章就通過explain執(zhí)行計劃研究一下聯(lián)合索引，能讓我們避免使用聯(lián)合索引的一些坑。
2、數(shù)據(jù)示例
創(chuàng)建一張表，有三個索引：主鍵索引、普通索引、聯(liá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+樹結(jié)構(gòu)
可以用如下圖來表示聯(lián)合索引的B+樹結(jié)構(gòu)：
解釋一下上面的圖：
1、藍色部分表示InnoDB的基本存儲單位"頁"，頁上的綠色部分代表目錄項記錄或者用戶記錄。
2、從上往下，第一層是非葉子節(jié)點，每個頁上存儲目錄項記錄，第二層是葉子節(jié)點，每個頁上存儲的是目錄項記錄。
3、目錄項記錄或用戶記錄會存儲聯(lián)合索引的str4列、str5列、主鍵id列。
4、同一個頁上記錄根據(jù)str4列和str5列的大小從左往右順序存儲，同一層葉子節(jié)點或非葉子節(jié)點的所有頁也是根據(jù)str4列和str5列的大小從左往右順序存儲，并且這些頁組成一個雙向鏈表。
5、聯(lián)合索引的大小規(guī)則為先按照最左邊的列str4的大小排列，在列str4相同的情況下再根據(jù)列str5進行排列。
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)
可以看到組合索引依然被使用，這是因為sql執(zhí)行過程中的優(yōu)化器會將sql進行優(yōu)化，優(yōu)化之后就會優(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)
使用右邊的列就沒用到聯(lián)合索引，并且rows為100，會進行全表掃描。
所以在使用聯(lián)合索引的列作為查詢條件時，使用最左邊連續(xù)的列作為查詢條件才能用到聯(lián)合索引。
【3】列前綴匹配
前綴匹配可以分為：左前綴、右前綴、中間部分。模糊查詢只有左前綴會生效，我們依次看看這三種情況是否使用到索引。先看左前綴：
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)
也是全表掃描了。
左前綴匹配不一定會使用到索引，也有可能會全表掃描，這是因為有時候優(yōu)化器認為通過索引查詢的開銷比全表掃描開銷還要大，因為索引查找到的數(shù)據(jù)回表的也是有開銷的，具體細節(jié)后面再講。
【4】范圍匹配
只有使用最左邊的列進行范圍匹配才會生效，例如：
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)
可以看到可能會使用到聯(lián)合索引，但是最后使用了全表掃描，因為優(yōu)化器認為全表掃描更快，這個時候聯(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)合索引，當左邊的列精準匹配，右邊的列范圍匹配時也可能用到聯(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ù)索引列進行排序也是有可能用到索引的，例如：
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排序進行了全表掃描，我們限制一下數(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)合索引。
還有幾種情況會用不到聯(lián)合索引，寫個sql帶上explain驗證一下就行了：
- 1、AESC和DESC混用。
- 2、非聯(lián)合索引精確匹配，聯(lián)合索引列范圍查詢。
- 3、排序的多個列包含非聯(lián)合索引的列。
- 4、使用了表達式。
【6】分組
分組也可能會用到聯(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)合索引的使用，其它二級索引也很好理解，至于索引最終是否一定會用到，可以通過explain去查看執(zhí)行計劃。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
DROP TABLE在不同數(shù)據(jù)庫中的寫法整理
這篇文章主要介紹了DROP TABLE在不同數(shù)據(jù)庫中的寫法整理的相關(guān)資料,需要的朋友可以參考下2017-04-04
mysql group_concat()函數(shù)用法總結(jié)
這篇文章主要介紹了mysql group_concat()函數(shù)用法,結(jié)合實例形式較為詳細的group_concat()函數(shù)的功能、使用方法與相關(guān)注意事項,需要的朋友可以參考下2016-06-06
MySQL INNER JOIN 的底層實現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個步驟，連接時可以使用多種算法，通過本文，我們深入了解了MySQL中INNER JOIN的底層實現(xiàn)原理，需要的朋友可以參考下2023-06-06
mysql 5.7.17 安裝教程 附MySQL服務(wù)無法啟動的解決方法
這篇文章主要為大家詳細介紹了mysql 5.7.17安裝教程，并且為大家分享了MySQL服務(wù)無法啟動的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03
這篇文章主要介紹了mysql 字符串正則表達式及說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
這篇文章主要為大家詳細介紹了MySQL數(shù)據(jù)庫中1045錯誤的解決方法，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-12-12
后來注意到，在上面我們恢復(fù)數(shù)據(jù)的時候是在沒有連接數(shù)據(jù)的狀態(tài)下執(zhí)行的。2009-05-05
這篇文章主要介紹了MySQL中的各種查詢問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
這篇文章主要介紹了MySQL多表查詢詳解上，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07
這篇文章主要為大家詳細介紹了mysql5.7.18版本免安裝的配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-07-07