Optimization example of Mysql stored procedure _mysql

Objective

During the development of the database, the complex business logic and the operation of the database are often encountered, and the stored procedures are used to encapsulate the database operations. If the project has more stored procedures, writing and there is no specification, will affect the future system maintenance difficulties and large stored process logic is difficult to understand, in addition, if the data volume of the database or the project on the performance requirements of the stored procedures, you will encounter optimization problems, or speed may be very slow, after personal experience, An optimized stored procedure is even hundreds of times times more efficient than a poorly performing stored procedure. The entire process of optimizing a MySQL stored procedure is described below.

In this article, the stored procedures that need to be optimized are as follows:

drop procedure if exists pr_dealtestnum;
Delimiter//

CREATE PROCEDURE Pr_dealtestnum
(
  in  p_boxnumber  varchar ()
)
Pr_ Dealtestnum_label:begin

    INSERT INTO Tb_testnum select Boxnumber,usertype from tb_testnum_tmp where boxnumber= p_ Boxnumber;

    Leave Pr_dealtestnum_label;
End;
delimiter;
Select ' CREATE PROCEDURE Pr_dealtestnumok ';

The table tb_testnum structure used in the stored procedure is as follows:

drop table if exists tb_testnum;

CREATE TABLE Tb_testnum
(
  boxnumber varchar () NOT NULL,
  usertype  int not     null                                         
);
Create unique index Idx1_tb_testnum ontb_testnum (boxnumber);

Another table used in the stored procedure TB_TESTNUM_TMP structure is as follows:

drop table if exists tb_testnum_tmp;

CREATE TABLE Tb_testnum_tmp
(
  boxnumber varchar () NOT NULL,
  usertype  int not     null  
);
Create unique index idx1_tb_testnum_tmp ontb_testnum_tmp (boxnumber);

As you can see from the structure of the two tables,tb_testnum and tb_testnum_tmp contain exactly the same fields, and the stored procedure pr_dealtestnum is based on the input parameters The data in the tb_testnum_tmp table is inserted into the tb_testnum table.

Obviously, although the expected functionality can be achieved, the code for the stored procedure Pr_dealtestnum has been improved.

Next, we optimize it step-by-step.

Optimize a

The body of the stored procedure pr_dealtestnum is an INSERT statement, but the INSERT statement contains a SELECT statement, which is not canonical. So we're going to split this insert statement into two statements, which is to first look up the data from the tb_testnum_tmp table and insert it into the tb_testnum table. The modified stored procedures are as follows:

drop procedure if exists pr_dealtestnum;
Delimiter//

CREATE PROCEDURE Pr_dealtestnum
(
  in  p_boxnumber  varchar ()
)
Pr_ Dealtestnum_label:begin
    declare p_usertype  int;

    Select Usertype into P_usertype from tb_testnum_tmp where Boxnumber=p_boxnumber;

    INSERT into tb_testnum values (p_boxnumber,p_usertype);

    Leave Pr_dealtestnum_label;
End;
delimiter;
Select ' CREATE procedure pr_dealtestnum OK ';

Optimize two

Before inserting data into the tb_testnum table, determine whether the piece of data already exists in the table, and if so, no longer insert the data. In the same way, before querying data from the tb_testnum_tmp table, you must determine whether the data exists in the table, and if it exists, you can look up data from the table. The modified stored procedures are as follows:

drop procedure if exists pr_dealtestnum;
Delimiter//

CREATE PROCEDURE Pr_dealtestnum
(
  in  p_boxnumber  varchar ()
)
Pr_ Dealtestnum_label:begin
    declare p_usertype  int;
    declare p_datacount int;

    Select COUNT (*) into the p_datacount from tb_testnum_tmp where Boxnumber=p_boxnumber;
    If P_datacount > 0 then
    begin
      Select usertype into P_usertype fromtb_testnum_tmp where boxnumber=p_ Boxnumber;
    End;
    else
    begin
      leave Pr_dealtestnum_label;
    End;
    End If;

    Select COUNT (*) into the p_datacount from Tb_testnum where Boxnumber=p_boxnumber;
    If P_datacount = 0 Then
    begin
      INSERT INTO tb_testnum values (p_boxnumber,p_usertype);
      Leave Pr_dealtestnum_label;
    End;
    else
    begin
      leave Pr_dealtestnum_label;
    End;
    End If;
End;
delimiter;
Select ' CREATE procedure pr_dealtestnum OK ';

Optimization Three

Regardless of the success of the operation that inserts data into the tb_testnum table, you should have an identity value that represents the results of the execution, which also facilitates the tracing and debugging of the program process by the developer. In other words, there should be a return value before each leave statement, and we define an output parameter for that. The stored procedure after the modification is as follows:

