一句sum千行泪，笛卡尔积多坑人，mysql执行的先后顺序

我们每一个人都想要优化 SQL 语句，以便能够提升性能，但是，如果不了解其机制，可能就会事倍功半。我以一个简单的例子 ，来讲解SQL的部分机制。

今天在公司工作时，面临这样一个需求：

根据条件查询项目的预算金额。

查询要求：

1. 项目的id
2. 项目人员的类型

数据库表设计

数据库有这样的两张表，一张是项目表project，下图是简单裁剪的图：

一张是项目人员表，这张表记录的是某个项目涉及哪些类型的人员，人员类型(枚举)如下表所示：

因而，数据表项目人员（project_person）的的设计为：

查询条件

• 条件1：我们首先查询项目编号为167的项目

SELECT SUM(budgetary_amount) FROM zq_project WHERE is_deleted = 0 AND id=167

输出结果为 10

• 条件2：关联项目人员表，查找编号为167的项目

SELECT
    SUM(zp.budgetary_amount)
FROM
    zq_project zp
LEFT JOIN zq_project_person zpp ON(zpp.is_deleted =  0 AND zpp.project_id = zp.id)
WHERE zp.is_deleted = 0 AND zp.id=167

输出结果为 60

为什么会这样呢

为什么会出现上诉情况，当我们在做一对多的sum求和时，就出现了笛卡尔积的现象。我们查找出项目人员表中的项目编号为167的有多少条记录

SELECT * from zq_project_person zpp WHERE zpp.is_deleted = 0 and zpp.project_id = 167

输出结果如图所示：

由上图可知，一共有六条记录，也就是说，项目表中编号为167的这条记录对应着项目人员表中的6条记录，sum之后要计算6次，才变成60，比如下面的代码：

SELECT
    zp.id AS projectId,
    zp.budgetary_amount,
    zpp.id AS personId
FROM
    zq_project zp
LEFT JOIN zq_project_person zpp ON(zpp.is_deleted =  0 AND zpp.project_id = zp.id)
WHERE zp.is_deleted = 0 AND zp.id=167;

输出结果如图所示：

这就涉及到 mysql 的执行先后的顺序造成笛卡尔积的紊乱

在讲解mysql执行的先后顺序之前，我们了解一下left join的 on 和 where的区别。

left join 的on和where的区别

on中的是副表的条件，where会将left join转化为inner join格式的数据，这是过滤数据用的。

假设有这两张表，一张是商品表（goods表）,一张是商品分类表（goods_category），商品表的外键是商品分类表的主键。我们来做left join的测试

查找语句为：

SELECT
    *
FROM
    cce_goods cg
LEFT JOIN cce_goods_category cgc ON(cgc.is_deleted =  0 AND cgc.id = cg.goods_category_id)
WHERE
    cg.is_deleted = 0

查找结果如图所示：

你会发现，编号为1的商品分类的字段属性 is_deleted 的值明明是 1 ，而on之后的 is_deleted 的值为 0 ，这应该是筛选不出来了，但还是能筛选出来呢？这里就涉及到on的条件了。

• 首先，left join是并集，那么又是谁的并集？是主表和副表的并集。这时，主表和副表就有两种情况了，一种是主表的外键引用副表的主键，另一种就是主表的主键是副表的外键，那么，这就得分情况了。

• 针对第一种情况

  • 我们以商品和商品表为例子，显然，商品表是主键，引用副表商品分类表的外键。
  • 主表和副表进行笛卡尔积（主表的外键和副表的主键进行匹配）得到一张临时表，临时表中存储主表和副表的字段属性。这时，以主表为主，副表为辅，即便副表没有数据，其也还会展示副表的字段。
  • 所以，编号为1的商品分类副表条件不满足，也就是没有满足的数据，因而，就把商品分类的字段属性为空。
  • 换个角度来看，如果我们把 WHERE cg.is_deleted = 0 这个条件去掉，你会发现会有很多数据出来。 筛选条件where在left join之后，它的优先级低于left join。
  • 假如，我们把cgc.is_deleted = 0 改成为 cgc.is_deleted = 1，你会发现神奇的一幕，如图所示：

