被快手面试官追着问的真正线上索引失效的问题是如何排查的

高手回答

MySQL 索引失效是一种常见问题，在处理慢查询时经常需要考虑索引失效的可能性。

针对索引失效的排查，关键步骤包括确定需要分析的 SQL 语句，并通过EXPLAIN查看其执行计划。主要关注type、key和extra这几个字段。

SQL 执行计划分析的时候，要关注哪些信息？

以下是一次EXPLAIN返回的 SQL 语句执行计划的内容：

1. id：每个操作在执行计划中的唯一标识符。对于单条查询语句，每个操作具有独特的 id。在多表连接时，多条记录的 id 会相同。

2. select_type：操作的类型。常见类型包括 SIMPLE、PRIMARY、SUBQUERY、UNION 等。不同类型的操作会影响查询效率。

3. table：当前操作涉及的表。

4. partitions：当前操作涉及的分区。

5. type：表示查询时使用的索引类型，包括 ALL、index、range、ref、eq_ref、const 等。

6. possible_keys：可能被查询优化器选择使用的索引。

7. key：查询优化器选择使用的索引。

8. key_len：索引的长度。较短的索引长度意味着更高的查询效率。

9. ref：指示用于与选择的索引列进行比较的列或常量。

10. rows：表示此操作需要扫描的行数，即扫描表中的行数以获取结果。

11. filtered：表示操作过滤掉的行数占扫描行数的百分比。数值越大，查询结果越精确。

12. Extra：提供额外信息，如 Using index、Using filesort、Using temporary 等。

假设我们拥有以下一张表（MySQL InnoDB 5.7）：

