需求:有如下兩張表,其中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)
- BEGIN
- DECLARE 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)
- );
-
- -- 按條件找到webapp
- CALL 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