前提:因为之前查询健康百科文章过慢,能力问题暂时优化不了sql,暂时改用存储过程调用,很久没有写存储过程了,而且公司的mysql又是5.1的,也没有oracle的强大,菜鸟一把辛酸泪,先记录一下!
原先的sql:在加组合索引的情况下还要3s多!!!
SELECT
t.id sid,
t.sectionName sname,
a.id uuid,
a.title title,
a.summary summary,
a.imageTitle imgUrl
FROM
TB_CM_NewsSections t, TB_CM_NewsSummary a
WHERE
3 > (
SELECT
COUNT(*)
FROM
TB_CM_NewsSummary b
WHERE
b.ID>a.ID
AND b.Section=a.Section
AND b.Status=a.Status
AND b.IsValid=a.IsValid
)
AND t.parentCode = 'jkbk0'
AND t.Status = 0
AND a.Section = t.id
AND a.Status=0
AND a.IsValid=1
ORDER BY
t.sort DESC,
a.isTop DESC,
a.Order,
a.createdTime DESC
执行计划:
存储过程:
CREATE DEFINER = `root`@`%` PROCEDURE `proc_findNewsSummaryBySections`(IN p_sids VARCHAR(100),IN p_parentCode VARCHAR(20), IN p_page INT, IN p_pageSize INT)
BEGIN
DECLARE v_i INT DEFAULT 0;
DECLARE v_total INT;
DECLARE v_section_id INT;
DECLARE v_done INT DEFAULT 0;
DECLARE v_sql VARCHAR(255);
# 声明游标
DECLARE myCursor CURSOR FOR
(SELECT ID FROM TB_CM_NewsSections WHERE parentCode = p_parentCode AND status = 0 ORDER BY sort DESC);
# 定义退出标志
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1;
DROP TABLE IF EXISTS tmp_table;
#临时表存储结果集
CREATE TEMPORARY TABLE tmp_table(
sid INT(11),
sname VARCHAR(255),
uuid INT(11),
title VARCHAR(255),
summary text,
imgUrl VARCHAR(255)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
IF (LENGTH(p_sids) = 0) THEN #查询全部
BEGIN
# 打开游标
OPEN myCursor;
#循环遍历
emp_loop: LOOP
FETCH myCursor INTO v_section_id;
IF v_done=1 THEN
LEAVE emp_loop;
END IF;
#相应的记录插入到临时表
SET @stmt = concat('INSERT INTO tmp_table(sid,sname,uuid,title,summary,imgUrl)
SELECT t.id,t.sectionName,a.id,a.title,a.summary,a.imageTitle
FROM TB_CM_NewsSections t, TB_CM_NewsSummary a
WHERE t.ID = ? AND a.Section = t.ID AND a.Status=0 AND a.IsValid=1
ORDER BY a.isTop DESC, a.order,a.createdTime DESC ', 'LIMIT ?,?;');
PREPARE s from @stmt; #mysql5.6不需要,可直接limit page,pageSize
SET @sid = v_section_id;
SET @page = (p_page - 1)*p_pageSize;
SET @pageSize = p_pageSize;
EXECUTE s USING @sid,@page,@pageSize;
DEALLOCATE PREPARE s;
END LOOP emp_loop;
CLOSE myCursor;
END;
ELSE
BEGIN
SET v_total = 1+(LENGTH(p_sids) - LENGTH(REPLACE(p_sids,',','')));#获取字符串(p_sids = '39,40,44')中id的总数;
WHILE v_i<v_total DO
SET v_i = v_i + 1;
SET v_section_id = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(p_sids,',',v_i)),',',1));#从字符串中获取某个id
SET @stmt = concat('INSERT INTO tmp_table(sid,sname,uuid,title,summary,imgUrl)
SELECT t.id,t.sectionName,a.id,a.title,a.summary,a.imageTitle
FROM TB_CM_NewsSections t, TB_CM_NewsSummary a
WHERE t.ID = ? AND a.Section = t.ID AND a.Status=0 AND a.IsValid=1
ORDER BY a.isTop DESC, a.order,a.createdTime DESC ','LIMIT ?,?;');
PREPARE s from @stmt;
SET @sid = cast(v_section_id as UNSIGNED);#字符id转int
SET @page = (p_page - 1)*p_pageSize;
SET @pageSize = p_pageSize;
EXECUTE s USING @sid,@page,@pageSize;
DEALLOCATE PREPARE s;
END WHILE;
END;
END IF;
SELECT * FROM tmp_table;
TRUNCATE tmp_table;#清除临时表
END;
调用:
CALL proc_findNewsSummaryBySections('39,40','jkbk0',2,3);
或
CALL proc_findNewsSummaryBySections('','jkbk0',2,3);
mysql存储过程没有oracle强大,不能接受数组类型的参数,因为刚好业务要传入整型的参数,而且mysql也不能接受,所以就只能以"12,23,24,..."字符串传入然后再做相应的转换;
第一:先循环获取id,这里才用如下方式:
-- 得到分割符的总数。
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5)
) RETURNS INT(11)
BEGIN
-- Get the total number of given string.
RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));
END$$
DELIMITER;
-- 得到具体下表的子字符。
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
-- Get the separated number of given string.
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END$$
DELIMITER ;
第二:将获取的字符id转为int类型
//cast和convert
cast(v_section_id as UNSIGNED);
或
convert(v_section_id , UNSIGNED);