2023-05-15 mysql-join-思考

摘要:

记录mysql数据库里对于join的思考

思考:

1. 从磁盘IO角度，复杂度可以分为一趟算法和两趟算法, 至于多趟算法则被简化为两趟算法的合成
2. 从集合论的角度,可以分为外连接和内连接, 也就是left/right join和inner join
3. 外连接不能改变表的顺序, 但是内连接可以. 外连接改变表的顺序将破坏语义
4. 如果确定外连接查询的数据不存在null, 那么采用空值拒绝策略, 可以将外连接转换为内连接, 有助于后续优化. 可以参考函数 simpy_join
5. 从filter的角度, 过滤条件可以分为在join过程中和在join结束后, 也就是说谓词出现在on和出现在where, 是涉及的过滤的时机不一样
6. 即使是多表的join, 在mysql中也是会以两表join进行展开运算
7. 应该将小表放在前边, 作为驱动表. 
   1. 直观上是认为驱动表都在内存中, 所以小表作为驱动表, 但是实际情况要涉及到代价计算
   2. 假设两表数据量是n和m, 那么无论谁做驱动表, 都必须完成n+m次的磁盘io加载数据, 但是驱动表是需要额外存放在内存中, 那么小表作为驱动表, 磁盘io数量最少
8. 最简单粗暴和广为人知的join策略是nested loop, 可以理解成两层for循环, 驱动表在外层, 被驱动表在内层
9. mysql查询优化器的核心就是索引, 只有当没有索引的情况，才开始做nested loop
10. 对nested loop的改进有block nestd loop, 采用批量物化
11. 到了mysql8.0, nested loop的一个重大改进是hash join
12. 对于外连接, 条件出现在on和where有本质的不同, 涉及语义的变化
13. 对于内连接, 条件出现在on和where的执行结构是等价的
    1. 从而尽量的利用空值拒绝等特点将外连接转换成内连接
    2. 方便对条件进行下推和上推
14. 内连接可以不带on条件, 此时内连接的结果为两表的笛卡尔积
15. 外连接必须带on条件