本文查询优化器计算查询成本、选择查询方案是个人学习掘金小册子《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