Mysql中復合索引使用規則有哪些

這篇文章主要介紹了Mysql中復合索引使用規則有哪些，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

聯合索引驗證:從左向右發揮作用
索引：(c1,c2,c3,c4):找到c1的基礎上，可以找到c2，找到c3的基礎上，可以找到c4
a:select * from t where c1=x and c2=x and c3=x and c4=x;
b:select * from t where c1=x and c2=x and c4>x and c3=x; 用到了c1+c2+c3+c4
c:select * from t where c1=x and c2=x and c4=x order by c3;   C1+C2用到了索引查找，C3只發揮了排序的作用，C3不用（order by c3:發揮作用了，排序不用作了），C4的索引就不用，4塊木板，中間斷了，后面也就用不上了
d:select * from t where c1=x and c4=x group by c3,c2;
e:select * from t where c1=x and c5=x order by c2,c3;
f:select * from t where c1=x and c2=x and c5=? order by c2,c3;


create table t (c1 char(10),c2 char(10),c3 char(10),C4 char(10),c5 char(10));
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');
insert into t values('a1','a2','a3','a4','a5'),('b1','b2','b3','b4','b5');


create index idx_t_c1234 on t(c1,c2,c3,c4);
create index idx_t_c1 on t(c1);
create index idx_t_c2 on t(c2);
create index idx_t_c3 on t(c3);
create index idx_t_c4 on t(c4);

alter table t drop index idx_t_c1234; 

a:
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a';
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 44      | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+


key_len: 44 // CHAR(10)*4 + 4 * NULL:說明全用到了3個索引，且都是等值查詢的索引：c1,c2,c3,c4


刪除了復合索引后：發現只用到c1索引，c2,c3,c4索引全沒用上
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a';
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                       | key      | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c1 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+


刪除了復合索引后：發現只使用了一個索引c4，沒有用c1索引，這是因為優化器發現c4='a1'一條也沒找到，用這個索引查詢是最快的
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a1';
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                       | key      | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c4 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+


刪除了復合索引后：發現只使用了一個索引c1，沒有用其它索引，這是因為優化器沒有發現哪個條件取值記錄最少（c2,c3,c4='等值連接也是匹配多條）就選第最左列索引
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4='a4';
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys                       | key      | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c3,idx_t_c4 | idx_t_c1 | 11      | const |   18 | Using index condition; Using where |
+----+-------------+-------+------+-------------------------------------+----------+---------+-------+------+------------------------------------+

a:
explain select * from t where c4='a1' and c2='b2' and c3='a3'  and c1='a1';
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 44      | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------------------------+------+--------------------------+
where條件后面的順序無關 

b:
explain select * from t where c1='a1' and c2='b2' and c4>'a' and c3='a3';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | idx_t_c1234   | idx_t_c1234 | 44      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+


 key_len: 44 // CHAR(10)*4 + 4 * NULL:說明全用到了4個索引，且都是等值查詢的索引：c1,c2,c3,c4,全通過
 Using index condition：5.6新特性，Where條件過濾是在innodb引擎層就可做掉了，這樣innodb發送給server層的會少很多，如果不啟用該功能，則數據通過索引訪問后，數據要發送到server層進行where過濾


b:
explain select * from t where c1='a1' and c2='b2' and c3='a3'  and c4>'a';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | idx_t_c1234   | idx_t_c1234 | 44      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+


range：代表c4采用索引了，且使用到范圍查找


c:
explain select * from t where c1='a1' and c2='b2' and c4='b4' order by c3; 
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 22      | const,const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+


 key_len: 22 // CHAR(10)*2 + 2 * NULL:說明全用到了c1,c2索引,且都是等值查詢的索引：c1,c2
 Using where:說明c4在server層進行where過濾操作
 c3：用到了索引排序
 
ref 需要與索引比較的列 列名或者const（常數，where id = 1的時候就是const了）



刪除了復合索引后：只用到了c1索引，也就是只用一個索引，其它索引也沒用上，排序也沒用上
explain select * from t where c1='a1' and c2='b2' and c4='b4' order by c3; 
+----+-------------+-------+------+----------------------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys              | key      | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+----------------------------+----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2,idx_t_c4 | idx_t_c1 | 11      | const |    2 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+----------------------------+----------+---------+-------+------+----------------------------------------------------+

d:
explain select * from t where c1='a1' and c4='c4' group by c3,c2;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                                               |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition; Using where; Using temporary; Using filesort |


key_len: 11 // CHAR(10)*1 + 1 * NULL:說明全用到了c1索引,且都是等值查詢的索引：c1
Using temporary：DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列,且沒用到索引，才會用臨時表來排序，該臨時表是內存臨時表，還不是最糟糕的，最怕的是Using disk temporary
Using filesort：當我們試圖對一個沒有索引的字段進行排序時，就是filesoft
c3,c2由于與(c1,c2,c3,c4)索引不連續，無法用到索引排序

刪除了復合索引后：只用到了c1索引，也就是只用一個索引，其它索引也沒用上，group by 也沒用上
explain select * from t where c1='a1' and c4='c4' group by c3,c2;
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys     | key      | key_len | ref   | rows | Extra                                                               |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c4 | idx_t_c4 | 11      | const |    1 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+---------------------------------------------------------------------+


d:
explain select * from t where c1='a1' and c4='c4' group by c2,c3;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+


c2,c3用到了(c1,c2,c3,c4)索引排序，與c1相連

e:
explain select * from t where c1='a3' and c5='a5' order by c2,c3;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------+


 key_len: 11 // CHAR(10)*1 + 1 * NULL:說明全用到了c1索引,且都是等值查詢的索引：c1

f:
explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c2,c3; 
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 22      | const,const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+------------------------------------+


 key_len: 11 // CHAR(10)*2 + 2 * NULL:說明全用到了c1索引,且都是等值查詢的索引：c1,c2

group by 中能通過索引避免排序的原理：
explain select * from t where c1='a1' and c4='c4' group by c3,c2;
explain select * from t where c1='a1' and c2='b2' and c4='b4' order by c3; 
where條件只是過慮數據，在過濾的過程中，如果c3,c2有索引，就可直接使用
在查找的過程中，己可得到c3在一起的數據，此時可以sum,avg等，不用排序了

刪除了復合索引后：只用到了c1索引，也就是只用一個索引，其它索引也沒用上， order by  也沒用上
explain select * from t where c1='a1' and c2='a2' and c5='a5' order by c2,c3; 
+----+-------------+-------+------+-------------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys     | key      | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1,idx_t_c2 | idx_t_c1 | 11      | const |    2 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+----------------------------------------------------+


g:
explain select * from t where c3='a%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |   36 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
全表掃，沒用到了復合索引idx_t_c1234，除非Where條件后面有c1,c2

explain select * from t where c1='a%';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t     | ref  | idx_t_c1234   | idx_t_c1234 | 11      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

用到了復合索引idx_t_c1234

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Mysql中復合索引使用規則有哪些”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!