为什么MySQL存储过程、函数和触发器对性能不好

4.The sys.ps_trace_thread collected the data (for 10 seconds, during which I ran the ), then it finished its collection and created the dot file:

+-----------------------------------------------------------------------+  
| Info                                                                  |  
+-----------------------------------------------------------------------+  
| Stack trace written to /var/lib/mysql-files/stack-func3nope-new12.dot |  
+-----------------------------------------------------------------------+ 
1 row in set (9.21 sec)  
+-------------------------------------------------------------------------------+  
| Convert to PDF                                                                |  
+-------------------------------------------------------------------------------+  
| dot -Tpdf -o /tmp/stack_49.pdf /var/lib/mysql-files/stack-func3nope-new12.dot |  
+-------------------------------------------------------------------------------+  
1 row in set (9.21 sec)  
+-------------------------------------------------------------------------------+  
| Convert to PNG                                                                |  
+-------------------------------------------------------------------------------+  
| dot -Tpng -o /tmp/stack_49.png /var/lib/mysql-files/stack-func3nope-new12.dot |  
+-------------------------------------------------------------------------------+  
1 row in set (9.21 sec)  
Query OK, 0 rows affected (9.45 sec)  

I repeated these steps for all the functions above and then created charts of the commands.

Here are the results:

Func1()

Func2()

Func3()

As we can see, there is a sp/jump_if_not call for every "if" check followed by an opening tables statement (which is quite interesting). So parsing the "IF" condition made a difference.

For MySQL 8.0 we can also see MySQL source code documentation for stored routines which documents how it is implemented. It reads:

Flow Analysis OptimizationsAfter code is generated, the low level sp_instr instructions are optimized. The optimization focuses on two areas:

Dead code removal,Jump shortcut resolution.These two optimizations are performed together, as they both are a problem involving flow analysis in the graph that represents the generated code.

The code that implements these optimizations is sp_head::optimize().

However, this does not explain why it executes "opening tables." I have filed a bug.

When Slow Functions Actually Make a Difference

Well, if we do not plan to run one million of those stored functions, we will never even notice the difference. However, where it will make a difference is ... inside a trigger. Let's say that we have a trigger on a table: every time we update that table it executes a trigger to update another field. Here is an example: let's say we have a table called "form" and we simply need to update its creation date:

mysql> update form set form_created_date = NOW() where form_id > 5000;  
Query OK, 65536 rows affected (0.31 sec)  
Rows matched: 65536  Changed: 65536  Warnings: 0  

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!