继续创造，加速成长！这是我参加「日新计划 6 月更文挑战」的第12天，点击查看活动概况

为了验证 MySQL 中哪些状况下会导致索引失效，咱们能够凭借 explain 执行计划来剖析索引失效的详细场景。

explain 运用如下，只需要在查询的 SQL 前面添加上 explain 关键字即可，如下图所示：

面试突击60：什么状况会导致 MySQL 索引失效？
而以上查询成果的列中，咱们最主要调查 key 这一列，key 这一列表明实际运用的索引，假如为 NULL 则表明未运用索引，反之则运用了索引。

以上所有成果列阐明如下：

  • id — 选择标识符，id 越大优先级越高，越先被执行；
  • select_type — 表明查询的类型；
  • table — 输出成果集的表；
  • partitions — 匹配的分区；
  • type — 表明表的衔接类型；
  • possible_keys — 表明查询时，或许运用的索引；
  • key — 表明实际运用的索引；
  • key_len — 索引字段的长度；
  • ref— 列与索引的比较；
  • rows — 大概估算的行数；
  • filtered — 按表条件过滤的行百分比；
  • Extra — 执行状况的描绘和阐明。

其间最重要的便是 type 字段，type 值类型如下：

  • all — 扫描全表数据；

  • index — 遍历索引；

  • range — 索引规模查找；

  • index_subquery — 在子查询中运用 ref；

  • unique_subquery — 在子查询中运用 eq_ref；

  • ref_or_null — 对 null 进行索引的优化的 ref；

  • fulltext — 运用全文索引；

  • ref — 运用非仅有索引查找数据；

  • eq_ref — 在 join 查询中运用主键或仅有索引相关；

  • const — 将一个主键放置到 where 后边作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

    创立测验表和数据

    为了演示和测验那种状况下会导致索引失效，咱们先创立一个测验表和相应的数据：

    -- 创立表
drop table if exists student;
create table student(
  id int primary key auto_increment comment '主键',
  sn varchar(32) comment '学号',
  name varchar(250) comment '名字',
  age int comment '年纪',
  sex bit comment '性别',
  address varchar(250) comment '家庭地址',
  key idx_address (address),
  key idx_sn_name_age (sn,name,age)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加测验数据
insert into student(id,sn,name,age,sex,address) 
  values(1,'cn001','张三',18,1,'高老庄'),
  (2,'cn002','李四',20,0,'花果山'),
  (3,'cn003','王五',50,1,'水帘洞');

    当前表中总共有 3 个索引，如下图所示：

    面试突击60：什么状况会导致 MySQL 索引失效？

    PS：本文以下内容根据 MySQL 5.7 InnoDB 数据引擎下。

索引失效状况1：非最左匹配

最左匹配准则指的是，以最左边的为起点字段查询能够运用联合索引，否则将不能运用联合索引。 咱们本文的联合索引的字段次序是 sn + name + age，咱们假定它们的次序是 A + B + C，以下联合索引的运用状况如下：

面试突击60：什么状况会导致 MySQL 索引失效？
从上述成果能够看出，假如是以最左边开端匹配的字段都能够运用上联合索引，比方：

  • A+B+C

  • A+B

  • A+C

    其间：A 等于字段 sn，B 等于字段 name，C 等于字段 age。

而 B+C 却不能运用到联合索引，这便是最左匹配准则。

索引失效状况2：过错含糊查询

含糊查询 like 的常见用法有 3 种：

  1. 含糊匹配后边恣意字符：like ‘张%’
  2. 含糊匹配前面恣意字符：like ‘%张’
  3. 含糊匹配前后恣意字符：like ‘%张%’

而这 3 种含糊查询中只有第 1 种查询方法能够运用到索引，详细执行成果如下：

面试突击60：什么状况会导致 MySQL 索引失效？

索引失效状况3：列运算

假如索引列运用了运算，那么索引也会失效，如下图所示：

面试突击60：什么状况会导致 MySQL 索引失效？

索引失效状况4：运用函数

查询列假如运用恣意 MySQL 提供的函数就会导致索引失效，比方以下列运用了 ifnull 函数之后的执行计划如下：

面试突击60：什么状况会导致 MySQL 索引失效？

索引失效状况5：类型转化

假如索引列存在类型转化，那么也不会走索引，比方 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效，如下图所示：

面试突击60：什么状况会导致 MySQL 索引失效？

索引失效状况6：运用 is not null

当在查询中运用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的，如下图所示：

面试突击60：什么状况会导致 MySQL 索引失效？

总结

导致 MySQL 索引失效的常见场景有以下 6 种：

  1. 联合索引不满足最左匹配准则。
  2. 含糊查询最前面的为不确定匹配字符。
  3. 索引列参加了运算。
  4. 索引列运用了函数。
  5. 索引列存在类型转化。
  6. 索引列运用 is not null 查询。

是非审之于己，毁誉听之于人，得失安之于数。

公众号：Java面试真题解析

面试合集：gitee.com/mydb/interv…