SQL优化 MySQL版 - 避免索引失效原则（二）

作者 ： Stanley 罗昊

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

体验SQL优化中的概率情况

在上一篇文章结尾处，我们在执行查询计划的时候，却发现我明明加了索引，并且也满足了使用索引的条件，但是，给我的优化结果却是失败，从而，得出一个结论便是，优化是概率的，也就跟彩票一样，不可能百分之百优化成功的，但是彩票我们都知道，全凭运气，但是这里就不一样了，我们需要了解SQL优化概率背后到底是谁导致它优化失败的；

首先，我们来了解下，出现概率优化的原因：因为在SQL底层中，有一个服务层，服务层有一个SQL优化器，当我们写一条语句，虽然我们手动优化了，但是，优化器觉得你优化的不太合适，它可能会进行一些自己的干扰，干扰完毕之后就执行结果就不再是你理想中的那样了，所以这个优化器有的时候会阻扰我们的优化工作；

接下来，我们就通过几个例子来体验一下我们设想的优化和实际不一样的一些操作；

首先，我们需要建立一个复合索引：

alter table book add index idx_book_at(authorid,typeid);

建立完索引后，我们进行一个简单的查询：

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

通过结果我们可以发现，复合索引全部生效了；

那么接下来，我们将体验一下让它产生概率问题，我把上面的SQL语句拿过来改改：

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

我们查看执行结果：

结果很明显，给authorid 添加了一个大于号，这样则导致了右侧索引全部失效，包括自身，从而得出一个结论，复合索引中如果有>，则自身已经后面的索引都将会失效；

但是，这次我SQL语句再次改变，奇怪的事情将会发生：

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

这次我把这个大于号加给了typeid字段，显然它也是索引，刚才我说了，添加大于号会导致自身并且右侧索引全部失效，但是接下来：

现在我们又发现，结论又不对了，我明明自身肯定失效啊，为啥这次偏偏却两个都生效了？

原因就是概率情况，咱们在实际执行时，复合索引全部使用了，并不是刚才我们说的那个结论，自身失效及右侧全部失效，当然，这个情况是大部分情况下都是有用了，仅有小部分情况会出现；

明显的概率问题

刚才我写了几个例子看起来不是特别的明显，下面我将写几个比较明显的例子来体验一下概率问题；

首先，我们编写一条SQL语句：

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

此时，我把authorid改成了小于号，我们看结果：

我们看到了，此时，我们换层了小于号，发现没有全部失效，此条语句得出结论，两个索引，仅生效了一个因为范围查询仅对自身生效，对后面的不会生效；

接下来，我再改变一下SQL语句：

explain select * from book where authorid < 4 and typeid = 2;

首先看清楚，我现在没有更改任何符号，仅把authorid小于号后面的数字条件写成了4，再来看看执行结果：

我们惊奇的发现，竟然全部失效了，我明明就光改了一个数字而已，就全部失效了，刚才还有一个生效，现在一个都没有了，这到底是为什么呢？

通过后两个例子我们发现，就改了一个数，索引都不一样了，所以，这就是SQL优化的一个概率；

因此得出结论，我们学习的索引优化，是一个大部分情况都适用的结论，但由于SQL优化器等原因，该结论不是100%正确，因为SQL的底层把我们写的语句给干扰了；

一般而言，范围查询（> < in），之后的索引失效，仅对自身生效；

补救

那么，如果这样一直干扰下去，我们到底还优不优化了？就没有办法来补救这个概率问题吗？答案是有的；

尽量使用索引覆盖 （using index）在Extra里面出现这个，就表示你的SQL语句不会出错，如果你怕在优化中出现概率问题，那么你就朝着using index这个方向去优化，因为，出现这个就代表你这条SQL100%生效，不会出现概率问题；

比如我现在有 a b c三张表;

现在我编写一条SQL，select a,b,c from 表名 where a = ... and b = ...;

在select后面我们用到了abc 并且查询条件也是a b 没有跨列，满足最佳做前缀，最主要的是查询条件也是索引，所有的索引你都按照规则全部用上了，这样就会出现索引覆盖，大大的提高了系统性能；

like尽量以“常量”开头，不要以'%'开头，否则索引失效

我现在编写一条SQL语句;

select * from 表名 where name like '%x%';

首先，这条sql语句是查询表名中name 带有x的数据，如果你这样写了，如果name是索引，那么name将会失效！

接下来，我结合数据库进行证实一下；

explain select tname  from teacher where tname like '%x%'

首先，tname我是加了一个索引的，但是看一下看一下执行结果：

没有失效，因为出现了覆盖索引，因为tname是索引，我刚好去查tname，所以出现了覆盖索引，导致本次查询没有失效，下面我把它换成“*”；

值得注意的是，在开发过程中，严禁出现“*”！本次为了说明问题，所以换成“*”；

执行结果：

索引全部失效！原因我刚才也说过了，在模糊查询是，不要以百分号开头；

如果想避免失效，可以变成以下这种写法：

explain select tname  from teacher where tname like 'x%'

这样虽然可以保证索引不会失效，但是，我们在项目开发中，难免遇到模糊查询，所以也是有解决方案的；

刚才我不小心也试出来了，因为我使用了索引覆盖，你想用模糊查询可以，但是你需要有索引覆盖，刚才我查询tname，tname本身就在索引里面，所以出现了索引覆盖；

如果必须使用模糊查询，那么就把查询条件以及需要查询的字段全部声明成索引即可；

尽量不要使用类型转换（显示、隐式），否则索引失效

这里我就简单的举个例子：

select * from teacher where tname = 'abc';

此时，tname是varchar类型，这个时候你你却写成int类型：

select * from teacher where tname = 123;

人家本来需要单引号的字符串类型，结果你给人家弄了一个去掉引号的int类型，所以索引就会失效；

尽量不要使用or，否则索引失效

select * from teacher where tname = " " or tcid>1;

这条sql语句就会导致索失效，所以要避免使用or这个关键字！

经过测试发现，or回导致以左的索引失效，也就是tname这个字段的索引失效了；

今日感悟：

努力就一定会有收获，心无旁骛