这样的SQL执行为什么不会报错？optimizer_trace深度历险
原创这样的SQL执行为什么不会报错？optimizer_trace深度历险
原创
这样的SQL执行为什么不会报错？optimizer_trace深度历险
起因
在一个风和日丽的周五，小菜终于忙完本周的工作，看了看屏幕右下角的时间 17：30
小菜伸了伸懒腰，惬意的说到：还有半个小时下班，晚上回去吃点什么好呢？
小菜手摸了摸下巴，进入思考：今天似乎还忘记了什么..
“原来今天忙到没空摸鱼”，于是小菜赶紧打开掘金，逛逛沸点
...
逛着逛着，发现这样一条沸点：
（由于图中查询的结果不符合社会主义核心价值观，我给打了码）
经过
有两张表：
t_user字段：id、namet_user_role字段：user_id、role
CREATE TABLE `t_user` (
`id` int NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_user` VALUES (1, 'caicai');
INSERT INTO `t_user` VALUES (2, '菜菜');
CREATE TABLE `t_user_role` (
`user_id` int NULL DEFAULT NULL,
`role` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_user_role` VALUES (1, '牛马');
INSERT INTO `t_user_role` VALUES (2, '老板');t_user_role通过字段user_id与t_user的字段id进行关联
来查看这一个子查询的SQL：
select * from t_user_role where user_id in (select user_id from t_user where id = 1);在子查询中 select user_id from t_user where id = 1 ，user_id 字段并不是 t_user 中的，而是 t_user_role 表中的
按道理，应该报错在t_user中找不到user_id字段才对，但这个SQL执行后却能够查询出数据：
user_id | role |
|---|---|
1 | 牛马 |
2 | 老板 |
小菜陷入了沉思，难道我的MySQL学错了？
小菜单独执行select user_id from t_user where id = 1时，发现正常报错：1054 - Unknown column 'user_id' in 'field list'
小菜挠了挠头，遇到这种情况好像无从下手，进入持续的思考后：小菜从单独执行报错，子查询执行就不报错的情况下，开始怀疑起子查询
小菜心想：子查询在某种情况下，优化器会使用半连接进行优化，会不会是这种情况造成的呢？
于是，小菜立马使用explain查看SQL的执行计划，但是很可惜，并没有在附加信息中查看半连接相关的优化
小菜不信邪，打算”打破砂锅问到底“，转眼一看右下角，时间已经来到了 17:45
小菜：怎么办，还有15分钟就下班了，再研究的话可能会超时，违背我以往到点就走的风格
“不管了”，小菜一怒之下怒了一下，准备开启优化器追踪，看看这个优化器到底再搞什么鬼
分析
#1.开启优化器追踪
SET optimizer_trace="enabled=on";
#2.执行SQL+查看优化器追踪（一起执行）
select * from t_user_role where user_id in (select user_id from t_user where id = 1);
select * from informationschema.OPTIMIZERTRACE;
#3.关闭
SET optimizer_trace="enabled=off";（文末附带TRACE的图片，不想实操的同学可以直接看图片）
然后查看第二个结果中的TRACK：
（由于数据太多，我们一个一个查看）
优化器追踪的过程分为：preparation准备、optimization优化、execution执行
小菜看到内容带有join，整个人都兴奋了，小菜：我就知道，肯定是将子查询优化为连接了，机智如我
子查询在MySQL中会被转换为内部/外部查询
in中的查询被解析为内部查询 select#2: 它将查询的user_id指明为t_user_role的字段（到这里已与我们写SQL本意不同了）
select `t_user_role`.`user_id` from `t_user` where (`t_user`.`id` = 1)
#简化后
select `t_user_role`.`user_id` from `t_user` where `t_user`.`id` = 1
#优化前
select `user_id` from `t_user` where `t_user`.`id` = 1到这里，原来的SQL被解析为：
#原SQL
select * from t_user_role where user_id in (select user_id from t_user where id = 1);
#解析后
select `t_user_role`.`user_id` AS `user_id`,`t_user_role`.`role` AS `role` from `t_user_role` where `t_user_role`.`user_id` in (select `t_user_role`.`user_id` from `t_user` where (`t_user`.`id` = 1))；
#优化下 方便查看
select * from t_user_role where user_id in (select t_user_role.user_id from t_user where id = 1)至此，我们已经能够理解为啥SQL不会报错，原来user_id被解析为t_user_role的字段，因此不会报错
接下来，会将in子查询转换为半连接semijoin （小菜心想：哈哈果然是半连接，我真聪明）
并将(t_user.id = 1) and (t_user_role.user_id = t_user_role.user_id)作为半连接的关联条件
transformations_to_nested_joins会将子查询转化为半连接
#转换的半连接
select `t_user_role`.`user_id` AS `user_id`,`t_user_role`.`role` AS `role`
from `t_user_role`
semi join (`t_user`)
where ((`t_user`.`id` = 1) and (`t_user_role`.`user_id` = `t_user_role`.`user_id`))
#优化 方便阅读 取消 `t_user_role`.`user_id` = `t_user_role`.`user_id`
select *
from `t_user_role`
semi join `t_user`
where `t_user`.`id` = 1至此，准备阶段完毕，接下来进入优化：
优化阶段名词太多，不一一介绍了（”俺也不会呀，俺又不是DBA“，小菜小声BB）
condition_processing优化where条件
substitute_generated_columns将列代替的表达式进行替换，这里没有优化
table_dependencies表中的依赖，如外键、视图、触发器...
ref_optimizer_key_uses 列出ref可用的列（执行计划中的执行方式ref）
rows_estimation计算扫描行数、成本
considered_execution_plans考虑的执行计划
attaching_conditions_to_tables 表附加条件
优化完后进行执行
那么半连接的SQL语句如何优化成真正的SQL呢？
#半连接
select *
from `t_user_role`
semi join `t_user`
where `t_user`.`id` = 1
#转换成内连接 + 去重
select *
from `t_user_role`
inner join `t_user`
where `t_user`.`id` = 1如果不太了解原理可以理解成 半连接 = 内连接 + 去重
MySQL会有多种去重手段来实现半连接，屏蔽去重的优化可以把运行的SQL看成内连接
至此，小菜终于分析完毕，一看时间17:58 “还剩两分钟，收拾一下准备下班”
最后（不要白嫖，一键三连求求拉~）
本篇文章被收入专栏 MySQL进阶之路，感兴趣的同学可以持续关注喔
本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~
有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~
关注菜菜，分享更多干货，公众号：菜菜的后端私房菜
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
