游标改写案例一则

    最近有用户反映表里的记录数只有10万多条，但存储过程执行时性能很差。检查存储过程的定义，发现其中用过了游标，而游标自身的特性决定了它不能批量处理数据，因此性能较差。根据用户的需求，下面我们做一个例子具体分析。

表结构定义：
create table emp1(name varchar(100), mobile varchar(100));
create table emp2(name varchar(100), mobile varchar(100));

emp1数据：
insert into emp1 values('张1/张2/张3','13111111111/13122222222');    -– 共10万多条记录，每个值拼接在一起的姓名大多数在3个，有的人只有姓名没有手机号。
insert into emp1 values('李1/李    2/','15011111111/15022222222');
insert into emp1 values('王1/王2/王 3/王4/王5','188 11 111111/18822222222/18833333333');
insert into emp1 values('赵1','17911111111');
insert into emp1 values('陈1',NULL);
......
需求如下：

1. 去除emp表中每个值中包含的所有空格；
2. 将每条记录的每个值按“/”进行拆分，拆分后的姓名与手机能一一对应，然后形成多条记录插入到emp2中。如元组：    ('张1/张  2/张3','13111111111/13122222222')  拆分后：    ('张1','13111111111')， ('张2','13122222222')， ('张3',NULL)

实现思路：
截取每个值子字段串，可以用substring函数实现，可是每行记录的姓名不固定，从而截取次数也无法确认，基于此，首先想到的是用游标实现该功能。

存储过程定义：

DROP PROCEDURE IF EXISTS prc_split01;
DELIMITER //
CREATE PROCEDURE prc_split01()
begin
    DECLARE v1 VARCHAR(100);
    DECLARE v2 VARCHAR(100);
    DECLARE DONE INT DEFAULT(0);
    DECLARE cur REF CURSOR;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur FOR SELECT * from db01.emp1;
    
    truncate table emp2;
    
    REPEAT
        FETCH cur INTO v1, v2;
        IF NOT done then
            set @f1 = 1, @f2 = 1;
            SET @v1 = REGEXP_REPLACE(v1,'[\' \']+',''), @v2 = REGEXP_REPLACE(v2,'[\' \']+','');
            while @f1 > 0
            do
                -- 判断是否继续循环
                if instr(@v1,'/') = 0 then    set @f1 = @f1 - 1;    end if;
                
                if instr(@v2,'/') = 0 then
                    set @f2 = @f2 - 1;
                    if @f2 < 0 then set @v2 = null; end if; 
                end if;

                – 截取一次字符串插入一条记录
                INSERT INTO db01.emp2 VALUES(substring_index(@v1,'/',1), substring_index(@v2,'/',1));
                SET @v1 = SUBSTRING(@v1,INSTR(@v1,'/')+1,lenGTH(@v1)), @v2 = SUBSTRING(@v2,INSTR(@v2,'/')+1,lenGTH(@v2));
                
                -- 结尾为/则终止循环
                if @v1='' then set @f1=0; end if;
            end while;
            
        END IF;
    UNTIL DONE END REPEAT;
    CLOSE cur;
END //
DELIMITER ;

执行结果：

gbase> select count(1) from emp1;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (Elapsed: 00:00:00.11)

gbase> call prc_split01();
Query OK, 0 rows affected (Elapsed: 00:39:14.80)

10万条记录耗时近40分钟，性能确实比较差，下面尝试改写上面的存储过程。因为需要将取到的每个值进行切分，所以我们可以构造一个fn_getspecval函数用来获取指定的值，
如：获取字符串“abc/def/123”中/分隔的第2个值，返回：def。
定义的函数：
DROP FUNCTION IF EXISTS fn_getspecval;
DELIMITER //
CREATE FUNCTION fn_getspecval
/*
* 功能：将字符串按分隔符拆分，返回指定位置的值。
* 如： fn_getspecval('a/b/c','/',2) 返回b
*/
(
 str VARCHAR(255),        -- 输入字符串
 del VARCHAR(5),        -- 字符串的分隔符
 pos INT                -- 指定需要返回的第几个值
) RETURNS VARCHAR
BEGIN
    SET @retval = REPLACE(SUBSTRING_INDEX(str,del,pos),SUBSTRING_INDEX(str,del,pos-1),'');
    -- 去掉尾部可能存在的分隔符，如返回''则置为NULL
    SET @retval = CASE WHEN REPLACE(@retval,del,'')='' THEN NULL ELSE REPLACE(@retval,del,'') END;
    -- 去除空格
    SET @retval = REGEXP_REPLACE(@retval,'[\' \']+','');
    RETURN @retval;
END //
DELIMITER ;

下一步解决循环取值的问题，由于name字段中分隔符/出现的最大次数可以确定name需要切分的最大次数，那么可以按最大次数批量处理。
改写后的存储过程如下：

DROP PROCEDURE IF EXISTS prc_split;
DELIMITER //
CREATE PROCEDURE prc_split()
/*
* 功能：将记录按分隔符拆分成多条记录
* */
BEGIN
    SET @x = 0;
    -- 根据姓名获取确定循环次数
    SELECT MAX(LENGTH(name)-LENGTH(REPLACE(name,'/','')))+1 INTO @maxcnt FROM emp1;
    -- 循环获取数据
    REPEAT 
        SET @x = @x + 1;
        INSERT INTO emp2
        SELECT fn_getspecval(name,'/',@x),fn_getspecval(mobile,'/',@x)
            FROM emp1
            WHERE fn_getspecval(name,'/',@x) IS NOT NULL;
    UNTIL @x > @maxcnt END REPEAT;

END //
DELIMITER ;

调用存储过程检验效果：
gbase> truncate table emp2;
Query OK, 11 rows affected (Elapsed: 00:00:00.05)

gbase> call prc_split();
Query OK, 0 rows affected (Elapsed: 00:00:00.14)

gbase> select * from emp2 order by name limit 20 ;
+------+-------------+
| name | mobile      |
+------+-------------+
| 张1  | 13111111111 |
| 张2  | 13122222222 |
| 张3  | NULL        |
| 李1  | 15011111111 |
| 李2  | 15022222222 |
| 王1  | 18811111111 |
| 王2  | 18822222222 |
| 王3  | 18833333333 |
| 王4  | NULL        |
| 王5  | NULL        |
| 赵1  | 17911111111 |
+------+-------------+

调用存储过程再次测试10万条记录运行所需时间：
gbase> truncate table emp2;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

gbase> select count(1) from emp1;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (Elapsed: 00:00:00.16)

-- 10万条记录耗时：27秒
gbase> call prc_split();  
Query OK, 0 rows affected (Elapsed: 00:00:27.39)

总结：

不采用游标改用批量处理后，存储过程执行耗时从近40分钟降至不到30秒，可见游标与批量处理的性能差异还是比较大的，因此日常开发时建议尽量避免使用游标，特别是数据量较大的情况下。