最近和同事排查了一个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
where test1.code = test2.code
看起来很简单吧,真实的SQL是这样的形式,确实有些复杂的感觉。
digital_test.comprehensive_orders co ,
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 )
WHERE uoi.order_code=temp0.order_code GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0
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 | |
| 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可以参考之前的一篇文章。
执行语句之后得到的结果如下:
>show profile for query 1;+---------------------------+-----------+| Status | Duration |+---------------------------+-----------+| starting | 0. || 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. 能够通过修改语句的方式来修改
这个语句的逻辑其实有点绕。主要就在于里面的子查询。
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 )
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可以简化为如下的形式:
digital_test.comprehensive_orders co, --800多万的数据
(xxxx from test.user_order_items xxxx) temp1 --1100多万的数据,但是子查询能够过滤剩下的数据是个位数。
where co.co_order_code = temp1.order_code
显然目前的情况下,优化器的实现还是不够全面,从优化的细则来看,它本身缺少一些信息的参考,比如统计信息,比如字段的数据分布等,他没法知道我们可以过滤掉如此多的数据。
所以怎么让优化器能够尽可能按照先temp1的查询,然后执行co的方式呢,一种可行的思路就是减少co的结果集大小,因为两个结果集都是按照order_code关联,既然order_code在temp1得到的是一个极小的结果集,那么order_code也是一个极小的结果集,那么映射到co里面,那结果集范围就更小更可控了。
所以原来的子查询虽然看起来有些啰嗦,但是性能还不错,我们可以在不影响逻辑的前提下,直接引用过来,于是上面的SQL最后建议的SQL语句如下:
digital_test.comprehensive_orders co ,
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 )
WHERE uoi.order_code=temp0.order_code GROUP BY uoi.order_code HAVING bit_and(uoi.item_stat>>1)!=0
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 (
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 )
WHERE uoi.order_code=temp0.order_code
只是添加了最后的蓝色部分,整个语句的性能就杠杠的了。
后续同事和业务同学进行了对接,基本符合我们的预期。所以第一阶段的优化目标算是搞定了。