Mysql体系化之JOIN运算实例剖析
发布时间：2023-06-12 14:41:05 所属栏目：MySql教程 来源：
导读：这篇文章主要介绍了Mysql体系化之JOIN运算实例分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇Mysql体系化之JOIN运算实例分析文章都会有所收获，下面我们一起来看看吧。
Mys
Mys
|
这篇文章主要介绍了Mysql体系化之JOIN运算实例分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇Mysql体系化之JOIN运算实例分析文章都会有所收获，下面我们一起来看看吧。 Mysql体系化之JOIN运算实例分析 SQL中的JOIN SQL是如何理解JOIN运算 SQL对JOIN的定义 两个集合（表）做笛卡尔积后再按某种条件过滤，写出来的语法就是A JOIN B ON …。 理论上讲，笛卡尔积的结果集应该是以两个集合成员构成的二元组作为成员，不过由于SQL中的集合也就是表，其成员总是有字段的记录，而且也不支持泛型数据类型来描述成员为记录的二元组，所以就简单地把结果集处理成两表记录的字段合并后构成的新记录的集合。 这也是JOIN一词在英语中的原意（即把两个记录的字段连接起来），并没有乘法（笛卡尔积）的意思。不过，把笛卡尔积成员理解成二元组还是合并字段的记录，并不影响我们后续的讨论。 JOIN定义 JOIN的定义中并没有约定过滤条件的形式，理论上，只要结果集是两个源集合笛卡尔积的子集，都是合理的JOIN运算。 例子：假设集合A={1,2},B={1,2,3}，A JOIN B ON A<B的结果就是{(1,2),(1,3),(2,3)}；A JOIN B ON A=B的结果是{(1,1),(2,2)}。 JOIN分类 我们把过滤条件为等式的称为等值JOIN，而不是等值连接的情况则称为非等值JOIN。这两个例子中，前者是非等值JOIN，后者是等值JOIN。 等值JOIN 条件可能由多个有AND关系的等式构成，语法形式A JOIN B ON A.ai=B.bi AND …，其中ai和bi分别是A和B的字段。 有经验的程序员都知道，现实中绝大多数JOIN都是等值JOIN，非等值JOIN要少见得多，而且大多数情况都可以转换成等值JOIN来处理，所以我们在这里重点讨论等值JOIN，并且后续讨论中也主要使用表和记录而不是集合和成员来举例。 空值处理规则下分类 根据对空值的处理规则，严格的等值JOIN又称为INNER JOIN，还可以再衍生出LEFT JOIN和FULL JOIN，共有三种情况（RIGHT JOIN可以理解为LEFT JOIN的反向关联，不再单独作为一种类型）。 谈论JOIN时一般还会根据两个表中关联记录（也就是满足过滤条件的二元组）的数量分为一对一、一对多、多对一以及多对多这几种情况，这些常规术语在SQL和数据库资料中都有介绍，这里就不再赘述了。 JOIN的实现 笨办法 最容易想到的简单办法就是按照定义做硬遍历，不区分等值JOIN和非等值JOIN。设表A有n条记录，B有m条记录，要计算A JOIN B ON A.a=B.b时，硬遍历的复杂度会是nm，即要进行nm次过滤条件的计算。 显然这种算法会比较慢。不过，支持多数据源的报表工具中有时就是用这种慢办法实现关联的，因为在报表中数据集的关联关系（也就是JOIN中的过滤条件）会拆散定义在单元格的运算式中，已经看不出是多个数据集之间的JOIN运算，也就只能用遍历方法去计算这些关联表达式了。 数据库对于JOIN优化 对于等值JOIN，数据库一般会采用HASH JOIN算法。即将关联表的记录按其关联键（过滤条件中对应相等的字段，即A.a和B.b)的HASH值分成若干组，将相同HASH值的记录分到一组。如HASH值范围是1…k，则将A和B表都分成k个子集A1,…,Ak和B1,…,Bk。Ai中记录的关联键a的HASH值是i，Bi中记录的关联键b的HASH值也是i，然后，只要分别在Ai和Bi之间做遍历连接就可以了。 因为HASH不同时字段值也必然不同，i!=j时，Ai中记录不可能和Bj中记录发生关联。如果Ai的记录数是ni，Bi的记录数是mi，则过滤条件的计算次数为SUM(ni*mi)，最平均的情况时，ni=n/k，mi=m/k，则总的复杂度只有原始硬遍历手段的1/k，能有效地提高运算性能！ 所以，多数据源关联报表要提速的话，也需要在数据准备阶段做好关联，否则数据量稍大时性能就会急剧下降。 不过，HASH函数并不总能保证平均分拆，在运气不好的时候可能会发生某一组特别大的情况，那样性能提升效果就会差很多。而且还不能使用太复杂的HASH函数，否则计算HASH的时间又变多了。 当数据量大到超过内存时，数据库会使用HASH分堆的方法，算是HASH JOIN算法的推广。遍历A表和B表，将记录按关联键的HASH值拆分成若干小子集缓存到外存中，称为分堆。然后再在对应的堆之间做内存JOIN运算。同样的道理，HASH值不同时键值也必然不同，关联一定发生在对应的堆之间。这样就把大数据的JOIN转换成若干小数据的JOIN了。 但是类似地，HASH函数存在运气问题，有可能会发生某个分堆还特别大而无法装入内存，这时候就可能要进行二次HASH分堆，即换一个HASH函数对这组太大的分堆再做一次HASH分堆算法。所以，外存JOIN运算有可能出现多次缓存的现象，其运算性能有一定的不可控性。 分布式系统下JOIN 分布式系统下做JOIN也是类似的，根据关联键的HASH值将记录分发到各个节点机上，称为Shuffle动作，然后再分别做单机的JOIN。 当节点比较多的时候，造成的网络传输量带来的延迟会抵消多机分摊任务得到的好处，所以分布式数据库系统通常有个节点数的极限，达到极限后，更多的节点并不能获得更好的性能。 等值JOIN的剖析 三种等值JOIN： 外键关联 表A的某个字段和表B的主键字段关联（所谓字段关联，就是前一节说过的在等值JOIN的过滤条件中要对应相等的字段）。A表称为事实表，B表称为维表。A表中与B表主键关联的字段称为A指向B的外键，B也称为A的外键表。 这里说的主键是指逻辑上的主键，也就是在表中取值唯一、可以用于唯一某条记录的字段（组），不一定在数据库表上建立过主键。 外键表是多对一的关系，且只有JOIN和LEFT JOIN，而FULL JOIN非常罕见。 典型案例：商品交易表和商品信息表。 显然，外键关联是不对称的。事实表和维表的位置不能互换。 同维表 表A的主键与表B的主键关联，A和B互称为同维表。同维表是一对一的关系，JOIN、LEFT JOIN和FULL JOIN的情况都会有，不过在大多数数据结构设计方案中，FULL JOIN也相对少见。 典型案例：员工表和经理表。 同维表之间是对称的，两个表的地位相同。同维表还构成是等价关系，A和B是同维表，B和C是同维表，则A和C也是同维表。 主子表 表A的主键与表B的部分主键关联，A称为主表，B称为子表。主子表是一对多的关系，只有JOIN和LEFT JOIN，不会有FULL JOIN。 典型案例：订单和订单明细。 主子表也是不对称的，有明确的方向。 在SQL的概念体系中并不区分外键表和主子表，多对一和一对多从SQL的观点看来只是关联方向不同，本质上是一回事。确实，订单也可以理解成订单明细的外键表。但是，我们在这里要把它们区分开，将来在简化语法和性能优化时将使用不同的手段。 我们说，这三种JOIN已经涵盖了绝大多数等值JOIN的情况，甚至可以说几乎全部有业务意义的等值JOIN都属于这三类，把等值JOIN限定在这三种情况之中，几乎不会减少其适应范围。 仔细考察这三种JOIN，我们发现所有关联都涉及主键，没有多对多的情况，是不是可以不考虑这种情况？ 是的！多对多的等值JOIN几乎没有业务意义。 如果两个表JOIN时的关联字段没有涉及到任何主键，那就会发生多对多的情况，而这种情况几乎一定还会有一个规模更大的表把这两个表作为维表关联起来。比如学生表和科目表在JOIN时，会有个成绩表把学生表和科目表作为维表，单纯只有学生表和科目表的JOIN没有业务意义。 当写SQL语句时发现多对多的情况，那大概率是这个语句写错了！或者数据有问题！这条法则用于排除JOIN错误很有效。 不过，我们一直在说“几乎”，并没有用完全肯定的说法，也就是说，多对多在非常罕见的情况下也会业务意义。可举一例，用SQL实现矩阵乘法时会发生多对多的等值JOIN，具体写法读者可以自行补充。 笛卡尔积再过滤这种JOIN定义，确实非常简单，而简单的内涵将得到更大的外延，可以把多对多等值JOIN甚至非等值JOIN等都包括进来。但是，过于简单的内涵无法充分体现出最常见等值JOIN的运算特征。这会导致编写代码和实现运算时就不能利用这些特征，在运算较为复杂时（涉及关联表较多以及有嵌套的情况），无论是书写还是优化都非常困难。而充分利用这些特征后，我们就能创造出更简单的书写形式并获得更高效的运算性能，后面的内容中将会逐步加以说明。 与其为了把罕见情况也被包括进来而把运算定义为更通用的形式，还不如把这些情况定义成另一种运算更为合理。 JOIN的语法简化 如何利用关联都涉及主键这个特征来简化JOIN的代码书写？ 外键属性化 例子，设有如下两个表： employee 员工表 id 员工编号 name 姓名 nationality 国籍 department 所属部门 department 部门表 id 部门编号 name 部门名称 manager 部门经理 employee表和department表的主键都是其中的id字段，employee表的department字段是指向department表的外键，department表的manager字段又是指向employee表的外键（因为经理也是个员工）。这是很常规的表结构设计。 现在我们想问一下：哪些美国籍员工有一个中国籍经理？用SQL写出来是个三表JOIN的语句： SELECT A.* FROM employee A JOIN department B ON A.department=B.id JOIN employee C ON B.manager=C.id WHERE A.nationality='USA' AND C.nationality='CHN' 首先要FROM employee用于获取员工信息，然后这个employee表要和department做JOIN获取员工的部门信息，接着这个department表还要再和employee表JOIN要获取经理的信息，这样employee表需要两次参与JOIN，在SQL语句中要为它起个别名加以区分，整个句子就显得比较复杂难懂。 如果我们把外键字段直接理解成它关联的维表记录，就可以换一种写法： SELECT * FROM employee WHERE nationality='USA' AND department.manager.nationality='CHN' 当然，这不是标准的SQL语句了。 第二个句子中粗体部分表示当前员工的“所属部门的经理的国籍”。我们把外键字段理解成维表的记录后，维表的字段被理解为外键的属性，department.manager即是“所属部门的经理”，而这个字段在department中仍然是个外键，那么它对应的维表记录字段可以继续理解为它的属性，也就会有department.manager.nationality，即“所属部门的经理的国籍”。 外键属性化:这种对象式的理解方式即为外键属性化，显然比笛卡尔积过滤的理解方式要自然直观得多。外键表JOIN时并不会涉及到两个表的乘法，外键字段只是用于找到维键表中对应的那条记录，完全不会涉及到笛卡尔积这种有乘法特性的运算。 我们前面约定，外键关联时时维表中关联键必须是主键，这样，事实表中每一条记录的外键字段关联的维表记录就是唯一的，也就是说employee表中每一条记录的department字段唯一关联一条department表中的记录，而department表中每一条记录的manager字段也唯一关联一条employee表中的记录。这就保证了对于employee表中的每一条记录，department.manager.nationality都有唯一的取值，可以被明确定义。 但是，SQL对JOIN的定义中并没有主键的约定，如果基于SQL的规则，就不能认定与事实表中外键关联的维表记录有唯一性，有可能发生与多条记录关联，对于employee表的记录来讲，department.manager.nationality没有明确定义，就不能使用了。 事实上，这种对象式写法在高级语言（如C，Java）中很常见，在这类语言中，数据就是按对象方式存储的。employee表中的department字段取值根本就是一个对象，而不是编号。其实许多表的主键取值本身并没有业务意义，仅仅是为了区分记录，而外键字段也仅仅是为了找到维表中的相应记录，如果外键字段直接是对象，就不需要再通过编号来标识了。不过，SQL不能支持这种存储机制，还要借助编号。 我们说过外键关联是不对称的，即事实表和维表是不对等的，只能基于事实表去找维表字段，而不会有倒过来的情况。 同维表等同化 同维表的情况相对简单，还是从例子开始，设有两个表： employee 员工表 id 员工编号 name 姓名 salary 工资 ... manager 经理表 id 员工编号 allowance 岗位津贴 .... 两个表的主键都是id，经理也是员工，两表共用同样的员工编号，经理会比普通员工多一些属性，另用一个经理表来保存。 现在我们要统计所有员工（包括经理）的总收入（加上津贴）。用SQL写出来还是会用到JOIN： SELECT employee.id, employee.name, employy.salary+manager.allowance FROM employee LEFT JOIN manager ON employee.id=manager.id 而对于两个一对一的表，我们其实可以简单地把它们看成一个表： SELECT id,name,salary+allowance FROM employee 类似地，根据我们的约定，同维表JOIN时两个表都是按主键关联的，相应记录是唯一对应的，salary+allowance对employee表中每条记录都是唯一可计算的，不会出现歧义。这种简化方式称为同维表等同化。 同维表之间的关系是对等的，从任何一个表都可以引用到其它同维表的字段。 子表集合化 订单&订单明细是典型的主子表： Orders 订单表 id 订单编号 customer 客户 date 日期 ... OrderDetail 订单明细 id 订单编号 no 序号 product 订购产品 price 价格 ... Orders表的主键是id，OrderDetail表中的主键是(id,no)，前者的主键是后者的一部分。 现在我们想计算每张订单的总金额。用SQL写出来会是这样： SELECT Orders.id, Orders.customer, SUM(OrderDetail.price) FROM Orders JOIN OrderDetail ON Orders.id=OrderDetail.id GROUP BY Orders.id, Orders.customer 要完成这个运算，不仅要用到JOIN，还需要做一次GROUP BY，否则选出来的记录数太多。 如果我们把子表中与主表相关的记录看成主表的一个字段，那么这个问题也可以不再使用JOIN以及GROUP BY： SELECT id, customer, OrderDetail.SUM(price) FROM Orders 与普通字段不同，OrderDetail被看成Orders表的字段时，其取值将是一个集合，因为两个表是一对多的关系。所以要在这里使用聚合运算把集合值计算成单值。这种简化方式称为子表集合化。 这样看待主子表关联，不仅理解书写更为简单，而且不容易出错。 假如Orders表还有一个子表用于记录回款情况： OrderPayment 订单回款表 id 订单编号 date 回款日期 amount 回款金额 .... 我们现在想知道那些订单还在欠钱，也就是累计回款金额小于订单总金额的订单。 简单地把这三个表JOIN起来是不对的，OrderDetail和OrderPayment会发生多对多的关系，这就错了（回忆前面提过的多对多大概率错误的说法）。这两个子表要分别先做GROUP，再一起与Orders表JOIN起来才能得到正确结果，会写成子查询的形式： SELECT Orders.id, Orders.customer,A.x,B.y FROM Orders LEFT JOIN ( SELECT id,SUM(price) x FROM OrderDetail GROUP BY id ) A ON Orders.id=A.id LEFT JOIN ( SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B ON Orders.id=B.id WHERE A.x>B.y 如果我们继续把子表看成主表的集合字段，那就很简单了： SELECT id,customer,OrderDetail.SUM(price) x,OrderPayment.SUM(amount) y FROM Orders WHERE x>y 这种写法也不容易发生多对多的错误。 主子表关系是不对等的，不过两个方向的引用都有意义，上面谈了从主表引用子表的情况，从子表引用主表则和外键表类似。 我们改变对JOIN运算的看法，摒弃笛卡尔积的思路，把多表关联运算看成是稍复杂些的单表运算。这样，相当于把最常见的等值JOIN运算的关联消除了，甚至在语法中取消了JOIN关键字，书写和理解都要简单很多。 维度对齐语法 我们再回顾前面的双子表例子的SQL： SELECT Orders.id, Orders.customer, A.x, B.y FROM Orders LEFT JOIN (SELECT id,SUM(price) x FROM OrderDetail GROUP BY id ) A ON Orders.id=A.id LEFT JOIN (SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B ON Orders.id=B.id WHERE A.x > B.y 那么问题来了，这显然是个有业务意义的JOIN，它算是前面所说的哪一类呢？ 这个JOIN涉及了表Orders和子查询A与B，仔细观察会发现，子查询带有GROUP BY id的子句，显然，其结果集将以id为主键。这样，JOIN涉及的三个表（子查询也算作是个临时表）的主键是相同的，它们是一对一的同维表，仍然在前述的范围内。 但是，这个同维表JOIN却不能用前面说的写法简化，子查询A,B都不能省略不写。 可以简化书写的原因：我们假定事先知道数据结构中这些表之间的关联关系。用技术术语的说法，就是知道数据库的元数据（metadata）。而对于临时产生的子查询，显然不可能事先定义在元数据中了，这时候就必须明确指定要JOIN的表（子查询）。 不过，虽然JOIN的表（子查询）不能省略，但关联字段总是主键。子查询的主键总是由GROUP BY产生，而GROUP BY的字段一定要被选出用于做外层JOIN；并且这几个子查询涉及的子表是互相独立的，它们之间不会再有关联计算了，我们就可以把GROUP动作以及聚合式直接放到主句中，从而消除一层子查询： SELECT Orders.id, Orders.customer, OrderDetail.SUM(price) x, OrderParyment.SUM(amount) y FROM Orders LEFT JOIN OrderDetail GROUP BY id LEFT JOIN OrderPayment GROUP BY id WHERE A.x > B.y 这里的JOIN和SQL定义的JOIN运算已经差别很大，完全没有笛卡尔积的意思了。而且，也不同于SQL的JOIN运算将定义在任何两个表之间，这里的JOIN，OrderDetail和OrderPayment以及Orders都是向一个共同的主键id对齐，即所有表都向某一套基准维度对齐。而由于各表的维度（主键）不同，对齐时可能会有GROUP BY，在引用该表字段时就会相应地出现聚合运算。OrderDetail和OrderPayment甚至Orders之间都不直接发生关联，在书写运算时当然就不用关心它们之间的关系，甚至不必关心另一个表是否存在。而SQL那种笛卡尔积式的JOIN则总要找一个甚至多个表来定义关联，一旦减少或修改表时就要同时考虑关联表，增大理解难度。 维度对齐：这种JOIN称即为维度对齐，它并不超出我们前面说过的三种JOIN范围，但确实在语法描述上会有不同，这里的JOIN不象SQL中是个动词，却更象个连词。而且，和前面三种基本JOIN中不会或很少发生FULL JOIN的情况不同，维度对齐的场景下FULL JOIN并不是很罕见的情况。 虽然我们从主子表的例子抽象出维度对齐，但这种JOIN并不要求JOIN的表是主子表（事实上从前面的语法可知，主子表运算还不用写这么麻烦），任何多个表都可以这么关联，而且关联字段也完全不必要是主键或主键的部分。 设有合同表，回款表和发票表： Contract 合同表 id 合同编号 date 签订日期 customer 客户 price 合同金额 ... Payment 回款表 seq 回款序号 date 回款日期 source 回款来源 amount 金额 ... Invoice 发票表 code 发票编号 date 开票日期 customer 客户 amount 开票金额 ... 现在想统计每一天的合同额、回款额以及发票额，就可以写成： SELECT Contract.SUM(price), Payment.SUM(amount), Invoice.SUM(amount) ON date FROM Contract GROUP BY date FULL JOIN Payment GROUP BY date FULL JOIN Invoice GROUP BY date 这里需要把date在SELECT后单独列出来表示结果集按日期对齐。 这种写法，不必关心这三个表之间的关联关系，各自写各自有关的部分就行，似乎这几个表就没有关联关系，把它们连到一起的就是那个要共同对齐的维度（这里是date）。 这几种JOIN情况还可能混合出现。 继续举例，延用上面的合同表，再有客户表和销售员表 Customer 客户表 id 客户编号 name 客户名称 area 所在地区 ... Sales 销售员表 id 员工编号 name 姓名 area 负责地区 ... 其中Contract表中customer字段是指向Customer表的外键。 现在我们想统计每个地区的销售员数量及合同额： SELECT Sales.COUNT(1), Contract.SUM(price) ON area FROM Sales GROUP BY area FULL JOIN Contract GROUP BY customer.area 维度对齐可以和外键属性化的写法配合合作。 这些例子中，最终的JOIN都是同维表。事实上，维度对齐还有主子表对齐的情况，不过相对罕见，我们这里就不深入讨论了。 另外，目前这些简化语法仍然是示意性，需要在严格定义维度概念之后才能相应地形式化，成为可以解释执行的句子。 我们把这种简化的语法称为DQL（Dimensional Query Languange），DQL是以维度为核心的查询语言。我们已经将DQL在工程上做了实现，并作为润乾报表的DQL服务器发布出来，它能将DQL语句翻译成SQL语句执行，也就是可以在任何关系数据库上运行。 对DQL理论和应用感兴趣的读者可以关注乾学院上发布的论文和相关文章。 解决关联查询 多表JOIN问题 我们知道，SQL允许用WHERE来写JOIN运算的过滤条件（回顾原始的笛卡尔积式的定义），很多程序员也习惯于这么写。 当JOIN表只有两三个的时候，那问题还不大，但如果JOIN表有七八个甚至十几个的时候，漏写一个JOIN条件是很有可能的。而漏写了JOIN条件意味着将发生多对多的完全叉乘，而这个SQL却可以正常执行，会有以下两点危害： 一方面计算结果会出错:回忆一下以前说过的，发生多对多JOIN时，大概率是语句写错了 另一方面，如果漏写条件的表很大，笛卡尔积的规模将是平方级的，这极有可能把数据库直接“跑死”！ 简化JOIN运算好处： 一个直接的效果显然是让语句书写和理解更容易 外键属性化、同维表等同化和子表集合化方案直接消除了显式的关联运算，也更符合自然思维 维度对齐则可让程序员不再关心表间关系，降低语句的复杂度 简化JOIN语法的好处不仅在于此，还能够降低出错率，采用简化后的JOIN语法，就不可能发生漏写JOIN条件的情况了。因为对JOIN的理解不再是以笛卡尔积为基础，而且设计这些语法时已经假定了多对多关联没有业务意义，这个规则下写不出完全叉乘的运算。 对于多个子表分组后与主表对齐的运算，在SQL中要写成多个子查询的形式。但如果只有一个子表时，可以先JOIN再GROUP，这时不需要子查询。有些程序员没有仔细分析，会把这种写法推广到多个子表的情况，也先JOIN再GROUP，可以避免使用子查询，但计算结果是错误的。 使用维度对齐的写法就不容易发生这种错误了，无论多少个子表 （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
