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