本文查询优化器计算查询成本、选择查询方案是个人学习掘金小册子《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个时就不走索引，全表扫描导致接口超时的问题。
一眼看过去，明显是走索引效率更高的查询语句，为什么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表的数据：
1.范围区间的数量 = 2
2.预估需要回表的记录数 = 3
I/O成本 = 2 + 3 * 1.0 (范围区间数量 + 回表记录数）= 5
CPU成本 = 3 * 0.2 + 0.01(读取索引的记录数 + 微调）= 0.61
总成本 = 5.61
-
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
三、复现、分析、解决问题
获取到系统变量eq_range_index_dive_limit的值为10，将本地MySQL的配置也改为10（默认是200），成功复现问题。
-
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 = 1992848
索引idx_user_id成本 = 9964250 + 1992848 = 11957098
查询优化器计算出走索引的成本高于全表扫描的成本，便选择了全表扫描的查询方案。
-
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;