你会发现，这是商品分类的字段属性是有值的，因为，副表的条件满足了，能拿到副表中的字段属性值。

如果我们把left join 改成inner join ，而cgc.is_deleted = 0 不变，这又不一样了，如代码所示：

SELECT
    *
FROM
    cce_goods cg
INNER JOIN cce_goods_category cgc ON(cgc.is_deleted =  0 AND cgc.id = cg.goods_category_id)
WHERE
    cg.is_deleted = 0

这样，上面的两条数据也没了，因为，inner join 是主表和副表的交集，主表和副表的条件是平行条件，具有同样的权重，也就是说同时满足主副表的条件，才能出现数据。

再假如，我们cgc.is_deleted = 0放到外面，如代码所示：

SELECT
    *
FROM
    cce_goods cg
INNER JOIN cce_goods_category cgc ON(cgc.id = cg.goods_category_id)
WHERE
    cg.is_deleted = 0 AND cgc.is_deleted =  0

这样，也就把left join 隐性成了 inner join了，主表和副表的条件也是平行条件，具有同样的权重。

• 针对第二种情况

  1、 以项目和项目人员来看，项目是主表，项目人员是副表，目前有三条没被删除的记录，如图所示：

  

  2、 我们来执行以下的查询语句，如代码所示：

SELECT
    zp.id AS projectId,
    zp.budgetary_amount,
    zpp.id AS personId
FROM
    zq_project zp
LEFT JOIN zq_project_person zpp ON(zpp.is_deleted =  0 AND zpp.project_id = zp.id)
WHERE zp.is_deleted = 0 AND zp.id=167;

目前只有三条记录，其他的五条记录没有展示，这是为什么呢？这个只能意会，无法言传。就比如 java 中的对象，类Project对象是类ProjectPerson的成员属性，我们能在ProjectPerson对象李填充Project对象，但无法在Project对象中填充ProjectPerson的对象是一样的道理。

上面也提到了mysql执行的先后顺序了，在下面，详细介绍mysql执行的先后顺序。

mysql执行的先后顺序

mysql在执行的过程会有一定的先后顺序的，它是按照什么顺序来的呢？

任何一种开发语言，不管是面向结构的c语言，还是面向对象的JAVA语言，或者，结构化查询语言sql，其都有一个入口，C语言是main，java是public static void main(String[] args){...},SQL语言比如mysql，其入口是From，然后根据各个优先级。依次往下进行。

1. from
2. join
3. on
4. where
5. group by(开始使用select中的别名，后面的语句中都可以使用)
6. avg,sum.... 复合函数
7. having
8. select
9. distinct
10. order by

以项目表为主表，以项目人员表和项目进程表为副表，查找出项目名和项目的预算金额

SELECT DISTINCT
    zp.id AS projectId,
    SUM(zp.budgetary_amount) AS totalBugAmo,
    zp.`name` AS projectName
FROM
    zq_project zp
LEFT JOIN zq_project_person zper ON (
    zper.is_deleted = 0
    AND zper.project_id = zp.id
)
LEFT JOIN zq_project_process zpro ON (
    zpro.is_deleted = 0
    AND zpro.project_id = zp.id
)
WHERE
    zp.is_deleted = 0
GROUP BY
    zp.id
HAVING
    totalBugAmo <= 12000
ORDER BY
    totalBugAmo DESC

执行结果如图所示：

执行顺序如图所示

