游标是不推荐使用的，慢，如果真得用，那还是放到后台php去循环好。
主要是没弄懂你要做的 *** 作和逻辑，不然mysql用join来 *** 作，把游标去掉，换成mysql自己每行去遍历，估计也就毫秒级。（估计你目前整个表的数据也就几万而已）
我们先来看第一个阶段，MySQL慢的诊断思路，一般我们会从三个方向来做：
第一个方向是MySQL内部的观测
第二个方向是外部资源的观测
第三个方向是外部需求的改造
1.1 MySQL 内部观测
我们来看MySQL内部的观测，常用的观测手段是这样的，从上往下看，第一部分是Processlist，看一下哪个SQL压力不太正常，第二步是explain，解释一下它的执行计划，第三步我们要做Profilling，如果这个SQL能再执行一次的话, 就做一个Profilling，然后高级的DBA会直接动用performance_schema ，MySQL 5.7 以后直接动用sys_schema，sys_schema是一个视图，里面有便捷的各类信息，帮助大家来诊断性能。再高级一点，我们会动用innodb_metrics进行一个对引擎的诊断。
除了这些手段以外，大家还提出了一些乱七八糟的手段，我就不列在这了，这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外，MySQL还陆陆续续提供了一些暴露自己状态的方案，但是这些方案并没有在实践中形成套路，原因是学习成本比较高。
1.2 外部资源观测
外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令，这是前五条，我们一条一条来看。
1.uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少。
2.dmesg -T | tail，告诉我们系统日志里边有没有什么报错。
3.vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap有没有使用。
4. mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的。
5.pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。
我们来看一下后五条：
首先是iostat-xz 1，查看IO的问题，然后是free-m内存使用率，之后两个sar，按设备网卡设备的维度，看一下网络的消耗状态，以及总体看TCP的使用率和错误率是多少。最后一条命令top，看一下大概的进程和线程的问题。
这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。
1.3 外部需求改造
第三个诊断思路是外部的需求改造，我在这里引用了一篇文档，这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries，文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。
我们来看一下它其中提到的一个例子。
它做的事情是从一个表里边去选取，这张表有三列，article、dealer、price，选取每个作者的最贵的商品列在结果集中，这是它的最原始的SQL，非常符合业务的写法，但是它是个关联子查询。
关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。
第三步，会教大家直接把子查询拿掉，然后转成这样一个SQL，这个就叫业务改造，前后三个SQL的成本都不一样，把关联子查询拆掉的成本，拆掉以后SQL会跑得非常好，但这个SQL已经不能良好表义了，只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式。
为什么它能够把一个关联子查询拆掉呢？
这背后的原理是关系代数，所有的SQL都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。
上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。
第一，MySQL本身提供了很多命令来观察MySQL自身的各类状态，大家从上往下检一般能检到SQL的问题或者服务器的问题。
第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，我们把服务器的资源全部都观察一圈就可以了。
第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写SQL，这个成本会下降的非常快，这个是常规的MySQL慢的诊断思路。
在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的 *** 作，这个时候就会用存储过程来封装数据库 *** 作。如果项目的存储过程较多，书写又没有一定的规范，将会影响以后的系统维护困难和大存储过程逻辑的难以理解，另外如果数据库的数据量大或者项目对存储过程的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍。下面介绍某一个MySQL存储过程优化的整个过程。在本文中，需要被优化的存储过程如下：
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'
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）