开心一刻
楼主: 心都让你吓出来了!
狮王: 淡定, 打个小喷嚏而已
前情回顾
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程 (一) 中, 我们讲到了 3 种联表算法: SNL,BNL 和 INL, 了解了数据的查询方式是 one by one, 联表方式也是 one by one ; 并谈到了 ON 和 WHERE, 对下图中所说的提出了质疑
认为 ON 和 WHERE 的生效时机有待商榷; 此时楼主开始了欠大家的账
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程 (二) 中对联表算法进行了补充, 详细介绍了 MRR 和 BKA, 但还是未介绍 ON 和 WHERE, 楼主依旧欠着大家的账, 内心涌满了愧疚
终于在今天, 楼主痛定思痛, 决定将这笔账还上; 此刻楼主的内心独白是这样的
此时各位看官的内心肯定嘀咕着: 你特么欠账欠的这么义愤填膺 ? 不过我好喜欢
咳咳, 闲话不多说, 进入我们今天的正题
SQL 执行顺序
SQL 的执行顺序相信大家多少有所了解, 上网一搜也很快就能找到答案
除了 WITH 用的比较少之外, 其他都比较常用, 相信大家对上面的执行顺序也没有什么疑问; 我们重点关注下 JOIN,ON 和 WHERE
那么 WHERE 是不是一定是在 ON 之后生效了 ? 我们带着这个疑问往下看
ON 和 WHERE 的常规区别
on 针对的关联条件, 是表与表之间通过哪些列, 以什么条件进行关联, 而 where 针对的是过滤条件; 两者从概念上来讲是不同的
另外 on 一定是与 join 一并使用的, join 会添加外部行, 并将外部行中被驱动表的字段填充 null , 而 where 进行过滤的时候, 只有逻辑判断为 true 的记录才会保留, 逻辑值为 false 和 unknown 的记录都会过滤掉(更多详情: 神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !); 两者得到的结果会有所不同
上面说的可能有些抽象, 我们结合具体示例来看; MySQL 版本 5.7.21 , 准备表和初始数据
- create table tbl_a (a int primary key, b int, c int, d int, e varchar(50));
- insert into tbl_a values (4,3,1,1,'a');
- insert into tbl_a values (1,1,1,2,'d');
- insert into tbl_a values (8,8,7,8,'h');
- insert into tbl_a values (2,2,1,2,'g');
- insert into tbl_a values (5,2,2,5,'e');
- insert into tbl_a values (3,3,2,1,'c');
- insert into tbl_a values (7,4,0,5,'b');
- insert into tbl_a values (6,5,2,4,'f');
- create table tbl_b like tbl_a;
- insert into tbl_b SELECT * from tbl_a;
- insert into tbl_a values (9,9,9,9,'9');
- insert into tbl_b values (10,10,10,10,'10');
- View Code
我们先来看看 left join(right join 类似)
- SELECT * FROM tbl_a a LEFT JOIN tbl_b b ON a.a = b.a AND a.b = b.b; /*query_on*/
- SELECT * FROM tbl_a a LEFT JOIN tbl_b b ON a.a = b.a WHERE a.b = b.b; /*query_where*/
我们可以看到:
语句 query_on 返回了 tbl_a 中的全部记录, tbl_b 无对应记录的字段值填成 NULL, 这是因为 join 会添加外部行, 将 tbl_a 有而 tbl_b 中没有的记录添加到结果集
语句 query_where 返回的是 8 行. 因为最后的一行, 在表 tbl_b 中没有匹配的字段, 所以 where 后的 b.b 的值是 NULL, 而 a.b 的值是 9, 那么 where 9 = NULL 的结果是 unknown 而不是 true, 因此这条记录不能作为结果集的一部分
我们再来看看 inner join
- SELECT * FROM tbl_a a INNER JOIN tbl_b b ON a.a = b.a AND a.b = b.b; /*query_on*/
- SELECT * FROM tbl_a a INNER JOIN tbl_b b ON a.a = b.a WHERE a.b = b.b; /*query_where*/
我们可以看到, 执行结果是一样的, inner join 查询的就是驱动表与被驱动表同时存在的记录, 所以过滤条件不管放在 ON 里, 还是放在 WHERE 里, 执行结果是一样的
ON 和 WHERE 的生效时机
ON 后的关联条件与 WHERE 后的过滤条件, 这两者的执行顺序是否如 SQL 执行顺序图中说的那样, ON 一定先与 WHERE ?
问题先放着, 我们以 left join 为例, 来看看 4 个案例, 也许从中能找到我们想要的答案
1, 左表与右表都没二级索引
刚好上面的 tbl_a 和 tbl_b 满足条件, 我们来看看 SQL 的执行计划
- EXPLAIN SELECT * FROM tbl_a a
- LEFT JOIN tbl_b b
- ON a.b = b.b AND a.c = b.c
- WHERE a.b>= 2 AND a.b <10 AND a.c> 0
- AND a.d != 1 AND a.e != 'a'
- View Code
驱动表是 tbl_a, 这个相信大家没问题, 我们重点看下 type 和 Extra
type: 上面的 ALL 表示全表扫描 a 表, 下面的 ALL 表示全表关联, a 表中每一条满足条件的记录都会与 b 表中全部 9 条记录逐条进行关联
Extra:Using where 表示要进行 WHERE 条件过滤, Using join buffer (Block Nested Loop) 表示用到了 BNL
这条 SQL 的执行流程应该是这样的:
此时大家看出什么了没 ? ON 后的关联条件是在 WHERE 后的过滤条件之前生效的吗 ?
这个案例不太常见, 因为表没有二级索引, 我们接着往下看看有二级索引的情况
2, 左表有二级索引, 右表无二级索引
我们在 tbl_a 建一个组合索引 create index idx_bcd on tbl_a(b, c, d); , 然后往 tbl_a 和 tbl_b 中各插入 10W 条记录, 我们再来看执行计划
上图中红框标记的字段重点关注下, 不知道字段含义的小伙伴, 可以去翻翻我之前关于 explain 的博客
那么此时 SQL 的执行流程应该是这样的:
就步骤 1 与 示例 1 中的步骤 1 不同, 其余 2 步是一样的
此时 WHERE 后的过滤条件的生效时机也是早于 ON 后的关联条件的
3, 左表无二级索引, 右表有二级索引
将 tbl_b 作为左表, tbl_a 作为右表, 我们来看效果
此时 SQL 的执行流程应该是这样的:
此时 ON 后的关联条件的生效时机是早于 WHERE 后的过滤条件的
4, 左表与右表都有二级索引
我们在 tbl_b 表上建一个组合索引 create index idx_bcd on tbl_b(b, c, d); 我们来看看 SQL 的执行计划
此时 SQL 的执行流程应该是这样的:
先是 WHERE 中的 Index Filter 条件生效, 然后是 ON 后的关联条件生效, 最后是 WHERE 中的 Table Filter 生效, 关联条件的生效时间穿插在过滤条件的生效时间中
自此, 关于 ON 和 WHERE 的生效时机, 你清楚了吗 ?
总结
1, 关联博客
如果没有读楼主的前几篇博客, 那么有些概念可能不理解, 楼主把相关联的博客都列一下
神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !
神奇的 SQL 之 MySQL 执行计划 → EXPLAIN, 让我们了解 SQL 的执行过程!
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(二)
神奇的 SQL 之 WHERE 条件的提取与应用
神奇的 SQL 之 ICP → 索引条件下推
对相关概念不了解的可以去对应的博客查阅
2,ON 和 WHERE
两者好区分, 也容易混淆, 他们在概念上就做了明确区分, 但是又可以做概念之外的事, 所以用着用着就开始混淆了
楼主推荐: 严格按他们的概念来处理, ON 后跟关联条件, 其他的都放到 WHERE 后做过滤条件; 尽量保证 SQL 语义清晰
至于他两的生效时机, 需要结合表结构, 以及具体的 SQL 来分析, 而不是 ON 一定先于 WHERE
参考
What is the meaning of filtered in MySQL explain?
MySQL 的 server 层和存储引擎层是如何交互的 https://juejin.im/post/5cef832e5188257c6b516d1f
来源: https://www.cnblogs.com/youzhibing/p/12097397.html