符合中小企业对网站设计、功能常规化式的企业展示型网站建设
本套餐主要针对企业品牌型网站、中高端设计、前端互动体验...
商城网站建设因基本功能的需求不同费用上面也有很大的差别...
手机微信网站开发、微信官网、微信商城网站...
常听说MySQL中3表 join 的执行流程并不是前两张表 join 得出结果,再与第三张表进行 join;而是3表嵌套的循环连接。那这个3表嵌套的循环连接具体又是个什么流程呢?与前两张表 join 得出结果再与第三张表进行 join 的执行效率相比如何呢?下面通过一个例子来分析分析。
创新互联建站长期为上1000家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为色尼企业提供专业的成都网站设计、网站制作,色尼网站改版等技术服务。拥有10余年丰富建站经验和众多成功案例,为您定制开发。
set optimizer_switch='block_nested_loop=off';
关联字段无索引的情况下强制使用索引嵌套循环连接算法,目的是更好的观察扫描行数。
表结构和数据如下:
示例SQL:
通过 slow log 得知一共扫描 24100 行:
执行计划显示用的索引嵌套循环连接算法:
扫描行数构成:
总行数=100+4000+20000=24100。
从这个结果来看,join 过程像是先 t1 和 t3 join 得出 20 行中间结果,再与 t2 进行 join 得出结果。这结论与我们通常认为的 3表 join 实际上是3表嵌套的循环连接不一样,接着往下看。
查看执行计划成本:
mysql explain format=json select * from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.b where t1.a21\G
其他信息:
IO成本= 1*1.0 =1
CPU成本= 100*0.2 =20
t1总成本=21
IO成本= 1*1.0 =1
CPU成本= 200*0.2 =40
t3表总成本= 驱动表扇出*(IO成本+CPU成本) = 20*(1+40) =820
阶段性总成本= 21+820 =841
此处 eval_cost=80,实则为 驱动表扇出*被驱动每次扫描行数*filtered*成本常数 ,即 20*200*10%*0.2 。
简化公式为: eval_cost=rows_produced_per_json*成本常数
IO成本= 4*1.0 =4
CPU成本= 1000*0.2 =200
t2表总成本= 前2表join的扇出*(IO成本+CPU成本) = 400*(4+200) =81600
阶段性总成本= 841+81600 =82441
此处 eval_cost=8000,即 rows_produced_per_json*成本常数 ,即 40000*0.2
根据执行计划成本分析:
这样看,3表 join 流程是:
注意,由于造的数据比较特殊,所以第 3 步得出的中间结果集实际上只有 1行,所以最终 t2 表的查找次数是 20*1=20 ,所以扫描总行数是 20*1000 。所以单看 slow log 中显示的 24100 行,会误认为是先得出 t1 和 t3 join 的结果,再去和 t2 进行 join。
当我调整 t3 的数据,删除20行,再插入20行,使满足 b21 的数据翻倍,这样“第 3 步得出的中间结果集”变成 2 行:
再来看slow log 中扫描的总行数为44100,t1、t3的扫描行数不变,t2 的扫描行数变为 20*2*1000=40000 :
为什么执行计划中分析得到的是 t2 表查找 400 次呢?
因为执行计划对t1 join t3 的扇出是个估算值,不准确。而 slow log 是真实执行后统计的,是个准确值。
为什么执行计划中,t2表的执行次数是用“t1 join t3 的扇出”表示的?这不是说明 t1 先和 t3 join,结果再和 t2 join?
其实拆解来看,“3表嵌套循环” 和 “前2表 join 的结果和第3张表 join” 两种算法,成本是一样的,而且如果要按3表嵌套循环的方式展示每张表的成本将非常复杂,可读性不强。所以执行计划中这么表示没有问题。
总的来说,对于3表join或者多表join 来说,“3表嵌套循环” 和 “先2表 join,结果和第3张表join” 两种算法,成本是一样的。要注意的一点是3表嵌套循环成本并非如下图写的:n m x,而是 n (m+a x),其中 a 为 t2 满足单个等值条件的平均值。
当被驱动表的关联字段不是唯一索引,或者没有索引,每次扫描行数会大于1时,其扇出误差会非常大。比如在上面的示例中:
t3 实际的扇出只有 20,但优化器估算值是 总扫描行数的 10%,由于t3表的关联字段没有索引,所以每次都要全表扫描200行,总的扫描行数= 20*200 =4000,扇出= 4000*10% =400,比实际的20大了20倍。尤其对于后续表的 join 来说,成本估算会产生更严重的偏差。
如果是 left join,每个被驱动表的 filtered 都会被优化器认定为 100%,误差更大!
通常建议join不超过2表,就是因为优化器估算成本误差大导致选择不好的执行计划,如果要用,一定要记住:关联字段必须要有索引,最好有唯一性或者基数大。
select * from A full join B on a.col1=b.col1
left join C on a.col2=c.col2;
是不是你要的效果、C又是什么join呢,是在搞不清你可以把子查询括号括起来再join你的C表
select * from (
select * from A full join B on a.col1=b.col1
) left join C on a.col2=c.col2;
数据库多表关联,一般采用外键比较方便,也可以额外建一个连接表做多表关联的连接,但这样稍微有点儿复杂,这些是建表方面的关联。查询关联,可以采用多表查询的方式关联查询,这点要求稍高点儿,但关联后再操作单表时,别的表不用受太大的影响,这点特好。
select * from A,B,C 就这样就可以了,这样链接的是所有的内容。。要筛选的话后面用where语句,有什么问题再交流啊。。
假设三张表结构一样,题主可以参考下列sql语句,A表与B表union,然后将联合后的结果集再与C表union all
select t.* from
(select * from A union select * from B) t
union all
select * from C;
改为:
SELECT count(*) as num FROM (income as i join outgo as o on i.areaid=o.areaid) join centeroutgo as c on i.areaid =c.areaid
还有就是确保income表、outgo表和centeroutgo表这三个表的areaid字段类型一致。
我没见过有Full outer join的,查了下手册,也没看见。
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON condition
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor