深入探讨美团暑期实习面试中MySQL索引失效的多种场景与解决方案

图片

在复习数据库知识时，关于MySQL索引失效的概念应该十分熟悉，因为这一主题常常出现在各大公司的面试中。美团的暑期实习面试时就被问到了这个问题。

SQL优化不当，往往导致加班。在日常工作中，SQL是必不可少的一项技能。然而，很多人对SQL问题的关注度不高，可能是因为数据量较小，或者没有意识到索引的重要性。

本文将整理出几种常见的SQL索引失效场景。如果你对其中的细节了如指掌，说明你的学习能力相当出色！在撰写这篇文章的过程中，我发现自己之前的知识如同目录一般，真正的内容却并未深入理解。如果你能认真阅读本篇文章，定会有所收获，至少我在写作时感到思路更加清晰。从此以后，面对90%的SQL索引相关问题和面试问题，你都能游刃有余。

文章内容概览：

图片

数据准备

为了进行数据演示，我们创建了一个数据表，并在其中建立了三个索引：

  • 联合索引： sname, s_code, address
  • 主键索引： id
  • 普通索引： height
SET NAMES utf8mb4;  
SET FOREIGN_KEY_CHECKS = 0;  
  
-- 创建学生表结构  
DROP TABLE IF EXISTS `student`;  
CREATE TABLE `student` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  `s_code` int(100) NULL DEFAULT NULL,  
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  
  `height` double NULL DEFAULT NULL,  
  `classid` int(11) NULL DEFAULT NULL,  
  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),  
  PRIMARY KEY (`id`) USING BTREE,  
  INDEX `普通索引`(`height`) USING BTREE,  
  INDEX `联合索引`(`sname`, `s_code`, `address`) USING BTREE  
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  
  
-- 插入记录  
INSERT INTO `student` VALUES (1, '学生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');  
INSERT INTO `student` VALUES (2, '学生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');  
INSERT INTO `student` VALUES (3, '变成派大星', 3, '京东', 185, 3, '2022-11-02 20:44:19');  
INSERT INTO `student` VALUES (4, '学生4', 4, '联通', 190, 4, '2022-11-02 20:44:25');

问题思考

在创建了基本数据之后，我们可以开始验证之前提出的问题。

图片

最左匹配原则

最左匹配原则是一个大家耳熟能详的概念。简单来说，如果你的SQL语句中使用了联合索引的第一个字段，那么这条语句就有可能利用该索引。我们在此建立的联合索引可以用来测试最左匹配原则，索引字段为 sname, s_code, address。

以下是一些SQL语句的思考过程，看看哪些会使用到索引：

-- 联合索引 sname,s_code,address  

1、select create_time from student where sname = "变成派大星";  -- 会走索引吗？  
2、select create_time from student where s_code = 1;  -- 会走索引吗？  
3、select create_time from student where address = "上海";  -- 会走索引吗？  
4、select create_time from student where address = "上海" and s_code = 1;  -- 会走索引吗？  
5、select create_time from student where address = "上海" and sname = "变成派大星";  -- 会走索引吗？  
6、select create_time from student where sname = "变成派大星" and address = "上海";  -- 会走索引吗？  
7、select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海";  -- 会走索引吗？

凭借你的经验，哪些会使用到索引呢？可以先思考一下，将这些数字记在心中。

图片

走索引的示例

对于以下查询：

EXPLAIN select create_time from student where sname = "变成派大星";  -- 会走索引吗？

图片

而对于这个查询：

EXPLAIN select create_time from student where address = "上海" and s_code = 1;  -- 会走索引吗？

则会导致全表扫描，结果为 rows = 4。

图片

如果你不清楚 EXPLAIN 命令的作用，或者对分析出的数据感到困惑，建议查看另一篇相关的文章《MySQL 执行计划分析》(链接)。

如果你对上述问题的回答并不完全正确，可以继续阅读下去。

最左匹配原则强调，只有从最左边的字段开始，才有可能匹配任何连续的索引。但若遇到范围查询（如 >、<、between、like），匹配就会停止。例如，在使用 s_code = 2 的情况下，若建立了 (sname, s_code) 顺序的索引，则无法匹配到这个索引。

不过，如果查询条件采用 sname = "变成派大星" and s_code = 2，或者 a=1（或 s_code = 2 and sname = "变成派大星"），则可以成功匹配，原因在于优化器能够自动调整sname和s_code的顺序。

而在查询 sname = "变成派大星" and s_code > 1 and address = "上海" 时，由于 s_code 字段是一个范围查询，所以 address 字段将不会使用索引。

不带范围查询的索引使用情况

图片

带范围查询的索引使用情况

图片

通过前面的解释，可以理解 ref 和 range 的含义，级别的差异显而易见。

图片

思考

为什么左连接必须遵循最左匹配原则呢？

验证

看到一个很有趣的解释：

将联合索引视作一种闯关游戏的设计。

