蝙蝠岛资源网 Design By www.hbtsch.com

在数据库的应用中,我们经常需要对数据库进行多表查询,然而当数据量非常大时多表查询会对执行效率产生非常大的影响,因此我们在使用JOIN和LEFT JOIN 和 RIGHT JOIN语句时要特别注意;

SQL语句的join原理:

数据库中的join操作,实际上是对一个表和另一个表的关联,而很多错误理解为,先把这两个表来一个迪卡尔积,然后扔到内存,用where和having条件来慢慢筛选,其实数据库没那么笨的,那样会占用大量的内存,而且效率不高,比如,我们只需要的一个表的一些行和另一个表的一些行,如果全表都做迪卡尔积,这开销也太大了,真正的做法是,根据在每一个表上的条件,遍历一个表的同时,遍历其他表,找到满足最后的条件后,就发送到客户端,直到最后的数据全部查完,叫做嵌套循环查询。

1、LEFT JOIN 和 RIGHT JOIN优化

在MySQL中,实现如 A LEFT JOIN B join_condition 如下:

1、表B依赖赖与表A及所有A依赖的表

2、表A依赖于所有的表,除了LEFT JOIN 的表(B)

3、join_condition决定了怎样来读取表B,where条件对B是没有用的

4、标准的where会和LEFT JOIN联合优化

5、如果在A中的一行满足where和having条件,B中没有,会被填充null

RIGHT JOIN 与LEFT JOIN类似,这个位置是可以互换的

LEFT JOIN 与 正常JOIN之间的转换原则上当where条件,对于生成的null行总返回false时,可以直接转化为正常的join

如:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

将被转换为:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

注:因为设置了条件t2.column2 =  5,那么对于所有的生成的t2为null的行都是不成立的

这样的优化将非常快速,因为这样相当于把外连接转换为等值连接,少了很多行的扫描和判断。

嵌套循环JOIN算法----Nested-Loop Join

简单的嵌套循环算法就是从一个表开始,通过对表的条件找到一行,然后找下一个表的数据,找完后,又回到第一个表来寻找满足条件的行

例如,有三个表t1, t2, t3,他们的join类型为:

Table  Join Type
t1   range
t2   ref
t3   ALL

最终生成的伪代码为

for each row in t1 matching range { 
 for each row in t2 matching reference key { 
  for each row in t3 { 
   if row satisfies join conditions, 
     send to client 
  } 
 } 
} 

即,t1表通过范围扫描,t2关联t1,t3为全表扫描

注:先根据对t1表的条件范围找到一行,和t2匹配,然后寻找t3的满足条件的行

块嵌套循环JOIN算法 ---- Block Nested-Loop Join

这个算法的应用为:由于之前的嵌套算法每读一个表的一行后,就会读下表,这样内部的表会被读很多次,所以,数据库利用了join缓存(join buffer)来存储中间的结果,然后读取内部表的时候,找到一行,都和这个缓存中的数据比较,以此来提高效率。例如:一次从外表读10行,然后读内部表时,都和这10行数据进行比较。

MySQL使用join buffer的条件为:

1、join_buffer_size系统变量决定了每个join使用的buffer大小

2、join类型为index或all时,join buffer才能被使用

3、每一个join都会分配一个join buffer,即一个sql可能使用多个join buffer

4、join buffer 不会分配给第一个非常量表

5、只有需要引用的列会被放到join buffer中,不是整行

最终生成伪代码为:

for each row in t1 matching range { 
 for each row in t2 matching reference key { 
  store used columns from t1, t2 in join buffer 
    这里将t1和t2使用的列存到join buffer中 
    if buffer is full { 
   for each row in t3 { 
    for each t1, t2 combination in join buffer { 
     if row satisfies join conditions, 
     send to client 
    } 
   } 
   empty buffer 
  } 
 } 
} 
 
if buffer is not empty { 
 for each row in t3 { 
  for each t1, t2 combination in join buffer { 
   if row satisfies join conditions, 
   send to client 
  } 
 } 
} 

注:在第二个循环才把数据存在join buffer中,这正好印证了上面的第4点

标签:
sql语句优化,sql语句LEFT,JOIN优化,sql语句RIGHT,JOIN优化

蝙蝠岛资源网 Design By www.hbtsch.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
蝙蝠岛资源网 Design By www.hbtsch.com

评论“SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化”

暂无SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化的评论...

《魔兽世界》大逃杀!60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容,新游玩模式《强袭风暴》即将于3月21 日在亚服上线,届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕,并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时,他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中,玩家将会进入一个全新的海盗主题大逃杀式限时活动,其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场,作为一个独立于主游戏之外的活动,玩家可以用大逃杀的风格来体验《魔兽世界》,不分职业、不分装备(除了你在赛局中捡到的),光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式,玩家在加入海盗主题的预赛大厅区域前,可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹,《巨龙崛起》和《魔兽世界:巫妖王之怒 经典版》的玩家都可以获得奖励。