sql进阶：索引失效情况与原理

其实这个文章应该早早就写了，但是一直拖着，毕竟sql一直是个忽略的点。
看过那本sql必读必会，然后就没然后了，我不知道是我看的版本问题还是咋的，拍胸脯保证，真的全本书上多是实践操作的语法，不同版本数据库的区别，对比和写法，也有索引的创建，然后呢？好像是仅仅提了几个索引会失效的原则，例如in，例如like。
然后在那以后的工作中宁可看看框架，看看具体的方法，看看redis，看看elasticsearch。与人一说在研究cloud，研究es，研究分布式，研究消息队列。高大上又自我满足。
曾经我觉得sql有什么好学的？不就是增删改查么？不就是优化么？看过简单的优化的教程，亲自做过几个demo，大概知道什么表锁行锁事务隔离级别，也会建个索引，什么唯一，主键，复合。然后语法都会，自以为也就这样了。
你要是问我复杂sql？多关联查询？我甚至还能理直气壮的告诉你，所谓的关联查询不也就是简单查询拼出来的么？哪有什么不可拆分？（现在想想觉得自己真的蠢的可怜）
第一次开始怀疑自己是一次面试，面试官很温和，问的也很常见。聊到sql的时候从简单的索引问到了复合索引。看过我上一篇文章的朋友应该知道，复合索引我其实知道的太多，但是简单的原理还是稍微懂一点点，我也以为懂这么一点点够用了。有兴趣的朋友也可以点进去看看。
sql优化及讲解

上篇文章对复合索引的介绍
然后面试官问了一个问题。mysql数据库，现在A,B,C三个字段设置成联合索引，如果查询条件A>5会不会走索引？这个没啥问题，肯定走啊。。。自以为是的我有多信心满满现在脸有多疼。

查询数量是超过表的一部分,mysql30%,oracle 20%会导致索引无法使用。

这个我要放在第一个说，因为我也是多方求证，做了demo才相信的。
我用了比较极端的例子，建个表插入age>0的几条随机的数据，然后age设置索引，查询条件age>-1.结果发现确实是没走索引。
这个表结构虽然很简单，但是我用这个表做了好多demo，所以也贴出来一下。

表结构
这个是正常一点的查询，然后也走了索引，让大家知道范围查询也可能走索引的
正常使用索引查询
这个就是我说的极端的情况了，然后没走索引
不正常使用没走索引
然后现在继续说面试的那个题，A>5会不会走索引？还真不一定。

什么是负向查询?查询肯定不能命中索引。会导致索引无用。

这个其实还稍微好理解一点，比如说过我们查寻条件age！=-1，但是其实我们查询条件中age没有等于-1的。也就是全表扫描，索引也会失效（其实这个很上面的例子有点像，不走索引的原因可能也是因为上面那个，因为非命中所以肯定得到的结果也是全部。。不确定，但是不冲突，知道这样会不走索引就行了）
我刚刚看了下，age没有14的，所以就用了14，其实只要不命中都可以。

负向查询结果

大于小于和between in到底走不走索引？

这里要声明。我之前看到的in会导致索引失效是以前的，现在优化后in不一定了。至于走不走索引还是看范围。
in到底会不会让索引失效?答案是不一定。看下面两个图片的对比。区别只是in的数值命中不同。第一张都中了，第二张都没中。其实这个还是可以理解为第一条，也就是数据范围的原因

in让索引失效
in走了索引
between and 和<>有区别？没有的！不要相信网上那些过时言论或者胡说八道
between走了索引
<>也走了索引
接下来是两个都不走索引的例子：
<>没走索引
between and没走索引
好了，差不多这几个范围的例子就做到这里，由此可见，网上的好多言论都是不实的，不要瞎相信。

复合索引不是以为的那么简单,谁说顺序不能变？

其实这个概念，可能不是别人瞎说，而是以前的版本就是那样，但是现在随着更新优化，也该接触新知识了。A,B,C复合索引，条件上where A=1 AND C=2 AND B=3.到底会不会走索引?

字段（为了不影响测试我把id主键去了）
注意看索引的顺序，age。id。name
接下来运行的结果：
走了索引
顺序改了，也走了索引
最左边的带上，等级又上来了
打头的都变了还是走了索引
注意！！！！下面两个都是没走索引的，只不过因为我太懒了就三个字段还都是索引，所以查询的时候最少是index
没走索引，因为最左边的没带上
查最后一个不走索引不用多说了吧
是不是觉得贼神奇？超乎你的常识？这是因为mysql优化。
1. 其实 = 和 in 是可以乱序的，比如 a=1 and b=2 and c=3,建立(a,b,c)索引可以任意顺序，mysql查询优化器会帮助我们调整顺序。
2. mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a,b,c联合索引，条件a=1 and b>2 and c=3 ,如果建立(a,b,c)顺序的索引，d就用不到索引；如果建立(a,c,b)的索引则都可以用到，a,c的顺序是可以随意调整的(原因是第一条)。这就是最左匹配原则。
如果你理解不了为什么会这样，仔细看看下面这个图。
最左匹配原则原因
下面是实际操作的截图：
很明显只有age走了索引，下下个图是age和id都走了，虽然等级一样但是看key 的长度是有区别的
只有age走了索引
这个是age，id都走了索引
换了顺序依然age，id都走了索引，因为age可以和name位置随意换
三个都走了索引，看key_len的长度能看出区别

反正大概情况就这样，看那个B+树图，仔细看看很容易理解。这里说明，B+树，BitMap索引，Hash索引是不一样的。我也才看，以后有机会了也做个笔记。
剩下常见的索引失效情况就不多说了，主要是我也没做demo，，以后有时间再详细的测试和解释吧。
好了，今天就到这里了。争取每天学习一点点，不知道多久会发生质变，哈哈。然后大家共勉，祝大家工作生活顺顺利利的吧！
全文手打不易，如果你觉得有帮到你或者有点用，别吝啬的点个喜欢和点个关注哦~~