Mysql索引失效的情况

一、前提条件 

1、创建三张测试表：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(25) DEFAULT NULL,
 `age` int(11)  NOT NULL DEFAULT 0,
 `update_time` datetime DEFAULT NULL,
 `first_name` varchar(25) DEFAULT NULL,
 `last_name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `age`,`update_time` ,`first_name` ,`last_name`) VALUES (1,'张三',23,'2020-12-22 15:27:18','张','三'), (2,'李四',24,'2020-06-21 15:27:18','李','四'), (3,'王五',25,'2020-07-20 15:27:18','王','五');

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role` (`id`, `name`) VALUES (1,'产品经理'),(2,'技术经理'),(3,'项目总监');

DROP TABLE IF EXISTS `role_user`;
CREATE TABLE `role_user` (
 `id` int(11) NOT NULL,
 `role_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_role_user_id` (`role_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role_user` (`id`, `role_id`, `user_id`) VALUES (1,2,1),(2,1,2),(3,3,3);

2、创建对应的索引：

mysql> ALTER TABLE user ADD INDEX index_name (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE user ADD INDEX index_age (age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user add index index_first_last_name (first_name,last_name);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

二、索引失效情况

• 最常说的like匹配;
• 强制类型转换会让索引失效;
• 最左匹配原则（组合索引按照建立的顺序，如果前一个匹配不上就会导致后一个命中失效）;
• WHERE 条件中使用or即使有索引也会失效;

1、最常说的like匹配

例1 explain select * from user where name like '%张';

例2 explain select * from user where name like '张%';

【结论：】例1索引失效 ，例2索引生效（匹配列前缀 '张%'）

2. 强制类型转换会让索引失效

首先明确一点mysql优化器自动进行强制类型转换；也就是下面这两个sql都能查询到结果。

select * from user where name = '6'; 

select * from user where name = 6;

例1：explain select * from user where name = '6';

例2：explain select * from user where name = 6;

【 结论：】因为name在数据库的类型为：varchar，例2当mysql优化器自动进行强制类型转换时，索引失效；

 3.最左匹配原则

例1： explain select * from user where first_name = '王' and last_name = '五'; 

例2： explain select * from user where last_name = '五' and first_name = '王';

例3 ：explain select * from user where first_name = '王';

例4 ：explain select * from user where last_name = '五';

【结论：】当建立一个组合索引时，根据例1和例2 得出 组合索引 where条件后面的排放顺序不会影响索引的命中；

                  根据例3 例4 得出如果组合索引中有部分字段缺失（因为建立索引的顺序是first_name ,last_name），按照建索引的顺序从左到右，依次匹配如果前一个                    列不匹配，则后一个列索引失效。

 4. where条件中使用or即使有索引也会失效

例1：explain select * from user where name = '张三' or update_time = '2020-07-20 15:27:18';

例2：explain select * from user where update_time = '2020-07-20 15:27:18' or  name = '张三';

例3：explain select * from user where name = '张三' or age = 25;

 【结论：】通过例1和例2可以得出如果条件中or的两边有一个没有建立索引就会导致索引失效；通过例3可以到，如果or两边字段都建立了索引，索引不会失效。

 以上只是个人的一些简单总结 ，如果有什么问题希望指出，如果有什么问题可以一起讨论。