mysql存储过程使用批量插入来优化插入速度

优化前（部分语句）：

CREATE PROCEDURE update_player2coreUnit()
BEGIN
	DECLARE done int DEFAULT 0;
	declare a_city_id bigint default 0;
	declare a_player_id bigint default 0;
	declare a_player_level bigint default 0;
	declare a_core_unit_id bigint default 1000100000000000;

	declare city cursor for select id,player_id,level from city where id >0 and level>=3;  
	declare continue HANDLER for not found set done = 1;

	open city;
	REPEAT
		FETCH city INTO a_city_id,a_player_id,a_player_level;
 
			IF NOT DONE THEN 
					IF(a_player_level > 18) THEN
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,1,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+1;
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,2,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+1;
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,3,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+1;
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,4,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+5;
					
					ELSEIF(a_player_level > 13) THEN
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,1,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+1;
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,2,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+1;
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,3,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+6;

					ELSEIF(a_player_level > 7) THEN
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,1,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+1;
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,2,1,400,0,NULL,0,0);
					set a_core_unit_id = a_core_unit_id+7;
					
					ELSEIF(a_player_level > 2) THEN
					INSERT INTO `player2core_unit` VALUES (a_core_unit_id,a_player_id,1,1,400,0,NULL,0,0) ;
					set a_core_unit_id = a_core_unit_id+8;
					
				END IF;		
			END IF;

	 UNTIL done END REPEAT;
	close city;

END;
CALL update_player2coreUnit();

一万四千多条数据执行了822秒，实在无法接受。

优化后（部分语句）：

CREATE PROCEDURE update_player2coreUnit()  
BEGIN  
    DECLARE done int DEFAULT 0;  
    declare a_city_id bigint default 0;  
    declare a_player_id bigint default 0;  
    declare a_player_level bigint default 0;  
    declare a_core_unit_id bigint default 1000100000000000;  
		declare city cursor for select id,player_id,level from city where id >0 and level>=3;    
		declare continue HANDLER for not found set done = 1;  
    set @exesql = "";  
    set @exedata ="(1,1,12,1,400,0,NULL,0,0)";  
-- 这是一个多余数据，为了处理@exedata和@exesql拼接时的第一个逗号，之后可以删掉。  
    open city;  
    REPEAT  
        FETCH city INTO a_city_id,a_player_id,a_player_level;  
   
            IF NOT DONE THEN   
                    IF(a_player_level > 18) THEN  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","1,1,400,0,NULL,1,1)");  
                    set a_core_unit_id = a_core_unit_id+1;  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","2,1,400,0,NULL,0,0)");  
                    set a_core_unit_id = a_core_unit_id+1;  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","3,1,400,0,NULL,0,0)");  
                    set a_core_unit_id = a_core_unit_id+1;  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","4,1,400,0,NULL,0,0)");  
                    set a_core_unit_id = a_core_unit_id+5;  
                      
                    ELSEIF(a_player_level > 13) THEN  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","1,1,400,0,NULL,1,1)");  
                    set a_core_unit_id = a_core_unit_id+1;  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","2,1,400,0,NULL,0,0)");  
                    set a_core_unit_id = a_core_unit_id+1;  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","3,1,400,0,NULL,0,0)");  
                    set a_core_unit_id = a_core_unit_id+6;  
  
                    ELSEIF(a_player_level > 7) THEN  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","1,1,400,0,NULL,1,1)");  
                    set a_core_unit_id = a_core_unit_id+1;  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","2,1,400,0,NULL,0,0)");  
                    set a_core_unit_id = a_core_unit_id+7;  
                      
                    ELSEIF(a_player_level > 2) THEN  
                    set @exedata = CONCAT(@exedata,",(",a_core_unit_id,",",a_player_id,",","1,1,400,0,NULL,1,1)");  
                    set a_core_unit_id = a_core_unit_id+8;  
                      
                END IF;       
            END IF;  
  
     UNTIL done END REPEAT;  
    close city;  
                SET @exesql =CONCAT("INSERT INTO `player2core_unit` VALUES ",@exedata);  
                prepare stmt from @exesql;  
                execute stmt;  
                DEALLOCATE prepare stmt;  
                set @exesql = "";  
                set @exedata ="";  
  
END;  
CALL update_player2coreUnit();  

同样的数据量执行的8.143秒，性能提升了100倍左右。

以上数据都是在我本机测试的结果，数据执行时间有点偏长，如果是专业的数据库服务器，尤其是ssd的硬盘那么执行时间就会短很多。

如果一个session的预处理语句过多，可能会达到max_prepared_stmt_count的上限值。所以如果批量插入的数据太多则要考虑分批进行批量插入。

每一次执行完EXECUTE时，养成好习惯，须执行DEALLOCATE PREPARE … 语句，这样可以释放执行中使用的所有数据库资源（如游标）。