mysql 性能差的原因_为什么你的MySQL性能差?死代码牵连问题解决了吗?

39be5428d5166fd1d86a876bc88c0a1c.gif

MySQL存储过程、函数和触发器是开发人员常打交道的三大名词,但是,这三个过程可能会对数据库性能造成严重影响,尤其是函数中出现“死”代码时,其它两个过程都会受到牵连。以下是作者进行的一些基本测试,通过对比更好地衡量三者对数据库性能的影响。

触发器和存储过程对性能影响的重要原因是函数“死”代码。尽管所有开发人员都知道“死”代码(永远不会被运行的代码)不应该出现在代码行中,但可能并不清楚一行“死”代码到底可以对性能造成多大影响。

“死”代码会显著减缓函数运行、存储过程和触发器的响应时间,因此我们必须认真清理不需要的代码,以下是具体示例:

分析MySQL存储过程函数

让我们比较以下四个简单的存储函数(在MySQL 5.7中):

函数1

9de9fe3e11e07326d080fe681964d965.png

该函数只是声明一个r变量并返回它的值,这是一个虚函数。

函数2

2144d3e0bc415f3617fee121882c29c9.png

该函数调用另一个函数 levenshtein_limit_n(计算levenshtein距离)。但是,由于IF 1=2条件永远不会成立,因此这段代码永远不会被执行,这与函数1的返回结果类似。

函数3

656ef94e3f5e691f60badc9278d8646d.png

函数3看起来很复杂,总共有四个条件,但因为这些条件永远不会成立,因此同样是“死”代码调用,结果与函数2和函数1相同。

函数4

86fcbf6ef61b442de519ce42a7effd44.png

该函数与函数3情况相同,selectdoes_not_exit依旧不会被运行。

虽然所有函数最终返回的r值都为0,但这四段函数的性能有非常大的差异,为了衡量性能变化,我们使用benchmark功能对性能进行测试,在运行相同的1M次之后,四个函数的性能结果如下:

0d8806d328aad36f4f1e3721d6967010.png

我们可以看到fun 1只需要运行1.75 sec,具有一个死代码调用的fun 2需要运行2.45 sec,而func 3(有四个永不执行的死代码调用)运行速度比func 1()慢近3倍,func 3_nope()的响应时间与func 3()完全相同。

可视化函数的所有系统调用

要弄清楚函数调用过程发生了什么,我们可以使用performance_schema / sys模式创建一个带有 ps_trace_thread() procedure的跟踪。

获取MySQL连接的thread_id:

7ff280de475ecfb2413ca0364ff204b8.png

在另一个连接中通过thread_id = 49运行ps_trace_thread:

a88195a7242ca9d243ad223b313aabcb.png

此时切换到原始连接(thread_id = 49)并运行:

85a65b02fe68bc8e01b3e018851fb497.png

sys.ps_trace_thread收集数据(10秒钟,在此期间我运行了select func1()),然后它完成了集合并创建了点文件:

8e642f989a426a596b08f72953d94f2b.png

我为上面的所有函数重复了这些步骤,然后创建了命令图表。

结果如下:

Func1():

8be4da16704831eafe90c66d2b9834b4.png

Func2():

d80e77bcfe0c992f04b5d164b4272da9.png

Func3():

568ffafe424bda65c58a871a886c85da.png

正如我们所看到的,每个“if”检查都有一个sp / jump_if_not调用,后跟一个打开表的语句。因此,解析“IF”条件就会对性能产生影响。

对于MySQL 8.0,我们还可以看到存储过程的MySQL源代码文档,这些文档记录了它的实现方式,如下:

流量分析优化;

生成代码后,优化低级sp_instr指令。

优化主要集中在两方面:

死代码删除;

跳转快捷方式。

这两个优化是一起执行的,因为它们都涉及图表中流量分析的问题,代表生成的代码。实现这些优化的代码是sp_head :: optimize()。

但是,这并不能解释为什么会执行“opening tables”,因此,我向官网提交了一个bug——MySQL Bug #91585。

MySQL Bug #91585链接:

https://bugs.mysql.com/bug.php?id=91585

“死”代码如何影响触发器性能?

当然,如果我们执行的函数不太多,我们可能注意不到这些性能上的差异,但是它会在触发器内部产生影响。假设我们在表上有一个触发器,每次更新该表时,它都会执行触发器来更新字段。比如,假设我们有一个名为“form”的表,我们只需要更新它的创建日期:

73249492551e0d0962d36f7b147d104e.png

现在我们创建一个触发器,它将调用我们的虚函数func1():

47ca1036c42eec8e90678a800f1cc316.png

请记住:重复更新不会更改新的结果,因为我们没有在触发器中做任何事情。

3f15918db6d89a552ca8cfc4ee874f01.png

如果再添加一个虚拟触发器就会增加两倍的开销,即便触发器没有运行任何函数,同样会引起减速:

6246d9e0a4eedb07cc636046db77139a.png

现在,让我们使用func3(它具有“死”代码,但功能相当于func1):

0ef03cf5c74272631bba206d2cd7b668.png

如果,我们从触发器内的函数func3运行代码(而不是调用函数)将加快更新速度:

96813ab953520421d1a0cccc5aeb44b1.png

内存分配

即使代码永远不会被运行,MySQL仍然需要分析每次执行解析存储的过程或触发器代码,并预先为其留出足够的内存,这可能会导致内存泄漏,比如MySQL Bug #86821所描述的。

参考链接:https://bugs.mysql.com/bug.php?id=86821

结论

存储过程和触发器事件在执行时会被解析,即使是永不运行的“死”代码也会显著影响批量操作的性能(例如,在触发器内运行时)。这意味着通过设置“flag”来禁用触发器(例如,if @trigger_disable =0 then ...)仍然会影响批量操作的性能。

作者:赵钰莹

来源:

http://blog.itpub.net/31077337/viewspace-2158337

dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值