MySQL 临时表相关参数说明区别

MySQL 临时表参数innodb_temp_tablespaces_dir、innodb_temp_data_file_path、innodb_tmpdir、tmpdir 区分

解决方案

innodb_tmpdir： alter table生成中间表文件，innodb_tmpdir有指定效路径，优选选择innodb_tmpdir，没有则选择tmpdir,在alter table时rebulid表是生成大量临时排序文件，避免系统文件tmpfs占满

tmpdir:用户存放临时文件，默认取/tmp, /var/tmp, or /usr/tmp。
.ORDER BY 或 GROUP BY会生成临时文件，临时SQL表名#sql；
.select 查询生成临时SQL表用报存中间表数据；
.非ALGORITHM=INPLACE的ddl操作，创建的临时copy；ddl操作期间用于记录并发的dml日志文件，创建索引的排序文件，重建表时生成的临时中间表

临时表空间分为会话临时表空间和全局临时临时表空。

innodb_temp_tablespaces_dir：
会话临时表空间：用于存放用户创建的临时表和优化器optimizer创建内部临时表。在第一次请求创建临时表，会从临时表空间池中分配两个会话临时表空间，一个用户创建的临时表，一个优化器创建的其他内部临时表。但会话断开时，临时表空间被truncate并释放给池。一个池有10个临时表空间，并且不会shrink除非重启数据库或abrot时被remove.会话表空间文件是大小是5个页，后缀名.ibt

会话临时表空间space id 范围是40万，重启后可以重复使用

会话临时表空间参数 innodb_temp_tablespaces_dir：定义会话临时表空创建时的位置，默认位置是datafir/#innodb_temp

$> ls
temp_10.ibt temp_2.ibt temp_4.ibt temp_6.ibt temp_8.ibt
temp_1.ibt temp_3.ibt temp_5.ibt temp_7.ibt temp_9.ibt

会话临时表空间元数据信息表INNODB_SESSION_TEMP_TABLESPACES

±—±-----------±---------------------------±------±---------±----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
±—±-----------±---------------------------±------±---------±----------+
| 33 | 4294501266 | ./#innodb_temp/temp_10.ibt | 98304 | ACTIVE | INTRINSIC |
| 0 | 4294501257 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501258 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501259 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501260 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501261 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501262 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501263 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501264 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501265 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
±—±-----------±---------------------------±------±---------±----------+

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO：用于存放活动临时表元数据信息

-> 　　id int primary key,
-> 　　name varchar(20) 
-> )Engine=InnoDB ;

Query OK, 0 rows affected (0.00 sec)

root@localhost 13:04:27 [information_schema]>select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO ;
±---------±--------------±-------±-----------+
| TABLE_ID | NAME | N_COLS | SPACE |
±---------±--------------±-------±-----------+
| 1288 | #sql4de5_22_7 | 5 | 4294501265 |
±---------±--------------±-------±-----------+
1 row in set (0.00 sec)

Global Temporary Tablespace：
全局临时表空间(ibtmp1)用于存放用户临时表空间修改数据都回滚段。
innodb_temp_data_file_path：指定临时表空间路径、名称、大小、属性，默认放在innodb_data_home_dir目录下。数据库正常重启，abort初始化时会被移除重建。在数据库不正常halt时，Global Temporary Tablespace不会自动删除，管理员能手动删除或者重启数据库

INFORMATION_SCHEMA.FILES 存放全局临时表空间元数据信息：
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME=‘innodb_temporary’\G

*************************** 1. row ***************************
FILE_ID: 4294967293
FILE_NAME: ./ibtmp1
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_temporary
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 2
TOTAL_EXTENTS: 512
EXTENT_SIZE: 1048576
INITIAL_SIZE: 536870912
MAXIMUM_SIZE: 34359738368
AUTOEXTEND_SIZE: 67108864
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 524288000
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
1 row in set (0.00 sec)