为什么MySQL存储过程、函数和触发器不利于性能然而，正如我所发现的，使用MySQL存储例程会影响数据库性能。不完全确定我在客户访问期间看到了什么，我开始创建一些简单的测试来衡量触发器对数据库性能的影响。结果可能会让你吃惊。为什么存储例程不是性能最好的：简短版本？我最近与一位客户合作研究触发器和存储例程的性能。我对存储例程的了解是：“死”代码（永远不会运行的分支中的代码）仍然可以显着减少函数/过程/触发器的响应时间。我们需要仔细清理我们不需要的东西。分析MySQL存储函数让我们比较这四个简单的存储函数（在MySQL5.7中）：Function1CREATEDEFINER=`root`@`localhost`FUNCTION`func1`()RETURNSint(11)BEGINdeclarerintdefault0;RETURNr;END这个函数简单地声明一个变量并返回它。这是一个虚拟函数。Function2CREATEDEFINER=`root`@`localhost`FUNCTION`func2`()RETURNSint(11)BEGINdeclarerintdefault0;IF1=2THENselectlevenshtein_limit_n('testfinc','testfunc',1000)intor;ENDIF;RETURNr;ENDThisfunction调用另一个函数levenshtein_limit_n（计算levenshtein距离）。但是等等：这段代码永远不会运行——条件IF1=2永远不会为真。所以这与函数1相同。Function3CREATEDEFINER=`root`@`localhost`FUNCTION`func3`()RETURNSint(11)BEGINdeclarerintdefault0;IF1=2THENselectlevenshtein_limit_n('testfinc','testfunc',1)intor;ENDIF;IF2=3THENselectlevenshtein_limit_n('testfinc','testfunc',10)intor;ENDIF;IF3=4THENselectlevenshtein_limit_n('testfinc','testfunc',100)intor;ENDIF;IF4=5THENselectlevenshtein_limit_n('testfinc','testfunc',1000)intor;ENDIF;RETURNr;END这里有四个条件，这些条件都不为真：有4调用“死”代码。函数3的函数调用结果将与函数2和函数1相同。Function4CREATEDEFINER=`root`@`localhost`FUNCTION`func3_nope`()RETURNSint(11)BEGINdeclarerintdefault0;IF1=2THENselectdoes_not_exit('testfinc','testfunc',1)intor;ENDIF;IF2=3THENselectdoes_not_exit('testfinc','testfunc',10)intor;ENDIF;IF3=4THENselectdoes_not_exit('testfinc','testfunc',100)intor;ENDIF;IF4=5THENselectdoes_not_exit('testfinc','testfunc',1000)intor;ENDIF;RETURNr;END这个和函数3一样，但是我们运行的函数不存在。好吧，没关系，selectdoes_not_exit永远不会运行。所以，所有函数将始终返回0。我们期望这些函数的性能是the相同或非常相似。令人惊讶的是，事实并非如此！为了衡量性能，我使用“基准测试”功能将同一功能运行1M次。以下是结果：+----------------------------+|benchmark(1000000,func1())|+-----------------------------+|0|+----------------------------+1rowinset(1.75sec)+----------------------------+|基准（1000000,func2())|+----------------------------+|0|+--------------------------+1rowinset(2.45sec)+--------------------------+|基准（1000000，func3（））|+----------------------------+|0|+----------------------------+1rowinset(3.85sec)+----------------------------------+|基准（1000000，func3_nope（））|+----------------------------------+|0|+------------------------------+1rowinset(3.85sec)正如我们所见，func3（有四个永远不会执行的死代码调用，否则与func1相同）运行速度比func1()慢了近3倍；func3_nope()在o方面是相同的f对func3()的响应时间。可视化来自函数的所有系统调用为了弄清楚函数调用内部发生了什么，我使用performance_schema/sysschema通过ps_trace_thread()过程创建跟踪。1.获取MySQL连接的thread_id：mysql>selectTHREAD_IDfromperformance_schema.threadswhereprocesslist_id=connection_id();+------------+|THREAD_ID|+------------+|49|+------------+1rowinset(0.00sec)2.在另一个连接中运行ps_trace_thread传递thread_id=49:mysql>CALLsys.ps_trace_thread(49,concat('/var/lib/mysql-files/stack-func1-run1.dot'),10,0,TRUE,TRUE,TRUE);+--------------------+|总结|+--------------------+|Disabled0threads|+--------------------+1rowinset(0.00sec)+------------------------------------------+|信息|+------------------------------------------+|DatacollectionstartingforTHREAD_ID=49|+-------------------------------------------+1rowinset(0.00sec)3.此时我换了原版连接(thread_id=49)并运行：mysql>selectfunc1();+--------+|func1()|+--------+|0|+---------+1rowinset(0.00sec)4.sys.ps_trace_thread收集了数据（持续10秒，在此期间我运行了），然后它完成了收集并创建了点文件：+------------------------------------------------------------------+|信息|+------------------------------------------------------------------+|Stacktracewrittento/var/lib/mysql-文件/堆栈-func3nope-new12.dot|+------------------------------------------------------------------+1rowinset(9.21sec)+--------------------------------------------------------------------------+|ConverttoPDF|+-------------------------------------------------------------------------+|dot-Tpdf-o/tmp/stack_49.pdf/var/lib/mysql-files/stack-func3nope-new12.dot|+----------------------------------------------------------------------------+1rowinset(9.21sec)+----------------------------------------------------------------------------+|ConverttoPNG|+------------------------------------------------------------------------+|dot-Tpng-o/tmp/stack_49.png/var/lib/mysql-files/stack-func3nope-new12.dot|+------------------------------------------------------------------------------+1rowinset(9.21sec)QueryOK,0rowsaffected(9.45sec)我对上面的所有函数重复了这些步骤，然后创建了命令图表。结果如下：Func1()Func2()Func3()正如我们所见，每个“if”都有一个sp/jump_if_not调用check后跟一个openingtables语句（这很有趣）。所以解析“IF”条件会有所不同。对于MySQL8.0，我们还可以看到存储例程的MySQL源代码文档，其中记录了它是如何实现的。上面写着：FlowAnalysisOptimizationsAftercodegenerated，lowlevelsp_instrinstructionsareoptimized。优化主要集中在两个方面：Deadcode去除，Jumpshortcutresolution。这两个优化是一起进行的，因为它们都是一个涉及流分析的问题，图中代表生成的代码。实现这些优化的代码是sp_head::optimize()。但是，这样做不解释为什么要执行“开表”。我已经提交了一个错误。当慢速函数实际上产生影响时好吧，如果我们不打算运行一百万个这些存储的函数，我们甚至不会注意到差异。但是，它会有所作为的地方是......在触发器内部。假设我们在一个表上有一个触发器：每次我们更新该表时，它都会执行一个触发器来更新另一个字段。这是一个例子：假设我们有一个名为“form”的表，我们只需要更新它的创建日期：mysql>updateformsetform_created_date=NOW()whereform_id>5000;QueryOK,65536rowsaffected(0.31sec)Rowsmatched:65536Changed:65536Warnings:0That是克又好又快。现在我们创建一个触发器，它将调用我们的虚拟func1():CREATEDEFINER=`root`@`localhost`TRIGGER`test`.`form_AFTER_UPDATE`AFTERUPDATEON`form`FOREACHROWBEGINdeclarerintdefault0;selectfunc1()intor;END现在重复更新。请记住：它不会更改更新的结果，因为我们实际上并没有在trigger.mysql>updateformsetform_created_date=NOW()whereform_id>5000;QueryOK,65536rowsaffected(0.90sec)Rowsmatched:65536Changed:65536Warnings:0中添加一个虚拟对象触发器将增加2倍的开销：下一个甚至不运行函数的触发器会引入减速：CREATEDEFINER=`root`@`localhost`TRIGGER`test`.`form_AFTER_UPDATE`AFTERUPDATEON`form`FOREACHROWBEGINdeclarerintdefault0;ENDmysql>updateformsetform_created_date=NOW()whereform_id>5000;QueryOK,65536rowsaffected(0.52sec)Rowsmatched:65536Changed:65536Warnings:0Now,letsusefunc3(whichhas"dead"codeandisequivalenttofunc1):CREATEDEFINER=`root`@`localhost`TRIGGER`test`.`form_AFTER_UPDATE`AFTERUPDATEON`form`FOREACHROWBEGINdeclarerintdefault0;selectfunc3()intor;ENDmysql>updateformsetform_created_date=NOW()whereform_id>5000;QueryOK,65536rowsaffected(1.06sec)Rowsmatched:65536Changed:65536警告：0但是，从触发器内部的func3运行代码（而不是调用函数）将加快更新速度：CREATEDEFINER=`root`@`localhost`TRIGGER`test`.`form_AFTER_UPDATE`AFTERUPDATEON`form`FOREACHROWBEGINdeclarerintdefault0;IF1=2THENselectlevenshtein_limit_n('testfinc','testfunc',1)intor;ENDIF;IF2=3THENselectlevenshtein_limit_n('testfinc','testfunc',10)intor;ENDIF;IF3=4THENselectlevenshtein_limit_n('testfinc','testfunc',100)intor;ENDIF;IF4=5THENselectlevenshtein_limit_n('testfinc','testfunc',1000)intor;ENDIF;ENDmysql>updateformsetform_created_date=NOW()whereform_id>5000;QueryOK,65536rowsaffected(0.66sec)Rowsmatched:65536Changed:65536Warnings:0MemoryAllocationPot基本上，即使代码永远不会运行，MySQL仍然需要为每次执行解析存储的例程或触发器代码，这可能会导致内存泄漏，如该错误中所述。结论存储例程和触发器即使是永远不会运行的“死”代码也会显着影响批量操作的性能（例如，当在触发器中运行时）。这也意味着通过设置“标志”（例如）禁用触发器仍然会影响批量操作的性能。
