MySQL索引失效场景盘点

站长

2024年03月19日 19:40 · 阅读数 3

我们在实际工作中，对于查询效率提升的方法，通常来说就是对某些字段建立索引。但建立索引并不意味着查询能够正确的使用索引，查询语句编写的不当，往往会导致索引失效，导致查询性能大大降低。

本篇我们来聊聊一些常见的索引失效场景，根据这些场景来了解索引失效的情况。

在创建表时，InnoDB存储引擎默认会创建一个主键索引，即聚簇索引，其它索引都属于二级索引。

在聊索引的失效场景前，我使用的MySQL版本为8.0.35，我们定义如下表结构，省略一些字段及索引声明：

CREATE TABLE `sys_user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名',
  `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机',
  `birthday` int NOT NULL DEFAULT '0' COMMENT '生日',
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户登录邮箱',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '联系地址',
	...
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `mobile` (`mobile`) USING BTREE,
	...
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT COMMENT='用户表';

模糊匹配

当我们的SQL语句使用了左模糊匹配或左右模糊匹配时，即like %xx 或者 like %xx%，这两种WHERE条件都会造成索引失效。

例如我们执行如下这几条like条件SQL语句，使用EXPLAIN关键字来查看它们的执行情况：

SELECT * FROM sys_user WHERE user_name like '张%';

SELECT * FROM sys_user WHERE user_name like '%张';

SELECT * FROM sys_user WHERE user_name like '%张%';

我们来逐条使用EXPLAIN关键字分析一下

MySQL索引失效场景盘点我们可以看到，当我们查询对name字段使用右模糊查询时，会通过走索引查询，执行计划中的type=range以及key=idx_name表示走了索引扫描，实际走了index_name索引。

MySQL索引失效场景盘点上述SQL对name字段使用了左模糊查询，执行计划中的type=ALL就代表了全表扫描，而没有走索引执行查询。

MySQL索引失效场景盘点

同样，对name字段使用左右模糊查询页无法走索引扫描。

从上述执行计划来看，在对字段进行左模糊以及左右模糊查询时，查询无法走索引，那么为什么左模糊以及左右模糊查询无法走索引呢？

原因在于B+树索引叶子节点中的值是按照索引列来进行排序存储的，只能够根据前缀来进行索引查询比较。

具体来说，如果使用了WHERE user_name like '%张'或WHERE user_name like '%张%'条件进行查询，InnoDB无法通过索引定位到指定的叶子节点，因为查询的结果可能是陈张、和张、周张等等结果，因而不知道从索引的那个位置开始扫描，于是就只能通过全表扫描的方式来查询，具体了解了B+树的结果，想必一定会恍然大悟。

表达式计算

我们来看如下的查询语句，该语句对索引字段进行了表达式计算，这种情况下也是无法走索引的。

SELECT * FROM sys_user WHERE id + 1 = 5;

MySQL索引失效场景盘点从EXPLAIN执行计划可以看到，type = ALL说明是通过全表扫描的方式查询数据的。

当我们把查询语句的条件从WHERE id + 1 = 5改为WHERE id = 5 - 1，这样就不会在索引字段上进行表达式计算，这样是可以走索引的。

MySQL索引失效场景盘点

为什么对索引字段进行表达式计算会无法走索引呢？

原因在于索引的B+树中，保存的是索引的原始值，而不是id+1的值，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，所以采用的是全表扫描。在对索引进行表达式计算后的值，就无法再走索引了。

或许你可能会说，MySQL做一下转换处理，不就可以走索引了吗？的确是如此，但MySQL并没有这样做，由于表达式的不确定性，因此MySQL并没有实现，反而通过索引失效的方式告诉程序员，让其自己保证查询条件中不要对索引进行表达式计算，去避免索引失效。

使用函数

同样，在查询条件中对索引使用函数，也无法走索引查询，比如如下的查询语句：

SELECT * FROM sys_user WHERE length(user_name) = 6;

MySQL索引失效场景盘点

可以看到，在查询中对name字段使用了LENGTH函数，执行计划中的type=ALL，代表了全表扫描。

对查询条件中对索引使用函数无法走索引的原因与对索引进行表达式计算的相同，索引保存的是索引字段的原始值，经过函数计算后，就没有办法走索引了。

隐式类型转换

我们在建表时，定义字段的类型为字符串类型，并为其创建了索引，但是当我们在查询条件中，输入的是整型的话，通过执行计划可以发现，同样会没有走索引，发生了索引失效。例如如下sql语句：

