建立a,b,c三个字段的联合索引,判断下列语句是否使用到索引?用到了哪些索引?
创建一个test表,创建表的语句如下:
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`a` int(255) DEFAULT NULL,
`b` int(255) DEFAULT NULL,
`c` int(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_abc` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
联合索引:index_abc
(a
,b
,c
)
a、b、c三个字段类型均为int且允许为空。
问题:下列语句是否使用到索引?用到了哪些索引?
-
select * from test where a=1 and b>1 order by c;
如图:从key这一列的值为index_abc可以得出MySQL优化器使用到了联合索引index_abc,我们再来看一下key_len这一列,key_len就是索引长度,这里的长度为5,我们知道int类型的长度是4,又允许为空,所以key_len=4+1,就是这里的长度5了。答:语句使用到了索引,结合最左匹配原则,再加上key_len的长度,综合分析得出:使用的是a字段的单列索引。
为什么呢?
因为MySQL最左匹配原则会一直向右匹配,直到遇到范围查询就会停止匹配,这里条件是where a=1 and b>1 order by c,所以匹配完a字段,匹配b字段是遇到了范围查询就停止匹配了,所以字段b和c都没有使用到索引。 -
select * from test where a=1 and b=2 order by c;
如图:从key这一列的值为index_abc可以得出MySQL优化器使用到了联合索引index_abc,我们再来看一下key_len这一列,这里的长度为10,从上面分析我们知道int类型且允许为空时的长度是5,而我们的查询条件是where a=1 and b=2 order by c,所以用到了index_ab索引。答:语句使用到了索引,结合最左匹配原则,再加上key_len的长度,综合分析得出:使用的是ab字段的联合索引。
-
select * from test group by c;
如图:从key这一列的值为index_abc可以得出MySQL优化器使用到了联合索引index_abc,我们再来看一下key_len这一列,这里的长度为15,从上面分析我们知道int类型且允许为空时的长度是5,index_abc联合索引的最大可能长度也是15,所以是使用到了index_abc联合索引。答:语句使用到了索引,使用的是index_abc联合索引。
补充内容:
key_len表示使用的索引长度,key_len可以衡量索引的好坏,key_len越小 索引效果越好。常见的列类型长度计算:
列类型 | 是否为空 | 长度 | key_len | 备注 |
---|---|---|---|---|
tinyint | 允许Null | 1 | key_len = 1+1 | 允许NULL,key_len长度加1 |
tinyint | 不允许Null | 1 | key_len = 1 | 不允许NULL |
int | 允许Null | 4 | key_len = 4+1 | 允许NULL,key_len长度加1 |
int | 不允许Null | 4 | key_len = 4 | 不允许NULL |
bigint | 允许Null | 8 | key_len = 8+1 | 允许NULL,key_len长度加1 |
bigint | 不允许Null | 8 | key_len = 8 | 不允许NULL |
char(1) | 允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 1*3 + 1 | 允许NULL,字符集utf8,key_len长度加1 |
char(1) | 不允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 1*3 | 不允许NULL,字符集utf8 |
varchar(10) | 允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 10*3 + 2 + 1 | 动态列类型,key_len长度加2,允许NULL,key_len长度加1 |
varchar(10) | 不允许Null | utf8mb4=4,utf8=3,gbk=2 | key_len = 10*3 + 2 | 动态列类型,key_len长度加2 |