背??景无论你是技术达人还是业内新手，都会时不时的踩到Mysql数据库的坑，没有建立索引。一个普遍的现象是明明给字段加了索引，却没有生效。几天前，我遇到了一个稍微特殊的场景。同一条SQL语句在一定参数下生效，在一定参数下不生效。为什么？另外，无论是面试还是日常生活，Mysql索引失败的事情都是应该知道和学习的。为了便于学习和记忆，本文总结了15种不使用索引的常见情况，并举例说明，帮助大家更好的避免踩坑。建议收藏起来以备不时之需。数据库和索引准备创建表结构为了逐项验证索引的使用情况，我们先准备一张表t_user：CREATETABLE`t_user`(`id`int(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID',`id_no`varchar(18)CHARACTERSETutf8mb4COLLATEutf8mb4_binDEFAULTNULLCOMMENT'identitynumber',`username`varchar(32)CHARACTERSETutf8mb4COLLATEutf8mb4_binDEFAULTNULLCOMMENT'username',`age`int(11)DEFAULTNULLCOMMENT'age',`create_time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT'createtime',PRIMARYKEY(`id`),KEY`union_idx`(`id_no`,`username`,`age`),KEY`create_time_idx`(`create_time`))引擎=InnoDB默认字符集=utf8mb4整理=utf8mb4_bin；上面的表结构中有3个索引：id：数据库的主键；union_idx：由id_no、username、age组成的联合索引；create_time_idx：由create_time组成的普通索引；初始化数据初始化数据分为两部分：基础数据和批量导入数据。基础数据插入4条数据，第4条数据的创建时间为未来时间，用于后续特殊场景验证：INSERTINTO`t_user`(`id`,`id_no`,`username`,`age`,`create_time`)VALUES(null,'1001','Tom1',11,'2022-02-2709:04:23');INSERTINTO`t_user`(`id`,`id_no`,`username`,`age`,`create_time`)VALUES(null,'1002','Tom2',12,'2022-02-2609:04:23');INSERTINTO`t_user`(`id`,`id_no`,`username`,`age`,`create_time`)VALUES(null,'1003','Tom3',13,'2022-02-2509:04:23');INSERTINTO`t_user`(`id`,`id_no`,`username`,`age`,`create_time`)VALUES(null,'1004','Tom4',14,'2023-02-2509:04:23');除了基础数据，还有一个存储过程及其调用的SQL，方便批量插入数据，用于验证数据量大的场景：--删除历史存储过程DROPPROCEDUREIFEXISTS`insert_t_user`--创建存储过程分隔符$CREATEPROCEDUREinsert_t_user(INlimit_numint)BEGINDECLAREiINTDEFAULT10;声明id_novarchar(18)；声明用户名varchar(32)；声明年龄TINYINT默认值1；WHILEi”和“<”，索引也会失败：explainselect*fromt_userwhereid>1orid<80;解释结果：or-range第七种索引失败情况：查询条件使用or关键字，如果其中一个字段没有被索引，则整个查询语句的索引将失效；当or的范围在查询的两边都是“>”和“<”时，索引将无效。8、比较两列如果两列数据都有索引，但是在查询条件中对两列数据进行比较操作，索引就会失效。这里举个不恰当的例子，比如age小于id的两列（真实场景可能是同维度的两列数据对比，这里容纳现有的表结构）：explainselect*fromt_userwhereid>age;解释一下结果：索引-两列比较这里虽然id有索引，age也可以创建索引，但是两列比较时，索引还是会失败。第八种索引失效情况：比较两列数据，即使为两列都创建了索引，索引也会失效。9、不等于比较示例：explainselect*fromt_userwhereid_no<>'1002';可能没有索引，但并非总是如此。解释select*fromt_userwherecreate_time!='2022-02-2709:56:42';上面的SQL中，由于“2022-02-2709:56:42”是存储过程在同一秒生成的，所以这个时间的数据量很大。执行后你会发现，当查询结果集所占比例比较小时，会使用索引，当比例比较大时，则不会使用索引。这与结果集占整体的比例有关。需要注意的是，上面的语句如果是对id的不等操作，会正常走索引。explainselect*fromt_userwhereid!=2;explainresult:unequal-ID第九种索引失效情况：使用不等查询条件进行比较时，需要谨慎。普通索引在查询结果集占比较大时会失效。10.isnotnull例子：解释select*fromt_userwhereid_noisnotnull;解释结果：index-isnotnull第十种索引失败情况：当查询条件使用为null时，索引正常使用，使用isnotnull时，没有Go索引。11、Notin和notexists日常生活中使用比较频繁的范围查询有in、exists、notin、notexists、betweenand等。解释select*fromt_userwhereidin(2,3);解释select*fromt_userwhereid_noin('1001','1002');解释select*fromt_useru1whereexists(select1fromt_useru2whereu2.id=2andu2.id=u1.id);解释select*fromt_userwhereid_nobetween'1002'and'1003';以上4条语句执行时，会正常遍历索引，具体的explain结果不会显示。主要是如果你看不到索引：解释select*fromt_userwhereid_nonotin('1002','1003');解释结果：index-notin使用notin时，不使用索引？用主键替换条件列尝试：explainselect*fromt_userwhereidnotin(2,3);解释结果：primarykey-notin如果是主键，则正常去索引。第十一种索引失效情况：当查询条件使用notin时，如果是主键，则使用索引，如果是普通索引，则索引失效。再来看看notexists:explainselect*fromt_useru1wherenotexists(select1fromt_useru2whereu2.id=2andu2.id=u1.id);explainresult:index-not在当查询条件使用notexists时，不使用索引。第十二种索引失效情况：当查询条件使用notexists时，索引失效。12.orderby导致索引失效示例：explainselect*fromt_userorderbyid_no;explainresult:index-orderby其实本例中的索引失效很好理解，毕竟需要对整张表数据进行排序。那么，增删limit关键字后索引是否走呢？解释select*fromt_userorderbyid_nolimit10;解释一下结果：orderbylimit结果还是没有去索引。在网上看到如果orderbycondition满足最左边的匹配，索引就会正常走，但是现在的8.0.18版本没有出现。所以在使用orderby和limit的时候要特别注意。是否使用索引不仅与数据库版本有关，还取决于Mysql优化器如何处理。这里还有一个特例，就是当主键使用orderby时，索引可以正常使用。解释select*fromt_userorderbyiddesc;说明结果：primarykey-orderby主键可见，或者orderby可以正常走索引。另外，笔者测试了如下SQL语句：explainselectidfromt_userorderbyage；解释selectid,usernamefromt_userorderbyage;解释selectid_nofromt_userorderbyid_no;上面三个SQL语句都建立了索引，也就是说Covering索引场景也可以正常建立索引。现在将id和id_no合并为orderby：explainselect*fromt_userorderbyid,id_nodesc;解释select*fromt_userorderbyid,id_nodesclimit10;解释select*fromt_userorderbyid_nodesc,usernamedesc;解释结果：上面两条带orderby多索引的SQL语句没有建立索引。第十三种索引失效情况：当查询条件涉及orderby、limit等条件时，是否使用索引比较复杂，和Mysql版本有关。通常，如果没有对普通索引使用限制，则不会使用该索引。按多个索引字段排序时，可以不使用索引。其他情况，建议在使用时进行explain验证。13、参数不同导致索引失效这时候，如果你还没有执行一开始创建的存储过程，建议先执行存储过程，再执行如下SQL：explainselect*fromt_userwherecreate_time>'2023-02-2409:04:23';其中，时间是未来的时间，保证能查到数据。解释一下结果：可以看到index-parameters，指标正常。然后，我们将查询条件的参数改为日期：explainselect*fromt_userwherecreate_time>'2022-02-2709:04:23';explainresult:index-parameter此时进行了一次全表扫描。这也是开头提到的奇怪现象。为什么同一条查询语句有不同的查询参数值，但一个使用索引，一个不使用索引？答案很简单：上面提到的索引失败是因为DBMS发现全表扫描比索引效率更高，所以放弃了索引。也就是说，当Mysql发现通过索引扫描的行数超过全表的10%-30%时，优化器可能会放弃索引，自动转为全表扫描。在某些场景下，即使强制SQL语句使用索引，也会失败。在进行范围查询（如>、<、>=、<=、in等）时经常会出现类似的问题，而上面提到的临界值会根据不同的场景而有所不同。第十四种索引失效情况：当查询条件为大于等于、in等范围查询时，优化器可能会根据查询结果在整个表中所占的比例放弃索引，进行全表扫描表数据。14其他当然，是否使用索引还有其他的规则，这也与索引的类型有关，是B树索引还是位图索引，这里就不详细展开了。这里要说的其他的可以总结为第十五条索引失败：Mysql优化器的其他优化策略。比如，优化器认为在某些情况下，全表扫描比索引快，那么就会放弃索引。对于这种情况，一般不需要太在意。发现问题可以定点排查。总结本文为大家总结了15种常见的索引失效场景。由于Mysql版本不同，索引失效策略也不同。大部分索引失效是明确的，少数索引失效会随着Mysql版本的不同而不同。所以建议收藏这篇文章，在实践的过程中对比一下。如果没有办法准确把握，可以直接执行explain进行验证。
