对一个MySQL存储过程的优化

开发 开发工具
从上面可以看出,一个短短的存储过程,就有这么多需要优化的地方,看来存储过程的编写也不是一件很简单的事情。确实,我们在编写代码(不仅仅是存储过程)的时候,一定要从代码的功能、可读性、性能等多方面来考虑,这样才能够写出优美的、具备较长生命周期的代码,进而开发出高质量的软件产品。

[[179304]]

在编写MySQL存储过程的过程中,我们会时不时地需要对某些存储过程进行优化,其目的是确保代码的可读性、正确性及运行性能。本文以作者实际工作为背景,介绍了对某一个MySQL存储过程优化的整个过程。

在本文中,需要被优化的存储过程如下:

  1. drop procedure if exists pr_dealtestnum; 
  2. delimiter // 
  3.  
  4. create procedure pr_dealtestnum 
  5.    in    p_boxnumber    varchar(30) 
  6. pr_dealtestnum_label:begin 
  7.  
  8.        insert into tb_testnum select boxnumber,usertype from tb_testnum_tmp where boxnumber= p_boxnumber; 
  9.  
  10.        leave pr_dealtestnum_label; 
  11. end
  12. // 
  13. delimiter ; 
  14. select 'create procedure pr_dealtestnumok'

在存储过程中使用到的表tb_testnum结构如下:

  1. drop table if exists tb_testnum; 
  2.  
  3. create table tb_testnum 
  4.    boxnumber  varchar(30)  not null
  5.    usertype   int          not null                                                                                  
  6. ); 
  7. create unique index idx1_tb_testnum ontb_testnum(boxnumber); 

在存储过程中使用到的另外一张表tb_testnum_tmp结构如下:

  1. drop table if exists tb_testnum_tmp; 
  2.  
  3. create table tb_testnum_tmp 
  4.    boxnumber  varchar(30)  not null
  5.    usertype   int          not null     
  6. ); 
  7. create unique index idx1_tb_testnum_tmp ontb_testnum_tmp(boxnumber); 

从两个表的结构可以看出,tb_testnum和tb_testnum_tmp所包含的字段完全相同,存储过程pr_dealtestnum的作用是根据输入参数将tb_testnum_tmp表的数据插入到tb_testnum表中。

很明显,虽然能够实现预期的功能,但存储过程pr_dealtestnum的代码还有改进的地方。下面,我们一步一步来对其进行优化。

优化一

存储过程pr_dealtestnum的主体是一条insert语句,但这条insert语句里面又包含了select语句,这样的编写是不规范的。因此,我们要把这条insert语句拆分成两条语句,即先把数据从tb_testnum_tmp表中查找出来,再插入到tb_testnum表中。修改之后的存储过程如下:

  1. drop procedure if exists pr_dealtestnum; 
  2. delimiter // 
  3.  
  4. create procedure pr_dealtestnum 
  5.    in    p_boxnumber    varchar(30) 
  6. pr_dealtestnum_label:begin 
  7.        declare  p_usertype    int
  8.  
  9.        select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber; 
  10.  
  11.        insert into tb_testnum values(p_boxnumber,p_usertype); 
  12.  
  13.        leave pr_dealtestnum_label; 
  14. end
  15. // 
  16. delimiter ; 
  17. select 'create procedure pr_dealtestnum ok'

优化二

在向tb_testnum表插入数据之前,要判断该条数据在表中是否已经存在了,如果存在,则不再插入数据。同理,在从tb_testnum_tmp表中查询数据之前,要先判断该条数据在表中是否存在,如果存在,才能从表中查找数据。修改之后的存储过程如下:

  1. drop procedure if exists pr_dealtestnum; 
  2. delimiter // 
  3.  
  4. create procedure pr_dealtestnum 
  5.    in    p_boxnumber    varchar(30) 
  6. pr_dealtestnum_label:begin 
  7.        declare  p_usertype   int
  8.        declare  p_datacount  int
  9.  
  10.        select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber; 
  11.        if p_datacount > 0 then 
  12.        begin 
  13.            select usertype into p_usertype fromtb_testnum_tmp where boxnumber=p_boxnumber; 
  14.        end
  15.        else 
  16.        begin 
  17.            leave pr_dealtestnum_label; 
  18.        end
  19.        end if; 
  20.  
  21.        select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber; 
  22.        if p_datacount = 0 then 
  23.        begin 
  24.            insert into tb_testnum values(p_boxnumber,p_usertype); 
  25.            leave pr_dealtestnum_label; 
  26.        end
  27.        else 
  28.        begin 
  29.            leave pr_dealtestnum_label; 
  30.        end
  31.        end if; 
  32. end
  33. // 
  34. delimiter ; 
  35. select 'create procedure pr_dealtestnum ok'

优化三

不管向tb_testnum表插入数据的操作执行成功与否,都应该有一个标识值来表示执行的结果,这样也方便开发人员对程序流程的追踪和调试。也就是说,在每条leave语句之前,都应该有一个返回值,我们为此定义一个输出参数。修改之后的存储过程如下:

  1. drop procedure if exists pr_dealtestnum; 
  2. delimiter // 
  3.  
  4. create procedure pr_dealtestnum 
  5.    in    p_boxnumber  varchar(30), 
  6.    out   p_result     int  -- 0-succ, other-fail 
  7. pr_dealtestnum_label:begin 
  8.        declare  p_usertype   int
  9.        declare  p_datacount  int
  10.  
  11.        select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber; 
  12.        if p_datacount > 0 then 
  13.        begin 
  14.            select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber; 
  15.        end
  16.        else 
  17.        begin 
  18.            set p_result = 1; 
  19.            leave pr_dealtestnum_label; 
  20.        end
  21.        end if; 
  22.  
  23.        select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber; 
  24.        if p_datacount = 0 then 
  25.        begin 
  26.            insert into tb_testnum values(p_boxnumber,p_usertype); 
  27.            set p_result = 0; 
  28.            leave pr_dealtestnum_label; 
  29.        end
  30.        else 
  31.        begin 
  32.            set p_result = 2; 
  33.            leave pr_dealtestnum_label; 
  34.        end
  35.        end if; 
  36. end
  37. // 
  38. delimiter ; 
  39. select 'create procedure pr_dealtestnum ok'

