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

2

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

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

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

查询要求:

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

数据库表设计

数据库有这样的两张表,一张是项目表project。项目表有些字段不便展示,因而,只做部分截图:

项目表

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

key值 value值
PERSON_TYPE_SALESMAN 业务员
PERSON_TYPE_SALESMAN_MANAGER 业务部经理
PERSON_TYPE_DESIGNER 设计师
PERSON_TYPE_DESIGNER_MANAGER 设计部经理
PERSON_TYPE_PROJECT_SUPERVISION 工程监理
PERSON_TYPE_ENGINEERING_MANAGER 工程部经理

因而,数据表项目人员(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的项目人员的记录

由上图可知,一共有六条记录,也就是说,项目表中编号为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,你会发现神奇的一幕,如图所示:

clipboard.png

你会发现,这是商品分类的字段属性是有值的,因为,副表的条件满足了,能拿到副表中的字段属性值。
如果我们把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

执行结果如图所示:

项目名和项目的预算金额

执行顺序如图所示
MySQL语句的知心顺序

  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. 最后再排序,生成我们最后想要的表。

聚合函数的笛卡尔积错误

在讲解这个问题前,我们先看这张图:

项目表、项目人员表、项目进程表

我们的查语句是:

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

查询结果的截图为:

clipboard.png

你会发现,数据多了,为什么会多?以项目编号为167的为研究点,此时,当left join项目人员表时,根据排列组合而来,$C(1,1)*C(2,1)$=2,多生成一张有两条记录的虚拟表。此时,再left join项目进程表时,根据排列组合而来,$2* C(3,1)$=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里面,我们拿到的数据就是正确的,如图所示:

去重后的数据

如果,我们想要查找全部项目的统计金额,也可以重写代码。

重写代码的思想:我们先将查询结果去重,得到去重后的虚拟表;再过滤虚拟表的数据,从虚拟表中统计数据,于是乎得到:

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

这个执行结果为:

修改后的执行结果

结尾

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


如果觉得我的文章对你有用,请随意赞赏

你可能感兴趣的

消费 · 3月9日

要是能给sql数据就好了

回复

0

有些数据不方便涉及到公司业务的安全性,所以,不好意思,没办法展示

念兮 作者 · 3月12日
载入中...