Mysql 中几种索引失效场景分析

共计 2357 个字符，预计需要花费 6 分钟才能阅读完成。

表结构以及数据：

CREATE TABLE `t1` (
  `a` INT PRIMARY KEY,
  `b` INT,
  `c` INT,
  `d` INT,
  `e` VARCHAR(20)
) ENGINE=InnoDB;

CREATE INDEX idx_bcd ON `t1` (`b`, `c`, `d`);
CREATE INDEX idx_e ON `t1` (`e`);

insert into t1 values(4,3,1,1,'d');
insert into t1 values(1,1,1,1,'a');
insert into t1 values(8,8,8,8,'h');
insert into t1 values(2,2,2,2,'b');
insert into t1 values(5,2,3,5,'e');
insert into t1 values(3,3,2,2,'c');
insert into t1 values(7,4,5,5,'g');
insert into t1 values(6,6,4,4,'f');

索引情况：

a 字段是主键，对应主键索引，bcd 三个字段组成一个联合索引，e 字段一个索引。

另外，EXPLAIN语句返回的结果集包含了查询执行计划的各个方面，这些信息有助于开发者理解查询的执行方式和性能瓶颈。下面是一些常见的EXPLAIN结果字段及其含义：

• id: 查询的唯一标识符，用于标识查询中不同的查询块（query block）。
• select_type: 查询的类型，如SIMPLE、PRIMARY、SUBQUERY等，表示查询中的子查询类型。
• table: 此行的数据是关于哪个表的。
• type: 表示MySQL在表中找到所需行的方式，常见的值有ALL、index、range、ref、eq_ref、const等，从最差到最好的性能顺序排列。
• possible_keys: 显示查询中可能使用的索引。
• key: 实际上被查询优化器选择使用的索引。
• key_len: 表示索引使用的字节数。
• ref: 表示索引的哪一列被使用。
• rows: 表示MySQL估计查询要检索的行数。
• Extra: 包含了一些额外的信息，比如使用了哪些索引，是否使用了临时表等。

1. 不符合最左匹配原则

索引正常：

explain select * from t1 where b = 1 and c = 1 and d = 1

索引失效：

explain select * from t1 where c = 1 and d = 1

由于 bcd 为联合索引，去掉了 b 就不符合最左匹配原则，索引失效。

2. 不正确的Like查询

不用like:

explain select * from t1 where e = 'a'

正确使用like:

explain select * from t1 where e like 'a%'

不正确使用like：

explain select * from t1 where e like '%a'

3. 对索引列进行了计算或使用了函数

使用计算：

explain select * from t1 where b+1 = 2

使用函数：

explain select * from t1 where ifnull(b,0) = 0

4. 索引列进行了类型转换

e 字段的类型是 vachar，下面这个 sql 需要把 e 字段中的字符转换成数字，会导致索引失效。

explain select * from t1 where e = 1

5. <>不等于导致索引失效

b=1 可以走索引，b<>1 就不能走索引。

explain select * from t1 where b <> 1

6. order by导致索引失效

就算利用索引，但是由于是 select * 所以需要回表，而且回表成本比较高，所以不会走索引。

explain select * from t1 order by b,c,d

如果是 select b 就不需要回表了，就会选择走索引。

explain select b from t1 order by b,c,d

7. 使用or导致索引失效

正常索引：

explain select * from t1 where b = 1

使用 or：

explain select * from t1 where b = 1 or c = 2

8. select * 导致索引失效

使用 select *:

explain select * from t1

指定列查询：

explain select b from t1

9. 范围查询数据量过多导致索引失效

新增数据：

insert into t1 values(10,3,1,1,'d');
insert into t1 values(20,1,1,1,'a');
insert into t1 values(15,8,8,8,'h');
insert into t1 values(18,2,2,2,'b');
insert into t1 values(14,2,3,5,'e');
insert into t1 values(13,3,2,2,'c');
insert into t1 values(17,4,5,5,'g');
insert into t1 values(22,6,4,4,'f');

失效：

explain select * from t1 where b > 1

索引：

explain select * from t1 where b > 10

10. in范围过大

explain select * from t1 where a in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100)

提醒：本文发布于145天前，文中所关联的信息可能已发生改变，请知悉！