MySQL全值索引与最左查询法则及索引失效的情况

全值匹配

• 对索引中所有列都指定具体值，该情况下，索引生效，执行效率高。
• select all from tb_name where name='huawei' and status='1' and address='北京市'；

最左前缀法则

• 在复合索引中，如果查询了多列，索引需要从最左列开始，且不跳过中间的列。
  
• 最左前缀索引反例
  
• 跳过name，status去查adress，同样显示用到的索引（key）为空
  
• 在查询中调换顺序，那么显示用到了索引，所以这个跟查询时使用的索引位置信息无关，只要给的索引是遵从左到右的原则即可
  
• 如果跳过了中间索引，那么只会使用左边的索引，中间索引右边的索引不会使用到
  

不要在列上进行运算操作，否则索引失效：这里substring函数作用为截取字符串字串，3代表从第三个字符开始截取，2代表截取2个

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

• 注意的是status索引失效，通过key_len可以看出，长度变了，说明status索引没有用到

覆盖索引

• 我们应当尽量使用覆盖索引，避免使用回调查询
• 注意：查询前提是建立了复合索引create index index_name from tb_name(name,status,address);
• 当使用explain select * from tb_name where name='小米科技;'时，extra里出现的是using index condition，就是回调了数据，这种情况下速度较慢些
  
• 使用explain select name from tb_name where name='小米科技;'时，extra里出现的是using where，using index，查询效率高
  
• 但是如果查询列，超出索引列，也会降低性能，如：explain select name,password from tb_name where name='小米科技';
  

or 索引失效情况

• 用or分开后的条件，如果前面使用了索引，后面没有使用索引，那么整个索引都失效

以%开头的like模糊查询，索引失效

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
• 以科技开头的模糊查询explain select * from tb_name where name='科技%';走索引
  
• 以科技结尾的模糊查询explain select * from tb_name where name='%科技';不走索引
  
• 查询包含‘科技’ 的模糊查询，也不走索引explain select *from tb_name where name='%科技%';
  
• 这些情况都可以用覆盖索引来避免，如explain select sellerid from tb_name where name='%科技%';其中sellerid为主键，

创建了相应的索引而不走该索引而走全表查询的情况

• 当列表中基本都是要查询的字段时，那么走全表查询比走索引快，系统就会走全表查询

null 值的判定

• 前提：address字段空值基本没有
• select * from tb_name where address=null；，这种情况下，查询字段空值不多，走索引
• select * from tb_name where address=not null;，这种情况下，查询字段非空值多，不如走全表查询。效率高

in 走索引 not in 索引失效

单列索引与复合索引

• 在实际应用中，我们应当尽量使用复合索引
• 因为如果对他们分别创建单列索引，在实际运行中，只会用到其中最优的一个

查看索引使用情况

• show status like Handler_read%;，查看当前会话索引的使用情况
• show global status like Handler_read%;查看全局的索引使用情况

范围查询之后的索引失效

select * from tb_name where name='huawei' and status>'0'and address='北京市';

• 这里name跟status用到了索引，但address字段索引失效