SELECT * FROM sys_user WHERE mobile = 10012344321;

MySQL索引失效场景盘点

在上述查询中，查询条件我们输入了一个整型，而mobile字段在表定义时为varchar类型，此时执行计划中type = ALL，所以是通过全表扫描来查询数据的。

我们再来看如下的sql语句，这条sql语句中，查询字段id定义为整型类型，而我们输入的查询条件为字符串类型。

SELECT * FROM sys_user WHERE id = '1';

MySQL索引失效场景盘点

我们可以看到，当查询字段为整型时，我们输入字符串条件是可以走索引扫描的。

为什么索引字段为字符串型，我们输入整型时会导致索引失效，而索引字段为整型，我们输入字符串型却能够走索引扫描呢？

首先，我们需要了解MySQL的数据类型转换规则，在MySQL中，面对字符串与整型，到底是整型转换成字符串型，还是字符串型转换成整型呢？

通过一个简单的小查询来测试一下：

SELECT "10" > 9

我们可以通过上述的查询结果来判断MySQL 的数据类型转换规则是什么：

如果MySQL的转换规则将字符串转换为数字，则上述查询语句相当于SELECT 10 > 9，此时的结果应该为1；
如果MySQL的转换规则将数字转换为字符串，则上述查询语句相当于SELECT "10" > "9"，此时的结果应该为0；

执行一下，查看结果

MySQL索引失效场景盘点

我们可以看到执行的结果为1，说明MySQL的转换规则：当字符串与数字进行比较时，会将字符串转为数字。

在知道结论后，我们在回头来看上面的两条SQL语句：

SELECT * FROM sys_user WHERE mobile = 10012344321;

上述的sql语句会走全表扫描，实际上是因为mobile为字符串类型，而我们输入的条件为整型，因此MySQL会对mobile字段转换成数字，相当于：

SELECT * FROM sys_user WHERE CAST(mobile AS signed int) = 10012344321;

可以看到，CAST函数作用在了索引字段mobile上，而我们都知道在索引字段上使用函数时无法走索引的，因此导致了索引失效。

我们再来看第二条SQL语句

SELECT * FROM sys_user WHERE id = '1';

上述SQL语句在前面测试是会走索引扫描的，这是因为索引字段为整型，而我们输入的条件为字符串类型，MySQL将字符串类型转换为整型，因此函数时操作在输入参数中，索引字段并没有使用任何函数，因此可以走索引扫描

SELECT * FROM sys_user WHERE id = CAST("1" AS signed int);

OR拼接

在查询WHERE条件中，如果在OR前的条件字段为索引列，而OR后的条件字段不为索引列，那么会发生索引失效。

我们来看看如下查询语句：

SELECT * FROM sys_user WHERE id = 1 or address = '广东省';

上述查询语句中，id为主键，address为普通列，并没有创建其列的索引，我们查看这条SQL的执行计划，可以看到这条sql语句走了全表扫描。

MySQL索引失效场景盘点

未走索引的原因在于OR连接的含义为只要两个条件满足一个即可放入结果集，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

当然，我们可以通过为address字段建立索引，这样这条sql语句的执行计划中，type=index merge，index merge表示分别对 id 和 address的索引进行了扫描，然后将这两个结果集合并并返回，避免了全表扫描。

MySQL索引失效场景盘点

联合索引非最左匹配

我们都知道，在创建联合索引中，需要重点关注索引列的顺序问题，不同的顺序创建出不同排序值的索引B+树。联合索引在查询时遵循最左匹配原则，即按联合索引最左的索引列开始进行索引匹配。

例如我们创建了一个(a, b, c)的联合索引，如下几个条件查询则可以匹配上这个联合索引，这几个条件满足一个共同的特点，即遵循最左匹配原则：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

但如下几个查询则不满足最左匹配原则，则无法走联合索引：

where b=2；
where c=3；
where b=2 and c=3；

还有一种条件查询：where a = 1 and c = 3，这种查询在MySQL 5.5时，会通过联合索引查找到满足a条件的索引行，然后通过主键值回表到主键索引读取数据行，并比对满足c字段的数据行返回。

但在MySQL 5.6 之后，MySQL提供了索引下推（关于索引下推可以看一下我前面两篇博客），由于联合索引包含了c字段，因此，查询可以直接在索引行中判断是否满足c条件，如果不满足则直接过滤掉不满足条件的记录，从而减少回表次数。