导致MySQL索引失效的几种常见写法

最近忙着处理一些原来老项目遗留下来的SQL优化问题。由于原有的表设计和字段设计问题，随着业务的增长，出现了大量的慢SQL，导致MySQL的CPU资源飙升。基于此，我想把这些比较的易学易用的心得分享给大家。这一次，让我们简单谈谈如何防止索引失败。另外，我根据我最近的经历谈谈我对索引的看法。我不认为所有的表都需要索引。对于一些业务数据，可能量比较大，查询数据已经有点压力了。那么最简单快捷的方法就是建立一个合适的索引，但是有些业务可能表中的数据不多，或者表的使用频率不高，所以没有必要做索引。就像我们有些表，2年可能有10条左右的数据，加索引和不加索引性能差不多。索引只是我们优化业务的一种方式。我们必须为了建索引而建索引。下面是我在本次测试中使用的表结构和一些测试数据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`(`a??ddress`)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`冰峰`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(2,'2020-09-0210:17:47','pine',13,'female','1',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`地址`，`高度`)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,'男','我可以存几个中国人charactersinmytest',NULL,'180');INSERTINTO`bingfeng`.`user`(`id`,`create_time`,`name`,`age`,`sex`,`mobile`,`address`,`height`)VALUES(256,'2020-09-0310:17:47','Bingfeng',21,'Female','',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`,`性别`,`手机`,`地址`,`身高`)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自增。涉及到用户的account表的user_id字段，必须是int类型，不能写成varchar或者char。SELECT*FROM`user`WHEREheight=175;这个SQL你一定看清楚了。height表的字段类型是varchar，但是我查询的时候使用的是numeric类型，因为中间有一个隐式的类型转换，所以会导致索引无效，进行全表扫描。现在我明白为什么我说在设计字段时必须保持类型一致性了。不行，如果不保证一致性，一个int和一个varchar在进行多表联合查询（eg:1='1'）索引时必然会失败。遇到这样的表，里面有几千万的数据，而且是改不了的。您可能暂时仍会感到疼痛。孩子们，记住，记住。3、SELECT*FROM`user`WHEREDATE(create_time)='2020-09-03'函数导致的索引失败；如果你的索引字段使用了索引，对不起，他真的没有使用索引。4.操作符SELECT*FROM`user`WHEREage-1=20导致的索引失效；如果你在列上执行(+,-,*,/,!)，那么索引将不会被获取。5.ORSELECT*FROM`user`WHERE`name`='张三'ORheight='175'导致索引失效；OR导致索引在特定情况下，并不是所有的OR都会使索引失效，如果OR连接到同一个字段，那么索引就不会失效，否则索引就会失效。6、模糊搜索导致索引失效SELECT*FROM`user`WHERE`name`LIKE'%bing';这个相信大家都明白，模糊搜索如果你对前缀也进行模糊搜索，那么索引就用不上了。7.NOTIN,NOTEXISTS导致索引失败`WHERE`name`NOTIN('冰峰');这两种用法也会使索引失效。但是NOTIN仍然被索引。不要误解所有的IN都没有被索引。我误解了（羞耻......）。8、ISNULL不走索引，ISNOTNULL走索引，SE??LECT*FROM`user`WHEREaddressISNULL不走索引。SELECT*FROM`user`WHEREAddressISNOTNULL;去索引。根据这种情况，建议在设计字段的时候，如果没有必要要求必须为NULL，那么最好给一个空字符串的默认值，这样可以解决很多后续的麻烦（有深刻的体验)。符合索引1，最左匹配原则EXPLAINSELECT*FROM`user`WHEREsex='male';EXPLAINSELECT*FROM`user`WHEREname='Bingfeng'ANDsex='male';测试前先删除其他单列索引。什么是最左匹配原则？对于匹配索引，一个索引的顺序是从左到右比较的。和第二条查询语句一样，name去索引，然后回去找age。结果条件中如果没有年龄，后面的性别就不会被索引。注：SELECT*FROM`user`WHEREsex='Male'ANDage=22AND`name`='Bingfeng';可能一开始有搬砖的人误会了，我们的索引顺序明明是name,sex,age，你现在的查询顺序是sex,age,name，肯定是没有索引的。如果你没有亲身试验过，有如此不成熟的想法，那你还是像我一样太年轻了。它实际上与顺序无关。关系型的，因为mysql底层会帮我们做一个优化，它会优化你的SQL，按照它认为最高效的方式去执行。所以不要有这个误会。2、如果使用!=，后面的所有索引都会失效SELECT*FROM`user`WHEREsex='male'AND`name`!='Bingfeng'ANDage=22;它是最左边的字段。根据最左匹配原则，如果name没有被索引，后面的字段也不会被索引。关于索引导致的索引失效，只能说两点。其实我觉得对于索引来说，最重要的是如何建立一个高效的索引。你一定不要说我用那个字段的时候会单独创建一个字段。该索引可以在全球范围内使用。这是可以的，但这不符合索引的高效性，所以要想成为高级搬砖人，我们还是需要继续学习如何创建高效的索引。