MySQL復(fù)合索引的深入探究
復(fù)合索引(又稱(chēng)為聯(lián)合索引)，是在多個(gè)列上創(chuàng)建的索引。創(chuàng)建復(fù)合索引最重要的是列順序的選擇，這關(guān)系到索引能否使用上，或者影響多少個(gè)謂詞條件能使用上索引。復(fù)合索引的使用遵循最左匹配原則，只有索引左邊的列匹配到，后面的列才能繼續(xù)匹配。本文主要探究復(fù)合索引的創(chuàng)建順序與使用情況。
（一）復(fù)合索引的概念
在單個(gè)列上創(chuàng)建的索引我們稱(chēng)為單列索引，在2個(gè)以上的列上創(chuàng)建的索引稱(chēng)為復(fù)合索引。在單個(gè)列上創(chuàng)建索引相對(duì)簡(jiǎn)單，通常只需要考慮列的選擇率即可，選擇性越好，代表數(shù)據(jù)越分散，創(chuàng)建出來(lái)的索引性能也就更好。通常，某列選擇率的計(jì)算公式為：
selectivity = 施加謂詞條件后返回的記錄數(shù) / 未施加謂詞條件后返回的記錄數(shù)
可選擇率的取值范圍是(0,1]，值越小，代表選擇性越好。
對(duì)于復(fù)合索引(又稱(chēng)為聯(lián)合索引)，是在多個(gè)列上創(chuàng)建的索引。創(chuàng)建復(fù)合索引最重要的是列順序的選擇，這關(guān)系到索引能否使用上，或者影響多少個(gè)謂詞條件能使用上索引。復(fù)合索引的使用遵循最左匹配原則，只有索引左邊的列匹配到，后面的列才能繼續(xù)匹配。
（二）什么情況下會(huì)使用復(fù)合索引的列
復(fù)合索引遵循最左匹配原則，只有索引中最左列匹配到，下一列才有可能被匹配。如果左邊列使用的是非等值查詢(xún)，則索引右邊的列將不會(huì)被查詢(xún)使用，也不會(huì)被排序使用。
實(shí)驗(yàn)：哪些情況下會(huì)使用到復(fù)合索引
復(fù)合索引中的哪些字段被使用到了，是我們非常關(guān)心的問(wèn)題。網(wǎng)絡(luò)上一個(gè)經(jīng)典的例子：
-- 創(chuàng)建測(cè)試表
CREATE TABLE t1(
c1 CHAR(1) not null,
c2 CHAR(1) not null,
c3 CHAR(1) not null,
c4 CHAR(1) not null,
c5 CHAR(1) not null
)ENGINE innodb CHARSET UTF8;
-- 添加索引
alter table t1 add index idx_c1234(c1,c2,c3,c4);
--插入測(cè)試數(shù)據(jù)
insert into t1 values('1','1','1','1','1'),('2','2','2','2','2'),
('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');
需要探索下面哪些查詢(xún)語(yǔ)句使用到了索引idx_c1234，以及使用到了索引的哪些字段？
(A) where c1=? and c2=? and c4>? and c3=?
(B) where c1=? and c2=? and c4=? order by c3
(C) where c1=? and c4=? group by c3,c2
(D) where c1=? and c5=? order by c2,c3
(E) where c1=? and c2=? and c5=? order by c2,c3
(F) where c1>? and c2=? and c4>? and c3=?
A選項(xiàng)：
mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4>'1' and c3='2'; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | idx_c1234 | idx_c1234 | 12 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
使用的索引長(zhǎng)度為12，代表4個(gè)字段都使用了索引。由于c1、c2、c3都是等值查詢(xún)，所以后面的c4列也可以用上。
注：utf8編碼，一個(gè)索引長(zhǎng)度為3，這里12代表4個(gè)字段都用到該索引。
B選項(xiàng)：
mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4='2' order by c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 1 | 20.00 | Using index condition | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
使用的索引長(zhǎng)度為6，代表2個(gè)字段使用了索引。根據(jù)最左使用原則，c1、c2使用了索引。因?yàn)椴樵?xún)中沒(méi)有c3謂詞條件，所以索引值使用到c2后就發(fā)生了中斷，導(dǎo)致只使用了c1、c2列。這里SQL使用了order by排序，但是在執(zhí)行計(jì)劃Extra部分未有filesort關(guān)鍵字，說(shuō)明在索引中按照c3字段順序讀取數(shù)據(jù)即可。
這里特別留意，雖然索引中的c3字段沒(méi)有放在索引的最后，但是確實(shí)使用到了索引中c2字段的有序特性，因?yàn)閳?zhí)行計(jì)劃的Extra部分未出現(xiàn)"fileasort"關(guān)鍵字。這是為什么呢？這里用到了MySQL5.6版本引入的Index Condition Pushdown (ICP) 優(yōu)化。其核心思想是使用索引中的字段做數(shù)據(jù)過(guò)濾。我們來(lái)整理一下不使用ICP和使用ICP的區(qū)別：
如果沒(méi)有使用ICP優(yōu)化，其SQL執(zhí)行步驟為：
1.使用索引列c1,c2獲取滿(mǎn)足條件的行數(shù)據(jù)。where c1='2' and c2='2'
2.回表查詢(xún)數(shù)據(jù)，使用where c4='2'來(lái)過(guò)濾數(shù)據(jù)
3.對(duì)數(shù)據(jù)排序輸出
如果使用了ICP優(yōu)化，其SQL執(zhí)行步驟為：
1.使用索引列c1,c2獲取滿(mǎn)足條件的行數(shù)據(jù)。where c1='2' and c2='2'
2.在索引中使用where c4='2'來(lái)過(guò)濾數(shù)據(jù)
3.因?yàn)閿?shù)據(jù)有序，直接按順序取出滿(mǎn)足條件的數(shù)據(jù)
C選項(xiàng)：
mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group by c3,c2; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------------------------------------------+
使用的索引長(zhǎng)度為3，代表1個(gè)字段使用了索引。根據(jù)最左使用原則，c1使用了索引。因?yàn)椴樵?xún)中沒(méi)有c2謂詞條件，所以索引值使用到c1后就發(fā)生了中斷，導(dǎo)致只使用了c1列。該SQL執(zhí)行過(guò)程為：
1.在c1列使用索引找到c1='2'的所有行，然后回表使用c4='2'過(guò)濾掉不匹配的數(shù)據(jù)
2.根據(jù)上一步的結(jié)果，對(duì)結(jié)果中的c3,c2聯(lián)合排序，以便于得到連續(xù)變化的數(shù)據(jù)，同時(shí)在數(shù)據(jù)庫(kù)內(nèi)部創(chuàng)建臨時(shí)表，用于存儲(chǔ)group by的結(jié)果。
C選項(xiàng)擴(kuò)展：
mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group by c2,c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
使用的索引長(zhǎng)度為3，代表1個(gè)字段使用了索引。根據(jù)最左使用原則，c1使用了索引。
D選項(xiàng)：
mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order by c2,c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------+
使用的索引長(zhǎng)度為3，代表1個(gè)字段都使用了索引。根據(jù)最左使用原則，c1使用了索引。因?yàn)椴樵?xún)中沒(méi)有c2謂詞條件，所以索引值使用到c1后就發(fā)生了中斷，導(dǎo)致只使用了c1列。
D選項(xiàng)擴(kuò)展：
mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order by c3,c2; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Using index condition; Using where; Using filesort | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+----------------------------------------------------+
使用的索引長(zhǎng)度為3，代表1個(gè)字段都使用了索引。根據(jù)最左使用原則，c1使用了索引。因?yàn)椴樵?xún)中沒(méi)有c2謂詞條件，所以索引值使用到c1后就發(fā)生了中斷，導(dǎo)致只使用了c1列。
E選項(xiàng)：
mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c5='2' order by c2,c3; +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+ | 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 2 | 14.29 | Using index condition; Using where | +----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+------------------------------------+
使用的索引長(zhǎng)度為6，代表2個(gè)字段都使用了索引。根據(jù)最左使用原則，c1、c2使用了索引。這里SQL使用了order by排序，但是在執(zhí)行計(jì)劃Extra部分未有filesort關(guān)鍵字，說(shuō)明在索引中按照c3字段順序讀取數(shù)據(jù)即可(c2是常量)。
F選項(xiàng)：
mysql> explain select c1,c2,c3,c4,c5 from t1 where c1>'4' and c2='2' and c3='2' and c4='1'; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | idx_c1234 | idx_c1234 | 3 | NULL | 1 | 20.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
使用的索引長(zhǎng)度為3，代表1個(gè)字段都使用了索引。根據(jù)最左使用原則，c1使用了索引。這里c1使用了不等值查詢(xún)，導(dǎo)致后面的c2查詢(xún)無(wú)法使用索引。該案例非常值得警惕，謂詞條件中含有等值查詢(xún)和范圍查詢(xún)時(shí)，如果范圍查詢(xún)?cè)谒饕懊?，則等值查詢(xún)將無(wú)法使用索引；如果等值查詢(xún)?cè)谇懊?，范圍查?xún)?cè)诤竺妫瑒t都可以使用到索引。
（三）如何創(chuàng)建復(fù)合索引
復(fù)合索引創(chuàng)建的難點(diǎn)在于字段順序選擇，我的觀點(diǎn)如下：
- 如果存在等值查詢(xún)和排序，則在創(chuàng)建復(fù)合索引時(shí)，將等值查詢(xún)字段放在前面，排序放在最后面；
- 如果存在多個(gè)等值查詢(xún)，則選擇性好的放在前面，選擇性差的放在后面；
- 如果存在等值查詢(xún)、范圍查詢(xún)、排序。等值查詢(xún)放在最前面，范圍查詢(xún)和排序需根據(jù)實(shí)際情況決定索引順序；
此外，《阿里巴巴Java開(kāi)發(fā)手冊(cè)-2020最新嵩山版》中有幾個(gè)關(guān)于復(fù)合索引的規(guī)約，我們可以看一下：
1.如果有order by的場(chǎng)景，請(qǐng)注意利用索引的有序性。order by后的字段是組合索引的一部分，并且放在組合索引的最后，避免出現(xiàn)filesort的情況，影響查詢(xún)性能。
正例：where a=? b=? order by c; 索引a_b_c
反例：索引如果存在范圍查詢(xún)，那么索引有序性將無(wú)法使用。如：where a>10 order by b; 索引a_b無(wú)法排序。
2.建復(fù)合索引的時(shí)候，區(qū)分度最高的在最左邊，如果where a=? and b=?，a列的值幾乎接近唯一值，那么只需建單列索引idx_a即可。
說(shuō)明：存在等號(hào)和非等號(hào)混合判斷條件時(shí)，在建索引時(shí)，請(qǐng)把等號(hào)條件的列前置。如：where c>? and d=?,那么即使c的區(qū)分度
更高，也必須把d放在索引的最前列，即創(chuàng)建索引idx_d_c。
實(shí)驗(yàn)：應(yīng)該如何創(chuàng)建復(fù)合索引
在有的文檔里面講到過(guò)復(fù)合索引的創(chuàng)建規(guī)則：ESR原則：精確(Equal)匹配的字段放在最前面，排序(Sort)條件放中間，范圍(Range)匹配的字段放在最后面。接下來(lái)我們來(lái)探索一下該方法是否正確。
例子：存在員工表employees
mysql> show create table employees;
+-----------+-------------------------------
| Table | Create Table
+-----------+-------------------------------------
| employees | CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------
-- 數(shù)據(jù)量約30萬(wàn)行
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
現(xiàn)在需要查詢(xún)1998年后入職的first_name為"Ebbe"員工，并按照出生日期升序排序。
其SQL語(yǔ)句如下：
select emp_no,birth_date,first_name,last_name,gender,hire_date from employees where hire_date >= '1998-01-01' and first_name = 'Ebbe' order by birth_date;
為了優(yōu)化該SQL語(yǔ)句的性能，需要在表上創(chuàng)建索引，為了保證where與order by都使用到索引，決定創(chuàng)建復(fù)合索引，有如下創(chuàng)建順序：
（A）hire_date,first_name,birth_date
（B）hire_date,birth_date,first_name
（C）first_name,hire_date,birth_date
（D）first_name,birth_date,hire_date
（E）birth_date,first_name,hire_date
（F）birth_date,hire_date,first_name
確認(rèn)哪種順序創(chuàng)建索引是最優(yōu)的。
Note:
1.date類(lèi)型占3個(gè)字節(jié)的空間，hire_date和 birth_date都占用3個(gè)字節(jié)的空間。
2.first_name是變長(zhǎng)字段，多使用2個(gè)字節(jié)，如果允許為NULL值，還需多使用1個(gè)字節(jié)，占用16個(gè)字節(jié)
A選項(xiàng)：hire_date,first_name,birth_date
create index idx_a on employees(hire_date,first_name,birth_date);
其執(zhí)行計(jì)劃如下：
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | range | idx_a | idx_a | 19 | NULL | 5678 | 10.00 | Using index condition; Using filesort | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+
這里key_len長(zhǎng)度為19，令人不解，hire_date是非等值查詢(xún)，理論上key_len應(yīng)該為3，通過(guò)使用MySQL workbench查看執(zhí)行計(jì)劃，也可以發(fā)現(xiàn)索引只使用了hire_date列(如下圖)。為什么會(huì)是19而不是3呢？實(shí)在令人費(fèi)解，思考了好久也沒(méi)有想明白，如有知道，望各位大神不吝解答。
B選項(xiàng)：hire_date,birth_date,first_name
為避免干擾，刪除上面創(chuàng)建的索引idx_a，然后創(chuàng)建idx_b。
create index idx_b on employees(hire_date,birth_date,first_name);
其執(zhí)行計(jì)劃如下：
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | range | idx_b | idx_b | 3 | NULL | 5682 | 10.00 | Using index condition; Using filesort | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+
這里key_len長(zhǎng)度為3，hire_date是非等值查詢(xún)，導(dǎo)致后面的索引列無(wú)法使用到。
C選項(xiàng)：first_name,hire_date,birth_date
為避免干擾，刪除上面創(chuàng)建的索引idx_b，然后創(chuàng)建idx_c。
create index idx_c on employees(first_name,hire_date,birth_date);
其執(zhí)行計(jì)劃如下：
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | range | idx_c | idx_c | 19 | NULL | 5 | 100.00 | Using index condition; Using filesort | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+
這里key_len長(zhǎng)度為19，first_name是等值查詢(xún)，可以繼續(xù)使用hire_date列，因?yàn)閔ire_date列是非等值查詢(xún)，導(dǎo)致索引無(wú)法繼續(xù)使用birth_date。
D選項(xiàng)：first_name,birth_date,hire_date
為避免干擾，刪除上面創(chuàng)建的索引idx_c，然后創(chuàng)建idx_d。
create index idx_d on employees(first_name,birth_date,hire_date);
其執(zhí)行計(jì)劃如下：
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_d | idx_d | 16 | const | 190 | 33.33 | Using index condition | +----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
這里key_len長(zhǎng)度為16，first_name是等值查詢(xún)，在謂詞過(guò)濾中未使用birth_date，導(dǎo)致只有first_name列使用上索引，但是birth_date列用于排序，上面執(zhí)行計(jì)劃顯示SQL最終并沒(méi)有排序，說(shuō)明數(shù)據(jù)是從索引按照birth_date有序取出的。
E選項(xiàng)：birth_date,first_name,hire_date
為避免干擾，刪除上面創(chuàng)建的索引idx_d，然后創(chuàng)建idx_e。
create index idx_e on employees(birth_date,first_name,hire_date);
其執(zhí)行計(jì)劃如下：
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 3.33 | Using where; Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
這里未使用到索引，說(shuō)明排序列放在復(fù)合索引的最前面是無(wú)法被使用到的。
F選項(xiàng)：birth_date,hire_date,first_name
為避免干擾，刪除上面創(chuàng)建的索引idx_e，然后創(chuàng)建idx_f。
create index idx_f on employees(birth_date,hire_date,first_name);
其執(zhí)行計(jì)劃如下：
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 3.33 | Using where; Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
與E選項(xiàng)一樣，這里未使用到索引，說(shuō)明排序列放在復(fù)合索引的最前面是無(wú)法被使用到的。
通過(guò)上面的6個(gè)索引測(cè)試，我們發(fā)現(xiàn)，等值查詢(xún)列和范圍查詢(xún)列放在復(fù)合索引前面，復(fù)合索引都能被使用到，只是使用到的列可能不一樣。哪種方式創(chuàng)建索引最好呢？MySQL的查詢(xún)優(yōu)化器是基于開(kāi)銷(xiāo)(cost)來(lái)選擇最優(yōu)的執(zhí)行計(jì)劃的，我們不妨來(lái)看看上面的6個(gè)索引的執(zhí)行開(kāi)銷(xiāo)。
索引 開(kāi)銷(xiāo)cost
---------- ------------
idx_a 8518
idx_b 8524
idx_c 13
idx_d 228
idx_e 78083
idx_f 78083
通過(guò)上面的開(kāi)銷(xiāo)，可以看到：
- idx_a和idx_b：索引使用范圍查詢(xún)字段開(kāi)頭，導(dǎo)致索引只能使用到第一列，無(wú)法消除排序，導(dǎo)致開(kāi)銷(xiāo)較大；
- idx_c和idx_d：索引使用等值查詢(xún)字段開(kāi)頭，范圍查詢(xún)和排序位于后面，開(kāi)銷(xiāo)是最小的；
- idx_e和idx_f ：索引使用排序字段開(kāi)頭，導(dǎo)致索引無(wú)法被使用到，走的全表掃描，開(kāi)銷(xiāo)巨大。
更進(jìn)一步，idx_c和idx_d如何選擇呢？idx_c使用索引進(jìn)行等值查詢(xún)+范圍查詢(xún)，然后對(duì)數(shù)據(jù)進(jìn)行排序；idx_d使用索引進(jìn)行等值查詢(xún)+索引條件下推查詢(xún)，然后按照順序直接獲取數(shù)據(jù)。兩種方式各有優(yōu)劣，我們不妨再來(lái)看一個(gè)例子：
把上面6個(gè)索引都加到表上，看看如下SQL會(huì)選擇哪個(gè)索引。
mysql> show index from employees; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | emp_no | A | 299468 | NULL | NULL | | BTREE | | | | employees | 1 | idx_a | 1 | hire_date | A | 5355 | NULL | NULL | | BTREE | | | | employees | 1 | idx_a | 2 | first_name | A | 290745 | NULL | NULL | | BTREE | | | | employees | 1 | idx_a | 3 | birth_date | A | 299468 | NULL | NULL | | BTREE | | | | employees | 1 | idx_b | 1 | hire_date | A | 6237 | NULL | NULL | | BTREE | | | | employees | 1 | idx_b | 2 | birth_date | A | 297591 | NULL | NULL | | BTREE | | | | employees | 1 | idx_b | 3 | first_name | A | 299468 | NULL | NULL | | BTREE | | | | employees | 1 | idx_c | 1 | first_name | A | 1260 | NULL | NULL | | BTREE | | | | employees | 1 | idx_c | 2 | hire_date | A | 293517 | NULL | NULL | | BTREE | | | | employees | 1 | idx_c | 3 | birth_date | A | 299468 | NULL | NULL | | BTREE | | | | employees | 1 | idx_d | 1 | first_name | A | 1218 | NULL | NULL | | BTREE | | | | employees | 1 | idx_d | 2 | birth_date | A | 294525 | NULL | NULL | | BTREE | | | | employees | 1 | idx_d | 3 | hire_date | A | 298095 | NULL | NULL | | BTREE | | | | employees | 1 | idx_e | 1 | birth_date | A | 4767 | NULL | NULL | | BTREE | | | | employees | 1 | idx_e | 2 | first_name | A | 292761 | NULL | NULL | | BTREE | | | | employees | 1 | idx_e | 3 | hire_date | A | 299468 | NULL | NULL | | BTREE | | | | employees | 1 | idx_f | 1 | birth_date | A | 4767 | NULL | NULL | | BTREE | | | | employees | 1 | idx_f | 2 | hire_date | A | 297864 | NULL | NULL | | BTREE | | | | employees | 1 | idx_f | 3 | first_name | A | 299468 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SQL1
mysql> explain select emp_no,birth_date,first_name,last_name,gender,hire_date from employees where hire_date >= '1998-01-01' and first_name = 'Ebbe' order by birth_date; +----+-------------+-----------+------------+-------+-------------------------+-------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-------------------------+-------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | range | idx_a,idx_b,idx_c,idx_d | idx_c | 19 | NULL | 5 | 100.00 | Using index condition; Using filesort | +----+-------------+-----------+------------+-------+-------------------------+-------+---------+------+------+----------+---------------------------------------+
這里MySQL自動(dòng)選擇了idx_c，是因?yàn)閒irst_name+hire_date兩個(gè)字段已經(jīng)將數(shù)據(jù)過(guò)濾了只有5行，由于數(shù)據(jù)少，排序非常快。反之，如果選擇idx_d，則需要先通過(guò)first_name字段過(guò)濾出符合條件的190行數(shù)據(jù)，然后再使用hire_date篩選數(shù)據(jù)，工作量較大。
SQL2
mysql> explain select emp_no,birth_date,first_name,last_name,gender,hire_date from employees where hire_date >= '1980-01-01' and first_name = 'Ebbe' order by birth_date; +----+-------------+-----------+------------+------+-------------------------+-------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-------------------------+-------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_a,idx_b,idx_c,idx_d | idx_d | 16 | const | 190 | 50.00 | Using index condition | +----+-------------+-----------+------------+------+-------------------------+-------+---------+-------+------+----------+-----------------------+
如果選擇idx_c，first_name+hire_date兩個(gè)字段通過(guò)索引過(guò)濾數(shù)據(jù)之后，數(shù)據(jù)量較大，導(dǎo)致排序非常慢。MySQL自動(dòng)選擇了idx_d，通過(guò)索引的first_name列過(guò)濾數(shù)據(jù)，并通過(guò)索引條件下推過(guò)濾hire_date字段，然后從索引中有序的取出數(shù)據(jù)，相對(duì)來(lái)說(shuō)，由于使用idx_d無(wú)需排序，速度會(huì)更快。
（四）復(fù)合索引總結(jié)
1.復(fù)合索引的創(chuàng)建，如果存在多個(gè)等值查詢(xún)，則將選擇性好的列放在最前面，選擇性差的列放在后面；
2.復(fù)合索引的創(chuàng)建，如果涉及到等值查詢(xún)和范圍查詢(xún)，不管非等值查詢(xún)的列的選擇性如何好，等值查詢(xún)的字段要放在非等值查詢(xún)的前面；
3.復(fù)合索引的創(chuàng)建，如果涉及到等值查詢(xún)和范圍查詢(xún)和排序(order by、group by)，則等值查詢(xún)放在索引最前面，范圍查詢(xún)和排序哪個(gè)在前，哪個(gè)在后，需要根據(jù)實(shí)際場(chǎng)景決定。如果范圍查詢(xún)?cè)谇埃瑒t無(wú)法使用到索引的有序性，需filesort，適用于返回結(jié)果較少的SQL，因?yàn)榻Y(jié)果少則排序開(kāi)銷(xiāo)?。蝗绻判蛟谇?，則可以使用到索引的有序性，但是需要回表(或者索引條件下推)去查詢(xún)數(shù)據(jù)，適用于返回結(jié)果較多的SQL，因?yàn)闊o(wú)需排序，直接取出數(shù)據(jù)。
4.復(fù)合索引的創(chuàng)建，一定不能把order by、group by的列放在索引的最前面，因?yàn)椴樵?xún)中總是where先于order by執(zhí)行；
5.使用索引進(jìn)行范圍查詢(xún)會(huì)導(dǎo)致后續(xù)索引字段無(wú)法被使用，如果有排序，無(wú)法消除filesort排序。例子：a_b_c索引，where a>? and b = ? order by c，則a可以被使用到，b無(wú)法被使用，c字段需filesort。
總結(jié)
到此這篇關(guān)于MySQL復(fù)合索引的文章就介紹到這了,更多相關(guān)MySQL復(fù)合索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在創(chuàng)建監(jiān)控項(xiàng)之前要盡量考慮清楚要監(jiān)控什么，怎么監(jiān)控，監(jiān)控?cái)?shù)據(jù)如何存儲(chǔ)，監(jiān)控?cái)?shù)據(jù)如何展現(xiàn)，如何處理報(bào)警等。本文主要介紹了zabbix監(jiān)控mysql的實(shí)現(xiàn)方法，感興趣的可以了解一下2021-06-06
如何使用MySQL一個(gè)表中的字段更新另一個(gè)表中字段
這篇文章主要介紹了如何使用MySQL一個(gè)表中的字段更新另一個(gè)表中字段,需要的朋友可以參考下2018-11-11
這篇文章主要給大家介紹了關(guān)于MySQL復(fù)合索引的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
mysql實(shí)現(xiàn)不用密碼登錄的實(shí)例方法
在本篇文章里小編給大家整理的是一篇關(guān)于mysql實(shí)現(xiàn)不用密碼登錄的實(shí)例方法，有需要的朋友們可以學(xué)習(xí)參考下。2020-08-08
這篇文章主要介紹了windows下Mysql多實(shí)例部署,需要部署Mysql多個(gè)實(shí)例，原理很簡(jiǎn)單，多個(gè)mysql服務(wù)運(yùn)行使用不同的配置及數(shù)據(jù)管理，需要的朋友可以參考下2022-09-09
在看discuz源碼時(shí)看到了sql_mode=""，不知道是干嘛的，猜想是設(shè)置模式，但是不清楚是什么模式，于是從網(wǎng)上搜了下，sql_mode="" 的作用2011-08-08
這篇文章主要介紹了在Docker中使用MySQL的教程,介紹了簡(jiǎn)單的內(nèi)部搭建步驟,需要的朋友可以參考下2015-04-04
跳槽必備之你設(shè)計(jì)索引的原則是什么?怎么避免索引失效?
索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量符合這些原則，便于提升索引地使用效率，更高效地使用索引。今天給大家介紹跳槽必備之你設(shè)計(jì)索引的原則是什么?怎么避免索引失效?感興趣的朋友一起看看吧2021-05-05
MySql如何解決mysql沒(méi)有root用戶(hù)問(wèn)題
文章主要描述了在MySQL中沒(méi)有root用戶(hù)的情況如何解決,首先,作者提到由于沒(méi)有root用戶(hù),無(wú)法進(jìn)行后續(xù)的權(quán)限設(shè)置和操作,然后,作者詳細(xì)介紹了創(chuàng)建root用戶(hù)并賦予其所有權(quán)限的步驟,包括解決可能遇到的錯(cuò)誤,最后,作者總結(jié)了整個(gè)過(guò)程,并希望大家能夠從中受益2025-02-02
mysql下怎樣運(yùn)行腳本以運(yùn)行niuzi.sql為例
mysql下運(yùn)行腳本，有兩種方法，都是在命令行下進(jìn)行的，需要的朋友可以記錄下2014-07-07