背景最近生产中爆发了一个慢sql。原因是使用了or和!=，导致索引失败。所以，我总结了索引失效的十大杂病，希望对大家有所帮助，加油。1、查询条件中包含or，可能会导致索引失效。新建一个用户表，有一个普通索引userId，结构如下：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`int(11)NOTNULL,`age`int(11)NOTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_userId`(`userId`))ENGINE=InnoDBDEFAULTCHARSET=utf8;分析&结论：对于没有索引的or+age这种情况下，假设它使用了userId索引，但是当它到达age查询条件时，仍然需要扫描全表，即需要一个三步过程：fulltablescan+indexscan+merge如果是从全表Scan开始，扫描一次就完了。Mysql有一个优化器。考虑到效率和成本，遇到or条件时让索引失效似乎是合理的。注意：如果or条件的列都被索引了，索引可能会消失。你可以自己试试。2.如果字段类型是字符串，where必须用引号括起来，否则索引失败。假设demo表结构如下：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`varchar(32)NOTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_userId`(`userId`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;userId是string类型，是B+树的普通索引，如果查询条件传过去一个数字，则不使用索引，如图：如果在数字后面加上''，即,传一个字符串，当然是索引了，如下图：分析结论：为什么第一个语句不加单引号，就不会去索引？这是因为不加单引号时，是字符串和数字的比较，类型不匹配。MySQL会进行隐式类型转换，将它们转换为浮点数进行比较。3.like通配符可能会导致索引失败。并不是说使用like通配符就一定索引失败，而是like查询以%开头，会导致索引失败。表结构：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`varchar(32)NOTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_userId`(`userId`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;like查询以%开头，索引无效，如图：把%放在后面，发现索引还在正常运行，如下：把%加回来，改成只检查索引字段（Coveringindex），发现还是用了索引，是不是很意外，结论：like查询以%开头，会导致索引失效。优化的方式有两种：使用覆盖索引Put%behind：索引包含所有满足查询要求的数据，称为覆盖索引（CoveringIndex）。四、联合索引，查询中的条件列不是联合索引中的第一列，索引无效。表结构：（有一个联合索引idx_userid_age，userId在前，age在后）CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`int(11)NOTNULL,`age`int(11)DEFAULTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_userid_age`(`userId`,`age`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;联合索引中，查询条件满足最左匹配原则上索引正常生效。请看demo：如果条件列不是联合索引的第一列，索引就会失败，如下：分析与结论：当我们创建联合索引时，比如(k1,k2,k3)，它是相当于创建(k1),(k1,k2),(k1,k2,k3)三个索引，这就是最左匹配原则。联合索引不满足最左原则，索引一般会失效，但这也和Mysql优化器有关。5、如果在索引列上使用mysql的内置函数，会导致索引失效。表结构：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`varchar(32)NOTNULL,`loginTime`datetimeNOTNULL,PRIMARYKEY(`id`),KEY`idx_userId`(`userId`)USINGBTREE,KEY`idx_login_time`(`loginTime`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;loginTime虽然有索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接GG了，如图：6.对索引列的操作（例如+、-、*、/),索引无效。表结构：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`varchar(32)NOTNULL,`age`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_age`(`age`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;age虽然有索引，但是因为它的运行索引丢失了。..山河疑惑无路可走，算计得我脑袋疼，指数真不知道路。如图：7、在索引字段上使用(!=or<>,notin)时，可能会导致索引失败。表结构：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`userId`int(11)NOTNULL,`age`int(11)DEFAULTNULL,`name`varchar(255)NOTNULL,PRIMARYKEY(`id`),KEY`idx_age`(`age`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;年龄虽然有索引，但它被使用了！=或<>，不在，索引就像是假的。如下：8.在索引字段上使用isnull和isnotnull可能会导致索引失败。表结构：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`card`varchar(255)DEFAULTNULL,`name`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE,KEY`idx_card`(`card`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;给单名字段加索引，查询name不为空的statement，其实就是会用到索引，如下：给单card字段加索引，查询name不为空的statement，其实也会去索引，如下：但是如果用or连接，索引就会失效，如下：9.左连接查询或右连接查询查询关联的字段编码格式不一样,可能导致索引失败。新建两张表，一张user，一张user_jobCREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)CHARACTERSETutf8mb4DEFAULTNULL,`age`int(11)NOTNULL,PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;CREATETABLE`user_job`(`id`int(11)NOTNULL,`userId`int(11)NOTNULL,`job`varchar(255)DEFAULTNULL,`name`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8;user表的name字段的编码是utf8mb4，而user_job表的name字段的编码是utf8。执行左外连接查询，user_job表依然采用全表扫描，如下：如果改成name字段编码相同，依然会一路高歌，得意洋洋，去索引。10、mysql估计使用全表扫描比使用索引快，所以没有使用索引。当查询表的索引时，将使用最佳索引，除非优化器使用全表扫描更有效。优化器根据使用最佳索引检索到的数据是否超过表中数据的30%来优化全表扫描。不要为'性别'等添加索引。如果一个数据列包含“0/1”或“Y/N”等值，即包含很多重复值，即使为其建立了索引，索引效果不会很好，还可能造成全表扫描。为了效率和成本，Mysql估计是全表扫描还是索引更快。这和它的优化器有关。我们来看看它的逻辑架构图（图片来自网络）。总结了指标失效的十大杂病。让我们在这里回显开始和结束并分析我们产生的慢SQL。.模拟表结构和sql如下：CREATETABLE`user_session`(`user_id`varchar(32)CHARACTERSETutf8mb4NOTNULL,`device_id`varchar(64)NOTNULL,`status`varchar(2)NOTNULL,`create_time`datetimeNOTNULL,`update_time`datetimeDEFAULTNULLONUPDATECUPRENT_,PRIMARYKEY(`user_id`,`device_id`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8;explainupdateuser_sessionsetstatus=1where(`user_id`='1'and`device_id`!='2')or(`user_id`!='1'and`device_id`='2')分析：执行的sql使用了or条件，因为复合主键（user_id,device_id）貌似每一列都有索引，索引才会生效。但是，!=的出现可能会导致索引失败。也就是or+!=两大综合症，导致sql更新慢。解决方案：那么，如何解决呢？我们去掉or条件，分成两次执行。同时给device_id增加一个公共索引。最后总结了指标失效的十大杂病。希望大家在工作学习中能够参考这十种杂病，多点结合执行计划阐述和场景进行详细分析，而不是循序渐进，墨守成规，确定哪个场景必须索引无效。ETC。
