这篇文章主要介绍了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 | ro免费主机域名ws | 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中复合索引使用规则有哪些”这篇文章对大家有帮助,同时也希望大家多多支持云技术,关注云技术行业资讯频道,更多相关知识等着你来学习!
这篇文章主要介绍“如何规划数据库环境标准化管理”,在日常操作中,相信很多人在如何规划数据库环境标准化管理问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”如何规划数据库环境标准化管理”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!…