手记

SQL优化 MySQL版 - 避免索引失效原则(一)

作者 : Stanley 罗昊

避免索引失效的一些原则

我们编写SQL语句后会进行添加一些索引进行优化,但是有时候确实建了索引,但索引有时候会失效

比如在模糊查询使用 in 关键字的时候索引就失效了,这只是其中的一个条件;

1.复合索引的时候,不要跨列或无序使用(最佳左前缀)

我在前几篇文章有重点介绍过;

就比如你建立了一个索引 分别字段为 a b c,你使用的时候却没有从a开始向后依次使用,而是使用了a c 把中间的b漏掉了下面我举个例子:

比如我 where a ...  and b ...  order by c,这种使用顺序就符合最佳做前缀,我从左向右依次使用了索引,如果你写成下面这样:

where b ... and a order by c,这样很明显你顺序不对,并不满足最佳左前缀,从而导致了索引失效;

2.复合索引,尽量使用全索引匹配

假设我现在建立了一个复合索引 a b c,在查询的时候,尽量把这些索引字段都用上;

比如我现在想找一个张三,你先根据 a 找,再根据b找,最后再根据c找,这样找就会更快一点,尽量不要你建了三个索引你却只用两个,这样虽然可以,但是却把一级目录给删了;

就跟看书一样,我要找书上一个精确内容,准确来说先看目录,再看章节,再看最后的小结,道理是一样的;

3.不要在索引上进行任何操作

如果你在索引上进行任何操作,索引就必将失效,什么是任何操作,下发我将举例说明:

比如你对索引进行加减乘除计算,进行一些函数计算,或进行一些类型转换,在这种情况下,索引都会失效;

比如我select ... where A.x = ...;简单写一个这样的就行了,不要再进行一些花里胡哨的操作,这里假设A.x是索引

你不要写成:select... where A.x*3 = ....;即是是索引,你算完之后就失效了,就不要这样干!

这里就给大家演示一下:我现在编写一条SQL语句记得前面加explain:

select * from book where authroid = 1 and typeid = 2;

首先book表中的 authroid 跟 typeid 均是索引:

通过key_len可以清楚的发现我用到了两个索引一个是四,两个加起来就是八,这两个索引本别是authroid  typeid ,而且查询效率是ref级别,接下来我就对它进行一些操作;

explain select * from book where authroid = 1 and typeid*2 = 2;

执行结果:

虽然我们的查询级别仍然是ref,但是值得注意的是,key_len变成了四,我明明写了两个索引,现在却少了一个,明明有两个索引,你却用了一个,原因很简单,因为我在typeid上进行乘法操作了!

如果还不能明白,我这次把这两个索引字段都进行操作

explain select * from book where authroid*2 = 1 and typeid*2 = 2;

执行结果:

这个结果够明显了把,查询级别变成了ALL,而且我明明有两个索引,却一个都没有用!所以从这里面就能得出结论,不能对索引进行任何操作,否则就会导致索引失效!

4.对于复合索引左边失效右边全部失效

现在我们对上一条SQL语句再进行更改操作:

select * from book where authroid*2 = 1 and typeid = 2;

这条SQL语句authroid typeid 这两个字段是复合索引,我现在给authroid字段进行操作,下面我们看执行结果:

我即便没有对typid进行任何操作,但是导致的结果却是全部失效

原因很简单,在复合索引下,你左前第一个失效,那么你后面全部跟着失效;

假设有 a b c 这些字段是复合索引,我给a 字段进行乘法操作,那么b c 字段都将会失效;

给b加字段,b后面的全部字段都会失效,a不受影响;

这里值得一提的是,如果有两个复合索引,比如 a1 a2是一个复合索引,b1 b2也是一个复合索引,即便a1在左边a1失效了a2会跟着失效但是b1 b2不受影响,因为复合索引与复合索引之间是没有任何关系的;

5.复合索引不能使用不等于(!= 或 <> is null (is not null))

如果用以上其中一种,会导致资深以及右侧索引全部失效,下面我将会举例说明:

我现在编写一条SQL语句j记得前面加上explain 

select * from book where authoid = 1 and typeid = 2

其中authoid typeid 均是复合索引:

执行结果发现一些异常现象,首先key里面只生效了一个,在key_len里面是4不是8,因为我用了两个索引应该是8才对我也没有对索引进行操作,但就是失效了一个;

原因:SQL优化是一种概率层面的优化,至于是否实际使用了我们的优化,需要通过explaini进行推测,在possible_keys中我们可以看到,它预测使用了两个索引,但实际它就是只用了一个,因为MySQL优化是概率的,即便你手动优化的很少,有时你照样正常生效,这正常现象,因为手动优化试只是概率;

现在我们不管概率问题,我们继续紧接着上刚才的例子;

我们看到authroid仍在生效,我们这次把authroid进行一些不等于操作

explain  select * from book where authoid ! = 1 and typeid = 2

执行结果:

奇怪的事情又发生了,这次查询级别仍是fef但是我们发现authoid确实失效了但是typeid生效了

原来authoid失效只是你自身失效了,并不影响其他字段的生效概率,也可以理解为typeid把authoid给干掉了!

那么接下来我都给他加上不等于试试:

explain  select * from book where authoid ! = 1 and typeid ! = 2;

执行结果:

这里我就不再过多阐述了,很显然,全部失效了

原文出处:https://www.cnblogs.com/StanleyBlogs/p/10482048.html  


1人推荐
随时随地看视频
慕课网APP