1、背景
在MySQL 8.0.23出现执行SQL超时的报错，而在MySQL8.0.18版本上未出现，故做个记录，以飨读者。
2、故障提要
在客户MySQL版本8.0.23，在部署客户测试环境时出现SQL执行超时报错。通过慢日志查询，找到对应的SQL。SQL如下：
SELECT * FROM t_dict WHERE FIND_IN_SET(id,(SELECT getDictChildrenNew( (select id from t_dict where code = 'metadata_classify' and root_code = 'metadata_classify' ), (select code from t_dict where code = 'metadata_classify' and root_code = 'metadata_classify' ), (select root_code from t_dict where code = 'metadata_classify' and root_code = 'metadata_classify' ) ))) order by dict_level,dict_order asc;
这个SQL用到了一个自定义函数getDictChildrenNew。函数内容如下：（真实的账号使用user_name代替了，已脱敏）
CREATE DEFINER=`user_name`@`%` FUNCTION `user_name`.`getDictChildrenNew`(iid varchar(255),icode varchar(255),ircode varchar(255)) RETURNS varchar(5000) CHARSET utf8mb4
READS SQL DATABEGIN
DECLARE oTemp VARCHAR(5000);
DECLARE oTempChild VARCHAR(5000);
DECLARE oTempId VARCHAR(5000);
DECLARE oRootCode VARCHAR(5000);
SET oTemp = '';
SET oTempChild = icode;
SET oTempId = iid;
SET oRootCode = ircode;
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempId);
SELECT GROUP_CONCAT(ID) INTO oTempId
FROM t_dict
WHERE ROOT_CODE = oRootCode AND FIND_IN_SET(PARENT_CODE,oTempChild);
SELECT GROUP_CONCAT(CODE) INTO oTempChild
FROM t_dict
WHERE ROOT_CODE = oRootCode AND FIND_IN_SET(PARENT_CODE,oTempChild);
END WHILE;
RETURN oTemp;
END
3、测试现象
我单独将where子句中这部分单独执行只需要122ms
SELECT getDictChildrenNew(
(select id from t_dict where code = 'metadata_classify' #58
and root_code = 'metadata_classify' ),
(select code from t_dict where code = 'metadata_classify' #metadata_classify
and root_code = 'metadata_classify' ),
(select root_code from t_dict where code = 'metadata_classify' #metadata_classify
and root_code = 'metadata_classify' )
)
或者我单独将这个查询的值以字符串形式作为FIND_IN_SET的第二个参数传入执行也很快。仅需79ms。
而我单独执行上面完整的SQL就会出现超时报错：（这为了演示，我就没有等到报错再截图了）
</div>