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');

索引情况:

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

/>

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

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

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

1. 不符合最左匹配原则

索引正常:

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

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

/>

索引失效

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

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

/>

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

2. 不正确的Like查询

不用like:

explain select * from t1 where e = 'a'

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

/>

正确使用like:

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

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

/>

不正确使用like

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

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

/>

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

使用计算:

explain select * from t1 where b+1 = 2

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

/>

使用函数:

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

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

/>

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

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

explain select * from t1 where e = 1

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

/>

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

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

explain select * from t1 where b <> 1

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

/>

6. order by导致索引失效

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

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

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

/>

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

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

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

/>

7. 使用or导致索引失效

正常索引:

explain select * from t1 where b = 1

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

/>

使用 or

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

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

/>

8. select * 导致索引失效

使用 select *:

explain select * from t1

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

/>

指定列查询:

explain select b from t1

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

/>

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

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

/>

索引:

explain select * from t1 where b > 10

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

/>

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)

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

提醒:本文发布于108天前,文中所关联的信息可能已发生改变,请知悉!

/>

Tips:清朝云网络工作室

阅读剩余
THE END