Mysql体系化之多表JOIN问题

发布时间：2024-01-01 02:42:39 所属栏目：MySql教程来源：DaWei

导读： 我们知道，SQL允许用WHERE来写JOIN运算的过滤条件（回顾原始的笛卡尔积式的定义），很多程序员也习惯于这么写。

当JOIN表只有两三个的时候，那问题还不大，但如果JOIN表有七八个甚至十几

我们知道，SQL允许用WHERE来写JOIN运算的过滤条件（回顾原始的笛卡尔积式的定义），很多程序员也习惯于这么写。

当JOIN表只有两三个的时候，那问题还不大，但如果JOIN表有七八个甚至十几个的时候，漏写一个JOIN条件是很有可能的。而漏写了JOIN条件意味着将发生多对多的完全叉乘，而这个SQL却可以正常执行，会有以下两点危害：
一方面计算结果会出错:回忆一下以前说过的，发生多对多JOIN时，大概率是语句写错了
另一方面，如果漏写条件的表很大，笛卡尔积的规模将是平方级的，这极有可能把数据库直接“跑死”！

简化JOIN运算好处：
一个直接的效果显然是让语句书写和理解更容易

外键属性化、同维表等同化和子表集合化方案直接消除了显式的关联运算，也更符合自然思维

维度对齐则可让程序员不再关心表间关系，降低语句的复杂度

简化JOIN语法的好处不仅在于此，还能够降低出错率，采用简化后的JOIN语法，就不可能发生漏写JOIN条件的情况了。因为对JOIN的理解不再是以笛卡尔积为基础，而且设计这些语法时已经假定了多对多关联没有业务意义，这个规则下写不出完全叉乘的运算。

对于多个子表分组后与主表对齐的运算，在SQL中要写成多个子查询的形式。但如果只有一个子表时，可以先JOIN再GROUP，这时不需要子查询。有些程序员没有仔细分析，会把这种写法推广到多个子表的情况，也先JOIN再GROUP，可以避免使用子查询，但计算结果是错误的。

使用维度对齐的写法就不容易发生这种错误了，无论多少个子表，都不需要子查询，一个子表和多个子表的写法完全相同。

关联查询
重新看待JOIN运算，最关键的作用在于实现关联查询。

当前BI产品是个热门，各家产品都宣称能够让业务人员拖拖拽拽就完成想要的查询报表。但实际应用效果会远不如人意，业务人员仍然要经常求助于IT部门。造成这个现象的主要原因在于大多数业务查询都是有过程的计算，本来也不可能拖拽完成。但是，也有一部分业务查询并不涉及多步过程，而业务人员仍然难以完成。

这就是关联查询，也是大多数BI产品的软肋。在之前的文章中已经讲过为什么关联查询很难做，其根本原因就在于SQL对JOIN的定义过于简单。

结果，BI产品的工作模式就变成先由技术人员构建模型，再由业务人员基于模型进行查询。而所谓建模，就是生成一个逻辑上或物理上的宽表。也就是说，建模要针对不同的关联需求分别实现，我们称之为按需建模，这时候的BI也就失去敏捷性了。

但是，如果我们改变了对JOIN运算的看法，关联查询可以从根本上得到解决。回忆前面讲过的三种JOIN及其简化手段，我们事实上把这几种情况的多表关联都转化成了单表查询，而业务用户对于单表查询并没有理解障碍。无非就是表的属性（字段）稍复杂了一些：可能有子属性（外键字段指向的维表并引用其字段），子属性可能还有子属性（多层的维表），有些字段取值是集合而非单值（子表看作为主表的字段）。发生互相关联甚至自我关联也不会影响理解（前面的中国经理的美国员工例子就是互关联），同表有相同维度当然更不碍事（各自有各自的子属性）。

在这种关联机制下，技术人员只要一次性把数据结构（元数据）定义好，在合适的界面下（把表的字段列成有层次的树状而不是常规的线状），就可以由业务人员自己实现JOIN运算，不再需要技术人员的参与。数据建模只发生于数据结构改变的时刻，而不需要为新的关联需求建模，这也就是非按需建模，在这种机制支持下的BI才能拥有足够的敏捷性。

Mysql体系化之JOIN运算实例分析

外键预关联
我们再来研究如何利用JOIN的特征实现性能优化，这些内容的细节较多，我们挑一些易于理解的情况来举例，更完善的连接提速算法可以参考乾学院上的《性能优化》图书和SPL学习资料中的性能优化专题文章。
全内存下外键关联情况
设有两个表：

customer 客户信息表
key 编号
name 名称
city 城市
...

orders 订单表
seq 序号
date 日期
custkey 客户编号
amount 金额
...

其中orders表中的custkey是指向customer表中key字段的外键，key是customer表的主键。

现在我们各个城市的订单总额（为简化讨论，就不再设定条件了），用SQL写出来：
SELECT customer.city, SUM(orders.amount)
FROM orders
JOIN customer ON orders.custkey=customer.key
GROUP BY customer.city

数据库一般会使用HASH JOIN算法，需要分别两个表中关联键的HASH值并比对。

我们用前述的简化的JOIN语法（DQL）写出这个运算：
SELECT custkey.city, SUM(amount)
FROM orders
GROUP BY custkey.city

这个写法其实也就预示了它还可以有更好的优化方案，下面来看看怎样实现。

如果所有数据都能够装入内存，我们可以实现外键地址化。

将事实表orders中的外键字段custkey，转换成维表customer中关联记录的地址，即orders表的custkey的取值已经是某个customer表中的记录，那么就可以直接引用记录的字段进行计算了。

用SQL无法描述这个运算的细节过程，我们使用SPL来描述、并用文件作为数据源来说明计算过程：

A
1   =file(“customer.btx”).import@b()
2   >A1.keys@i(key)
3   =file(“orders.btx”).import@b()
4   >A3.switch(custkey,A1)
5   =A3.groups(custkey.city;sum(amount))
A1读出客户表，A2为客户表设置主键并建立索引。

A3读出订单表，A4的动作是将A3的外键字段custkey转换成对应的A1的记录，执行完后，订单表字段custkey将变成客户表的某条记录。A2建了索引能让switch更快，因为通常事实表远大于维表，这个索引能被复用很多次。

A5就可以执行分组汇总了，遍历订单表时，由于custkey字段取值现在已经是一条记录，那么可以直接用.操作符引用其字段了，custkey.city就可以正常执行。

完成A4中的switch动作之后，内存中事实表A3的custkey字段存储内容已经是维表A1的某条记录的地址，这个动作即称为外键地址化。这时候引用维表字段时，可以直接取出，而不需要再用外键值在A1中查找，相当于在常数时间内就能取到维表的字段，避免了HASH值计算和比对。

不过，A2建主键索引一般也会用HASH办法，对key计算HASH值，A4转换地址时也是计算custkey的HASH值与A2的HASH索引表对比。如果只做一次关联运算，地址化的方案和传统HASH分段方案的计算量基本上一样，没有根本优势。

但不同的是，如果数据能在内存中放下，这个地址一旦转换之后可以复用，也就是说A1到A4只要做一次，下次再做关于这两个字段的关联运算时就不必再计算HASH值和比对了，性能就能大幅提高。

内存数据库是当前比较火热的技术，但上述分析表明，采用SQL模型的内存数据库在JOIN运算上是很难快起来的！

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!