脚本宝典收集整理的这篇文章主要介绍了索引优化：如何避免索引失效？，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

索引失效：索引已经创建，建索引相当于给字段进行排序，如按顺序建立了三个索引。而索引失效就是你建立的索引的顺序用不上了，即索引建了但是没用上。

 

 

不让索引失效的SQL就是好sql. 

一、 全值匹配我最爱。全值匹配即个数和顺序一致。

我们已经建了一个复合索引（name,age,pos）,

最好最完美的sql就是你的索引怎么建的，我就怎么用。

在相同结果下，key_len越小越好。但是如果精度提高，付出的代价要大一些，即key_len更大。精度要求越来越高，花费的代价要多一些。

1、如果建了3个索引，而where后条件只用了第一个索引。索引不会失效，只是使用部分索引。

2、如果建了3个索引，而where后条件只用了前两个索引。索引不会失效，只是使用部分索引。

3、如果建了3个索引，where后条件用了全部三个个索引。索引不会失效，这即全值匹配。

4、如果建了3个索引，而where后条件只用了后两个索引。虽然能查询出来，索引会失效。

 

发现全表扫描，实际建了索引，但是没有用到。另外ref为null，原来为const。

5、如果建了3个索引，而where后条件只用了第一个和第三个索引。虽然能查询出来且索引不会失效，但不是全值匹配，而是使用部分索引，索引只用到了第一个即name。

6、如果建了3个索引，而where后条件只用了最后一个索引或者只用了一个中间的字段。虽然能查询出来，索引会失效。

总结：我们建立了复合索引（name,age,pos）,但是如果查询条件中没有第一个name字段，只有单独的age字段或者单独的pos字段，或者只有后面两个字段，索引会失效。违背了高效sql最重要的原则。最佳左前缀法则。

最佳左前缀法则：指的是查询从索引的最左前列开始并且不跳过索引中的列。如果索引了多列，要遵守最左前缀法则。

一、即带头大哥不能死。从最左开始，你建的索引的第一个字段不能丢，

二、中间兄弟不能断。

要区分：索引失效、部分使用索引、全值匹配。

三、不在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描。

Left是MySQL自带的函数，有点类似于java的subString函数。Left（name,4）表示从左边开始选四位。此时在索引列name上包了一个函数。·

仍然能够查出来，但是索引会失效。

四、存储引擎不能使用索引中范围条件右边的列。

全值匹配

 

TyPE由ref变为了range，范围之后的索引全失效。但是范围本身的字段age有被用到。Age与name还有点区别：name用于了查询，但是age只给了一个范围，age也用到了，否则不会给range，但是age用于去排序，而不是像name一样着重检索（查询），

下图的key_len证明用到了age字段

五、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

我们写sql的原则：最好是按需取数据，用多少取多少，尽量跟索引重合。尽量少用*

没有使用索引覆盖。

使用*的话，共有四个字段，除了name，age,pos，还有入职时间add_time。

Using where：表示使用了where过滤，去表中检索。

索引覆盖了

用到了Using index，表示使用了索引覆盖，避免了访问表的数据行。从索引中取数据，性能更加优秀，更好一些。

 

部分使用索引，且索引覆盖

 注意：ref只有一个const

范围之后的索引会失效，Using index表示去索引上拿，key_len为74而不是78，没有用到range，

部分使用索引，且索引覆盖

上面的例子中，你建的索引是name,age,pos,而你查的刚好是name,age,pos,一一吻合。

而如果你只查询索引字段的一部分，此时也会用到Using index，

 部分使用索引，且进行了索引覆盖。

六、mysql在使用不等于（!=或者<>，<>也表示不等于）!=或者<,>的时候无法使用索引会导致全表扫描。

我们也不能为了优化而束手束脚，如果有些表数据了非常少，而且各种特殊的历史原因或者极端的需求下面，即便索引失效了，我们也要不得已而为之。即不要为了避免索引失效而不敢写sql,你要知道这种情况下会导致失效，但是该写的时候也要写。具体要看生产环境和业务，具体问题具体分析。

七、is null ，is not null也无法使用索引。

由于在网站注册的时候，name不能为null。大家都知道，实在不行，关键字段也要写default值等于-1或者其他，避免null值。

而对于is not null，理论上用到索引，实际上没有用到索引。

八、like以通配符开头（‘%abc’），mysql索引失效会变成全表扫描的操作。使用“%July%”和“%July”虽然可以查询出来，但是会导致索引失效而全表扫描。使用 “July”则不会导致索引失效。所以说一般写like查询，百分号写在右边。即百分like加右边。

 

百万级别的数据量的单表必须要避免全表扫描。

一般就是在你的where语句中出现了between、<、>、in、like等的查询，type才为range。即like查询是一个范围。

如果生产环境中非要两边写百分号，否则数据不对。如何避免能查出数据又避免索引不失效呢？

解决like “%字符串%”时索引不被使用的方法：用覆盖索引来解决。

 

建索引之前，使用like “%aa%”查询id，name,age中的单个字段,会导致索引失效。

建索引之前，使用like “%aa%”查询id，name,age中的两个或三个字段,会导致索引失效。

建索引之前，使用like “%aa%”查询id，name,age，email中的全部字段,会导致索引失效。

结论：没建索引之前，都是全表扫描。

用覆盖索引来防止索引失效，从而避免全表扫描。

创建索引：假设经常要查询name和age这两个字段。

 

 

因为id是主键，它也可以从主键上去取。

 

 

 

 

 

 

覆盖索引就是：你建的索引和我查的字段个数和顺序完全一致。你建的索引是复合索引（name,age）,

八、字符串不加单引号索引失效。如果vArchar类型写错了，一定会被项目经理骂死，即VARchar类型绝对不能忘记单引号。

 

 

 

 

 

Name是varchar类型，name=2000仍然能查出结果，Mysql功能很强大，整数的2000和String的2000一个是int类型，一个是varchar类型，当name是varchar类型，而你写成整数的时候，这是mysql将会在底层自动的做一次类型转换，实现了从数字到String，从而帮你查出来。前面讲过，不要在索引列上做任何操作（自动类型转换），否则会导致索引失效。

九、少用or，用它来连接时会索引失效。注意是少用而不是不用。

小总结：

 

脚本宝典总结

以上是脚本宝典为你收集整理的索引优化：如何避免索引失效？全部内容，希望文章能够帮你解决索引优化：如何避免索引失效？所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。