MySQL 5.5迁移到5.7的性能问题排查案例

最近和同事排查了一个MySQL的SQL性能问题。问题的背景是有一个业务的数据库从MySQL 5.5迁移到了MySQL 5.7，原来在5.5中有一个SQL秒级就能完成，但是在5.7版本中执行时间长了好多，业务也产生了延迟。

按道理5.7的功能和改进更多，比5.5要更稳定，出现这样的问题，其实是比较奇怪的，从我们的初步理解来看，方向应该是优化器参数的影响。在MySQL中有一个优化器的总开关optimizer_switch,这个参数真是包罗万象，里面包含了很多优化器属性，优化器属性都可以通过这个总开关进行启用和关闭。

优化器开关参数opertimizer_switch的属性还是很多的，比如我们可以看到一些高级的优化器开关ICP,MRR,BKA等等。

>>show variables like '%optimize%';

| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=off,condition_fanout_filter=on,derived_merge=on |

来说这个SQL问题。

这个SQL语句逻辑还是相对简单的，伪SQL是这样的形式：

update test1,test2

set xxxx

where test1.code = test2.code

看起来很简单吧，真实的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=35313 AND a.item_pid=48 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;

从5.5升级到了5.7出现了性能问题，证明这个SQL尽管看起来不大简洁，但是曾经性能很好。另外这个SQL在5.5中性能很好，在5.7中性能很差，一种很直接的思路就是不用修改SQL,同时在5.7中得把它优化好。

我们最初的思路就是查找优化器开关，通过查看执行计划，很快锁定语句执行过程中会创建多个派生表（derived_table）.

派生表其实是一个不大好的使用方式，同时也是MySQL不擅长的，主要有几点，第一是MySQL里面的派生表会生成临时文件，存储引擎默认是MyISAM,第二是在性能上会有很多隐患。

既然有很多派生表，那么我们能在派生表上怎么优化呢，一种思路就是看看相关的参数有哪些，很快发现有一个参数derived_merge这个优化器参数。

对此我和同事商量了下，我们应该按照这个思路来测试。

1.搭建MySQL 5.5和MySQL 5.7的测试环境

2.把相关表的数据导入两个环境

3.模拟测试指定的SQL语句，在MySQL 5.7中查看指定语句的执行计划。

4.重点测试5.7版本的情况，分别测试开启和关闭derived_merge前后SQL的行情况

5.如果性能差别很明显，则说明是参数影响导致，可以再次确认，

6.如果确认无误，可以在线上变更，可能需要应用重新连接

7.如果没有性能差别，当时还真没往下想，当时感觉是信誓旦旦。。。

然后没多久，同事就反馈说测试也做了，但是发现真是没有差别，结果就是性能都很差。

然后我们扩大了影响范围，是不是有其他我们不知道的优化器参数导致的呢。我们调整了思路。

查看5.5版本中的优化器参数，大概不到7个左右，然后把5.7没有列出来的参数都置为False，然后逐步的调整，查看是否有影响。

但是显然这个过程不是严格意义上的测试，如果有些参数是互相依赖或者组合的关系，我们的测试显然是没法覆盖到的。另一方面，似乎是在做一种黑洞测试。

应用那边也开始催促，一旦影响到业务，最差的情况就是需要把已有的5.7环境降级到5.5， 这显然不是我们彼此希望的，从技术可控的角度来说，我们可以确定下思路。

1. MySQL5.5到5.7的这个性能变化，很可能不是单纯的参数开关可以搞定，的。在适当的时候，还是可以建议开发同学调整下SQL,但是SQL的逻辑要等价，同时修改的幅度要小，大刀阔斧的修改不合时宜。

2.尽可能从MySQL 5.7的一些新特性方向进行排查，是否有一些其他的特性会导致这类问题，比如半同步，比如派生表等，不能单纯从优化器开关入手。

3.从问题的本质来说，就是希望SQL执行效率提高，如果从SQL的角度进行调整，对已有的SQL实现做改动，能够重写SQL,哪怕这道坎需要和业务方反复确认，只要目标明确，也是值得的。这是在前面的方案不起效的时候我们需要做的最后保证，如果这些都无法保证，我们显然会很被动。

所以我开始正式介入的时候，就没有花太多功夫在优化器参数上。

而是逐步从子查询，派生表的角度来考虑。

首先这个SQL的执行性能比较差，在测试环境5.7执行时间大约是2分半，在测试环境5.5是秒出。

很多同学说那就看执行计划啊，纠结的是5.5版本中update是看不了执行计划的，我们只能间接转换为等价的select,但是很可能转换过程还真不一定等价。

我的工作重点其实主要在5.7中，毕竟缅怀5.5的性能好已经没有意义了。

怎么去诊断一个SQL的执行细节呢。

第一种思路，我们可以使用show session status的方式来查看。

>>show session status like '%handler%';

+----------------------------+----------+

| Variable_name | Value |

+----------------------------+----------+

| Handler_commit | 1 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_external_lock | 6 |

