mysql copying to tmp table on disk状态说明和引起的问题

缘起：

• 最近在处理一个项目中需求场景时，需要并发读mysql，当时在调并发线程数过程中，报出了“ java.sql.SQLException: Incorrect key file for table ‘/tmp/#sql_1ab8_6.MYI’; try to repair it” 的错误，这个错误的基本原因是mysql 磁盘已满。

• 后来网络上查询了一下问题，这里稍微总结一下。

状态说明：

• mysql控制台 show processlist命令查看连接主机（show processlist经常用于查看慢查询等操作），在state栏偶尔会发现copying to tmp table on disk状态的连接

• copying to tmp table on disk和copying to tmp table是两个不同状态一个向磁盘
  临时目录写，一个不会。临界值取决于tmp_table_sizes属性大小，如果超过上限会将数据写到磁盘上，从而会有物理磁盘的读写操作，导致影响性能。

• 查看和设置tmp_table_sizes命令：SHOW VARIABLES LIKE ‘tmp_table_size%’;和SET GLOBAL tmp_table_size=100000000;

• 这个状态出现的原因是查询数据超出了mysql参数tmp_table_size的限制导致。

• 如果查询数据大小超出tmp_table_size设置的大小，会进入下面三个状态流程中：
  • mysql首先会将查询数据保存到磁盘的临时表中，而上面的/tmp/#sql_1ab8_6.MYI就是文件保存的绝对路径。state＝copying to tmp table on disk
  • 其次，当保存完后，会将数据进行排序，state ＝ sorting result
  • 最后才是将数据发送给客户端，state ＝ sending data
• 而经过了这三个步骤，也间接的导致了慢查询的产生。所以查询语句最好不要出现“copying to tmp table on disk”状态
• sending data之后临时表空间会重新释放，tmp目录下的文件会被rm掉。 state = removing tmp table
• 未超出tmp_table_size大小的查询一般会直接进入sending data状态（也会出现copying to tmp table状态但是不想磁盘写）。

引起的问题：

当出现copying to tmp table on disk时很可能会间接引起

• 磁盘空间满（磁盘空间不足的情况下）
• I/O超时（time过大，慢查询导致）

解决：

针对自身项目情况做了如下解决：

• 调优连接数据库的并发线程数
  • 这里由于某月份表一天数据可能将近150W，故采取了并发多个线程扫表的方式，调整扫表线程数有效利用临时表释放空间的解决方法。或者每个线程获取一定少数据直接绕过copying to tmp table on disk状态
• 避免直接操作mysql，源数据备份到缓存（比如redis），业务逻辑直接扫缓存。
• 修改tmp_table_size参数