例如，联合索引是由 state/city/zipCode 组成的，那么 state 就是第一关，city 是第二关，zipCode 是第三关。必须先通过第一关，才能进入第二关；通过第一关和第二关后，才能进入第三关。

这个描述虽然不完全准确，但确实反映了这种思想。

为了理解联合索引的最左匹配原则，首先需要了解索引的底层原理。索引底层是由 B+树构成的，而联合索引的底层也是一棵 B+树，只不过联合索引的 B+树节点中存储的是键值。由于构建一棵 B+树只能依赖一个值来确定索引关系，因此数据库依赖联合索引的最左字段来构建索引。

接下来，我们可以重创建一个数据表：

DROP TABLE IF EXISTS `leftaffix`;  

CREATE TABLE `leftaffix` (  
  `a` int(11) NOT NULL AUTO_INCREMENT,  
  `b` int(11) NULL DEFAULT NULL,  
  `c` int(11) NULL DEFAULT NULL,  
  `d` int(11) NULL DEFAULT NULL,  
  `e` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  
  PRIMARY KEY (`a`) USING BTREE,  
  INDEX `联合索引`(`b`, `c`, `d`) USING BTREE  
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  

-- 插入记录  
INSERT INTO `leftaffix` VALUES (1, 1, 1, 1, '1');  
INSERT INTO `leftaffix` VALUES (2, 2, 2, 2, '2');  
INSERT INTO `leftaffix` VALUES (3, 3, 2, 2, '3');  
INSERT INTO `leftaffix` VALUES (4, 3, 1, 1, '4');  
INSERT INTO `leftaffix` VALUES (5, 2, 3, 5, '5');  
INSERT INTO `leftaffix` VALUES (6, 6, 4, 4, '6');  
INSERT INTO `leftaffix` VALUES (7, 8, 8, 8, '7');  
SET FOREIGN_KEY_CHECKS = 1;

图片

在创建索引树时，数据将被排序。按照最左匹配原则，首先根据 B 进行排序；如果出现值相同，则根据 C 排序；如果 C 相同，则根据 D 排序。排序完成后，结果如图所示：

图片

简化来说，MySQL 创建联合索引的规则是首先对联合索引的最左边第一个字段进行排序，然后在此基础上对第二个字段进行排序。因此，如果条件中没有第一个字段，查询的值就相当于 *11，这意味着匹配到所有数据。

这也说明了为什么我们在创建索引时，不推荐将索引建立在经常变动的字段上。因为这样就会导致索引结构需要频繁调整，从而消耗性能。

补充

根据评论区的反馈，最左匹配原则可以通过跳跃扫描的方式打破。在MySQL 8.0版本中，引入了索引跳跃扫描功能。当第一列索引的唯一值较少时，即使查询条件不包含第一列索引，依然可以使用联合索引。例如，当我们使用的联合索引为 bcd ，但字段 b 的唯一值较少，在使用联合索引时，甚至可以不使用 b。

总结一下，最左匹配原则的核心在于第一个字段。如果查询条件是 c = 1 and d = 1，却没有 b 的条件，那么无法通过索引匹配；而如果是 b = 1 and d = 1，则可以走索引，因为前面的 b 可以用于索引匹配。

继续分析索引失效场景

使用 SELECT *

思考

之前我有一个误区，认为使用 SELECT * 不会导致索引失效。然而，在一些情况下，索引失效实际上是由于 WHERE 后的查询范围过大，而非 SELECT * 本身的问题。那么，为什么不推荐使用 SELECT * 呢？

解释

  • 增加查询解析器的解析成本。
  • 字段的变化容易导致与 resultMap 配置不一致。
  • 包含无用字段会增加网络消耗，尤其是 text 类型的字段。阿里的开发手册中对以上几点做了较全面的概述。

尽管 SELECT * 在某些情况下依然能够走索引，但并不推荐这种写法。

图片

虽然可以走索引，但使用 SELECT * 获取了不必要的数据，首先通过辅助索引过滤数据，然后再通过聚集索引获取所有列，这增加了额外的 B+树查询，必然速度较慢。因此，减少使用 SELECT * 是为了降低回表带来的损耗。

图片

如上所示，SELECT * 在某些情况下是可以走索引的；如果不走索引，可能是因为 WHERE 查询范围过大，导致MySQL选择全表扫描，而非 SELECT * 的问题。

图片

上图展示了索引失效的情况。

范围查找并不总是导致索引失效，以下情况则会激活索引，因为范围较小：

图片

小结

  • SELECT * 有时会走索引。
  • 范围查找有时会导致索引失效，但在特定情况下会生效，范围小则会使用索引。
  • 在MySQL中，连接查询的原理是先对驱动表进行查询操作，然后用驱动表得到的数据逐条到被驱动表查询。
  • 每次驱动表加载一条数据到内存，随后被驱动表的所有数据都需加载到内存进行比较，效率低下。因此，MySQL允许指定一个缓冲池的大小，缓冲池越大能够同时加载多条驱动表的数据进行比较，减少I/O操作，提升性能。因此，如果此时使用 SELECT * 带上无用列，只会占用缓冲空间，浪费提高性能的机会。
  • 根据评论区的反馈，SELECT * 并不是导致索引失效的直接原因，更多是因为 WHERE 条件的问题，但仍然建议尽量减少使用 SELECT *，以避免潜在影响。

使用函数

在SELECT后使用函数时，索引可以正常使用，但以下写法则无法走索引：

图片

图片

由于索引存储的是字段的原始值，而非经过函数计算的值，因此无法走索引。

不过，从MySQL 8.0开始，索引特性增加了函数索引，即可以针对函数计算后的值建立索引，这意味着可以通过扫描索引查询数据。

这种写法虽然不常见，但依然需要注意。

计算操作

这类情况与上面类似，索引失效的原因在于改变了索引原有的值，因此在树中找不到对应的数据，只能进行全表扫描。

图片

同样，由于索引保存的是字段的原始值，而非 b - 1 表达式计算后的值，因此无法走索引。MySQL将不得不将所有索引字段取出，逐项进行表达式计算，因此只能通过全表扫描的方式进行条件判断。

而以下计算方式则可以使用索引：

图片

对于熟悉Java语言的朋友来说，这种对索引进行简单表达式计算的方式理论上可以实现，但实际上MySQL未实现这一功能。

小结

总而言之，只要影响到索引列的值，索引就会失效。

LIKE %

使用 LIKE % 会导致索引失效，需谨慎使用。首先，了解 LIKE 的基本用法：

  1. %（百分号通配符）：表示任意字符出现任意次数（包括0次）。
  2. _（下划线通配符）：只能匹配单个字符。
  3. LIKE 操作符：告诉MySQL后面的搜索模式是利用通配符而非直接相等匹配进行比较。

注意：使用 LIKE 时，如果后面没有通配符，效果与 = 一致，例如：

SELECT * FROM products WHERE products.prod_name LIKE '1000';
  1. 匹配包含 "Li" 的记录（包括记录 "Li"）：
SELECT * FROM products WHERE products.prod_name LIKE '%Li%';
  1. 匹配以 "Li" 结尾的记录：
SELECT * FROM products WHERE products.prod_name LIKE '%Li';

在使用 LIKE 时，左侧使用通配符会导致索引失效；而右侧使用通配符则会走索引。虽然查询仍然走索引，但级别较低，主要是因为模糊查询范围较大。

图片

左侧的范围非常大，因此没有使用索引的必要。

图片

小结

在索引使用时，查询范围的大小关系密切，范围过大将导致索引失效。

使用 OR 导致索引失效

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，则会导致索引失效。以下是一个例子，其中 b 是主键，e 是普通列，从执行计划的结果看，走的是全表扫描。

图片

优化

对于这种情况的优化方式是为OR两边的条件都添加索引，从而避免全表扫描。

图片

IN 使用不当

使用 IN 不一定会导致全表扫描。IN 一般会走索引，但当 IN 的取值范围较大时，可能导致索引失效，进而走全表扫描。

图片

当 IN 的结果集大于30%时，索引也会失效。

NOT IN 和 IN 的失效场景相同

ORDER BY

图片

ORDER BY是排序操作，这意味着需要对数据进行排序。如果走索引，索引是有序的，但需要回表查询，消耗时间；而直接全表扫描排序则无需回表。因此，MySQL认为直接全表扫描效率更高，因此在ORDER BY的情况下，通常选择全表扫描。

子查询是否走索引？

答案是：会。但使用不当则可能导致不走索引。

总结

图片

减少回表优化思路

对于熟悉SQL的人来说，回表查询是一个相对陌生但重要的概念。关于索引问题，还有一个细节值得关注，即回表会导致效率下降。我们日常工作中通常使用单列索引，而联合索引对新手来说并不常用，但在某些情况下，单列索引并不是最优解。例如，LIKE %问题会导致索引失效。最近了解到的 ICP 知识（Index Condition Pushdown）在此处非常相关。以下是一些整理：

首先，ICP 意为索引下推，主要作用是解决数据查询中的回表问题。值得一提的是，只有在联合索引的情况下，ICP 才能发挥作用。接下来，当面试官提问什么版本增加了什么优化时，你可以自信地回答 MySQL 5.6 之后的 ICP 功能，这会让面试官对你更有好感，因为这表明你对数据库有深入了解。

回表问题

回表查询通常发生在非主键索引上，需要两次树查询，因此效率较低。为了优化这一过程，可以使用联合索引。

ICP 索引下推

ICP 是在MySQL 5.6版本后引入的特性。若面试中被问起 MySQL 5.6 之后的优化，牢记 ICP 将是一个加分项。简单而言，ICP 是为了充分利用联合索引的特性，减少回表的工作量，从而提高查询效率。

如何查看是否使用了索引下推，可以通过配置参数来监督执行计划。

图片