mysql 左查询剔除空_MySQL索引优化2-优化法则

从上图看到使用name,age,pos建立了一个复合索引，并且排序顺序为name->age->pos。使用此表结构来说一下索引优化和索引失效。

大概总结分为一下几点

• 全值匹配我最爱(怎么建怎么用)

从三条语句中看出三条语句都用到了索引，而且type类型为ref，where后面的条件越来越多精度越来越高，精度越来越高带来的就是长度和花费的代价也就越来越多(key_len由74-78-140，ref从一个常量变为3个常量)。但是来看下面的情况。

综合上述，我们建的索引是nameAgePos，但是没有了开头的name，如果打破前面创建的索引规则，把where条件剔除掉，使用age和pos或pos来查询的话，紧接着看到的就是全表扫描。没有使用到索引。结合我们下面的说法，就是违背了最佳作前缀法则

• 最佳作前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且 不跳过索引中的列。综合起来口诀：带头大哥不能死。

我们可以这么联想，我们创建的索引是nameAgePos，name就好比火车的车头，而后面的age和pos就是车厢，火车头自己可以跑，所以说，单独有name的时候索引没有失效。有火车头带着一个车厢也能跑得起来(name,age)。没了车头车厢也不用说只能晾干了。

再来看一个例子

我们把中间的age去掉，显而易见看到理论和实际都使用了索引，ref也是使用常量。但是两个key_len都是62，说明索引并不是全职匹配而是部分匹配。综合口诀：中间兄弟不能断。

• 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描

相同结果，做出的东西是一样的，但是分析出的性能却差得多。口诀：索引列上少计算

• 存储引擎不能使用索引中范围条件右面的列

我们可以看到索引执行的四条中，前三条的type类型都是ref，根据where条件的精度key_len也都在增加，最后一条由于age使用了范围搜索，导致age后的查询条件失效，key_len还是66。综合口诀：范围之后全失效。

• 尽量使用覆盖索引(只访问索引的查询（索引列和查询列一直))，减少select *。

首先看第一条和第二条的对比，使用*和使用索引列去查询返回的Extra是多了一个using index（用什么取什么会比写*要好很多）。第三条虽然使用了范围查询导致后面的查询条件失效，但是age确是从索引上拿此时key_len的长度为62，而且type也没有使用range而是ref，效果有所提升。第四条同样是根据name,age,pos查询，条件为等于的type相同，key_len确为66，ref为两个常量，此时的查询也比较优秀。还有一种情况只需要个别字段(第五条)，也可以使用using index。

• MySQL在使用不等于(!= 或<>)的时候无法使用索引会导致全表扫描

这个时候不能因为会导致索引失效，而不写，但是得知道这种情况下会导致失效，改写的时候还是也要写。最重要还是看生产环境、业务、技术。具体业务具体分析。

• is null,is not null 也无法使用索引

• like以通配符开头('%abc...')mysql索引失效回变成全表扫描的操作

从上述结果看出，只有百分号在右面的才能避免索引失效。且type是range。综合口诀：百分like加右面。

那么如何解决百分号在左边导致全表扫描的问题呢？

我们的解决办法是使用覆盖索引 like字符串时索引失效

• 字符串不加单引号索引失效

• 少用or，用它来连接时回索引失效

以上就是索引优化的一些方法，根据上面的例子总结出的一些口诀如下：

1. 全值匹配我最爱，最左前缀要遵守
2. 带头大哥不能死，中间兄弟不能断(永远要符合最佳左前缀原则)
3. 索引列上无计算(计算、函数、(自动、手动)类型转换)，范围之后全失效
4. like百分写最右，覆盖索引不写星
5. 不等控制还有or，索引失效要少用
6. var引号不能丢，SQL优化也不难

小例子：