drop procedure if exists pr_dealtestnum; 
Delimiter//CREATE PROCEDURE Pr_dealtestnum (in P_boxnumber varchar (a), out P_result int--0-SUCC, Other-fail
    ) Pr_dealtestnum_label:begin declare p_usertype int;

    declare p_datacount int;
    Select COUNT (*) into the p_datacount from tb_testnum_tmp where Boxnumber=p_boxnumber; If p_datacount > 0 THEN BEGIN select Usertype into P_usertype from tb_testnum_tmp where Boxnumber=p_boxnumber
    ;
    End
      else BEGIN set P_result = 1;
    Leave Pr_dealtestnum_label;
    End

    End If;
    Select COUNT (*) into the p_datacount from Tb_testnum where Boxnumber=p_boxnumber;
      If P_datacount = 0 THEN begin INSERT into tb_testnum values (P_boxnumber,p_usertype);
      Set p_result = 0;
    Leave Pr_dealtestnum_label;
    End
      else begin set P_result = 2;
    Leave Pr_dealtestnum_label;
    End
End If;
End
delimiter; Select ' CREATE procedure pr_dealtestnum OK ';

Optimize four

We note that " insert into tb_testnum values(p_boxnumber,p_usertype);” in the statement, thetb_testnum table does not list the specific field names, and this is not canonical." If a new field is added to thetb_testnum table in a later version of the software, the INSERT statement is highly likely to complain. Therefore, the specification is written to list the specific field names, regardless of the number of fields in the tb_testnum table, when the insert operation is performed. The modified stored procedures are as follows:

drop procedure if exists pr_dealtestnum; Delimiter//CREATE PROCEDURE Pr_dealtestnum (in P_boxnumber varchar (a), out P_result int--0-SUCC, Other-fai
    L) Pr_dealtestnum_label:begin declare p_usertype int;

    declare p_datacount int;
    Select COUNT (*) into the p_datacount from tb_testnum_tmp where Boxnumber=p_boxnumber; If p_datacount > 0 THEN BEGIN select Usertype into P_usertype from tb_testnum_tmp where Boxnumber=p_boxnumber
    ;
    End
      else BEGIN set P_result = 1;
    Leave Pr_dealtestnum_label;
    End

    End If;
    Select COUNT (*) into the p_datacount from Tb_testnum where Boxnumber=p_boxnumber;
      If P_datacount = 0 THEN begin INSERT into Tb_testnum (Boxnumber,usertype) values (P_boxnumber,p_usertype);
      Set p_result = 0;
    Leave Pr_dealtestnum_label;
    End
      else begin set P_result = 2;
    Leave Pr_dealtestnum_label;
    End
End If;
End
delimiter; Select ' CREATE PROCEDURE Pr_dealtestnuM OK '; 

Optimize five

After the INSERT statement is executed, it is useful to use the parameters in MySQL to @error_count determine whether the insert data is successful or not, so that developers can track execution results. If the value of this parameter is not 0, which means that the insertion failed, then we use a return parameter value to indicate that the operation failed. The modified stored procedures are as follows:

drop procedure if exists pr_dealtestnum; 
Delimiter//CREATE PROCEDURE Pr_dealtestnum (in P_boxnumber varchar (a), out P_result int--0-SUCC, Other-fail
    ) Pr_dealtestnum_label:begin declare p_usertype int;

    declare p_datacount int;
    Select COUNT (*) into the p_datacount from tb_testnum_tmp where Boxnumber=p_boxnumber; 
    If p_datacount> 0 THEN begin select Usertype into P_usertype from tb_testnum_tmp where Boxnumber=p_boxnumber;
    End
      else BEGIN set P_result = 1;
    Leave Pr_dealtestnum_label;
    End

    End If;
    Select COUNT (*) into the p_datacount from Tb_testnum where Boxnumber=p_boxnumber;
      If p_datacount = 0then begin INSERT INTO Tb_testnum (Boxnumber,usertype) values (P_boxnumber,p_usertype);
      If @error_count <>0 then begin set p_result= 3;
      End
      Else begin set p_result= 0;
      End
    End If;
    End
    else begin set P_result = 2;
    End EndIf
Leave Pr_dealtestnum_label;
End
delimiter; Select ' CREATE procedure pr_dealtestnum OK ';

Summarize

As can be seen from the above, a short stored procedure, there are so many need to optimize the place, it seems that the writing of stored procedures is not a very simple thing. Indeed, when we write code (not just stored procedures), we must take into account the function of the code, readability, performance and so on, so as to be able to write beautiful, with a longer life cycle of code, and then develop high-quality software products. I hope this article will help you learn the MySQL stored process, but also thank you for your support to the cloud-dwelling community.