mysql 存储过程优化

Question

mysql存储优化

Answer 1

 
执行elseif vDeport='0' then
        -- 仓库未收=全部-仓库已收
        open mycur;
            myloop:loop
                fetch mycur into tpid,tdeliverydate,tdes,tcountry,tfactory,txxxs,txxs,txs,ts,tm,tl,txl,txxl,txxxl,txxxxl,txxxxxl,tf,tqty,
                            tiscoating,tishotprintlogo,tpatternno,toid,torderno,tcusno,tcusname,titemNo;
                if done=1 then leave myloop;end if;
                    SELECT SUM(xxxs),SUM(xxs),SUM(xs),SUM(s),SUM(m),SUM(l),SUM(xl),SUM(xxl),SUM(xxxl),SUM(xxxxl),SUM(xxxxxl),SUM(f),sum(qty) from csp_printing_wkscan
                    where orderdetailid=tpid into txxxs1,txxs1,txs1,ts1,tm1,tl1,txl1,txxl1,txxxl1,txxxxl1,txxxxxl1,tf1,tqty1;
                -- 仓库未收
                if isnull(tqty1) then
                    insert into temp_unheat values(tpid,tdeliverydate,tDes,tcountry,tfactory,tcusno,tcusname,tXxxs,tXxs,tXs,tS,tM,tL,tXl,tXxl,tXxxl,tXxxxl,tXxxxxl,tF,tQty,
                    tIsCoating,tIsHotPrintLogo,tPatternNo,toid,tOrderNo,tItemNo);
                elseif tQty-tQty1>0 then
                    insert into temp_unheat
                    values(tpid,tdeliverydate,tDes,tcountry,tfactory,tcusno,tcusname,tXxxs-tXxxs1,tXxs-tXxs1,tXs-tXs1,tS-tS1,tM-tM1,tL-tL1,tXl-tXl1,tXxl-tXxl1,
                    tXxxl-tXxxl1,tXxxxl-tXxxxl1,tXxxxxl-tXxxxxl1,tF-tF1,tQty-tQty1,tIsCoating,tIsHotPrintLogo,tPatternNo,
                    toid,tOrderNo,tItemNo);
                end if;
            end loop myloop;       
        close mycur;
        set @vsql=concat('select * from temp_unheat order by ',vSortField,' ',vSortWay);  一样是一步一步执行的，怎么能看出问题？

Answer 2

 我整个存储过程已经贴上去了 麻烦帮我看下...执行游标 然后就是insert

Answer 3

存储过程里面那么多insert，可以设置一下set autocommit=0; 然后自己控制何时commit;

Answer 4

elseif vDeport='0' then
        -- 仓库未收=全部-仓库已收
        open mycur;这里面有游标，mysql里面的游标性能是最慢的，oracle或者sqlserver或者db2，游标遍历数一次超过10000，性能都会很差的。
而mysql的游标性能比oracle他们来说性能更差了，你一次游标遍历就3000以上有些多了。
你可以做一个循环，循环里面一次只能让游标遍历200条记录，释放游标资源。伪代码如下：
create procedure pro_curxx()
cursor ..... limit 200;
open cursor
....
end;for (i=0;i++;i)
{ 
  call pro_curxx();
}大概类似这种思路。
mysql里面存储过程能不用游标就不要用游标啊。

Answer 5

 你说的对于我有点高深，
什么是时标，什么是放矢？时标就是执行时间，比如select now();把这个输入到日志里面。关键步骤的地方，多输出几次，看看执行效率，到底是慢在哪一句sql代码上面。

Answer 6

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？

Answer 7

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s

Answer 8

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s1，explain下你的view sql，我看看分析结果。2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？

Answer 9

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s1，explain下你的view sql，我看看分析结果。2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？ 找来找去都不知道如何显示时间毫秒， now()只精确到了s

Answer 10

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s1，explain下你的view sql，我看看分析结果。2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？
explain select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view;
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | o     | ALL  | PRIMARY       | NULL       | NULL    | NULL              | 6771 | Using where |
|  1 | SIMPLE      | p     | ref  | fk_orderid    | fk_orderid | 4       | ch_base_data.o.id |    3 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+

Answer 11

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s1，explain下你的view sql，我看看分析结果。2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？
explain select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view;
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | o     | ALL  | PRIMARY       | NULL       | NULL    | NULL              | 6771 | Using where |
|  1 | SIMPLE      | p     | ref  | fk_orderid    | fk_orderid | 4       | ch_base_data.o.id |    3 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+1，看了下expkain是否没有问题的。也用到了索引了，对了你的csp_order表下面explain是all全表扫描 的，csp_order表数据量第多少条？
2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001,
你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？关于第2条，你尝试了吗，现象是什么呢？

Answer 12

为嘛要用loop？不就是两种insert么，写两个insert-select语句就搞定了。loop的话，你执行了多少sql？不慢才怪！

Answer 13

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s1，explain下你的view sql，我看看分析结果。2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？
explain select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view;
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | o     | ALL  | PRIMARY       | NULL       | NULL    | NULL              | 6771 | Using where |
|  1 | SIMPLE      | p     | ref  | fk_orderid    | fk_orderid | 4       | ch_base_data.o.id |    3 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+1，看了下expkain是否没有问题的。也用到了索引了，对了你的csp_order表下面explain是all全表扫描 的，csp_order表数据量第多少条？
2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001,
你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？关于第2条，你尝试了吗，现象是什么呢？
不好意思，前两天有事耽误了，
1.csp_order 一共6900左右，
2.在mysql中获取当前时间如何精确到毫秒呢？now()只精确到秒

Answer 14

我需要遍历数据判断之后insert

