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

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

CREATE TABLE `tb_system_category` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `c_parent_id` int(11) NOT NULL,  `c_name` varchar(50) NOT NULL,  `c_full_name` varchar(200) DEFAULT NULL,  `c_code` varchar(50) NOT NULL,  `c_describe` text,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8;CREATE TABLE `tb_web_app` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `c_name` varchar(255) NOT NULL,  `c_package_name` varchar(255) NOT NULL,  `c_category_code` varchar(50) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=138583 DEFAULT CHARSET=utf8;

        要求分页查询tb_web_app表，并显示其c_category_code对应的c_full_name（来自tb_system_category）。

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

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)BEGINDECLARE cRand VARCHAR(50) DEFAULT RAND();-- 创建临时表CREATE TEMPORARY TABLE IF NOT EXISTS tb_system_temp_wac(id INTEGER,c_name varchar(255),c_package_name varchar(255),c_category_code varchar(50),categoryName VARCHAR(255),cRand VARCHAR(50));-- 按条件找到webappCALL findWebapp(cName,pName,cCategoryCodes,cID,cType,startRow,pageSize,cRand);-- 将找到的webapp的c_category_code的值进行替换CALL generateCategoryName(cRand);-- 返回结果SET @mySql = CONCAT('select * from tb_system_temp_wac where cRand=\'',cRand,'\'');PREPARE stmt FROM @mySql;EXECUTE stmt;END

        存储过程findWebappAndCategory调用findWebapp和generateCategoryName，代码如下所示：

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))BEGINDECLARE sqlStr VARCHAR(4000);IF cType = 'count' THENSET sqlStr = 'select count(id) from tb_web_app where 1=1 ';ELSESET sqlStr = CONCAT('select id,c_name,c_package_name,c_category_code,c_category_code,\'',cRand,'\' from tb_web_app where 1=1 ');END IF;IF LENGTH(cName) > 0 THENSET sqlStr = CONCAT(sqlStr,'and c_name like \'%',cName,'%\' ');END IF;IF LENGTH(pName) > 0 THENSET sqlStr = CONCAT(sqlStr,'and c_package_name like \'%',pName,'%\' ');END IF;IF cID > 0 THENSET sqlStr = CONCAT(sqlStr,'and id=',cID,' ');END IF;IF LENGTH(cCategoryCodes) > 0 THENSET sqlStr = CONCAT(sqlStr,'and c_category_code in(',cCategoryCodes,') ');END IF;IF cType != 'count' THENSET sqlStr = CONCAT('insert into tb_system_temp_wac(id,c_name,c_package_name,c_category_code,categoryName,cRand) ',sqlStr,'limit ',startRow,',',pageSize);END IF;SET @mySql = sqlStr;PREPARE stmt FROM @mySql;EXECUTE stmt;END

CREATE PROCEDURE generateCategoryName(IN c_rand VARCHAR(50))BEGINDECLARE finished INTEGER DEFAULT 0;DECLARE thisCategoryName VARCHAR(20);DECLARE cur CURSOR FOR select categoryName from tb_system_temp_wac where cRand = c_rand;DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;OPEN cur;FETCH cur INTO thisCategoryName;WHILE finished = 0 DOSET @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,'\'');PREPARE stmt FROM @mySql;EXECUTE stmt;FETCH cur INTO thisCategoryName;END WHILE;END

        笔者当前tb_web_app表中含10万余条数据，查询时间为0.125ms。