索引是如何失效的？详谈MySQL索引使用规范
索引是如何失效的？详谈索引使用规范
承接上文，我们已经就索引的底层数据结构及其查找算法作了介绍，如下
以及对SQL语句建立SQL分析的方法
我们继续研究索引这个话题，有时候我们建立了索引，但通过分析发现，索引并没有被用到，这是什么情况呢，那就需要我们了解索引是如何进行匹配的，请看下面对索引的匹配方式的阐述
索引最爱的-全值匹配
有以下SQL语句
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 and t_emp.deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 and t_emp.deptid=4 AND t_emp.name = 'abcd'
建立索引
CREATE INDEX idx_age_deptid_name ON t_emp(age,deptid,NAME);
这是一个针对age，deptid，name三个字段建立的组合索引
之后，我们分别使用Explain分析三条SQL语句的执行情况
观察type字段三条全值匹配都使用了非唯一性索引扫描（ref）
SQL 中and后查询字段的顺序，跟使用索引中字段的顺序，没有关系
优化器会在不影响 SQL 执行结果的前提下，给你自动地优化，但这个顺序问题，也不是那么随意，请看下面
索引最固执的-最左匹配
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE deptid=4 and t_emp.name = 'abcd' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 and deptid=4 ;
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 and t_emp.name='abcd' ;
观察这三句SQL语句，跟上三条有何区别呢？
- 第一句：where后缺少了组合索引的第一个字段age
- 第二句：少了第三个字段
- 第三句：少了第二个字段
那么我们看看他们运行出来的‘化验单’吧
分析以下：
- 第一句少了索引中最左边的字段，导致索引完全失效
- 第二句则完全使用了索引，从何见得？见key_len
- 第三句则只使用了索引的第一个字段，因为中间出现断层，导致后面的索引无法使用
总结如下：
-
查询字段缺失会导致组合索引无法充分使用，甚至索引失效！
-
使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
-
索引的顺序和查询的顺序颠倒不影响结果，优化器会自动帮你处理
索引最敏感的-不要在索引上进行任何计算
不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描
你只需注意：
-
字符串数据类型的索引列，你可以不加单引号，但Mysql就要帮你做，导致它无法正常索引，所以记住：等号右边不要有转换
-
索引列上不要有范围查询
分析此图，在索引列上使用<>这种符号，会导致ref级别的type降到range级别
-
尽量使用覆盖索引
在之前的博客中我提到
要做到覆盖索引，你只需要：
查询列和索引列一致，不要写 select *!
-
索引列的 is not null 和 is null
is not null 会导致索引失效，is null 可以用到索引
-
Like的前后模糊匹配
- 前缀不能出现模糊匹配！如
&san会导致索引失效 - 双边模糊匹配必然导致该列索引失效如
%sa% - 后缀是可以支持的
- 前缀不能出现模糊匹配！如
-
使用union all 或者 union 来替代or的使用
- 组合索引情况下，or会使索引完全失效
- 主索引/唯一索引情况下则不会
-
其他索引小规范
- 尽量使用主键索引，较少得触发回表
- 区分度较小的列不建索引，如性别列，那完全没有意义
- 能使用limit尽量使用limit
有一段非常有意思的口诀，总结了索引的使用规范，来看看
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢，SQL 优化有诀窍。