为什么MySQL存储进程、函数和触发器对机能欠好

MySQL存储进程、函数和触发器是应用措施开拓职员的诱人结构。可是，正如我所发明的，行使MySQL存储例程会影响数据库机能。因为不能完全确定在客户会见时代看到了什么，我开始建设一些简朴的测试来怀抱触发器对数据库机能的影响。功效也许会让你大吃一惊。

为什么存储例程在机能上不是最佳的:短版本?

最近，我与一位客户相助，相识触发器和存储例程的机能。我对存储例程的相识是:“死”代码(分支中的代码永久不会运行)如故可以明显低落函数/进程/触发器的相应时刻。我们必要警惕地整理我们不必要的对象。

Profiling MySQL Stored Functions

Let's compare these four simple stored functions (in MySQL 5.7):

Function 1

CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11) 
BEGIN 
declare r int default 0; 
RETURN r; 
END 

This function simply declares a variable and returns it. It is a dummy function.

Function 2

CREATE DEFINER=`root`@`localhost` FUNCTION `func2`() RETURNS int(11) 
BEGIN 
    declare r int default 0; 
    IF 1=2 
    THEN 
select levenshtein_limit_n('test finc', 'test func', 1000) into r; 
    END IF; 
RETURN r; 
END 

This function calls another function, levenshtein_limit_n (calculates levenshtein distance). But wait: this code will never run — the condition IF 1=2 will never be true. So that is the same as function 1.

Function 3

CREATE DEFINER=`root`@`localhost` FUNCTION `func3`() RETURNS int(11) 
BEGIN 
    declare r int default 0; 
    IF 1=2 THEN 
select levenshtein_limit_n('test finc', 'test func', 1) into r; 
    END IF; 
    IF 2=3 THEN 
select levenshtein_limit_n('test finc', 'test func', 10) into r; 
    END IF; 
    IF 3=4 THEN 
select levenshtein_limit_n('test finc', 'test func', 100) into r; 
    END IF; 
    IF 4=5 THEN 
select levenshtein_limit_n('test finc', 'test func', 1000) into r; 
    END IF; 
RETURN r; 
END 

Here there are four conditions and none of these conditions will be true: there are 4 calls of "dead" code. The result of the function call for function 3 will be the same as function 2 and function 1.

Function 4

CREATE DEFINER=`root`@`localhost` FUNCTION `func3_nope`() RETURNS int(11) 
BEGIN 
    declare r int default 0; 
    IF 1=2 THEN 
select does_not_exit('test finc', 'test func', 1) into r; 
    END IF; 
    IF 2=3 THEN 
select does_not_exit('test finc', 'test func', 10) into r; 
    END IF; 
    IF 3=4 THEN 
select does_not_exit('test finc', 'test func', 100) into r; 
    END IF; 
    IF 4=5 THEN 
select does_not_exit('test finc', 'test func', 1000) into r; 
    END IF; 
RETURN r; 
END 

This is the same as function 3, but the function we are running does not exist. Well, it does not matter as the selectdoes_not_exit will never run.

So, all the functions will always return 0. We expect that the performance of these functions will be the same or very similar. Surprisingly, that is not the case! To measure the performance, I used the "benchmark" function to run the same function 1M times. Here are the results:

+-----------------------------+ 
| benchmark(1000000, func1()) | 
+-----------------------------+ 
|                           0 | 
+-----------------------------+ 
1 row in set (1.75 sec) 
+-----------------------------+ 
| benchmark(1000000, func2()) | 
+-----------------------------+ 
|                           0 | 
+-----------------------------+ 
1 row in set (2.45 sec) 
+-----------------------------+ 
| benchmark(1000000, func3()) | 
+-----------------------------+ 
|                           0 | 
+-----------------------------+ 
1 row in set (3.85 sec) 
+----------------------------------+ 
| benchmark(1000000, func3_nope()) | 
+----------------------------------+ 
|                                0 | 
+----------------------------------+ 
1 row in set (3.85 sec) 

（编辑：河北网）

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