【MySQL】索引失效情况 及 索引优化

BTree索引的工作原理

  若建的索引是index（a,b,c），会先排序a，如果遇到相同的a，再排序b，如果遇到相同的b，再排序c；如果此时中间的b查询条件为b>1，这是一个范围值range，mysql无法利用索引在对后面的c部分进行检索，即 range类型查询字段后面的索引无效

索引失效

1.若索引列有多列，要查询条件中要遵守最左前缀法则，即查询从索引的最左前列开始并且不跳过索引中的列；若不遵守此规则，索引就会失效

2.在索引列上做任何操作（计算，函数，自动或手动类型转换），会导致索引失效而转向全表扫描

3.存储索引不能使用索引中范围条件右边的列

4.尽量使用覆盖索引，减少select * ，这样只查询索引字段，提高了效率using index

5.使用不等于！=或<>会导致索引失效导致全表扫描

6.like以通配符%开头（%abc…）mysql索引会失效导致全表扫描

  但是实际需求当中，肯定有要以%开头的查询，为满足此种需求，我们可以使用覆盖索引，这样就成功解决了;

7.查询条件中有is null,is not null也无法使用索引

8.字符串不加单引号索引失效（因为系统默认自动进行数据类型的转换）

9.or连接时索引失效

【索引失效总结】

索引优化

• 单键索引，尽量选择针对当前query过滤性更好的索引
• 组合索引，当前query中过滤性最好的字段在索引字段顺序中位置越靠前越好
• 组合查询中，尽量选择可以能够包含当前query中where子句中更多字段的索引
• 组合查询join时：减少NestedLoop循环次数，遵循“永远用小结果驱动大的结果集”；首先优化内层循环；保证join语句中被驱动表上join条件字段已经被索引，最好的情况是join的连接的所有表都建索引；当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝啬JoinBuffer的设置

【索引优化总结】