10万以上数据查询-存储过程实现

需求：有如下两张表，其中tb_web_app表中数据有十万甚至百万，另，tb_web_app表中的c_category_code关联表tb_system_category中的c_code字段。

1. CREATE TABLE `tb_system_category` (  


2.   `id` int(11) NOT NULL AUTO_INCREMENT,  


3.   `c_parent_id` int(11) NOT NULL,  


4.   `c_name` varchar(50) NOT NULL,  


5.   `c_full_name` varchar(200) DEFAULT NULL,  


6.   `c_code` varchar(50) NOT NULL,  


7.   `c_describe` text,  


8.   PRIMARY KEY (`id`)  


9. ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8;  


10.   


11.   


12. CREATE TABLE `tb_web_app` (  


13.   `id` int(11) NOT NULL AUTO_INCREMENT,  


14.   `c_name` varchar(255) NOT NULL,  


15.   `c_package_name` varchar(255) NOT NULL,  


16.   `c_category_code` varchar(50) NOT NULL DEFAULT ‘0’,  


17.   PRIMARY KEY (`id`)  


18. ) ENGINE=InnoDB AUTO_INCREMENT=138583 DEFAULT CHARSET=utf8;  

        笔者先是使用sql联表语句进行查询，不外乎left join等语句的使用，结果发现执行速度巨慢无比，遂转成存储过程实现，存储过程代码如下所示：

1. CREATE PROCEDURE findWebappAndCategory(IN cName VARCHAR(255), IN pName VARCHAR(255), IN cCategoryCodes VARCHAR(100), IN cID INTEGER, IN cType VARCHAR(50), IN startRow INTEGER, IN pageSize INTEGER)  


2. BEGIN  


3.     DECLARE cRand VARCHAR(50) DEFAULT RAND();  


4.     — 创建临时表  


5.     CREATE TEMPORARY TABLE IF NOT EXISTS tb_system_temp_wac(  


6.         id INTEGER,  


7.         c_name varchar(255),  


8.         c_package_name varchar(255),  


9.         c_category_code varchar(50),  


10.         categoryName VARCHAR(255),  


11.         cRand VARCHAR(50)  


12.     );  


13.       


14.     — 按条件找到webapp  


15.     CALL findWebapp(cName,pName,cCategoryCodes,cID,cType,startRow,pageSize,cRand);  


16.     — 将找到的webapp的c_category_code的值进行替换  


17.     CALL generateCategoryName(cRand);  


18.       


19.     — 返回结果  


20.     SET @mySql = CONCAT(‘select * from tb_system_temp_wac where cRand=\”,cRand,‘\”);  


21.     PREPARE stmt FROM @mySql;  


22.     EXECUTE stmt;  


23. END  

1. CREATE PROCEDURE findWebapp(IN cName VARCHAR(255), IN pName VARCHAR(255), IN cCategoryCodes VARCHAR(100), IN cID INTEGER, IN cType VARCHAR(50), IN startRow INTEGER, IN pageSize INTEGER, IN cRand VARCHAR(50))  


2. BEGIN  


3.     DECLARE sqlStr VARCHAR(4000);  


4.   


5.     IF cType = ‘count’ THEN  


6.         SET sqlStr = ‘select count(id) from tb_web_app where 1=1 ‘;  


7.     ELSE  


8.         SET sqlStr = CONCAT(‘select id,c_name,c_package_name,c_category_code,c_category_code,\”,cRand,‘\’ from tb_web_app where 1=1 ‘);  


9.     END IF;  


10.   


11.     IF LENGTH(cName) > 0 THEN  


12.         SET sqlStr = CONCAT(sqlStr,‘and c_name like \’%’,cName,‘%\’ ‘);  


13.     END IF;  


14.     IF LENGTH(pName) > 0 THEN  


15.         SET sqlStr = CONCAT(sqlStr,‘and c_package_name like \’%’,pName,‘%\’ ‘);  


16.     END IF;  


17.     IF cID > 0 THEN  


18.         SET sqlStr = CONCAT(sqlStr,‘and id=’,cID,‘ ‘);  


19.     END IF;  


20.     IF LENGTH(cCategoryCodes) > 0 THEN  


21.         SET sqlStr = CONCAT(sqlStr,‘and c_category_code in(‘,cCategoryCodes,‘) ‘);  


22.     END IF;  


23.       


24.     IF cType != ‘count’ THEN  


25.         SET sqlStr = CONCAT(‘insert into tb_system_temp_wac(id,c_name,c_package_name,c_category_code,categoryName,cRand) ‘,sqlStr,‘limit ‘,startRow,‘,’,pageSize);  


26.     END IF;  


27.   


28.     SET @mySql = sqlStr;  


29.     PREPARE stmt FROM @mySql;  


30.     EXECUTE stmt;  


31. END  

1. CREATE PROCEDURE generateCategoryName(IN c_rand VARCHAR(50))  


2. BEGIN  


3.     DECLARE finished INTEGER DEFAULT 0;  


4.     DECLARE thisCategoryName VARCHAR(20);  


5.   


6.     DECLARE cur CURSOR FOR select categoryName from tb_system_temp_wac where cRand = c_rand;  


7.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;  


8.     OPEN cur;  


9.     FETCH cur INTO thisCategoryName;  


10.     WHILE finished = 0 DO  


11.                   


12.         SET @mySql = CONCAT(‘update tb_system_temp_wac set categoryName=(select c_full_name from tb_system_category where c_code=\”,thisCategoryName,‘\’ limit 0,1) where c_category_code=\”,thisCategoryName,‘\’ and cRand=\”,c_rand,‘\”);  


13.         PREPARE stmt FROM @mySql;  


14.         EXECUTE stmt;  


15.   


16.         FETCH cur INTO thisCategoryName;  


17.     END WHILE;  


18. END