mysql高级（二）——索引实战

上述就是mysql索引失效的各种原因,下面我们来实战

---

表数据：

ALTER TABLE tb_user ADD INDEX idx_user_nap(NAME,age,pos);//建立复合索引

• 最佳做前缀法则

  前面我们建立了（name,age,pos）的复合索引
  

  

  

  可以看出这三种都用到了索引，索引条件越多，越精确，key_len越大

  

  得出结论组合索引缺少前面的时候，不生效

  

  只有name索引生效，pos不生效，原因：复合索引是有顺序的（name,age,pos）中间缺少了age那么pos也会失效。
结论：如果索引了多列，要遵循最左前缀法则，查询从索引的最左前列开始并且不挑锅索引中的列

• 不要在索引列上进行计算以及类型转换

  我们插入一条name=‘2000’的数据
  

  正常查询type是ref级别
  

  对索引字段进行左截取操作导致索引失效
  

  大家都知道name的类型是varchar，当我们sql语句中的2000不加引号的时候，mysql也能查询出来，会帮我们完成自动转换，但是导致索引失效

• 范围之后全失效

  

  由于age使用了范围查找，导致type变成range并且根据key_len=66可以知道：只有name、age起到了索引的效果，pos失效

• 尽量使用覆盖索引

  覆盖索引就是：指访问索引的查询，索引列和查询字段一致，减少select *操作

  

• 使用!=或者<>会导致全表扫描

  
  

  结论：导致全表扫描，索引失效

• is null 和 is not null无法使用索引

  
  

• 有like的%要加在右边、

  
  

  上面两个都会导致全表扫描，索引失效

  

  %写在右边，索引生效，并且是range类型的查询。
如果业务中必须使用左右两个%，请使用覆盖索引来代替select * 如下：
  
  

  当加入了非索引的查询字段就会导致索引失效，如下：
  

  建议：尽量查询有索引的字段，避免select *的出现

• 少用or，会导致索引失效

  

总结：

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!