【项目实践05】【MySQL 临时表导致磁盘占用100%】

---

一、前言

本系列用来记录一些在实际项目中的小东西，并记录在过程中想到一些小东西，因为是随笔记录，所以内容不会过于详细。

---

1. 文件排序

在一般情况下，在需要排序的时候我们只能将记录加载到内存中然后再通过一些排序算法在内存中进行排序。有时候查询的结果集可能太大导致无法再内存中进行排序，此时就需要借助磁盘存放中间结果，在排序操作完成后再把排好序的结果集返回给客户端。而在 MySQL 中，这种在内存或磁盘中进行排序的方式统称为文件排序（filesort），但是如果 order by 字句中使用了索引列，就可能省去在内存或磁盘中排序的步骤。

通过 explain 命令 的 Extra 列可以看到是否使用了文件排序。如下：

mysql> explain select * from t1 order by c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

注意如下使用索引排序失效的情况：

1. 使用联合索引进行排序时也需要遵循最左匹配原则。
2. 使用联合索引进行排序时 ASC、DESC 混用时无法使用索引进行排序。
3. 排序列包含多个非同一索引的列也无法使用索引进行排序。
4. 查询列和排序列并不是同一索引列也会导致无法使用索引进行排序。
5. 排序列不能被函数修饰。

二、背景介绍

某年某月某日清晨，某台服务器磁盘告警，使用率达到100%导致服务崩溃。告警的服务器仅仅用来安装了几个中间件，包括 MySQL、RabbitMQ、Nacos等，其中可能产生大量落盘的只有 MySQL 数据落盘 或者 RabbitMQ 消息堆积。

通过 find /server -type f -size +100M -exec ls -lh {} \; | awk '{print $9 ": " $5}' 查看服务器server 目录下磁盘文件大于 100M 的文件，随后发现在 MySQL 目录下多了一个 ibtmp1 文件占用了40G的大小, 很明显这是一个 InnoDB 临时表文件，直接删除，并 kill 掉引用线程。

本以为到此已经结束，但是 1min 后磁盘又再次告警，发现临时文件又重新生成了，到这里不得不排查具体问题了，如果能用到这么大的临时表文件，大概率是满足慢SQL 的查询条件，因此查询 MySQL 的慢 SQL 记录，发现存在一条SQL执行耗时40+秒，SQL并不复杂，就是一个普通的关联查询，大致如下：

select <返回字段> 
from a 
left join b on <关联关系>
left join c on <关联关系>
where
<一堆查询条件> 
order by a.name
limit xxx, xxx

然后统计了下这个查询中涉及的表的记录数量，最大数量也就 10w+, 但是 Left join 笛卡尔积之后该SQL查询结果数量超过 100w+，又因为使用了临时表，所以所有的记录都会先落到磁盘再进行排序，而这个落盘的过程就就会生成大量的文件导致磁盘 100%。

问题确定之后，便从代码中寻找执行该SQL的地方，发现是对外提供的一个三方接口，临时禁用该接口后磁盘使用恢复正常。

至此，问题临时解决了, 当然后续要针对这种情况做进一步处理。

三、详细过程

本着知其然知其所以然的过程，这里再详细模拟并复现这种情况，并对其中的一些东西做解释。

1. 问题复现

启动虚拟机，虚拟机磁盘限制20G，通过 Docker 安装一个 MySQL 实例并启动（Docker 的 MySQL安装如有需要可以参考 【Docker笔记02】【常用软件安装】），MySQL 启动后磁盘占用如下：

[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G  9.2G  8.6G  52% /

随后建立一个临时数据库表如下，data_demo 表插入 100000 条记录，data_join 作为一个 join 表意义不大：

CREATE TABLE `data_demo` (
  `id&