Answer 15

我其他存储过程也是用了游标，数据量也差不多，那个2s就搞定了，这个也不知道是什么问题你存储过程里面，我看了下，很简单的select，insert之类的sql，唯一复杂的sql那就是你的游标部分：
declare mycur cursor for (select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view)这儿貌似webuser_view是视图view，你单独执行下这个：
select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
        productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view需要多长时间？看下是否时间消耗在这个view的查询里面呢？  耗时0.51s1，explain下你的view sql，我看看分析结果。2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？
explain select id,deliverydate,des,country,factory,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
productqty,iscoating,ishotprintlogo,patternno,orderid,orderno,cusno,cusname,itemNo  
        from webuser_view;
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | o     | ALL  | PRIMARY       | NULL       | NULL    | NULL              | 6771 | Using where |
|  1 | SIMPLE      | p     | ref  | fk_orderid    | fk_orderid | 4       | ch_base_data.o.id |    3 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+1，看了下expkain是否没有问题的。也用到了索引了，对了你的csp_order表下面explain是all全表扫描 的，csp_order表数据量第多少条？
2，在你每一次遍历循环游标的时候，都select now() 写入一个临时表里面，临时表就记录2个字段table_tmp(id int, do_time datetime); id是遍历游标的次数，datetime是每次遍历游标的时间，时间记录到毫秒，比如2014-09-13 12:12:12.001,
你再看看执行完游标之后，每次遍历游标所需要的时间，是不是刚开始很快，以后慢慢变得越来越慢？关于第2条，你尝试了吗，现象是什么呢？
不好意思，前两天有事耽误了，
1.csp_order 一共6900左右，
2.在mysql中获取当前时间如何精确到毫秒呢？now()只精确到秒1，表数据量少
2，去查了下，select now（）确实精确不到毫秒数。

Answer 16

elseif vDeport='0' then
        -- 仓库未收=全部-仓库已收
        open mycur;
            myloop:loop
                fetch mycur into tpid,tdeliverydate,tdes,tcountry,tfactory,txxxs,txxs,txs,ts,tm,tl,txl,txxl,txxxl,txxxxl,txxxxxl,tf,tqty,
                            tiscoating,tishotprintlogo,tpatternno,toid,torderno,tcusno,tcusname,titemNo;
                if done=1 then leave myloop;end if;
                    SELECT SUM(xxxs),SUM(xxs),SUM(xs),SUM(s),SUM(m),SUM(l),SUM(xl),SUM(xxl),SUM(xxxl),SUM(xxxxl),SUM(xxxxxl),SUM(f),sum(qty) from csp_printing_wkscan
                    where orderdetailid=tpid into txxxs1,txxs1,txs1,ts1,tm1,tl1,txl1,txxl1,txxxl1,txxxxl1,txxxxxl1,tf1,tqty1;
                -- 仓库未收
                if isnull(tqty1) then
                    insert into temp_unheat values(tpid,tdeliverydate,tDes,tcountry,tfactory,tcusno,tcusname,tXxxs,tXxs,tXs,tS,tM,tL,tXl,tXxl,tXxxl,tXxxxl,tXxxxxl,tF,tQty,
                    tIsCoating,tIsHotPrintLogo,tPatternNo,toid,tOrderNo,tItemNo);
                elseif tQty-tQty1>0 then
                    insert into temp_unheat
                    values(tpid,tdeliverydate,tDes,tcountry,tfactory,tcusno,tcusname,tXxxs-tXxxs1,tXxs-tXxs1,tXs-tXs1,tS-tS1,tM-tM1,tL-tL1,tXl-tXl1,tXxl-tXxl1,
                    tXxxl-tXxxl1,tXxxxl-tXxxxl1,tXxxxxl-tXxxxxl1,tF-tF1,tQty-tQty1,tIsCoating,tIsHotPrintLogo,tPatternNo,
                    toid,tOrderNo,tItemNo);
                end if;
            end loop myloop;       
        close mycur;
        set @vsql=concat('select * from temp_unheat order by ',vSortField,' ',vSortWay); 我看到这里面有 
        set @vsql=concat('select * from temp_unheat order by ',vSortField,' ',vSortWay); 是否慢在这一句select * from  temp_unheat order by ...上面，毕竟表数据越来越多，order by如果没有走索引的话，肯定是会越来越慢的，loop一次就select * from temp_unheat全表一次，而且还有order by操作。explain下这个select语句看看执行计划性能如何?

Answer 17

我是在关闭loop 和cursor 之后才select 的，那也才执行了一次，
explain select * from temp_ToDeport order by OrderNo desc;
id select_type  table         type      possible_keys  key   key_len  ref    rows Extra
1 SIMPLE  temp_ToDeport ALL      NULL                  NULL NULL NULL 34 Using filesort

Answer 18

我需要遍历数据判断之后insert
insert-select语句中，select的where不就可以判断吗？

Answer 19

 我终于找到了问题原因所在了 
SELECT SUM(xxxs),SUM(xxs),SUM(xs),SUM(s),SUM(m),SUM(l),SUM(xl),SUM(xxl),SUM(xxxl),SUM(xxxxl),SUM(xxxxxl),SUM(f),sum(qty) from csp_printing_wkscan
                    where orderdetailid=tpid into txxxs1,txxs1,txs1,ts1,tm1,tl1,txl1,txxl1,txxxl1,txxxxl1,txxxxxl1,tf1,tqty1;
是这个select 里面有5W条数据，又没有orderdetailid 索引，所以很慢，加了索引之后2S就整个存储过程就执行完了。很少接触这些，以前都是增删改查没涉及到这些，非常感谢你的耐心解答.