欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-01-21 10:42:46
...

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`

mysql复合索引,覆盖索引,普通索引,唯一索引
我们发现type是all,key也是null

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

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

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

mysql复合索引,覆盖索引,普通索引,唯一索引
虽然是select 查询全部用上了索引字段,但是where条件并没有使用索引字段,导致查询type还是all

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

mysql复合索引,覆盖索引,普通索引,唯一索引
虽然复合索引的左边是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'

mysql复合索引,覆盖索引,普通索引,唯一索引我们在where后面跟上索引发现能成功的触发索引,但注意如何发生了隐式的类型装换,那么就会导致索引失效

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

mysql复合索引,覆盖索引,普通索引,唯一索引
我们发现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`); 

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

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

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

mysql复合索引,覆盖索引,普通索引,唯一索引
我们发现这是时报错了,是因为user_phone字段已经有17777777777这个号码了,因为得唯一所以就没办法再插入了,我们可以试试其他字段

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

这是我们再来一个小吴
mysql复合索引,覆盖索引,普通索引,唯一索引
很轻松的就插入成功了,同时也有2个小吴同学了
mysql复合索引,覆盖索引,普通索引,唯一索引

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