Mysql存储过程优化——使用临时表代替游标

MySQL游标在操作小数据量时比较方便，效率可观，但操作大数据量，速度比较慢，甚至直接产生系统错误。

 

一般说来，当操作的数据超过1万条时，就避免用游标吧。

 

为了测试游标性能，写了下面一个游标对IDC_Gather_Info表中数据进行遍历

Sql代码  

1. CREATE DEFINER=`root`@`%` PROCEDURE `debug`(IN `beginTime` int, IN `checkTime` int)  
2. BEGIN  
3.     DECLARE t_id VARCHAR(64) DEFAULT '';  
4.     DECLARE t_item TINYINT DEFAULT 0;  
5.     DECLARE t_result VARCHAR(8192) DEFAULT '';  
6.   
7.     DECLARE cursorDone INT DEFAULT 0;  
8.     DECLARE cur CURSOR FOR SELECT Asset_Id, Check_Item, Check_Result from IDC_Gather_Info WHERE Check_Time > beginTime AND Check_Time <= checkTime;  
9.     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorDone = 1;  
10.   
11.     OPEN cur;  
12.     cursorLoop:LOOP  
13.         FETCH cur INTO t_id, t_item, t_result;  
14.         IF cursorDone = 1 THEN  
15.             LEAVE cursorLoop;  
16.         END IF;  
17.     END LOOP;  
18.     CLOSE cur;  
19. END  

下面是当表中数据分别为15万、5万、1万时游标的表现：

 

1.数据量15万，存储过程执行失败，提示错误：Incorrect key file for table '/tmp/#sql_3044_0.MYI';try to repair it

2.数据量5万，执行成功，耗时31.051s

3.数据量1万，执行成功，耗时1.371s

 

下面使用临时表替换游标：

Sql代码  

1. CREATE DEFINER=`root`@`%` PROCEDURE `debug`(IN `beginTime` int, IN `checkTime` int)  
2. BEGIN  
3.     DECLARE t_id VARCHAR(64) DEFAULT '';  
4.     DECLARE t_item TINYINT DEFAULT 0;  
5.     DECLARE t_result VARCHAR(8192) DEFAULT '';  
6.       
7.     DECLARE maxCnt INT DEFAULT 0;  
8.     DECLARE i INT DEFAULT 0;  
9.   
10.     DROP TABLE IF EXISTS Gather_Data_Tmp;  
11.     CREATE TEMPORARY TABLE Gather_Data_Tmp(  
12.         `Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,  
13.         `Asset_Id` VARCHAR(16) NOT NULL,  
14.         `Check_Item` TINYINT(1) NOT NULL,  
15.         `Check_Result` VARCHAR(8192) NOT NULL,  
16.         PRIMARY KEY (`Tmp_Id`)  
17.     )ENGINE=MyISAM DEFAULT CHARSET=utf8;  
18.   
19.     SET @tSql = CONCAT('INSERT INTO Gather_Data_Tmp (`Asset_Id`, `Check_Item`, `Check_Result`)   
20.                                             SELECT Asset_Id, Check_Item, Check_Result   
21.                                             FROM IDC_Gather_Info   
22.                                             WHERE Check_Time > ',beginTime,' AND Check_Time <= ',checkTime);  
23.     PREPARE gatherData FROM @tSql;  
24.     EXECUTE gatherData;  
25.   
26.     SELECT MIN(`Tmp_Id`) INTO i FROM Gather_Data_Tmp;  
27.     SELECT MAX(`Tmp_Id`) INTO maxCnt FROM Gather_Data_Tmp;  
28.   
29.     WHILE i <= maxCnt DO  
30.         SELECT Asset_Id, Check_Item, Check_Result INTO t_id, t_item, t_result FROM Gather_Data_Tmp WHERE Tmp_Id = i;  
31.         SET i = i + 1;  
32.     END WHILE;  
33. END  

 

1.数据量15万，执行成功，耗时8.928s

2.数据量5万，执行成功，耗时2.994s

3.数据量1万，执行成功，耗时0.634s

 

可以看到Mysql的游标在处理大一点的数据量时还是比较乏力的，仅适合用于操作几百上千的小数据量。