为了验证MySQL中哪些情况会导致索引失效，我们可以使用explain执行计划来分析索引失效的具体场景。explain的使用方式如下，只需在查询SQL前加上explain关键字即可，如下图：在上面查询结果的列中，我们主要观察key列，表示实际使用的索引，如果If为NULL，表示不使用索引，否则使用索引。以上所有结果列说明如下：id——选择标识，id越大，优先级越高，越早执行；select_type—表示查询的类型；table——输出结果集的表；partitions—匹配的分区；type—表示表的连接类型；possible_keys——表示查询时可能使用的索引；key——代表实际使用的索引；key_len——索引字段的长度；ref——列与索引的比较；rows——估计的行数；filtered——按表格条件过滤的行的百分比；Extra——实施的描述和解释。其中最重要的是type字段，type值类型如下：all——扫描全表数据；index—遍历索引；range—索引范围搜索；index_subquery—在子查询中使用ref；unique_subquery—在子查询中使用eq_ref；ref_or_null—用于索引null的优化ref；fulltext—使用全文索引；ref—使用非唯一索引查找数据；eq_ref—在连接查询中使用主键或唯一索引关联；const——在where后面放置一个主键作为条件查询，MySQL优化器可以将这个查询优化转化为一个常量。如何以及何时转换取决于优化器，它比eq_ref效率更高一点。创建测试表和数据为了演示和测试索引在这种情况下会失败，我们首先创建一个测试表和相应的数据：--createatabledroptableifexistsstudent;createtablestudent(idintprimarykeyauto_incrementcomment'主键',snvarchar(32)comment'学号',namevarchar(250)comment'name',ageintcomment'age',sexbitcomment'sex',addressvarchar(250)comment'homeaddress',keyidx_address(address),keyidx_sn_name_age(sn,name,age))ENGINE=InnoDBDEFAULTCHARSET=utf8;--addtestdatainsertintostudent(id,sn,name,age,sex,address)values(1,'cn001','张三',18,1,'高老庄'),(2,'cn002','李四',20,0,'花果山'),(3,'cn003','王5',50,1,'水帘洞');当前表中有3个索引，如下图所示：PS：本文以下内容基于MySQL5.7InnoDB数据引擎。索引失败案例一：非最左匹配最左匹配原则是指从最左边字段开始的查询可以使用联合索引，否则不能使用联合索引。我们这篇文章的联合索引的字段顺序是sn+name+age。我们假设他们的顺序是A+B+C，下面联合索引的用法如下：从上面的结果可以看出，如果从最左边开始匹配所有字段都可以使用上联合索引，这样as：A+B+CA+BA+C其中：A等于字段sn，B等于字段名，C等于字段age。但是B+C不能使用联合索引，即最左匹配原则。索引失败情况2：错误的模糊查询模糊查询常见的三种用法like：模糊匹配后任意字符：like'Zhang%'模糊匹配前任意字符：like'%Zhang'模糊匹配前后任意字符：like'%Zhang'%'这三个模糊查询中，只有第一种查询方式可以使用索引，具体执行结果如下：索引失败案例3：列操作如果索引列使用操作，索引也会失效，如下图所示：索引失效案例4：使用函数查询列如果使用MySQL提供的任何函数，索引都会失效。比如下面几列使用ifnull函数后的执行计划如下：索引失败案例5：类型转换不会去索引，比如address是string类型，int类型的值为查询时设置，会导致索引失败，如下图：索引失败情况6：查询中使用is时useisnotnullNotnull也会导致索引失败，而isnull会触发索引正常，如下图：总结了以下六种常见的导致MySQL索引失败的场景：联合索引不满足最左匹配原则。模糊查询中的第一个字符是一个不确定的匹配字符。索引列参与操作。索引列使用函数。索引列有类型转换。索引列使用非空查询。判断是非在自己，名誉在别人，得失在人数。公众号：Java面试真题分析面试合集：https://gitee.com/mydb/interview
