在本文中，需要被优化的存储过程如下：
drop procedure if exists pr_dealtestnum
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30)
)
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'
在存储过程中使用到的表tb_testnum结构如下：
drop table if exists tb_testnum
create table tb_testnum
(
boxnumber varchar(30) not null,
usertype int not null
)
create unique index idx1_tb_testnum ontb_testnum(boxnumber)
在存储过程中使用到的另外一张表tb_testnum_tmp结构如下：
drop table if exists tb_testnum_tmp
create table tb_testnum_tmp
(
boxnumber varchar(30) not null,
usertype int not null
)
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表中。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30)
)
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'
优化二
在向tb_testnum表插入数据之前，要判断该条数据在表中是否已经存在了，如果存在，则不再插入数据。同理，在从tb_testnum_tmp表中查询数据之前，要先判断该条数据在表中是否存在，如果存在，才能从表中查找数据。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30)
)
pr_dealtestnum_label:begin
declare p_usertype int
declare p_datacount int
select count(*) into 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 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'
优化三
不管向tb_testnum表插入数据的 *** 作执行成功与否，都应该有一个标识值来表示执行的结果，这样也方便开发人员对程序流程的追踪和调试。也就是说，在每条leave语句之前，都应该有一个返回值，我们为此定义一个输出参数。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30),
out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
declare p_usertype int
declare p_datacount int
select count(*) into 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 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'
优化四
我们注意到“insert into tb_testnum values(p_boxnumber,p_usertype)”语句中，tb_testnum表之后没有列出具体的字段名，这个也是不规范的。如果在以后的软件版本中，tb_testnum表中新增了字段，那么这条insert语句极有可能会报错。因此，规范的写法是无论tb_testnum表中有多少字段，在执行insert *** 作时，都要列出具体的字段名。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30),
out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
declare p_usertype int
declare p_datacount int
select count(*) into 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 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'
优化五
在执行insert语句之后，要用MySQL中自带的@error_count参数来判断插入数据是否成功，方便开发人员跟踪执行结果。如果该参数的值不为0，表示插入失败，那么我们就用一个返回参数值来表示 *** 作失败。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30),
out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
declare p_usertype int
declare p_datacount int
select count(*) into 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 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
end if
leave pr_dealtestnum_label
end
//
delimiter
select 'create procedure pr_dealtestnum ok'
1.当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate，Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的（没什么卵用的意思）。
3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析
4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlr.jar。这个就是专门做语法解析的工具.因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.
5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...
6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化
7.查询优化器，是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.
8.这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1
9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.(优化就是制定一个我们认为最快的执行方案,最节省IO,和执行最快)
10.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）