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 DATA BEGIN 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就会出现超时报错：（这为了演示，我就没有等到报错再截图了）