CREATE TABLE `t2` (  `id` INT(11),  `a` VARCHAR(64) NOT NULL,  `b` VARCHAR(64) NOT NULL,  `c` VARCHAR(64) NOT NULL,  `d` VARCHAR(64) NOT NULL,  `f` VARCHAR(64) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `f` (`f`),  KEY `idx_abc` (`a`, `b`, `c`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

首先说起"type"字段，它具有以下几种取值以及它们之间的区别，我将为每种情况提供具体的 SQL 示例（请注意，以下 SQL 语句是我实际测试过的，但实际索引器可能会根据不同的数据和情况做出不同的优化）：

• system：系统表，数据量较小，通常无需进行磁盘 IO。

explain select * from t2 where f='Paidaxing';

这里使用了唯一性索引进行唯一查询。

• const：使用常量索引，MySQL 在查询时只会使用常数值进行匹配。

explain select * from t2 where f='Paidaxing';

在这种情况下，使用了唯一性索引进行唯一查询。

• eq_ref：唯一索引扫描，只会扫描索引树中的一个匹配行。

explain select * from t1 join t2 on t1.id = t2.id where t1.f = 's';

当在连接操作中使用了唯一索引或主键索引，并且连接条件是基于这些索引的等值条件时，MySQL 通常会选择 eq_ref 连接类型以提高查询性能。

• ref：非唯一索引扫描，只会扫描索引树中的一部分来查找匹配的行。

explain select * from t2 where a = 'Paidaxing';

在这种情况下，使用了非唯一索引进行查询。

• range：范围扫描，只会扫描索引树中的一个范围来查找匹配的行。

explain select * from t2 where a > 'a' and a < 'c';

在这里，使用了索引执行范围查询操作。

• index：全索引扫描，会遍历索引树来查找匹配的行。

explain select c from t2 where b = 's';

这是一个不符合最左前缀匹配的查询示例。

• ALL：全表扫描，将遍历整个表来找到匹配的行。

explain select * from t2 where d = "ni";

这是一个使用非索引字段进行查询的示例。

需要注意的是，这里的"index"表示对索引树进行扫描，效率并不高。而以上类型的执行效率从快到慢依次为：system > const > eq_ref > ref > range > index > ALL。在谈到"possible_keys"和"key"时，"possible_keys"指出查询语句可能使用的索引，但不一定实际使用这些索引。该字段列出了可能用于该查询的所有索引，包括联合索引的组合。而"key"字段表示实际用于查询的索引。如果查询使用了索引，该字段将显示所使用的索引名称。

接下来谈到一个经常被忽视但非常重要的字段"extra"，该字段描述了 MySQL 在执行查询时额外进行的一些操作。以下是"extra"可能的取值及其含义：

• Using where：表示 MySQL 在存储引擎检索行后，再进行条件过滤（使用 WHERE 子句）。查询的列未被索引覆盖，where 筛选条件非索引的前导列或者 where 筛选条件非索引列。

explain select * from t2 where d = "ni";   非索引字段查询explain select d from t2 where b = "ni";   未索引覆盖，用联合索引的非前导列查询

• Using index：表示 MySQL 使用了覆盖索引（也称为索引覆盖）优化，只需扫描索引，而无需返回数据表中检索行。

explain select b, c from t2 where a = "ni";  索引覆盖

• Using index condition：表示查询在索引上执行了部分条件过滤。这通常涉及索引下推。

explain select d from t2 where a = "ni" and b like "s%";   使用到索引下推

• Using where; Using index：查询的列被索引覆盖，并且 where 筛选条件是索引列之一，但不是索引的前导列，或者 where 筛选条件是索引列前导列的一个范围。

explain select a from t2 where b = "ni";   索引覆盖，但不符合最左前缀explain select b from t2 where a in ('a', 'd', 'sd');   索引覆盖，但前导列是一个范围

• Using join buffer：表示 MySQL 使用连接缓存。

explain select * from t1 join t2 on t1.id = t2.id where a = 's';

• Using temporary：表示 MySQL 创建了临时表来存储查询结果，通常在排序或分组时发生。

explain select count(*), b from t2 group by b;

• Using filesort：表示 MySQL 将使用文件排序而不是索引排序，通常在无法使用索引进行排序时发生。

explain select count(*), b from t2 group by b; 

• Using index for group-by：表示 MySQL 在分组操作中使用了索引，通常在分组操作涉及索引中的所有列时发生。

• Using filesort for group-by：表示 MySQL 在分组操作中使用了文件排序，通常在无法使用索引进行分组操作时发生。

• Range checked for each record：表示 MySQL 在使用索引范围查找时，需要检查每一条记录。

• Using index for order by：表示 MySQL 在排序操作中使用了索引，通常在排序涉及索引中的所有列时发生。

• Using filesort for order by：表示 MySQL 在排序操作中使用了文件排序，通常在无法使用索引进行排序时发生。

• Using index for group-by; Using index for order by：表示 MySQL 在分组和排序操作中都使用了索引。

我们需要通过 key+type+extra 来判断一条 SQL 语句是否使用了索引。如果使用了索引，我们需要确定是覆盖索引、索引下推、还是整颗索引树的扫描，或者是索引跳跃扫描等情况。

一般来说，理想的情况应该是以下几种：

首先，key 字段一定不能是 NULL，必须有具体的值；其次，type 字段应该是 ref、eq_ref、range、const 等之一；另外，extra 字段如果是 NULL，或者是"using index"、"using index condition"都是符合条件的情况。

如果在执行计划中发现一条 SQL 语句没有使用到索引，比如 type=ALL、key=NULL，以及 extra=Using where，那就表示该查询未能利用索引。

在分析为什么一条 SQL 语句没有使用索引时，我们需要考虑到是否需要使用索引以及选择使用哪个索引是由 MySQL 的优化器决定的。优化器会根据成本估算做出这个决定。

以下是可能导致没有使用索引的几种情况：

1. 索引未正确创建：如果查询语句中的 where 条件字段没有创建索引，或者不符合最左前缀匹配的情况，就是未正确创建索引。

2. 索引区分度不高：索引的区分度不足可能导致不使用索引，因效率不高。

3. 表过小：当表中数据很少，优化器认为全表扫描成本不高时，也可能不使用索引。

4. 查询中的索引字段使用了函数、类型不一致等导致索引失效。

针对这些情况，我们需要逐一分析：

1. 如若索引未正确创建，根据 SQL 语句创建适当的索引。如果不符合最左前缀，调整索引或修改 SQL 语句。

2. 若索引区分度低，考虑更换索引字段。

3. 对于表很小的情况，影响可能不大，或许没有必要进行优化。

4. 排查具体失效原因，然后根据情况调整 SQL 语句。

如有问题，欢迎加微信交流：w714771310，备注- 技术交流  。或微信搜索【码上遇见你】。

免费的Chat GPT可微信搜索【AI贝塔】进行体现，无限使用。早用早享受

好了，本章节到此告一段落。希望对你有所帮助，祝学习顺利。