什么情况下导致mysql索引失效（导致MySQL索引失效的几种常见写法）

推荐学习

• 真真香！耗时大半个月收整全套「Java架构进阶pdf」没白费
• 全网独家的“MySQL高级知识”集合，骨灰级收藏，手慢则无

最近一直忙着处理原来老项目遗留的一些SQL优化问题，由于当初表的设计以及字段设计的问题，随着业务的增长，出现了大量的慢SQL，导致MySQL的CPU资源飙升，基于此，给大家简单分享下这些比较实用的易于学习和使用的经验。

这次的话简单说下如何防止你的索引失效。

再说之前我先根据我最近的经验说下我对索引的看法，我觉得并不是所有的表都需要去建立索引，对于一些业务数据，可能量比较大了，查询数据已经有了一点压力，那么最简单、快速的办法就是建立合适的索引，但是有些业务可能表里就没多少数据，或者表的使用频率非常不高的情况下是没必要必须要去做索引的。就像我们有些表，2年了可能就10来条数据，有索引和没索引性能方面差不多多少。

索引只是我们优化业务的一种方式，千万为了为了建索引而去建索引。

下面是我此次测试使用的一张表结构以及一些测试数据

CREATETABLE`user`( `id`int(5)unsignedNOTNULLAUTO_INCREMENT, `create_time`datetimeNOTNULL, `name`varchar(5)NOTNULL, `age`tinyint(2)unsignedzerofillNOTNULL, `sex`char(1)NOTNULL, `mobile`char(12)NOTNULLDEFAULT'', `address`char(120)DEFAULTNULL, `height`varchar(10)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_createtime`(`create_time`)USINGBTREE, KEY`idx_name_age_sex`(`name`,`sex`,`age`)USINGBTREE, KEY`idx_height`(`height`)USINGBTREE, KEY`idx_address`(`address`)USINGBTREE, KEY`idx_age`(`age`)USINGBTREE )ENGINE=InnoDBAUTO_INCREMENT=261DEFAULTCHARSET=utf8;

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

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

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

我们给name字段建立了索引，但是如果!= 或者 <> 这种都会导致索引失效，进行全表扫描，所以如果数据量大的话，谨慎使用

可以通过分析SQL看到，type类型是ALL，扫描了10行数据，进行了全表扫描。<>也是同样的结果。

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

在说这个之前，一定要说一下设计表字段的时候，千万、一定、必须要保持字段类型的一致性，啥意思？比如user表的id是int自增，到了用户的账户表user_id这个字段，一定、必须也是int类型，千万不要写成varchar、char什么的骚操作。

SELECT*FROM`user`WHEREheight=175;

这个SQL诸位一定要看清楚，height表字段类型是varchar，但是我查询的时候使用了数字类型，因为这个中间存在一个隐式的类型转换，所以就会导致索引失效，进行全表扫描。

现在明白我为啥说设计字段的时候一定要保持类型的一致性了不，如果你不保证一致性，一个int一个varchar，在进行多表联合查询（eg: 1 = '1'）必然走不了索引。

遇到这样的表，里面有几千万数据，改又不能改，那种痛可能你们暂时还体会。

少年们，切记，切记。

3、函数导致的索引失效

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

如果你的索引字段使用了索引，对不起，他是真的不走索引的。

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

SELECT*FROM`user`WHEREage-1=20;

如果你对列进行了（ ，-，*，/，!）, 那么都将不会走索引。

5、OR引起的索引失效

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

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

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

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

这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

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

SELECTs.*FROM`user`sWHERENOTEXISTS(SELECT*FROM`user`uWHEREu.name=s.`name`ANDu.`name`='冰峰')

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

这两种用法，也将使索引失效。但是NOT IN 还是走索引的，千万不要误解为 IN 全部是不走索引的。我之前就有误解（丢人了...）。

8、IS NULL不走索引，IS NOT NULL走索引

SELECT*FROM`user`WHEREaddressISNULL

不走索引。

SELECT*FROM`user`WHEREaddressISNOTNULL;

走索引。

根据这个情况，建议大家在设计字段的时候，如果没有必要的要求必须为NULL，那么最好给个默认值空字符串，这可以解决很多后续的麻烦（有深刻的体验<体验=教训>）。

符合索引1、最左匹配原则

EXPLAINSELECT*FROM`user`WHEREname='冰峰'ANDsex='男';

测试之前，删除其他的单列索引。

啥叫最左匹配原则，就是对于符合索引来说，它的一个索引的顺序是从左往右依次进行比较的，像第二个查询语句，name走索引，接下来回去找age，结果条件中没有age那么后面的sex也将不走索引。

注意：

SELECT*FROM`user`WHEREsex='男'ANDage=22AND`name`='冰峰';

可能有些搬砖工可能跟我最开始有个误解，我们的索引顺序明明是name、sex、age，你现在的查询顺序是sex、age、name，这肯定不走索引啊，你要是自己没测试过，也有这种不成熟的想法，那跟我一样还是太年轻了，它其实跟顺序是没有任何关系的，因为mysql的底层会帮我们做一个优化，它会把你的SQL优化为它认为一个效率最高的样子进行执行。所以千万不要有这种误解。

2、如果使用了!=会导致后面的索引全部失效

SELECT*FROM`user`WHEREsex='男'AND`name`!='冰峰'ANDage=22;

我们在name字段使用了 != ，由于name字段是最左边的一个字段，根据最左匹配原则，如果name不走索引，后面的字段也将不走索引。

关于符合索引导致索引失效的情况能说的目前就这两种，其实我觉得对于符合索引来说，重要的是如何建立高效的索引，千万不能说我用到那个字段我就去建立一个单独的索引，不是就可以全局用了嘛。这样是可以，但是这样并没有符合索引高效，所以为了成为高级的搬砖工，我们还是要继续学习，如何创建高效的索引。

作者：一个程序员的成长

链接：https://juejin.im/post/6869270318282080263

,