其实哪些情况下索引会失效的问题并不复杂，但是又很多的朋友都不太了解mysql为什么不建议类型索引，因此呢，今天小编就来为大家分享哪些情况下索引会失效的一些知识，希望可以帮助到大家，下面我们一起来看看这个问题的分析吧！

本文目录

1. mysql索引文件格式
2. 哪些情况下索引会失效
3. Mysql中明明创建了a字段的索引，为什么执行Sql时，却没有使用a索引进行查询，失效了
4. MySQL是如何利用索引的

mysql索引文件格式

mysql索引类型normal，unique，fulltext

normal：表示普通索引

unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

fulltextl:表示全文搜索的索引。FULLTEXT用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的INDEX也可以。

总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

MySQL目前主要有以下几种索引方法：B-Tree，Hash，R-Tree。

哪些情况下索引会失效

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：如果条件中有or，即使其中有条件带索引也不会使用(2.对于多列索引，不是使用的第一部分，则不会使用索引3.like查询是以%开头4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引此外，查看索引的使用情况showstatuslike‘Handler_read%’;大家可以注意：

Mysql中明明创建了a字段的索引，为什么执行Sql时，却没有使用a索引进行查询，失效了

在MySQL数据库中，的确存在一些场景会导致存储引擎放弃使用索引而进行全表扫描，下面我们将从索引失效的原因以及如何避免索引失效两个方面回答这个问题，希望对您有所帮助。

MySQL中一条查询SQL是如何被执行的？

如上图，我们可以看到一条Mysql查询语句，从被客户端下发到调用存储引擎读取数据，返回结果，经历了连接器、分析器、优化器、执行器。我们以下面SQL为例，简单说明下在各个环节中分别做了哪些事情。

如上SQL，实现了查询Score表中学号为9527同学的Math(数学)成绩，下面我们分析下这个语句的执行流程：

step1连接器：首先会检查该该查询SQL语句是否有权限，如果无权限，则直接返回错误信息，如果有权限，在MySQL8.0版本之前，会先查询缓存，以这条SQL语句作为KEY在内存中进行查询，如果有结果则直接将历史查询结果返回，如果没有，执行下一步。

step2分析器：在分析器中会进行词法分析与语法分析，通过分析器词法分析，提取SQL语句中关键字，比如，提取SQL语句中的SELECT、WHERE，提取查询的表名是CourseInfo，提取查询的字段是StudentId、Score，提取查询条件是Course等于'Math'且StudentId等于9527。

然后再通过再语法分析判断在该SQL语句是否有语法错误，比如，关键词是否正确、StudentId、Score字段是否存在于CourseInfo表中等等，若检查通过，则继续执行下一步。

step3优化器：优化器会通过自己的分析算法确认执行方案，上面的SQL语句，有两种执行方案，如下：

方案一：首先，查询课程是Math的所有学生的成绩。然后，再查询其学号是9527的成绩。方案二：首先，查询学号是9527的所有科目的成绩。然后，再查询其科目是Math的成绩。

因此，优化器会根据它的优化算法分析它所认为执行效率最高的一个方案（优化器认为不一定是最好。同时如果如优化器分析使用索引扫描比全表扫描效率低时，会放弃使用索引而选择全表扫描，一般数据量极少时，可能不会走索引）。

step4执行器：首先，进行权限校验，如果没有权限则会返回错误信息，如果有权限就会调用数据库存储引擎的查询接口，返回引擎的查询结果。

放弃使用索引而选择全表扫描

除了上面提到的当优化器分析使用索引扫描比全表扫描效率低时，优化器会放弃使用索引而选择全表扫描，还有哪些原因会导致放弃索引而选择全表扫描呢？

因索引失效，导致全表扫描的可能原因有以下几点：

WHERE子句中对索引列进行计算、函数、类型转换等操作。WHERE子句中对索引列使用不等于，如!=或者<>。WHERE子句中对索引列使用ISNULL，ISNOTNULL。WHERE子句中对索引列使用模糊查询LIKE并以通配符开头如，%ab。WHERE子句中对索引列使用OR来连接条件。WHERE子句中对索引列使用IN和NOTIN。WHERE子句中对索引列使用隐式类型转换，如字段age类型为int，WHERE条件中却使用varchar类型，如，age='17'。复合索引未遵循最佳左前缀原则或者存在断点。索引被禁用，开启索使用ALTERTABLETESTOPSENABLEKEYS。如何避免索引失效

避免在WHERE子句中使用!=或<>不等于操作符

在WHERE子句中使用!=或者<>操作符，将导致引擎放弃使用索引而进行全表扫描。MySQL仅有对以下操作符才会使用索引：<，>，<=，>=，=，BETWEEN，IN，以及使用LIKE时的后缀模糊查询%。

避免在WHERE子句中对索引列使用%前缀模糊查询

WHERE子句中使用%前缀模糊查询，将导致引擎放弃使用索引而进行全表扫描。解决%前缀模糊查询时索引失效的方法是添加覆盖索引（仅访问索引的查询，查询列都是索引，只需扫描索引而无须回表）。

避免在WHERE子句中对索引列使用OR来连接条件

在WHERE子句中使用OR来连接条件，将导致引擎放弃使用索引而进行全表扫描。使用OR的字句可以分解为多个查询，并且通过UNION连接多个查询的结果。他们的速度只同是否使用索引有关，若查询需要时能够用到复合索引，使用UNIONALL执行的效率更高。

我们在实际SQL设计时尽量UNIONALL代替UNION，UNION和UNIONALL的区别主要是UNION需要将结果集合并后并进行唯一性过滤操作，涉及到排序，产生大量的CPU运算，增加资源消耗及延迟。当然，使用UNIONALL的前提条件是两个结果集没有重复数据，或对是否存在重复数据无要求。

避免在WHERE子句中对索引列使用IN和NOTIN

在WHERE子句中使用IN和NOTIN，将导致引擎放弃使用索引而进行全表扫描。在SQL设计时对于连续的数值，可以使用BETWEEN…AND…尽量避免使用IN。除此之外，一般可使用EXISTS代替IN。若需要使用IN，在IN后面值的列表中，应按照值的分布数量降序排列，以减少判断的次数。

尝试使用BETWEENAND替换IN，示例如下。

我们使用EXISTS来替代IN，用NOTEXISTS来替代NOTIN，无论哪种情况NOTIN效率都是最低的。

除此之外，我们可以尝试使用LEFTJOIN替换IN。

避免在WHERE子句中对索引列使用计算、函数、类型转换等操作

在WHERE子句中对“=”左边的字段进行函数、算术运算及其他表达式运算，将导致引擎放弃使用索引而进行全表扫描，可以将表达式运算移至“=”右边。

避免在WHERE子句中对索引列进行NULL值判断

在WHERE子句中对字段进行NULL值判断，将导致引擎放弃使用索引而进行全表扫描。创建表时NULL是默认值，但大多数时候应该使用NOTNULL，或者使用一个默认值，如使用0作为默认值。

例如，性别字段，使用1表示男，2表示女，0表示未知，或者是当用户没有选择，默认值设置为0（大部分编程语言的数字类型的默认值0）。

如果字段允许为空，可能会有以下问题：查询条件中必须处理为空的情况，否则将会出现一些很奇怪的问题，比如NOTIN、!=等负向条件查询在有NULL值的时候返回永远为空结果，查询容易易出错。在部分数据库中将导致索引失效。可空列需要更多存储空间，导致空间变大。

凡事没有绝对的，使用默认值的思路一定程度可以解决很大一部分可为空的问题，但不是所有的都需这样做，具体还是需要根据具体业务进行分析。

避免在WHERE子句中对索引列进行隐式类型转换

WHERE子句中对索引列进行隐式类型转换（条件中字段赋值与字段定义类型不匹配），将导致引擎放弃使用索引而进行全表扫描。当我们对不同类型的值进行比较的时候，为了使得这些数值可比较，MySQL数据库会做一些隐式转化（Implicittypeconversion）。

SQL查询语句的条件中字段赋值与字段定义类型不匹配是一种常见的错误用法。

如上，字段account字段的定义为varchar类型，而在WHERE条件中account字段值是数字型，数据类型不匹配，此时是没法直接进行比较的，需要进行类型转换。MySQL的策略是将表中account字段全部转换为数字型之后再比较，因此引发函数作用于字段，使得索引失效，导致全表扫描，正确的写法如下：

MySQL是如何利用索引的

什么是索引？

索引是数据库快速找到记录行的一种数据结构，类似我们看书时的目录，它是良好性能的关键因素。尤其是表中的数据量越来越大时，如果索引使用不当，会严重影响性能。索引也是最常见的数据库优化手段，它能轻易的将查询性能提高好几个量级。

MySQL索引类型？

mysql索引数据是存储在存储引擎中的，所以不同存储引擎中索引的工作方式并不一样。

B-Tree索引：基于B+树（一种多叉搜索数树）来实现的索引类型，一般也是使用的最多的索引类型，之所以选择B+树而不是其他数据结构，是因为B+树在查询时间复杂度可以维持在O(logn)的级别上，由于B+的矮胖（从根节点到叶子节点的距离可以维持在较小范围）特性减少磁盘IO次数、数据只存在叶子节点中并且按顺序存储也可以支持快速的范围查询，这是其他结构无法满足的！

B+索引中值是按顺序存储的，叶子节点到根节点的距离都相同，从B+树的根节点开始往下查找，节点存储了指向叶子节点的指针，通过将要查找的值和每个节点值比较后，一层层定位到最终的叶子结点上，叶子节点存储的就是行数据、指针或主键。

假如我们索引列是：

key（lastname（姓）,firstname（名）,born），可以使用B+树索引的查询类型包括：全键值、键值范围、键前缀查找，其中键前缀只适用于最左前缀查找：

全值匹配：指的是和索引中所有的列进行匹配，如可以找到姓名为：Cuba（名）Allen(姓)、生于1988-10-04的人，如wherelastname=‘Allen’andfirstname=‘Cuba’andborn='1988-10-04'匹配最左前缀：可以查找姓为Allen的人，如wherelastname=‘Allen’匹配列前缀：也可以匹配某一列的值的开头部分，如wherelastnamelike‘A%’或者wherefirstnamelike‘M%’匹配范围：可以匹配姓在Allen和Bill之间的人精确匹配某一列并匹配另外一列：查找所有姓为Allen、并且名字是以M开头的人，如wherelastname=‘Allen’andfirstnamelike‘M%’访问索引数据：这种查询只需要访问索引本身就行了，不需要访问数据行，也就是常说的索引覆盖，举个例子：如果只需要找到姓为Allen的人的名称，而不需要这个人其他的信息，名称就存在与索引中，不需要再去数据行中查找数据了。

这里需要注意的是叶子节点存什么类型数据不同的存储引擎还不一样，在MyISAM中叶子节点存储的是数据物理位置（指针），而InnoDB使用B+结构存储的是原始数据或主键，也就是我们常说的聚簇索引，它存储的是原始全量数据、键值，聚簇索引指的是一种数据索引组织形式，它将数据和索引聚集在一起所以叫聚簇，它本身并不是一种索引类型。

一般InnoDB查找过程为从辅助索引上开始查找到数据主键，然后在主键索引中用主键再次查找，最后再找到数据，虽然多了一次查找过程，但更新数据不会导致聚簇索引频繁变化。而在MyISAM中不需要2次索引查找，因为叶子节点存储的是数据的物理地址可以直接定位，虽然查询看似简单了，但是物理地址会因为数据频繁变更而发生变化。

假设有以下数据：

InnoDB（聚簇索引）数据查找过程：

MyISAM（非聚簇索引结构）数据查找过程：

哈希索引：基于哈希表来实现的索引类型，如果存在哈希冲突，索引会使用链表来存放多个记录到一个哈希桶中。举个例子：如果存在以下索引keyUSINGHASH（firstname），哈希索引会使用哈希函数计算出firstname列的哈希值作为key，并将行指针作为value存储，当使用=、IN（）、<=>操作时，先计算出sql语句操作查找值的哈希值，并使用其来查找哈希表对应的行指针，从而返回数据。

这里需要注意是：

哈希索引只存储哈希值和行指针，索引索引本身没有行数据，也就没有所谓的索引覆盖。哈希索引没有按哈希值的顺序排列，所有不支持排序操作。不支持部分索引列的匹配，哈希索引使用你指定的全部列来计算哈希值，列入（A，B）如果查询只有列A，则索引无发匹配。哈希索引只支持等值比较（=、in(),<=>）。哈希冲突较高时，查找效率就变成了链表，复杂度从O（1）变为O（n）。

空间数据索引：MyISAM支持空间索引可以用来存储地理数据。必须使用GIS相关函数如MBRCONUNTAINS（）来维护数据，因为本身mysql对GIS的支持下不完善，这中特性使用很少。

全文索引：这是一种特殊类型的索引，他查找的是索引列中文本的关键词，而不是比较索引值，全文索引的使用要注意列的文本大小和数据量，它的匹配方式类似于搜索引擎。

索引的优缺点？大大减少了服务器扫描表的数据量。避免不必要的排序和临时表。将随机IO变为顺序IO。对于非常小的表，全表扫描可能比索引更快，对于中型数据量表，索引将会非常有效，对于TB级别的表来说，索引的维护和效果可能没有我们想象的那样好，这是可以使用表分区、业务拆分表和分库等技术。常见的索引优化方式及注意事项？不要把索引的列纳入表达式，也不能是函数参数，如whereaid+1=5、whereto_days（col）<=10.选择重复性较低的列建索引，重复性较高会导致索引失效，全表扫描。多列索引中很多常见的错误是，喜欢为每个列创建独立索引，实际上这是错误的！要选择合适的顺序和列来合并索引，来看个简单例子：

表数据为：

分别建2个独立索引：inx_name,inx_company：

现在执行以下语句：

SELECT*fromtuserwhere`name`='22'orcompany='bb'

结果显示并没有使用索引来查询数据：

现在加一个多列索引：inx_name_company

执行同样的sql显示使用了多列索引：

不要在大文本字段建全量索引，这会然导致索引数据较大，查询较慢，可以建一个前缀索引，例如

//在city列上取前7个字符作为索引mysql>altertabledemoaddkey(city(7))

这是一种使索引更小，更快的方法，但缺点是无法使用缀索引orderby或groupby

关于哪些情况下索引会失效，mysql为什么不建议类型索引的介绍到此结束，希望对大家有所帮助。