mysql复合索引，覆盖索引，普通索引，唯一索引

复合索引

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',
  `user_phone` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户手机号',
  `user_age` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

--- 建立复合索引

ALTER TABLE `cdqypt`.`my_test` ADD KEY `idx_phone_name_age` (`user_phone`, `user_name`, `user_age`); 

关于复合索引
Mysql从左到右的使用索引中的字段,一个查询可以索引的一部分或多部分,但只能是最左侧部分开始。例如索引ADD KEY idx_phone_name_age (user_phone, user_name, user_age)，从左边开始那么就只有5种情况(user_phone), (user_phone,user_name),(user_phone,user_age),(user_phone,user_age,user_name),( user_phone,user_name,user_age)

索引成功的情况

EXPLAIN SELECT * FROM `my_test` t1 WHERE t1.`user_phone` = '15555555555';
EXPLAIN SELECT * FROM `my_test` t1 WHERE t1.`user_phone` = '15555555555' AND t1.`user_age` = 7;
EXPLAIN SELECT * FROM `my_test` t1 WHERE t1.`user_phone` = '15555555555' AND t1.`user_name` = '小王';
EXPLAIN SELECT * FROM `my_test` t1 WHERE t1.`user_phone` = '15555555555' AND t1.`user_name` = '小王' AND t1.`user_age`=7;
EXPLAIN SELECT * FROM `my_test` t1 WHERE t1.`user_phone` = '15555555555' AND t1.`user_age` = 7 AND t1.`user_name` = '小王';

索引失败的情况

EXPLAIN SELECT * FROM `my_test` t1 WHERE t1.`user_age` = 7 AND t1.`user_name` = '小王';
EXPLAIN SELECT * FROM `my_test` t1 WHERE  t1.`user_name` = '小王' AND t1.`user_age` = 7 ;

覆盖索引

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中 （联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，这就是覆盖索引

EXPLAIN SELECT `id`, `user_name`, `user_phone`, `user_age`, `grade` FROM `my_test`

我们发现type是all，key也是null

EXPLAIN SELECT `user_name`, `user_phone`, `user_age` FROM `my_test` 

同样是查询全表，但是此刻type是index，key也用上了索引，还有发现之前的extra是null，这时已经变为了Using index，而出现Using index说明我们的sql成功的用上了覆盖索引

 EXPLAIN SELECT `user_name`, `user_phone`, `user_age` FROM `my_test` WHERE grade='111' 

虽然是select 查询全部用上了索引字段，但是where条件并没有使用索引字段，导致查询type还是all

EXPLAIN SELECT `user_name`, `user_phone`, `user_age` FROM `my_test` WHERE user_age=111

虽然复合索引的左边是user_phone，所以我们这里的user_age=111这里是不会触发索引，但是我们发现key还是用上了索引，这是因为select的字段和where的字段都在索引列里，符合覆盖索引的条件
最后页出现了Using index，说明的确是触发了覆盖索引

普通索引

删除复合索引，建立idx_phone索引

ALTER TABLE `cdqypt`.`my_test` DROP INDEX `idx_phone_name_age`; 
ALTER TABLE `cdqypt`.`my_test` ADD KEY `idx_phone` (`user_phone`); 

EXPLAIN SELECT `user_name`, `user_phone`, `user_age` FROM `my_test` WHERE user_phone ='17777777777'

我们在where后面跟上索引发现能成功的触发索引，但注意如何发生了隐式的类型装换，那么就会导致索引失效

EXPLAIN SELECT `user_name`, `user_phone`, `user_age` FROM `my_test` WHERE user_phone =17777777777

我们发现type变成了null，key也是null了，是因为我们的user_phone是varchar类型，我们where的时候是int类型的177…，这时候mysql会去执行类型装换导致索引失效

唯一索引

删除普通索引，创建唯一索引

ALTER TABLE `cdqypt`.`my_test` DROP INDEX `idx_phone`; 
ALTER TABLE `cdqypt`.`my_test` ADD UNIQUE INDEX `idx_user_phone` (`user_phone`); 

查看表数据，这时候我们再去插入一个user_phone=17777777777的用户试试看

INSERT INTO `my_test` ( `user_name`, `user_phone`, `user_age`, `grade` ) VALUES ( '小米', '17777777777', 18, '高三' ); 

我们发现这是时报错了，是因为user_phone字段已经有17777777777这个号码了，因为得唯一所以就没办法再插入了，我们可以试试其他字段

INSERT INTO `my_test` ( `user_name`, `user_phone`, `user_age`, `grade` ) VALUES ( '小吴', '17777777778', 18, '高三' );

这是我们再来一个小吴

很轻松的就插入成功了，同时也有2个小吴同学了

在许多场合，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。