| Handler_mrr_init | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 2 |

| Handler_read_key | 14444 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 20226723 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 0 |

+----------------------------+----------+

18 rows in set (0.00 sec)

从上面的方式可以明显看到handler高达2000多万，其实那个就是表的数据量，也就意味着对一个2000多万的表走了全表扫描。

另外执行的细节，这些时间主要都消耗在了哪里，我们怎么去看，可以使用profile.

怎么用profile可以参考之前的一篇文章。MySQL Profile在5.7的简单测试(r10笔记第50天)

执行语句之后得到的结果如下：

>show profile for query 1; +---------------------------+-----------+ | Status | Duration | +---------------------------+-----------+ | starting | 0.000251 | | checking permissions | 0.000010 | | checking permissions | 0.000003 | | checking permissions | 0.000003 | | checking permissions | 0.000005 | | Opening tables | 0.000327 | | init | 0.000011 | | updating main table | 0.000037 | | System lock | 0.000019 | | optimizing | 0.000006 | | optimizing | 0.000003 | | optimizing | 0.000014 | | statistics | 0.028028 | | preparing | 0.000032 | | Creating tmp table | 0.000026 | | statistics | 0.000034 | | preparing | 0.000056 | | Creating tmp table | 0.000034 | | Sorting result | 0.000017 | | statistics | 0.000037 | | preparing | 0.000014 | | executing | 0.000014 | | Sending data | 43.094600 | | executing | 0.000019 | | Sending data | 0.007413 | | executing | 0.000005 | | Sending data | 36.759599 | | Creating sort index | 28.358099 | | updating reference tables | 0.000007 | | end | 0.000014 | | end | 0.000005 | | query end | 0.107934 | | removing tmp table | 0.000014 | | query end | 0.000008 | | removing tmp table | 0.000004 | | query end | 0.000004 | | closing tables | 0.000003 | | removing tmp table | 0.000005 | | closing tables | 0.000002 | | removing tmp table | 0.000004 | | closing tables | 0.000019 | | freeing items | 0.000074 | | logging slow query | 0.000003 | | logging slow query | 0.000365 | | cleaning up | 0.000007 | +---------------------------+-----------+ 45 rows in set, 1 warning (0.00 sec)

从以上的信息可以看出，97%以上的消耗都在数据的中转和一个临时索引的维护上。

对此我关闭了5.7中的半连接semijoin，甚至关闭了sql_mode，依然没有作用。所以我的方向就很明确了。

两个改进方向， 1. 能够通过参数的方式修改 2. 能够通过修改语句的方式来修改

这个语句的逻辑其实有点绕。主要就在于里面的子查询。

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=35313 AND a.item_pid=48 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 

单独执行的时候，性能还是不错的，尽管逻辑看起来有些别扭，逻辑的细节是需要后续和业务方进行确认和改进的。

但是一旦和update语句关联起来，整个语句的执行计划就会发生变化。

上一张执行计划的图：

可以看到执行计划里面已经出现了ALL的字眼，意味着都是全表，看起来这2000多万的量还不是一张表，而是两张表。

以上的SQL的瓶颈经过排查其实就纠结在这里：

表digital_test.comprehensive_orders的主键是co_order_code

表test.user_order_items的order_code不是主键，是一个辅助索引。

但是子查询的数据过滤效果非常直接。

原来复杂的SQL可以简化为如下的形式：

update

digital_test.comprehensive_orders co, --800多万的数据

（xxxx from test.user_order_items xxxx) temp1 --1100多万的数据，但是子查询能够过滤剩下的数据是个位数。

set xxxx

where co.co_order_code = temp1.order_code

显然目前的情况下，优化器的实现还是不够全面，从优化的细则来看，它本身缺少一些信息的参考，比如统计信息，比如字段的数据分布等，他没法知道我们可以过滤掉如此多的数据。

所以怎么让优化器能够尽可能按照先temp1的查询，然后执行co的方式呢，一种可行的思路就是减少co的结果集大小，因为两个结果集都是按照order_code关联，既然order_code在temp1得到的是一个极小的结果集，那么order_code也是一个极小的结果集，那么映射到co里面，那结果集范围就更小更可控了。

所以原来的子查询虽然看起来有些啰嗦，但是性能还不错，我们可以在不影响逻辑的前提下，直接引用过来，于是上面的SQL最后建议的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=35313 AND a.item_pid=48 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
and  co.co_order_code in  ( 
    SELECT 
    uoi.order_code 
   FROM test.user_order_items uoi ,
   ( SELECT DISTINCT a.order_code FROM test.user_order_items a WHERE a.match_id=35313 AND a.item_pid=48 AND a.item_stat>0 )
    AS temp0 
   WHERE uoi.order_code=temp0.order_code 
)

只是添加了最后的蓝色部分，整个语句的性能就杠杠的了。

后续同事和业务同学进行了对接，基本符合我们的预期。所以第一阶段的优化目标算是搞定了。