MySQL聯(lián)合索引的使用解讀
更新時間：2025年06月25日 09:54:41 作者：在成都搬磚的鴨鴨
這篇文章主要介紹了MySQL聯(lián)合索引的使用方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
1、背景
聯(lián)合索引就是給多個列建一個索引，使用聯(lián)合索引時要滿足最左匹配原則，不然會索引失效，本篇文章就通過explain執(zhí)行計(jì)劃研究一下聯(lián)合索引，能讓我們避免使用聯(lián)合索引的一些坑。
2、數(shù)據(jù)示例
創(chuàng)建一張表，有三個索引：主鍵索引、普通索引、聯(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+樹結(jié)構(gòu)
可以用如下圖來表示聯(lián)合索引的B+樹結(jié)構(gòu)：
解釋一下上面的圖：
1、藍(lán)色部分表示InnoDB的基本存儲單位"頁"，頁上的綠色部分代表目錄項(xiàng)記錄或者用戶記錄。
2、從上往下，第一層是非葉子節(jié)點(diǎn)，每個頁上存儲目錄項(xiàng)記錄，第二層是葉子節(jié)點(diǎn)，每個頁上存儲的是目錄項(xiàng)記錄。
3、目錄項(xiàng)記錄或用戶記錄會存儲聯(lián)合索引的str4列、str5列、主鍵id列。
4、同一個頁上記錄根據(jù)str4列和str5列的大小從左往右順序存儲，同一層葉子節(jié)點(diǎn)或非葉子節(jié)點(diǎn)的所有頁也是根據(jù)str4列和str5列的大小從左往右順序存儲，并且這些頁組成一個雙向鏈表。
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í)行過程中的優(yōu)化器會將sql進(jìn)行優(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，會進(jìn)行全表掃描。
所以在使用聯(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àn)橛袝r候優(yōu)化器認(rèn)為通過索引查詢的開銷比全表掃描開銷還要大，因?yàn)樗饕檎业降臄?shù)據(jù)回表的也是有開銷的，具體細(xì)節(jié)后面再講。
【4】范圍匹配
只有使用最左邊的列進(jìn)行范圍匹配才會生效，例如：
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àn)閮?yōu)化器認(rèn)為全表掃描更快，這個時候聯(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)匹配，右邊的列范圍匹配時也可能用到聯(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)合索引。
還有幾種情況會用不到聯(lián)合索引，寫個sql帶上explain驗(yàn)證一下就行了：
- 1、AESC和DESC混用。
- 2、非聯(lián)合索引精確匹配，聯(lián)合索引列范圍查詢。
- 3、排序的多個列包含非聯(lián)合索引的列。
- 4、使用了表達(dá)式。
【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ì)劃。
以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于MySQL組合索引與最左匹配原則的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用Mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
這篇文章為大家分享了MySQL最基本的命令使用匯總，MySQL最基本的命令使用，包括如何正確連接MySQL(和PHP搭配之最佳組合)，修改密碼與增加新用戶等相關(guān)內(nèi)容的描述，感興趣的小伙伴們可以參考一下2015-11-11
這篇文章主要介紹了MySQL密碼正確卻無法本地登錄的解決方法,需要的朋友可以參考下2014-12-12
MySQL8.0.26的安裝與簡化教程(全網(wǎng)最全)
MySQL關(guān)是一種關(guān)系數(shù)據(jù)庫管理系統(tǒng)，所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用的標(biāo)準(zhǔn)化語言，今天通過本文給大家分享MySQL8.0.26的安裝與簡化教程使全網(wǎng)最詳細(xì)的安裝教程，需要的朋友參考下吧2021-07-07
本文系統(tǒng)講解SQL查詢技術(shù),涵蓋基本查詢（條件篩選、排序、聚合）、多表連接、自連接、子查詢（單行/多行/多列）及合并查詢（UNION/UNION?ALL）,通過emp、dept、salgrade三張表演示員工與部門數(shù)據(jù)關(guān)聯(lián)分析方法2025-08-08
這篇文章主要給大家介紹了關(guān)于MySQL/MariaDB的Root密碼重置的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-09-09
mysql如何增加數(shù)據(jù)表的字段(ALTER)
這篇文章主要介紹了mysql如何增加數(shù)據(jù)表的字段(ALTER),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
這篇文章主要為大家介紹了SELECT?*?效率低原理解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-02-02
這篇文章主要介紹了了解MySQL查詢語句執(zhí)行過程(5大組件),文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08![UCenter info: MySQL Query Error SQL:SELECT value FROM [Table]vars WHERE noteexists](http://img.jbzj.com/images/xgimg/bcimg9.png)
UCenter info: MySQL Query Error SQL:SELECT value FROM [Table
大家先看下數(shù)據(jù)庫權(quán)限問題，然后再進(jìn)行如下操作。2011-09-09