优化四

我们注意到“insert into tb_testnum values(p_boxnumber,p_usertype);”语句中,tb_testnum表之后没有列出具体的字段名,这个也是不规范的。如果在以后的软件版本中,tb_testnum表中新增了字段,那么这条insert语句极有可能会报错。因此,规范的写法是无论tb_testnum表中有多少字段,在执行insert操作时,都要列出具体的字段名。修改之后的存储过程如下:

  1. drop procedure if exists pr_dealtestnum; 
  2. delimiter // 
  3.  
  4. create procedure pr_dealtestnum 
  5.    in    p_boxnumber  varchar(30), 
  6.    out   p_result     int   -- 0-succ, other-fail 
  7. pr_dealtestnum_label:begin 
  8.        declare  p_usertype   int
  9.        declare  p_datacount  int
  10.  
  11.        select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber; 
  12.        if p_datacount > 0 then 
  13.        begin 
  14.            select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber; 
  15.        end
  16.        else 
  17.        begin 
  18.            set p_result = 1; 
  19.            leave pr_dealtestnum_label; 
  20.        end
  21.        end if; 
  22.  
  23.        select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber; 
  24.        if p_datacount = 0 then 
  25.        begin 
  26.            insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype); 
  27.            set p_result = 0; 
  28.            leave pr_dealtestnum_label; 
  29.        end
  30.        else 
  31.        begin 
  32.            set p_result = 2; 
  33.            leave pr_dealtestnum_label; 
  34.        end
  35.        end if; 
  36. end
  37. // 
  38. delimiter ; 
  39. select 'create procedure pr_dealtestnum ok'

优化五

在执行insert语句之后,要用MySQL中自带的@error_count参数来判断插入数据是否成功,方便开发人员跟踪执行结果。如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。修改之后的存储过程如下:

  1. drop procedure if exists pr_dealtestnum; 
  2. delimiter // 
  3.  
  4. create procedure pr_dealtestnum 
  5.     in    p_boxnumber varchar(30), 
  6.     out   p_result    int   -- 0-succ, other-fail 
  7. pr_dealtestnum_label:begin 
  8.        declare  p_usertype   int
  9.        declare  p_datacount  int
  10.  
  11.        select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber; 
  12.        if p_datacount> 0 then 
  13.        begin 
  14.            select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber; 
  15.        end
  16.        else 
  17.        begin 
  18.            set p_result = 1; 
  19.            leave pr_dealtestnum_label; 
  20.        end
  21.        end if; 
  22.  
  23.        select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber; 
  24.        if p_datacount = 0then 
  25.        begin 
  26.            insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype); 
  27.            if @error_count<>0 then 
  28.            begin 
  29.                set p_result= 3; 
  30.            end
  31.            else 
  32.            begin 
  33.                set p_result= 0; 
  34.            end
  35.            end if; 
  36.        end
  37.        else 
  38.        begin 
  39.            set p_result = 2; 
  40.        end
  41.        end if; 
  42.  
  43.        leave pr_dealtestnum_label; 
  44. end
  45. // 
  46. delimiter ; 
  47. select 'create procedure pr_dealtestnum ok'

结束语

从上面可以看出,一个短短的存储过程,就有这么多需要优化的地方,看来存储过程的编写也不是一件很简单的事情。确实,我们在编写代码(不仅仅是存储过程)的时候,一定要从代码的功能、可读性、性能等多方面来考虑,这样才能够写出优美的、具备较长生命周期的代码,进而开发出高质量的软件产品。

【本文是51CTO专栏作者周兆熊的原创文章,作者微信公众号:周氏逻辑(logiczhou)】

责任编辑:武晓燕 来源: csdn博客
相关推荐

2010-04-15 17:45:26

Oracle存储过程

2011-03-28 09:56:03

存储增删操作

2010-04-16 13:22:39

OracleJavaP

2011-07-18 15:08:19

SQL存储过程

2022-10-26 10:56:29

数据库存储引擎

2010-05-31 16:57:09

2011-06-30 09:37:08

JavaDB2SQL

2010-06-04 13:50:24

MySQL存储过程

2011-07-21 16:28:20

MySQL数据库带游标的存储过程

2010-04-15 17:31:10

Oracle存储过程

2011-05-17 15:13:59

oracle分页存储

2010-07-21 13:33:13

SQL Server存

2012-02-16 10:12:23

JavaScript

2010-05-19 14:13:12

MySQL存储过程

2011-07-22 15:38:54

SQL Server数存储过程切割字符串

2021-01-28 19:31:59

MySQL手册方法

2012-03-01 13:34:02

Java

2011-08-18 18:18:05

MySQL数据库优化

2010-09-07 15:12:25

SQL语句优化

2011-08-29 14:33:02

Oracle存储过程
点赞
收藏

51CTO技术栈公众号