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

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

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

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

而以上查询成果的列中，咱们最主要调查 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 个索引，如下图所示：

  

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

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

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

从上述成果能够看出，假如是以最左边开端匹配的字段都能够运用上联合索引，比方：

• 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 种查询方法能够运用到索引，详细执行成果如下：

索引失效状况3：列运算

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

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

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

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

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

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

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

总结

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

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

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

公众号：Java面试真题解析

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