社区首页 >专栏 >MySQL表连接优化的初步分析

MySQL表连接优化的初步分析

jeanron100

发布于 2018-08-22 06:35:19

1.6K00

代码可运行

文章被收录于专栏：杨建荣的学习笔记杨建荣的学习笔记

运行总次数：0

代码可运行

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

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

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

伪SQL如下：

    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

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

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

伪SQL可以更加丰富一些。

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的部分。得到的是小的结果集，自然压力就小了。

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

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

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的写法。

那么这里就有两个问题，

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

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

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 小表，看看效果如何。

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