索引优化:如何避免索引失效?

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

 

 

不让索引失效的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,ageemail中的全部字段,会导致索引失效。

结论:没建索引之前,都是全表扫描。

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

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

 

 

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

 

 

 

 

 

 

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

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

 

 

 

 

 

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

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

小总结: