关于java:面试突击60什么情况会导致-MySQL-索引失效

为了验证 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 面试真题解析

面试合集：https://gitee.com/mydb/interview