DELIMITER $$
USE `xxx`$$
DROP FUNCTION IF EXISTS `F_getBuluDates`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `F_getBuluDates`(`PuserId` INT(11)) RETURNS VARCHAR(3000) CHARSET gbk
BEGIN
DECLARE buluDate,buluDateTime,LiWaibuluDates,nowdate,bldate VARCHAR(3000) DEFAULT "";
DECLARE rtMsg,RuserName,maxRiZhiDate,btjrqs,blyqrq,tmpdate,yxblsj VARCHAR(255) DEFAULT "";
DECLARE Ra51count INT(30);
DECLARE RbeginDate,RendDate,RtmpDate VARCHAR(2550);
DECLARE u,gqcount,a21Count INT(30) DEFAULT 0;
DECLARE allReadRows,readRows,mySYL,blCount,recordCount,btjryCount,btjrqCount,yxblts,i INT(11) DEFAULT 0;
SELECT a5204,a5205 INTO yxblts,yxblsj FROM a52 WHERE a5218="xxxxxxx" LIMIT 0,1;
SELECT username INTO RuserName FROM USER WHERE id=PuserId;
SELECT COUNT(id) INTO btjryCount FROM a52 WHERE a5214=PuserId AND a5218='xxxxx';
IF btjryCount=0 THEN
SELECT GROUP_CONCAT(a5215) INTO btjrqs FROM a52 WHERE a5218='btjrq';
SET tmpdate=CURDATE();
SET nowdate=DATE_SUB(CURDATE(),INTERVAL yxblts DAY);
REPEAT
SELECT MAX(a2104) INTO maxRiZhiDate FROM a21 WHERE issubmit='y' AND a2104
SET tmpdate=maxRiZhiDate;
IF tmpdate>=nowdate THEN
IF F_isWorkDay(maxRiZhiDate)=1 OR ISNULL(maxRiZhiDate) THEN
IF NOT ISNULL(maxRiZhiDate) AND LENGTH(maxRiZhiDate)>0 THEN
IF FIND_IN_SET(maxRiZhiDate,btjrqs)=0 THEN
SELECT COUNT(id) INTO recordCount FROM a21 WHERE a2104=maxRiZhiDate AND creatorid=PuserId;
IF recordCount=0 THEN
IF DAYOFWEEK(maxRiZhiDate)=6 THEN
SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+2+yxblts)," ",yxblsj);
ELSEIF DAYOFWEEK(maxRiZhiDate)=7 THEN
SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+1+yxblts)," ",yxblsj);
ELSE
SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+yxblts)," ",yxblsj);
END IF;
IF(CURRENT_TIMESTAMP()
SET buluDate=maxRiZhiDate;
END IF;
END IF;
END IF;
END IF;
END IF;
IF CHAR_LENGTH(buluDate)>0 THEN
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(buluDate,",",bldate);
ELSE
SET bldate=buluDate;
END IF;
END IF;
END IF;
SET buluDate='';
UNTIL tmpdate<=nowdate
END REPEAT;
SELECT COUNT(id) INTO gqcount FROM a52 WHERE a5216=CURDATE() AND a5218='xxxxxx';
IF gqcount>0 THEN
SELECT a5215 INTO blyqrq FROM a52 WHERE a5216=CURDATE() AND a5218='xxxxxx';
SELECT COUNT(id) INTO a21Count FROM a21 WHERE a2104=blyqrq AND creatorid=PuserId;
IF a21Count=0 THEN
IF FIND_IN_SET(blyqrq,bldate)=0 THEN
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(blyqrq,",",bldate);
ELSE
SET bldate=blyqrq;
END IF;
END IF;
END IF;
END IF;
IF ISNULL(btjrqs) THEN
SET btjrqs="";
END IF;
SELECT GROUP_CONCAT(a5108) INTO LiWaibuluDates FROM a51 WHERE a5110='y' AND F_ifInSet(a5108,btjrqs)=0 AND a5106 LIKE RuserName AND a5112='否' AND CONCAT(a5108," ",a5113)
IF NOT ISNULL(LiWaibuluDates) AND LENGTH(TRIM(LiWaibuluDates))>0 THEN
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(LiWaibuluDates,",",bldate);
ELSE
SET bldate=LiWaibuluDates;
END IF;
END IF;
SELECT COUNT(id) INTO RA51count FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId;
IF RA51count>0 THEN
SET u=0;
WHILE u< RA51count DO
SELECT a5108,a5109 INTO RbeginDate,RendDate FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId ORDER BY id LIMIT u,1;
SET RtmpDate=RbeginDate;
WHILE RtmpDate<=RendDate DO
IF FIND_IN_SET(RtmpDate,bldate)>0 THEN
SET bldate=F_removeFromSet(RtmpDate,bldate);
END IF;
SET RtmpDate=DATE_ADD(RtmpDate, INTERVAL 1 DAY);
END WHILE;
SET u=u+1;
END WHILE;
END IF;
END IF;
RETURN bldate;
END$$
DELIMITER ;
在老系统中该函数调用一次需要话20多秒到30秒左右。
拿到sql之后,首先要确定思路。不能着急这下手。
1. 首先查看各个表的数据量:
select count(*) from xxx;
发现只有 a21 的数据量达到了十几万,其他表数据量都比较小。所以重点是 a21表,仔细阅读了一遍函数的定义,发现涉及 到 a21 的有一处循环:
REPEAT
SELECT MAX(a2104) INTO maxRiZhiDate FROM a21 WHERE issubmit='y' AND a2104
很显然使用了 find_in_set 函数,所以改语句无法使用索引。所以想要简单的通过增加索引来解决问题,应该是行不通的。
这样就定位到了函数运行慢的问题。结合业务理解改函数的含义。
为了使用索引,我们需要去掉 find_in_set 函数,理解了业务和原函数的功能之后,对改函数的 repeat 循环部分进行了重写:
DELIMITER $$USE`oa`$$DROP FUNCTION IF EXISTS`F_getBuluDates_inner`$$CREATE DEFINER=`root`@`localhost` FUNCTION `F_getBuluDates_inner`(`PuserId` INT(11), tmpdate VARCHAR(32), nowdate VARCHAR(32)) RETURNS VARCHAR(3000) CHARSET gbkBEGIN
DECLARE buluDate,buluDateTime,LiWaibuluDates,bldate VARCHAR(3000) DEFAULT"";DECLARE rtMsg,RuserName,maxRiZhiDate,btjrqs,blyqrq,yxblsj VARCHAR(255) DEFAULT"";DECLARE Ra51count INT(30);DECLARE RbeginDate,RendDate,RtmpDate VARCHAR(2550);DECLARE u,gqcount,a21Count INT(30) DEFAULT 0;DECLARE allReadRows,readRows,mySYL,blCount,recordCount,btjryCount,btjrqCount,yxblts,i INT(11) DEFAULT 0;DECLARE no_more_data INT DEFAULT 0;DECLARE my_cursor CURSOR FOR SELECT DISTINCT a2104 FROM a21 WHERE issubmit='y' AND a2104 < tmpdate AND a2104 >= nowdate ORDER BY a2104 DESC;DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;SELECT a5204,a5205 INTO yxblts,yxblsj FROM a52 WHERE a5218="gzrz" LIMIT 0,1;SELECT username INTO RuserName FROM USER WHERE id=PuserId;SELECT COUNT(id) INTO btjryCount FROM a52 WHERE a5214=PuserId AND a5218='xxxxx';IF btjryCount=0 THEN
SELECT GROUP_CONCAT(a5215) INTO btjrqs FROM a52 WHERE a5218='xxx';OPENmy_cursor;FETCH my_cursor INTOmaxRiZhiDate;
REPEATIF F_isWorkDay(maxRiZhiDate)=1 OR ISNULL(maxRiZhiDate) THEN
IF NOT ISNULL(maxRiZhiDate) AND LENGTH(maxRiZhiDate)>0 THEN
IF FIND_IN_SET(maxRiZhiDate,btjrqs)=0 THEN
SELECT COUNT(id) INTO recordCount FROM a21 WHERE a2104=maxRiZhiDate AND creatorid=PuserId;IF recordCount=0 THEN
IF DAYOFWEEK(maxRiZhiDate)=6 THEN
SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+2+yxblts)," ",yxblsj);
ELSEIF DAYOFWEEK(maxRiZhiDate)=7 THEN
SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+1+yxblts)," ",yxblsj);ELSE
SET buluDateTime=CONCAT(FROM_DAYS(TO_DAYS(maxRiZhiDate)+yxblts)," ",yxblsj);END IF;IF(CURRENT_TIMESTAMP()
SET buluDate=maxRiZhiDate;END IF;END IF;END IF;END IF;END IF;IF CHAR_LENGTH(buluDate)>0 THEN
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(buluDate,",",bldate);ELSE
SET bldate=buluDate;END IF;END IF;FETCH my_cursor INTOmaxRiZhiDate;SET buluDate='';
UNTIL no_more_data= 1
ENDREPEAT;SELECT COUNT(id) INTO gqcount FROM a52 WHERE a5216=CURDATE() AND a5218='xxx';IF gqcount>0 THEN
SELECT a5215 INTO blyqrq FROM a52 WHERE a5216=CURDATE() AND a5218='xxxxxxxxxx';SELECT COUNT(id) INTO a21Count FROM a21 WHERE a2104=blyqrq AND creatorid=PuserId;IF a21Count=0 THEN
IF FIND_IN_SET(blyqrq,bldate)=0 THEN
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(blyqrq,",",bldate);ELSE
SET bldate=blyqrq;END IF;END IF;END IF;END IF;IF ISNULL(btjrqs) THEN
SET btjrqs="";END IF;SELECT GROUP_CONCAT(a5108) INTO LiWaibuluDates FROM a51 WHERE a5110='y' AND F_ifInSet(a5108,btjrqs)=0 AND a5106 LIKE RuserName AND a5112='否' AND CONCAT(a5108," ",a5113)0 THEN
IF CHAR_LENGTH(bldate)>0 THEN
SET bldate=CONCAT(LiWaibuluDates,",",bldate);ELSE
SET bldate=LiWaibuluDates;END IF;END IF;SELECT COUNT(id) INTO RA51count FROM a51 WHERE a5110='n' AND F_getid1value2(a5105,1)=PuserId;IF RA51count>0 THEN
SET u=0;WHILE u0 THEN
SET bldate=F_removeFromSet(RtmpDate,bldate);END IF;SET RtmpDate=DATE_ADD(RtmpDate, INTERVAL 1 DAY);END WHILE;SET u=u+1;END WHILE;END IF;END IF;RETURNbldate;END$$
DELIMITER ;
然后为了让新的 sql 使用索引:
DECLARE my_cursor CURSOR FOR
SELECT DISTINCT a2104 FROM a21 WHERE issubmit='y' AND a2104 < tmpdate AND a2104 >= nowdate ORDER BY a2104 DESC;
新增了索引:
add index issubmit_a2104(issubmit,a2104);
最终的提供给外部的调用函数:
DELIMITER $$CREATE FUNCTION F_getBuluDates_new(`PuserId` INT(11)) RETURNS VARCHAR(3000) CHARSET gbkBEGIN
DECLARE bldate VARCHAR(3000) DEFAULT"";DECLARE tmpdate,nowdate VARCHAR(32) DEFAULT"";DECLARE yxblts INT;SELECT a5204 INTO yxblts FROM a52 WHERE a5218="gzrz" LIMIT 0,1;SET tmpdate =CURDATE();SET nowdate = DATE_SUB(tmpdate,INTERVAL yxblts DAY);SET bldate =F_getBuluDates_inner(PuserId,tmpdate,nowdate);RETURNbldate;END$$
测试效果:
mysql> select F_getBuluDates_new(10687);+---------------------------------------------------------------------------------------------------------------+
| F_getBuluDates_new(10687) |
+---------------------------------------------------------------------------------------------------------------+
| 2016-08-04,2016-08-05,2016-08-08,2016-08-09,2016-08-10,2016-08-11,2016-08-12,2016-08-15,2016-08-16,2016-08-17 |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (1.10sec)
mysql> select F_getBuluDates(10687);+---------------------------------------------------------------------------------------------------------------+
| F_getBuluDates(10687) |
+---------------------------------------------------------------------------------------------------------------+
| 2016-08-04,2016-08-05,2016-08-08,2016-08-09,2016-08-10,2016-08-11,2016-08-12,2016-08-15,2016-08-16,2016-08-17 |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (13.10 sec)
性能提升了 11 倍多。客户对效果很满意。
新的执行计划:
mysql> explain SELECT DISTINCT a2104 FROM a21 WHERE issubmit='y' AND a2104 < '2016-08-16' AND a2104 >= '2016-08-10' ORDER BY a2104 DESC;+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | a21 | range | a2104,issubmit_a2104 | issubmit_a2104 | 516 | NULL | 834 | Using where; Using index |
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
总结:
从原函数实现来看,在数量小时,还是可以的。但是一旦数据量大了,就会出现性能问题。主要问题是在 where 条件中使用了太多的自定义函数,而且这个函数的参数还是表的数据列。
导致无法使用索引。无法使用索引就会进行全表扫描,所以数据量大时会导致性能问题。