MySQL查询优化器--逻辑查询优化技术（五）--外连接的消除（二）
来源：互联网 发布：如何对待网络语言 编辑：程序博客网 时间：2024/05/23 22:37
--观察查询执行计划，看哪个真正执行的是外连接，哪个被从外连接优化为了内连接
语句十：EXPLAIN EXTENDED SELECT* FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_col_1 = t_2_col_1;
语句十一：EXPLAIN EXTENDED SELECT* FROM t_1 LEFT JOIN t_2 ON t_1_col_1 = t_2_col_1;
语句十二：EXPLAIN EXTENDED SELECT* FROM t_1 LEFT JOIN t_2 ON t_1_col_1 = t_2_col_1 WHERE t_1_col_1 = t_2_col_1;
语句
查询语句优化后的结果
语句十、语句十二
/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`,
`test`.`t_1`.`t_1_col_1` AS `t_1_col_1`,
`test`.`t_1`.`t_1_col_2` AS `t_1_col_2`,
`test`.`t_2`.`t_2_id` AS `t_2_id`,
`test`.`t_2`.`t_2_col_1` AS `t_2_col_1`,
`test`.`t_2`.`t_2_col_2` AS `t_2_col_2`
from `test`.`t_1` join `test`.`t_2`
where (`test`.`t_2`.`t_2_col_1` = `test`.`t_1`.`t_1_col_1`)
语句十一
/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`,
`test`.`t_1`.`t_1_col_1` AS `t_1_col_1`,
`test`.`t_1`.`t_1_col_2` AS `t_1_col_2`,
`test`.`t_2`.`t_2_id` AS `t_2_id`,
`test`.`t_2`.`t_2_col_1` AS `t_2_col_1`,
`test`.`t_2`.`t_2_col_2` AS `t_2_col_2`
from `test`.`t_1` left join `test`.`t_2` on((`test`.`t_1`.`t_1_col_1` = `test`.`t_2`.`t_2_col_1`))
where 1
对比四
语句十一，多了“left”，表明最后得到的查询执行计划还是按照左连接的语义执行，没有被优化为内连接；而语句十、十二，执行两表连接时，只是“join”，没有外连接存在，表明最后得到的查询执行计划是按照内连接的语义执行的，外连接被优化为了內连接
--第三组
--观察WHERE条件是非连接条件，且条件分别在左表和右表上，查询执行计划，看哪个真正执行的是外连接，哪个被从外连接优化为了内连接
语句十三：EXPLAIN EXTENDEDSELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_id>0and t_2_id>0;
语句十四：EXPLAIN EXTENDEDSELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_1_id>0;
语句十五：EXPLAIN EXTENDEDSELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_2_id>0;
语句十六：EXPLAINEXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_col_1>0OR t_2_col_1>0;
语句
查询语句优化后的结果
语句十三
/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`,
`test`.`t_1`.`t_1_col_1` AS `t_1_col_1`,
`test`.`t_1`.`t_1_col_2` AS `t_1_col_2`,
`test`.`t_2`.`t_2_id` AS `t_2_id`,
`test`.`t_2`.`t_2_col_1` AS `t_2_col_1`,
`test`.`t_2`.`t_2_col_2` AS `t_2_col_2`
from `test`.`t_1` join `test`.`t_2`
where ((`test`.`t_1`.`t_1_id` > 0) and (`test`.`t_2`.`t_2_id` > 0))
语句十四
/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`,
`test`.`t_1`.`t_1_col_1` AS `t_1_col_1`,
`test`.`t_1`.`t_1_col_2` AS `t_1_col_2`,
`test`.`t_2`.`t_2_id` AS `t_2_id`,
`test`.`t_2`.`t_2_col_1` AS `t_2_col_1`,
`test`.`t_2`.`t_2_col_2` AS `t_2_col_2`
from `test`.`t_1` left join `test`.`t_2` on((`test`.`t_2`.`t_2_id` = `test`.`t_1`.`t_1_id`))
where (`test`.`t_1`.`t_1_id` > 0) |
语句十五
/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`,
`test`.`t_1`.`t_1_col_1` AS `t_1_col_1`,
`test`.`t_1`.`t_1_col_2` AS `t_1_col_2`,
`test`.`t_2`.`t_2_id` AS `t_2_id`,
`test`.`t_2`.`t_2_col_1` AS `t_2_col_1`,
`test`.`t_2`.`t_2_col_2` AS `t_2_col_2`
from `test`.`t_1` join `test`.`t_2`
where ((`test`.`t_2`.`t_2_id` = `test`.`t_1`.`t_1_id`) and (`test`.`t_1`.`t_1_id` > 0))
语句十六
/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`,
`test`.`t_1`.`t_1_col_1` AS `t_1_col_1`,
`test`.`t_1`.`t_1_col_2` AS `t_1_col_
2`,`test`.`t_2`.`t_2_id` AS `t_2_id`,
`test`.`t_2`.`t_2_col_1` AS `t_2_col_1`,
`test`.`t_2`.`t_2_col_2` AS `t_2_col_2`
from `test`.`t_1` left join `test`.`t_2` on(1)
where ((`test`.`t_1`.`t_1_col_1` > 0) or (`test`.`t_2`.`t_2_col_1` > 0))
对比五
语句十三，WHERE条件是“t_1_id>0 and t_2_id>0”，满足如下四种情况：
1) t_1_id = NULL，t_2_id = NULL：则NULL>0为UNKOWEN， UNKOWEN and UNKOWEN值为UNKOWEN
2) t_1_id = NULL，t_2_id != NULL：则NULL>0为UNKOWEN，t_2_id>0且t_2_id非NULL值为TRUE或FALSE，所以“UNKOWEN and TRUE”或者“UNKOWEN and FALSE”值为UNKOWEN
3) t_1_id != NULL，t_2_id = NULL： 同上
4) t_1_id != NULL，t_2_id !=NULL：“t_1_id>0 and t_2_id>0”值要么是TRUE要么是FALSE,但毕竟可能为“TRUE”，这不符合“空值拒绝”的条件一，但满足条件二“外连接的提供空值的一侧为另一侧的每行只返回一行”（t_1_id和t_2_id都是QUIQUE键至多能为连接后的结果集返回一行）
所以，四种情况都是满足“空值拒绝”的，所以可以把外连接转换为內连接
语句十四，WHERE条件是“t_1_id>0”，有如下两种情况：
1) t_1_id = NULL：则NULL>0为UNKOWEN
2) t_1_id != NULL：非NULL值与零比较，可为TRUE或FALSE，当为FALSE时满足“空值拒绝”条件一，当值为TRUE时不满足“空值拒绝”条件二，所以外连接不能转换为內连接
语句十五，WHERE条件是“t_2_id>0”，有如下两种情况：
3) t_2_id = NULL：则NULL>0为UNKOWEN
4) t_2_id != NULL：非NULL值与零比较，可为TRUE或FALSE，当为FALSE时满足“空值拒绝”条件一，当值为TRUE时满足“空值拒绝”条件二，所以可以把外连接转换为內连接
语句十六，WHERE条件是“t_1_col_1>0 or t_2_col_1>0”，类似语句十四、十五的组合，只是列不同（UNIQUE列和普通列的差别），“t_2_col_1>0”能够像语句十五一样，保证满足“空值拒绝”，但“t_1_col_1>0”如同语句十四，不能够保证满足“空值拒绝”，所以经过“or”操作，只能以“left Join”执行两表连接
- MySQL查询优化器--逻辑查询优化技术（五）--外连接的消除（二）
- MySQL查询优化器--逻辑查询优化技术（五）--外连接的消除（一）
- MySQL查询优化器--逻辑查询优化技术（四）--连接的消除
- MySQL查询优化器--逻辑查询优化技术（三）--嵌套连接消除
- MySQL查询优化器--逻辑查询优化技术（二）--子查询优化（二）
- MySQL查询优化器--逻辑查询优化技术（二）--子查询优化（三）
- MySQL查询优化器--逻辑查询优化技术（二）--子查询优化（四）
- MySQL查询优化器--逻辑查询优化技术（二）--子查询优化
- MySQL查询优化器--逻辑查询优化技术（六）--条件化简（表达式处理）
- MySQL查询优化器--逻辑查询优化技术（一）--视图重写
- MySQL查询优化器--逻辑查询优化技术（七）--等价谓词重写
- PostgreSQL查询优化器--逻辑查询优化--子查询优化（二）
- PostgreSQL查询优化器--逻辑查询优化--视图优化（二）
- SQL优化--逻辑优化--子查询优化（MySQL）
- MYSQL查询优化（二）
- MYSQL查询优化（二）
- MYSQL查询优化（二）
- MYSQL查询优化（二）
- CentOS6.8 网络配置ip 连接外网
- MySQL查询优化器--逻辑查询优化技术（四）--连接的消除
- 将Linux下python默认版本切换成替代版本
- MySQL查询优化器--逻辑查询优化技术（五）--外连接的消除（一）
- ubuntu16.04中将python3设置为默认
- MySQL查询优化器--逻辑查询优化技术（五）--外连接的消除（二）
- Jquery.Pagination分页插件的学习
- windows10下安装ubuntu16.04 双系统
- tensorflow学习笔记三：实例数据下载与读取
- PostgreSQL查询计划中的路径-BitmapHeapPath-辨析（二）
- 深度学习框架的评估与比较
- MySQL查询优化器--逻辑查询优化技术（六）--条件化简（表达式处理）
- Deep learning与Neural Network
- MySQL查询优化器--逻辑查询优化技术（七）--等价谓词重写
