MySQL表连接优化的初步分析
每每一些很深刻的优化案例时，就会无比想念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这种情况下，SQL的性能是比较差的，大概1分钟后才有反应。
所以在这种场景下，join的写法明显没有特殊的改进。
我们简单总结一下，在这个SQL优化场景中，为了得到更好的性能，需要做到一个平衡，即小表和大表的关联方式，效率是最佳的，至于你是写成join还是逗号分隔的表关联，从目前的测试来看，差别不大。