作者 ： 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