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

我们每一个人都想要优化SQL语句，以便能够提升性能，但是，如果不了解其机制，可能就会事倍功半。我以一个简单的例子 ，来讲解SQL的部分机制。今天在公司工作时，面临这样一个需求：根据条件查询项目的预算金额。查询要求：项目的id项目人员的类型数据库表设计数据库有这样的两张表，一张是项目表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 zpLEFT 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 personIdFROM zq_project zpLEFT 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 cgLEFT 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 cgINNER 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 cgINNER 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 personIdFROM zq_project zpLEFT 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，然后根据各个优先级。依次往下进行。fromjoinonwheregroup by(开始使用select中的别名，后面的语句中都可以使用)avg,sum…. 复合函数havingselectdistinctorder by以项目表为主表，以项目人员表和项目进程表为副表，查找出项目名和项目的预算金额SELECT DISTINCT zp.id AS projectId, SUM(zp.budgetary_amount) AS totalBugAmo, zp.name AS projectNameFROM zq_project zpLEFT 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 = 0GROUP BY zp.idHAVING totalBugAmo <= 12000ORDER BY totalBugAmo DESC执行结果如图所示：执行顺序如图所示第一步骤， 以from为入口进入查询语句中，确定主表是zq_project，然后从主表中取数据源LEFT JOIN zq_project_person zper ON (。。。)此时生成一张虚拟表vt1，根据虚拟表vt1中的on之后的筛选条件匹配数据，生成虚拟表vt2LEFT JOIN zq_project_process zpro ON(。。。)在vt2的基础上生成vt3和vt4,where筛选器，过滤掉已被逻辑删除的项目，生成虚拟表vt5,在group by这里出现了分水岭，之后就可以使用select中的别名了。这个为什么要分组呢？比如，项目人员表中相同项目编号的人员不止一个，这个要以项目id来对其进行分组统计，但此时的分组统计，是有问题的，因为，项目的预算金额是在项目表中的，而相同的项目编号的人员不止一个，那么，就出现了人员项目重复统计的现象。下面再细分析。生成虚拟表vt6所以，分组之后再sum等这些复合函数，于是，就出现了同一个项目的项目预算相加。这就出现了数据的累加错误。生成虚拟表vt7having是对虚拟表vt7进行数据过滤的，也就是说，它服务的对象是复合函数。生成虚拟表vt8select是将vt8的根据我们写出的条件筛选出来数据，比如我们只想要项目的id、项目的预算金额、项目的名字等，生成虚拟表vt9使用distinct 对虚拟表vt9进行去重，生成虚拟表vt1010.最后再排序，生成我们最后想要的表。为什么说sum会出现笛卡尔积的统计错误在讲解这个问题前，我们先看这张图：我们的查语句是：SELECT zp.id AS projectId, zp.budgetary_amount AS bugAmo, zp.name AS projectNameFROM zq_project zpLEFT 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项目人员表时，根据排列组合而来，12=2，多生成一张有两条记录的虚拟表再left join 项目进程表时，根据排列组合而来，23=6，就会出现，这时就会出现6条数据的虚拟表，这时，我们再sum的话，就会计算6次，从而得出项目编号为167的预算金额是60，而不是10。上面就出现了分组之后的项目编号为167的预算金额为90的了，一对多的关系如果sum，是会出现笛卡尔积的错误的。因为，我们需要使用disdict去重，于是，我们重写代码后为：SELECT vt1.projectId, SUM(vt1.bugAmo), vt1.projectNameFROM ( 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 toalBugAmoFROM ( 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 vt1GROUP BY vt1.projectIdHAVING toalBugAmo <= 12000ORDER BY toalBugAmo DESC这个执行结果为：结尾任何一门语言，只要掌握住了，它的机制是怎么运行的，你也就学会了如何优化，提升该语言的性能等。只要你真正掌握住了一门变成语言，你掌握其他的变成语言，学起来就非常地快。