2023年6月19日大约 3 分钟数据库技术MySQL数据库存储过程
由于项目中使用了多级目录结构，数据库的存储使用id,parentId进行存储，有个需求就是通过id查询最顶层目录的id（最顶层目录id的parentId=0），想尝试使用存储过程来解决这个问题。因为存储过程使用到了临时表导致存储空间被占满。
现象与起因
根据监控查看到mysql存储空间被占满，但是使用SQL查询数据库表文件却只是占用了几个G。
SELECT
table_schema AS '数据库',
sum( table_rows ) AS '记录数',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
sum(
TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
GROUP BY
table_schema
ORDER BY
sum( data_length ) DESC,
sum( index_length ) DESC;
到mysql数据库存储目录下使用du sh * 发现有一个ibtmp1文件很大，占用了分区所有空闲空间。
网上查了一下其中有一条原因可能是使用了大量的临时表造成的，回想起前几天执行了一个存储过程。
DELIMITER //
CREATE PROCEDURE get_top_folder_id(IN p_folder_id BIGINT, OUT p_top_folder_id BIGINT)
BEGIN
DECLARE v_parent_id BIGINT;
SET p_top_folder_id = p_folder_id;
SET v_parent_id = (SELECT parent_id FROM test_issue_folder WHERE folder_id = p_folder_id);
WHILE v_parent_id != 0 DO
SET p_top_folder_id = v_parent_id;
SET v_parent_id = (SELECT parent_id FROM test_issue_folder WHERE folder_id = v_parent_id);
END WHILE;
END//
DELIMITER ;
CALL get_top_folder_id(:folder_id, @top_folder_id);
SELECT 184611653340499968 as folderId,@top_folder_id;
这个存储过程本身没有问题，只是184611653340499968这个folderId的parent_id是其本身，查询时造成了死循环，导致创建了大量的临时表。
如何处理
1）首先备份数据库，如果Mysql服务还能正常使用，可以用日常的备份机制做一次全备；如果Mysql服务已经异常了，可以考虑物理备份。
2）为了避免以后再次出现ibtmp1文件暴涨，限制其大小，需在mysql配置文件加入：
$ vim /etc/my.cnf
[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
3）与业务各方沟通好，重启Mysql实例（重启后ibtmp1文件会自动清理）。
4）重启后，验证配置是否生效：
mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+------------------------------+
| Variable_name | Value |
+----------------------------+------------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:5G |
+----------------------------+------------------------------+
1 row in set (0.01 sec)
总结
- 尽量避免使用数据库的存储过程，可迁移性是个问题，另一个就是如果存储过程使用到临时表就很有可能出现本文的问题。
- 今后无论是代码还是SQL中，遇到递归/循环问题一定加上一个深度控制。比如在我的场景下加上循环10次还没找到顶级目录就退出循环。
**可能导致ibtmp1文件会暴涨的情况: **
用到临时表，当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary就表示会用到临时表。
GROUP BY无索引字段或GROUP BY + ORDER BY的子句字段不一样时。
order by与distinct共用，其中distinct与order by里的字段不一致（主键字段除外）。
insert into table1 select xxx from table2语句。