MySQL IN查询索引失效问题分析 - 从查询优化器如何基于查

作者: ToufuDrago_c53f | 来源:发表于2020-11-16 09:19 被阅读0次

MySQL IN查询索引失效问题分析 - 从查询优化器如何基于查
mysql性能优化-慢查询分析、优化索引和配置
63 MySQL实战性能优化-optimizer_trace
MySQL8.0-隐藏索引（Invisible Indexes）
MySQL性能调优
MySQL 索引及查询优化总结-2018-03-20
MySQL索引优化分析
mysql索引查询
mysql数据库优化
MySQL索引原理及慢查询优化

本文查询优化器计算查询成本、选择查询方案是个人学习掘金小册子《MySQL 是怎样运行的：从根儿上理解 MySQL》后总结的，与原文可能有所不同，感兴趣的可以前往购买阅读。
https://juejin.im/book/6844733769996304392

一、问题介绍

线上环境有一张千万数据的表，新增了一个字段user_id，默认值为空字符串''，并为这个字段添加了普通二级索引。
简化后的表结构如下，这里我只插入了100万数据，user_id字段的值全是空字符串‘’

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` char(32) NOT NULL DEFAULT '',
  `version` int(10) unsigned NOT NULL DEFAULT '0',
  `name` char(3) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8mb4

查询语句大致如下：

select * from test_table where user_id in ('aaa', 'bbb', 'ccc' ......);

代码上线后，出现了IN内的元素大于等于10个时就不走索引，全表扫描导致接口超时的问题。

image.png

一眼看过去，明显是走索引效率更高的查询语句，为什么MySQL的查询优化器选择了全表扫描？
本文首先将介绍线上问题的这条查询语句，查询优化器是如何选择查询方案的，之后会在测试环境复现线上的问题，最后给出问题的解决方式。

二、查询优化器如何基于成本选择查询方案

在用explain查看select语句的执行计划时，是否有过以下疑问：

where条件明明可以走索引，为什么explain的结果是全表扫描？
where条件可以走多个索引时，为什么explain的结果是走了A索引而不走B索引？

我们的第一反应大概是：因为MySQL计算出来这样查询的成本最低，效率最高。
那么，MySQL是如何选择查询方案，计算查询的成本的呢？

1.查询方案选择的流程

（1）根据搜索条件，找出所有可能使用的索引；
（2）计算全表扫描的代价；
（3）计算使用不同索引查询的代价；
（4）对比各个方案，采用成本最低的那个。

2.查询成本的计算

2.1查询成本的组成

（1） I/O成本：将数据(包括索引)从磁盘加载到内存的成本。
（2）CPU成本：读取、检测记录是否满足搜索条件，以及对结果集进行排序等操作的成本。

MySQL默认的配置是，读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。

2.2全表扫描成本计算

接下来以这条查询语句为例，计算全表扫描的成本。

select * from test_table where user_id in ('aaa', 'bbb');

全表扫描会读取改表所有记录，判断user_id是否符合条件。

I/O成本= 聚簇索引占用的页面数 * 1.0
CPU成本 = 全表记录数 * 0.2

可以通过

SHOW TABLE STATUS LIKE 'test_table';

查到表的记录数ROWS、聚簇索引占用的存储空间的字节数Data_length。最后计算的成本为：

ROWS = 996424，Data_length = 69812224。
默认是一个页面占用16kb，所以 聚簇索引占用的页面数 = 69812224 / 16 / 1024 = 4261
I/O成本 = 4261 * 1.0  = 4261
CPU成本 = 996424 * 0.2 = 199284.8
全表扫描成本 = 203545.8

2.3索引idx_user_id成本计算

查询走索引时，会先查索引获取到主键id，再用主键id回表。计算成本时主要涉及查询的范围区间数量以及需要回表的次数。
范围区间的计算方法：

where user_id > 'aaa' and user_id < 'bbb'; -> 范围区间数量为1
where user_id = 'aaa'; -> 范围区间数量为1
where user_id in ('aaa', 'bbb', 'ccc); -> 范围区间数量为3

查询优化器认为读取索引的1个范围区间的I/O成本和读取一个页面是相同的。
查询优化器认为一次回表的成本相当于读取一个页面。(查询优化器可以精确或估算出需要回表的记录数）
当前test_table表的数据：

image.png

1.范围区间的数量 = 2   
2.预估需要回表的记录数 = 3
I/O成本 = 2 + 3 * 1.0 (范围区间数量 + 回表记录数）= 5
CPU成本 = 3 * 0.2 + 0.01 + 3 * 0.2（读取索引的记录数 + 微调 + 回表读取聚簇索引记录）= 1.21
总成本 = 6.21

2.4预估需要回表的记录数

如果 where user_id in ('aaa', 'bbb', ······）的括号内有非常多个元素（几千甚至上万个)，查询优化器也要一个一个地去计算需要回表的记录数吗？答案是否定的，不然的话光是计算回表的记录数成本就非常大了。因此，需要一种预估回表记录数的方式。

系统变量eq_range_index_dive_limit：如果 in 内的元素数量n 大于等于这个系统变量，查询优化器会使用“平均一个值重复的次数”来预估需要回表的记录数。

假设in内元素的数量为n
SHOW INDEX FROM test_table;
        Cardinality : 索引列中不重复的值的数量。
SHOW TABLE STATUS LIKE 'test_table';
        Rows : 记录数
平均一个值重复的次数 = Rows / Cardinality 
总回表记录数 = 平均一个值重复的次数 * n

三、复现、分析、解决问题

image.png

获取到系统变量eq_range_index_dive_limit的值为10，将本地MySQL的配置也改为10（默认是200），成功复现问题。

image.png

1.全表扫描的成本

全表扫描成本 = 203545.8（上面已经算过）

2.走索引idx_user_id的成本

select * from test_table where user_id in ('000','111','222','333', '444', '555','666', '777', '888', '999');
范围区间数量n = 10
回表数量：因为n >= 10(系统变量eq_range_index_dive_limit) ，所以采用一个值平均重复次数的方式计算。
SHOW INDEX FROM test_table;
        Cardinality = 1。
SHOW TABLE STATUS LIKE 'test_table';
        Rows = 996424
平均一个值重复的次数 = Rows / Cardinality = 996424
I/O成本 = 10 + 10 * 996424 = 9964250
CPU成本 = 9964240 * 0.2 + 0.01 + 9964240 * 0.2 = 3985696
索引idx_user_id成本 = 9964250 + 3985696 = 13949946

查询优化器计算出走索引的成本高于全表扫描的成本，便选择了全表扫描的查询方案。

3.分析

查询优化器出现误判的原因在于，user_id列的值全是空字符串，重复值太多，查询优化器预估出的回表的记录数远大于实际需要回表的记录数。

4.解决方式

方式一：SQL语句，in内每次最多只传9个元素。这样查询大量数据时太低效了！
方式二：增大系统变量eq_range_index_dive_limit的值。但是并不推荐，因为这个值设置的高了，当查询语句in内的元素很多且小于这个值时，计算回表数量的效率会很低。并且也无法从根本上解决问题，一旦in内的元素大于等于我们设置的值，全表扫描问题还是会出现。
方式三：修改SQL语句，使用force index():

select * from test_table force index(idx_user_id) where user_id in ('000','111','222','333', '444', '555','666', '777', '888', '999');

这种方式比较简单，不需要修改数据库配置，但是需要预防类似问题再次出现。

四、感想

虽然这个问题上来直接用force index()就解决了，不需要特意去了解查询优化器是如何计算成本、选择查询方案，但我个人认为，拥有这些知识，对我们排查问题、分析问题、选择解决方案，还是有挺大的帮助的。
最后再提供两种方式，可以直接查看查询优化器计算出来的查询成本。（第二种建议在测试环境或本地的MySQL用）

explain format=JSON select * from test_table where user_id in ('000','111','222','333', '444', '555','666', '777', '888', '999');

SET global optimizer_trace="enabled=on";
SHOW VARIABLES LIKE 'optimizer_trace';
select * from test_table where user_id in ('000','111','222',
'333', '444', '555','666','777', '888', '999');
SELECT * FROM information_schema.OPTIMIZER_TRACE;