- 在一次项目开发过程中,遇到了从数据库中获取父子关系链路的问题,当时使用了FIND_IN_SET函数去解决这个问题,解决函数如下:
DELIMITER $$
DROP FUNCTION IF EXISTS getParentList$$
CREATE FUNCTION `getParentList`(rootId BIGINT) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sParentList varchar(1000);
DECLARE sParentTemp varchar(1000);
SET sParentTemp =cast(rootId as CHAR);
WHILE sParentTemp is not null DO
IF (sParentList is not null) THEN
SET sParentList = concat(sParentTemp,',',sParentList);
ELSE
SET sParentList = concat(sParentTemp);
END IF;
SELECT group_concat(parent_id) INTO sParentTemp FROM j_oss_cloud_disk where
FIND_IN_SET(file_id,sParentTemp)>0;
END WHILE;
RETURN sParentList;
END
$$
DELIMITER ;
2. 初次看这个函数,并没有什么问题,也完全可以实现获取父子链路的功能,但是不久后问题就显现出来了。项目快要完成时,空了一些时间,便为其做了一个压力测试,开始显示造了十万条数据,这时候,FIND_IN_SET劣势显现出来了,十万数据就需要几秒的时间,一查才知道,这个函数是权标扫描,索引对其无效。
于是对其做了以下改正:
DELIMITER $$
DROP FUNCTION IF EXISTS getParentList$$
CREATE FUNCTION `getParentList`(_file_id bigint(20)) RETURNS varchar(1000) CHARSET utf8
COMMENT '根据文件ID获得父id'
BEGIN
DECLARE pathList varchar(1000);
DECLARE fileId varchar(256);
DECLARE fileIdTmp bigint(20);
DECLARE parentIdTmp bigint(20);
DECLARE countTmp int(10);
SET fileIdTmp =_file_id;
SET countTmp =0;
WHILE fileIdTmp is not null AND countTmp < 10 DO
SELECT file_id,parent_id INTO fileId,parentIdTmp FROM j_oss_cloud_disk where file_id=fileIdTmp limit 1;
SET fileIdTmp = parentIdTmp;
IF (pathList is not null) THEN
SET pathList = concat(fileId,',',pathList);
SET countTmp = 1 + countTmp;
ELSE
SET pathList = fileId;
END IF;
END WHILE;
RETURN pathList;
END
$$
DELIMITER ;
3. 这样一改之后,速度就快多了,后来即使造了一千万数据,也丝毫没有影响,依然可以在半秒之内查出一个翻页(50条数据)之内的所有链路。(为每一个文件一次次的去获取它的链路)