MySQL联合索引生效验证

建表、添加数据，用于测试

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gid` varchar(20) NOT NULL,
`cid` int(11) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_Gid_Cid_SId` (`gid`,`cid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student (gid,cid,uid,name) values
('10001',1,1,'张三'),
('10002',2,2,'李四'),
('10003',3,3,'王五');

select * from student;

查询结果字段说明

id：编号
select_type：查询类型
table：表
type：类型
possible_keys：预测用到的索引
key：实际使用的索引
key_len：实际使用的索引的长度
ref：表之间的引用
rows：通过索引查询到的数据量
Extra：额外的信息

type 字段解释

type 表示对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL — MySQL将遍历全表以找到匹配的行（全表扫描）
index —index与ALL区别为index类型只遍历索引树
range —只检索给定范围的行，使用一个索引来选择行
ref —表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref —类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const —当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
system —system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL —MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

全部索引字段生效

explain select * from student where gid='10001' and cid=1 and uid=1;
explain select * from student where gid = '10001' and cid = 1 and uid>1;

预计不使用索引，实际全部索引字段生效

　　前提是 gourp by 和 order by 后面使用的字段是联合索引的字段，且查询的字段也是联合索引的字段，那么索引会全生效

　　这里 type 的类型为 index，综上所述，应该是扫描了整个索引树，比全表扫描好点。

explain select uid from student order by cid;
explain select gid from student group by gid;

只有一个索引字段生效

explain select * from student where gid='10001';
explain select * from student where gid='10001' and uid=1;
explain select * from student where gid > '10001';

只有两个索引字段生效

explain select * from student where gid='10001' and cid=1;
explain select * from student where gid = '10001' and cid > 1;

预计会使用索引，实际没使用索引

　　下面关于 between 的语句，是将 gid 字段改成 int ，再使用 between 做的测试，仍然不使用索引。

　　之所以预计会使用索引，是看很多博客都说 >=、<= 和 between 会使用索引，或者说符合最左原则会使用，所以验证了下。发现 >、<、>=、<= 都走索引，between 不走索引。

explain select * from student where gid like '100%';
explain select * from student where gid between 10001 and 10003;

like 真正能使用索引的情况

explain select * from student where gid like 'test%';

PS：

　　根据上面验证的情况，猜测 like 语句的参数值（与百分号拼接的内容）不能是数字类型，否则也会不生效。

　　另外，百分号在前（后缀）和在两边（中缀）索引不会生效。

以下sql未使用索引

　　where 子句中包好 or 会导致联合索引失效。

explain select * from student where cid=1 and uid=1;
explain select * from student order by gid;
explain select name from student order by gid;
explain select uid from student order by name;
explain select * from student where gid='10001' and cid=1 or uid=1;

说明

　　以上内容均经过本地测试。

参考：地址1、地址2