Mysql中复合索引使用规则有哪些

这篇文章主要介绍了关系型数据库中复合索引使用规则有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

联合索引验证：从左向右发挥作用

索引：(c1，c2，c3，c4):找到c1的基础上，可以找到c2，找到c3的基础上，可以找到补体第四成份缺乏

a:select *来自t，其中c1=x，c2=x，c3=x，C4=x；

b:select *来自t，其中c1=x，c2=x，c4x，C3=x；用到了c1 c2 c3 c4

c:select * from t其中c1=x和c2=x和c4=x按c3排序；C1C2用到了索引查找，C3只发挥了排序的作用，C3不用(c3:订购发挥作用了，排序不用作了)，C4的索引就不用，4块木板，中间断了，后面也就用不上了

d:select * from t其中c1=x和c4=x被c3、c2分组；

e:select * from t其中c1=x和c5=x按c2、c3排序；

来自t的f:select *其中c1=x和c2=x和c5=？按c2、c3排序；

创建表t (c1炭(10)、c2炭(10)、c3炭(10)、C4炭(10)、C5炭(10))；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

插入t值(' a1 '，' a2 '，' a3 '，' a4 '，' a5 '，(' b1 '，' b2 '，' b3 '，' b4 '，' b5 ')；

在t(c1，c2，c3，c4)上创建索引idx _ t _ c1234

在t(c1)上创建索引idx _ t _ c1

在t(c2)上创建索引idx _ t _ c2

在t(c3)上创建索引idx _ t _ c3

在t(c4)上创建索引idx _ t _ c4

更改表t删除索引idx _ t _ c1234

a:

解释从t中选择*其中c1='a1 '和c2='b2 '和c3='a3 '和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中复合索引使用规则有哪些”这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!