1. 第一步骤， 以from为入口进入查询语句中，确定主表是zq_project，然后从主表中取数据源
2. LEFT JOIN zq_project_person zper ON (。。。)此时生成一张虚拟表vt1，根据虚拟表vt1中的on之后的筛选条件匹配数据，生成虚拟表vt2
3. LEFT JOIN zq_project_process zpro ON(。。。)在vt2的基础上生成vt3和vt4,
4. where筛选器，过滤掉已被逻辑删除的项目，生成虚拟表vt5,
5. 在group by这里出现了分水岭，之后就可以使用select中的别名了。这个为什么要分组呢？比如，项目人员表中相同项目编号的人员不止一个，这个要以项目id来对其进行分组统计，但此时的分组统计，是有问题的，因为，项目的预算金额是在项目表中的，而相同的项目编号的人员不止一个，那么，就出现了人员项目重复统计的现象。下面再细分析。生成虚拟表vt6
6. 所以，分组之后再sum等这些复合函数，于是，就出现了同一个项目的项目预算相加。这就出现了数据的累加错误。生成虚拟表vt7
7. having是对虚拟表vt7进行数据过滤的，也就是说，它服务的对象是复合函数。生成虚拟表vt8
8. select是将vt8的根据我们写出的条件筛选出来数据，比如我们只想要项目的id、项目的预算金额、项目的名字等，生成虚拟表vt9
9. 使用distinct 对虚拟表vt9进行去重，生成虚拟表vt10

10.最后再排序，生成我们最后想要的表。

为什么说sum会出现笛卡尔积的统计错误

在讲解这个问题前，我们先看这张图：

我们的查语句是：

SELECT
    zp.id AS projectId,
    zp.budgetary_amount AS bugAmo,
    zp.`name` AS projectName
FROM
    zq_project zp
LEFT JOIN zq_project_person zper ON (
    zper.is_deleted = 0
    AND zper.project_id = zp.id
)
LEFT JOIN zq_project_process zpro ON (
    zpro.is_deleted = 0
    AND zpro.project_id = zp.id
)
WHERE
    zp.is_deleted = 0 AND zp.id=167

查询结果的截图为：

你会发现，数据多了，为什么会多？以项目编号为167的为研究点，此时，当left join项目人员表时，根据排列组合而来，1 2=2，多生成一张有两条记录的虚拟表再left join 项目进程表时，根据排列组合而来，2 3=6，就会出现，这时就会出现6条数据的虚拟表，这时，我们再sum的话，就会计算6次，从而得出项目编号为167的预算金额是60，而不是10。

上面就出现了分组之后的项目编号为167的预算金额为90的了，一对多的关系如果sum，是会出现笛卡尔积的错误的。

因为，我们需要使用disdict去重，于是，我们重写代码后为：

SELECT
    vt1.projectId,
    SUM(vt1.bugAmo),
    vt1.projectName
FROM
    (
        SELECT DISTINCT
            zp.id AS projectId,
            zp.budgetary_amount AS bugAmo,
            zp.`name` AS projectName
        FROM
            zq_project zp
        LEFT JOIN zq_project_person zper ON (
            zper.is_deleted = 0
            AND zper.project_id = zp.id
        )
        LEFT JOIN zq_project_process zpro ON (
            zpro.is_deleted = 0
            AND zpro.project_id = zp.id
        )
        WHERE
            zp.is_deleted = 0
        AND zp.id = 167
    ) AS vt1

此时，将其去重后的数据作为虚拟表，放置在from里面，我们拿到的数据就是正确的，如图所示：

![去重后的数据(/img/bVbpyxH)

如果，我们想要查找全部项目的统计金额，也可也可以重写代码，于是乎得到：

SELECT
    SUM(vt1.bugAmo) AS toalBugAmo
FROM
    (
        SELECT DISTINCT
            zp.id AS projectId,
            zp.budgetary_amount AS bugAmo,
            zp.`name` AS projectName
        FROM
            zq_project zp
        LEFT JOIN zq_project_person zper ON (
            zper.is_deleted = 0
            AND zper.project_id = zp.id
        )
        LEFT JOIN zq_project_process zpro ON (
            zpro.is_deleted = 0
            AND zpro.project_id = zp.id
        )
        WHERE
            zp.is_deleted = 0
    ) AS vt1
GROUP BY
    vt1.projectId
HAVING
    toalBugAmo <= 12000
ORDER BY
    toalBugAmo DESC

这个执行结果为：

结尾

任何一门语言，只要掌握住了，它的机制是怎么运行的，你也就学会了如何优化，提升该语言的性能等。只要你真正掌握住了一门变成语言，你掌握其他的变成语言，学起来就非常地快。