在MySQL中,存在各种各样的临时文件,其存放位置是五花八门,且不同版本也不尽相同,主要包括以下:
(1)SQL执行过程中using filesort产生的临时文件
(2)SQL执行过程中using temporary产生的临时文件
(3)binlog cache产生的临时文件
(4)未使用ROW_FORMAT=COMPRESSED创建的InnoDB临时表
(5)使用ROW_FORMAT=COMPRESSED创建的InnoDB临时表
(6)使用algorithm=copy的Online DDL产生的临时文件
(7)使用algorithm=inplace的Online DDL产生的临时文件
(8)Online DDL Log产生的临时文件
接下来,我们以MySQL 5.7版本为例,进行各场景的测试。
mysql> show global variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| datadir | /appdata/mysqldata/ |
+---------------+-----------------------+
1 row in set (0.00 sec)
mysql> show global variables like 'tmpdir';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| tmpdir | /tmp |
+---------------+----------+
1 row in set (0.00 sec)
(1)调小sort_buffer_size参数,让SQL语句使用临时文件进行排序
mysql> set sort_buffer_size=32768;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sort_buffer_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sort_buffer_size | 32768 |
+------------------+-------+
1 row in set (0.00 sec)
(2)执行order by语句,并查看执行计划,确定使用到using filesort
mysql> explain select * from t order by table_name limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 572575 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from t order by table_name limit 1;
+---------------+--------------------+------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------------+------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
| def | performance_schema | accounts | BASE TABLE | PERFORMANCE_SCHEMA | 10 | Fixed | 128 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | |
+---------------+--------------------+------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.34 sec)
(3)与此同时,通过ls/lsof获取到临时文件的存放位置
mysqld 2725 mysql 54u REG 253,0 48824320 795915 /tmp/MYO1D6lE (deleted)
mysqld 2725 mysql 55u REG 253,0 65536 795917 /tmp/MYzzZxhQ (deleted)
(1)调小tmp_table_size和max_heap_table_size,让SQL语句使用临时文件
mysql> set tmp_table_size=16384;
Query OK, 0 rows affected (0.00 sec)
mysql> set max_heap_table_size=16384;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'tmp_table_size';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| tmp_table_size | 16384 |
+----------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'max_heap_table_size';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| max_heap_table_size | 16384 |
+---------------------+-------+
1 row in set (0.00 sec)
(2)执行insert into ... select,并查看执行计划,确定使用到using temporary
mysql> explain insert into t select * from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| 1 | INSERT | t | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 572575 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
2 rows in set (0.00 sec)
mysql> insert into t select * from t;
Query OK, 581632 rows affected (5.92 sec)
Records: 581632 Duplicates: 0 Warnings: 0
(3)与此同时,通过ls/lsof获取到临时文件的存放位置
插入前:
# ls -lrt /appdata/mysqldata/ibtmp1
-rw-r----- 1 mysql mysql 12582912 Dec 9 14:55 /appdata/mysqldata/ibtmp1
# du -sh /appdata/mysqldata/ibtmp1
12M /appdata/mysqldata/ibtmp1
插入后:
# ls -lrt /appdata/mysqldata/ibtmp1
-rw-r----- 1 mysql mysql 146800640 Dec 9 14:58 /appdata/mysqldata/ibtmp1
# du -sh /appdata/mysqldata/ibtmp1
141M /appdata/mysqldata/ibtmp1
(1)调小binlog_cache_size,让DML使用临时文件
mysql> set global binlog_cache_size=4096;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| binlog_cache_size | 4096 |
+-------------------+-------+
1 row in set (0.00 sec)
(2)显式开启事务,插入大量数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select * from t;
Query OK, 1163264 rows affected (11.07 sec)
Records: 1163264 Duplicates: 0 Warnings: 0
(3)与此同时,通过ls/lsof获取到临时文件的存放位置(临时文件大小为125706240)
mysqld 6177 mysql 56u REG 253,0 125706240 795914 /tmp/ML72aaic (deleted)
(4)提交事务
mysql> commit;
Query OK, 0 rows affected (4.03 sec)
(5)与此同时,通过ls/lsof获取到临时文件的存放位置(临时文件大小释放为0)
mysqld 6177 mysql 56u REG 253,0 0 795914 /tmp/ML72aaic (deleted)
(1)创建非压缩临时表
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TEMPORARY TABLE `t1` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext,
`IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` longtext NOT NULL,
`COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
`EXTRA` varchar(30) NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT '',
`GENERATION_EXPRESSION` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
(2)与此同时,通过ls/lsof获取到临时文件的存放位置
创建前:
# ls -lrt /appdata/mysqldata/ibtmp1
-rw-r----- 1 mysql mysql 12582912 Dec 9 15:20 /appdata/mysqldata/ibtmp1
# du -sh /appdata/mysqldata/ibtmp1
12M /appdata/mysqldata/ibtmp1
创建后:
# ls -lrt /tmp/
-rw-r----- 1 mysql mysql 13722 Dec 9 15:22 #sql2896_3_0.frm
# ls -lrt /appdata/mysqldata/ibtmp1
-rw-r----- 1 mysql mysql 12582912 Dec 9 15:22 /appdata/mysqldata/ibtmp1
# du -sh /appdata/mysqldata/ibtmp1
12M /appdata/mysqldata/ibtmp1
mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
| 56 | #sql2896_3_0 | 24 | 51 | FALSE | FALSE |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.00 sec)
(3)往临时表插入大量数据
mysql> insert into t1 select * from t;
Query OK, 581632 rows affected (5.83 sec)
Records: 581632 Duplicates: 0 Warnings: 0
(4)再次查看临时表空间大小,增加至140M
# ls -lrt /appdata/mysqldata/ibtmp1
-rw-r----- 1 mysql mysql 146800640 Dec 9 15:25 /appdata/mysqldata/ibtmp1
# du -sh /appdata/mysqldata/ibtmp1
140M /appdata/mysqldata/ibtmp1
(1)创建压缩临时表
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TEMPORARY TABLE `t2` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext,
`IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` longtext NOT NULL,
`COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
`EXTRA` varchar(30) NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT '',
`GENERATION_EXPRESSION` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)
(2)与此同时,通过ls/lsof获取到临时文件的存放位置
# ls -lrt /tmp/
-rw-r----- 1 mysql mysql 13722 Dec 9 15:22 #sql2896_3_0.frm
-rw-r----- 1 mysql mysql 13722 Dec 9 15:28 #sql2896_3_1.frm
-rw-r----- 1 mysql mysql 32768 Dec 9 15:28 #sql2896_3_1.ibd
# du -sh /tmp/*
16K /tmp/#sql2896_3_0.frm
16K /tmp/#sql2896_3_1.frm
32K /tmp/#sql2896_3_1.ibd
mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
| 57 | #sql2896_3_1 | 24 | 52 | TRUE | TRUE |
| 56 | #sql2896_3_0 | 24 | 51 | FALSE | FALSE |
+----------+--------------+--------+-------+----------------------+---------------+
2 rows in set (0.00 sec)
(3)往临时表插入大量数据
mysql> insert into t2 select * from t;
Query OK, 581632 rows affected (5.91 sec)
Records: 581632 Duplicates: 0 Warnings: 0
(4)再次查看临时表空间大小,增加至41M
# ls -lrt /tmp/
-rw-r----- 1 mysql mysql 13722 Dec 9 15:22 #sql2896_3_0.frm
-rw-r----- 1 mysql mysql 13722 Dec 9 15:28 #sql2896_3_1.frm
-rw-r----- 1 mysql mysql 41943040 Dec 9 15:34 #sql2896_3_1.ibd
# du -sh /tmp/*
16K /tmp/#sql2896_3_0.frm
16K /tmp/#sql2896_3_1.frm
41M /tmp/#sql2896_3_1.ibd
(1)执行Online DDL
第一次:
mysql> alter table t add column test_col varchar(10),algorithm=copy;
Query OK, 581632 rows affected (5.92 sec)
Records: 581632 Duplicates: 0 Warnings: 0
第二次:
mysql> alter table t drop column test_col,algorithm=copy;
Query OK, 581632 rows affected (5.94 sec)
Records: 581632 Duplicates: 0 Warnings: 0
(2)与此同时,通过ls/lsof获取到临时文件的存放位置
第一次:
# ls -lrt /appdata/mysqldata/test/
-rw-r----- 1 mysql mysql 13722 Dec 9 14:46 t.frm
-rw-r----- 1 mysql mysql 83886080 Dec 9 15:49 t.ibd
-rw-r----- 1 mysql mysql 13760 Dec 9 15:50 #sql-2896_4.frm
-rw-r----- 1 mysql mysql 83886080 Dec 9 15:50 #sql-2896_4.ibd
第二次:
# ls -lrt /appdata/mysqldata/test/
-rw-r----- 1 mysql mysql 13760 Dec 9 15:50 t.frm
-rw-r----- 1 mysql mysql 88080384 Dec 9 15:51 t.ibd
-rw-r----- 1 mysql mysql 13722 Dec 9 15:51 #sql-2896_4.frm
-rw-r----- 1 mysql mysql 83886080 Dec 9 15:52 #sql-2896_4.ibd
(1)执行Online DDL
第一次:
mysql> alter table t add column test_col varchar(10),algorithm=inplace;
Query OK, 0 rows affected (19.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
第二次:
mysql> alter table t drop column test_col,algorithm=inplace;
Query OK, 0 rows affected (19.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
(2)与此同时,通过ls/lsof获取到临时文件的存放位置
第一次:
# ls -lrt /appdata/mysqldata/test/
-rw-r----- 1 mysql mysql 13722 Dec 9 15:51 t.frm
-rw-r----- 1 mysql mysql 83886080 Dec 9 15:52 t.ibd
-rw-r----- 1 mysql mysql 13760 Dec 9 15:52 #sql-2896_4.frm
-rw-r----- 1 mysql mysql 96468992 Dec 9 15:53 #sql-ib60-1377243455.ibd
第二次:
# ls -lrt /appdata/mysqldata/test/
-rw-r----- 1 mysql mysql 13760 Dec 9 15:52 t.frm
-rw-r----- 1 mysql mysql 96468992 Dec 9 15:53 t.ibd
-rw-r----- 1 mysql mysql 13722 Dec 9 15:55 #sql-2896_4.frm
-rw-r----- 1 mysql mysql 96468992 Dec 9 15:55 #sql-ib61-1377243457.ibd
(1)会话1执行Online DDL
mysql> alter table t add column test_col varchar(10),algorithm=inplace;
......
(2)会话2执行DML
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select * from tt;
Query OK, 581632 rows affected (5.92 sec)
Records: 581632 Duplicates: 0 Warnings: 0
(3)与此同时,通过ls/lsof获取到临时文件的存放位置
mysqld 10390 mysql 60u REG 253,0 13631488 795917 /tmp/iblqyj3U (deleted)
临时文件,是MySQL非常重要、但又容易忽略的一类文件;通过上述各场景的测试,我们对MySQL各类临时文件有了更为深入的理解,这对我们进行容量评估、问题诊断、性能优化是有很大帮助的;现将上述测试总结如下:
(1)SQL执行过程中using filesort产生的临时文件,存放位置由tmpdir决定,以MY开头。
(2)SQL执行过程中using temporary产生的临时文件,存放在临时表空间。
(3)binlog cache产生的临时文件,存放位置由tmpdir决定,以ML开头。
(4)未使用ROW_FORMAT=COMPRESSED创建的InnoDB临时表,表结构存放在tmpdir,以#sql开头frm结尾;表数据存放在临时表空间。
(5)使用ROW_FORMAT=COMPRESSED创建的InnoDB临时表,存放位置由tmpdir决定,以#sql开头,以frm/ibd结尾。
(6)使用algorithm=copy的Online DDL产生的临时文件,存放在操作表相同目录下,以#sql-开头,以frm/ibd结尾。
(7)使用algorithm=inplace的Online DDL产生的临时文件;存放在操作表相同目录下,以#sql-开头,以frm/ibd结尾。
(8)Online DDL过程中产生的临时日志(记录DML操作),存放位置由tmpdir决定,以ib开头。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。