这样的SQL执行为什么不会报错？optimizer_trace深度历险

这样的SQL执行为什么不会报错？optimizer_trace深度历险

起因

在一个风和日丽的周五，小菜终于忙完本周的工作，看了看屏幕右下角的时间 17：30

小菜伸了伸懒腰，惬意的说到：还有半个小时下班，晚上回去吃点什么好呢？

小菜手摸了摸下巴，进入思考：今天似乎还忘记了什么…

“原来今天忙到没空摸鱼”，于是小菜赶紧打开掘金，逛逛沸点

…

逛着逛着，发现这样一条沸点：

（由于图中查询的结果不符合社会主义核心价值观，我给打了码）

经过

有两张表：

1. t_user字段：id、name
2. t_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执行后却能够查询出数据：

小菜陷入了沉思，难道我的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下持续关注喔~

有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~

关注菜菜，分享更多干货，公众号：菜菜的后端私房菜