MySQL索引失效问题详解

MySQL5.7

创建数据表user：

CREATE TABLE `user` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `name` varchar(5) NOT NULL,
  `age` tinyint(2) unsigned zerofill NOT NULL,
  `sex` char(1) NOT NULL,
  `mobile` char(12) NOT NULL DEFAULT '',
  `address` char(120) DEFAULT NULL,
  `height` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_createtime` (`create_time`) USING BTREE,
  KEY `idx_name_age_sex` (`name`,`sex`,`age`) USING BTREE,
  KEY `idx_ height` (`height`) USING BTREE,
  KEY `idx_address` (`address`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;

 准备数据：

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (1, '2019-09-02 10:17:47', '冰峰', 22, '男', '1', '陕西省咸阳市彬县', '175');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (2, '2020-09-02 10:17:47', '松子', 13, '女', '1', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (3, '2020-09-02 10:17:48', '蚕豆', 20, '女', '1', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (4, '2020-09-02 10:17:47', '冰峰', 20, '男', '17765010977', '陕西省西安市', '155');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (255, '2020-09-02 10:17:47', '竹笋', 22, '男', '我测试下可以储存几个中文', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (256, '2020-09-03 10:17:47', '冰峰', 21, '女', '', NULL, '167');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (257, '2020-09-02 10:17:47', '小红', 20, '', '', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (258, '2020-09-02 10:17:47', '小鹏', 20, '', '', NULL, '188');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (259, '2020-09-02 10:17:47', '张三', 20, '', '', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (260, '2020-09-02 10:17:47', '李四', 22, '', '', NULL, '165');

 

1、使用!= 或者 <> 导致索引失效

SELECT * FROM `user` WHERE `name` != '冰峰';

尽管给name字段建立了索引，但是用了 !=  或者<> 都会导致索引失效，进行全表扫描，请慎用

 如上图sql解释可以看出，type类型是ALL,扫描了10行数据，进行了全表扫描。（<> 也是同样的结果）

2、字段类型不一致导致的索引失效

SELECT * FROM `user` WHERE height= 175;

尽管给height建立了索引，height表字段类型是varchar，但是查询时使用了数字类型，中间会存在一个隐式的类型转换，就会导致索引失效，从而进行全表扫描。

因此，设计字段时候，一定要保证类型的一致性，避免多表联合查询情况下（eg:1 = '1' ）必然不走索引。

3、函数导致的索引失效

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

尽管给字段create_time建立了索引，在where条件上对该字段使用函数也同样是不走索引的。

4、运算符导致的索引失效

SELECT * FROM `user` WHERE age - 1 = 20;

尽管对字段age建立了索引，如果对该列进行了（+，-，*，/，！）运算，那都是不走索引的。

5、OR引起的索引失效

SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

OR导致的索引失效得根据情况，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效。

此处应该是走了索引，有待于进一步验证。

6、模糊搜索导致的索引失效

SELECT * FROM `user` WHERE `name` LIKE '%冰';

like 模糊搜索模糊搜索，那么不会走索引。

7、NOT IN、NOT EXISTS导致索引失效

SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = '冰峰')

SELECT * FROM `user` WHERE `name` NOT IN ('冰峰');

8、is null 不走索引，is not null 走索引

不走索引： 

SELECT * FROM `user` WHERE address IS NULL

走索引： 

SELECT * FROM `user` WHERE address IS NOT NULL;

建议大家设计字段的时候若没有特别说明必须为NULL，那么最好给个默认值空字符串，避免后续的麻烦。