前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL表连接优化的初步分析

MySQL表连接优化的初步分析

作者头像
jeanron100
发布2018-08-22 14:35:19
1.5K0
发布2018-08-22 14:35:19
举报
文章被收录于专栏:杨建荣的学习笔记

每每一些很深刻的优化案例时,就会无比想念Oracle里的优化技巧,因为无论是从工具还是信息,都会丰富许多。

数据库技术就是这么一路走过来,MySQL的优化器也是,所以在MySQL最流行的情况下,我只能更多的去摸清楚优化器里的一些实现差异。

还是昨天的那个SQL优化案例,我会从另外几个维度来说下优化的思路。

伪SQL如下:

代码语言:javascript
复制
    update big_table,
    (xxxxxx ) small_table
    set xxxxx
    where xxxxxx;
看起来这个语句很简单,如果展开,完整的SQL如下:
UPDATE 
 digital_test.comprehensive_orders co , --千万级大表
 ( SELECT 
    uoi.order_code ,
    MAX(uoi.item_stat) AS costat ,
    SUM(uoi.winning_gold) AS winningGold ,
    SUM(uoi.winning_gold-uoi.item_price) as profit 
   FROM  
   test.user_order_items uoi ,  --近千万级大表
   ( SELECT DISTINCT a.order_code FROM test.user_order_items a WHERE a.match_id=35303 AND a.item_pid=51 AND a.item_stat>0 )

    AS temp0 
   WHERE uoi.order_code=temp0.order_code GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0  

 ) 
   AS temp1 
   SET 
    co.co_stat=(CASE WHEN temp1.coStat IN (6,9) THEN 4 ELSE temp1.coStat END),
    co.co_winning_gold=temp1.winningGold, co.co_test_draw_time='2018-08-07 16:20:45', 
    co.co_share_income_outcome = CASE WHEN co.co_order_type=4 THEN ( CASE WHEN temp1.profit>0 THEN ROUND(ifnull(co.co_share_payoff_ratio,0) * temp1.profit) ELSE 0 END ) ELSE 0 END ,
    co.co_award_id=35309 
WHERE co.co_order_code=temp1.order_code AND co.co_stat=1

我从几个维度来简单说一下,核心的优化思想还是“平衡”

  1. 首先我们定位到最初的解决方案。

伪SQL可以更加丰富一些。

代码语言:javascript
复制
UPDATE 
 digital_test.comprehensive_orders co ,  
 ( SELECT 
    xxxx
   FROM  
   test.user_order_items uoi ,
   ( SELECT DISTINCT a.order_code FROM test.user_order_items a WHERE a.match_id=35303 AND a.item_pid=51 AND a.item_stat>0 )

    AS temp0 
   WHERE uoi.order_code=temp0.order_code GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0  

 ) 
   AS temp1 
   SET 
   xxxx
WHERE co.co_order_code=temp1.order_code AND co.co_stat=1

其中temp1里的查询过滤的结果集是个位数,而外层的表digital_test.comprehensive_orders数据上千万,但是关联的条件是走主键的。

我们画个图来说。

上面这种情况其实MySQL是很容易区分的,难就难在这个情况真实情况是这样的。

如果碰到这种情况,MySQL优化器就有点懵了。这两个大表自己关联,结果集到底有多大,因为没有更丰富的信息,要定位还是有些难的。

所以从执行计划来看,为什么性能差,最后优化器的判断是对两个大表做了全表扫描。

所以我的思路是通过对where条件的过滤来做的,既然他没法确定更小的结果集,那么我就在where部分过滤,SQL肯定会优先处理where的部分。得到的是小的结果集,自然压力就小了。

还有没有更好的方案呢,同事也提供了一些思路,最后的方案是根据他的建议来做的。

这个改进是怎么做的呢, 带颜色的部分就是改动的地方。

代码语言:javascript
复制
UPDATE digital_test.comprehensive_orders co,
    (
    SELECT xxxxx
    FROM 
    (
    SELECT  a.order_code
    FROM test.user_order_items a
    WHERE a.match_id=35303
    AND a.item_pid=51
    AND a.item_stat>0
    ) AS temp0  join test.user_order_items uoi 
    on uoi.order_code=temp0.order_code
    GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0
     ) AS temp1
SET
 xxxxx
WHERE co.co_order_code=temp1.order_code
            AND co.co_stat=1 ;

可以看到这种改法,没有添加额外的SQL逻辑,把原来的表关联改为了join的方式,效果是立竿见影。

这里的改动思路是把原来的大表小表关联,改为小表大表关联,然后改为join的写法。

那么这里就有两个问题,

  1. 同样是表关联,小表大表关联和大表小表关联,这种写法在MySQL那么重要吗?
  2. 是否join的写法效果要更好一些?

要验证这两个问题,其实也不难。我们使用如下的SQL来验证。

代码语言:javascript
复制
UPDATE 
 digital_test.comprehensive_orders co ,
 ( SELECT 
   xxxxx
   FROM 
   ( SELECT DISTINCT a.order_code FROM test.user_order_items a WHERE a.match_id=35303 AND a.item_pid=51 AND a.item_stat>0 )
    AS temp0 ,
 test.user_order_items uoi 
   WHERE uoi.order_code=temp0.order_code GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0  
 ) 
   AS temp1 
   SET 
   xxxx
WHERE co.co_order_code=temp1.order_code AND co.co_stat=1

这种改法简直天然无公害,执行效率也是杠杠的。在这个场景下,确实顺序还是有很大的关联的。

然后第二个问题,是否join的方式要更好一些?

我们可以把表关联写为大表 join 小表,看看效果如何。

代码语言:javascript
复制
UPDATE 
 digital_test.comprehensive_orders co ,
 (SELECTxxxx
    FROM 
       test.user_order_items uoi join(
    SELECT  a.order_code
    FROM test.user_order_items a
    WHERE a.match_id=35303
    AND a.item_pid=51
    AND a.item_stat>0
    ) AS temp0
  on uoi.order_code=temp0.order_code
    GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0 
 ) 
   AS temp1 
   SET 
  xxxxx
WHERE co.co_order_code=temp1.order_code AND co.co_stat=1

这种情况下,SQL的性能是比较差的,大概1分钟后才有反应。

所以在这种场景下,join的写法明显没有特殊的改进。

我们简单总结一下,在这个SQL优化场景中,为了得到更好的性能,需要做到一个平衡,即小表和大表的关联方式,效率是最佳的,至于你是写成join还是逗号分隔的表关联,从目前的测试来看,差别不大。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-08-09,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档