市场网站建设,广州企业vi设计公司,做网站注册商标,怀化职院网站文章目录 MySQL 8.0 InnoDB Tablespaces之Temporary Tablespaces#xff08;临时表空间#xff09;会话临时表空间会话临时表空间的磁盘分配和回收会话临时表空间的创建创建临时表和查看临时表信息会话临时表空间相关的设置参数innodb_temp_tablespaces_dir 全局临时表空间查… 文章目录 MySQL 8.0 InnoDB Tablespaces之Temporary Tablespaces临时表空间会话临时表空间会话临时表空间的磁盘分配和回收会话临时表空间的创建创建临时表和查看临时表信息会话临时表空间相关的设置参数innodb_temp_tablespaces_dir 全局临时表空间查看全局临时表空间的信息全局临时表空间相关的设置参数innodb_temp_data_file_path 参考 【免责声明】文章仅供学习交流观点代表个人与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB) MySQL 8.0 OCP (1Z0-908) 考点概要
MySQL 8.0 OCP (1Z0-908) 考点精析-安装与配置考点1设置系统变量 【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量 【MySQL】MySQL系统变量system variables列表mysqld --verbose --help的结果例 【MySQL】MySQL系统变量system variables列表SHOW VARIABLES 的结果例 MySQL 8.0 OCP (1Z0-908) 考点精析-备份与恢复考点1MySQL Enterprise Backup概要 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1sys.statement_analysis视图 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2系统变量的确认 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点3EXPLAIN ANALYZE MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点4慢速查询日志slow query log MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点5表连接算法join algorithm MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点6MySQL Enterprise Monitor之Query Analyzer MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1二进制日志文件Binary log MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点5数据字典(Data Dictionary) MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点6InnoDB Tablespaces之系统表空间System Tablespace MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces单独表空间 MySQL 8.0 InnoDB Tablespaces之General Tablespaces通用表空间/一般表空间 【MySQL】在数据目录之外创建InnoDB 表Creating Tables Externally
MySQL 8.0 InnoDB Tablespaces之Temporary Tablespaces临时表空间
InnoDB表空间是MySQL中用于存储InnoDB存储引擎表数据和索引的物理文件。 InnoDB表空间根据用途可以分成多种类型
数据表空间: System tablespace系统表空间File-per-table tablespaces单独表空间General tablespaces通用表空间/一般表空间 Undo 表空间临时表空间Temporary table tablespaces
其中MySQLInnoDB引擎的临时表空间可以分为两类
会话临时表空间用于存储用户创建的临时表和由优化器创建的内部临时表全局临时表空间用于存储对用户创建的临时表所做更改的回滚段rollback segments/undo logs。
会话临时表空间
会话临时表空间用于存储用户创建的临时表和由优化器创建的内部临时表。
会话临时表空间的磁盘分配和回收
当一个会话首次请求创建磁盘上的临时表时服务器会从临时表空间池中分配临时表空间给会话分配给会话的临时表空间将用于会话创建的所有磁盘上的临时表。当会话断开连接时其临时表空间被截断并释放回池中。一个会话最多分配两个临时表空间一个用于用户创建的临时表另一个用于优化器创建的内部临时表。
会话临时表空间的创建
服务器启动时将创建了一个包含10个临时表空间的池。池的大小永远不会缩小表空间会根据需要自动添加到池中。当服务器正常关闭或初始化中止时临时表空间池将被移除。会话临时表空间文件在创建时为5个页面大小并具有.ibt文件扩展名。
例默认分配的10个会话临时表空间
mysql select * from information_schema.INNODB_SESSION_TEMP_TABLESPACES\g
------------------------------------------------------------------------
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
------------------------------------------------------------------------
| 18 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767289 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
------------------------------------------------------------------------
10 rows in set (0.00 sec)会话临时表空间数据文件信息大小为80k的10个.ibt文件。
rootmysql-vm:/var/lib/mysql/#innodb_temp# pwd
/var/lib/mysql/#innodb_temp
rootmysql-vm:/var/lib/mysql/#innodb_temp# ls -larth
total 808K
drwxr-x--- 2 mysql mysql 4.0K Dec 28 11:28 .
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_9.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_1.ibt
drwx------ 12 mysql mysql 4.0K Dec 29 14:53※关于磁盘上内部临时表的存储引擎: - MySQL 旧版本中用于磁盘上内部临时表的存储引擎由internal_tmp_disk_storage_engine的值确定MYISAM/InnoDB默认值为InnoDB。- MySQL 8.0.16版本开始参数internal_tmp_disk_storage_engine被废止用于磁盘上内部临时表的存储引擎固定为InnoDB。创建临时表和查看临时表信息
用户可以通过 CREATE TEMPORARY TABLE语句创建临时表。 并且可以通过information_schema.INNODB_SESSION_TEMP_TABLESPACES查看临时表空间的元数据信息通过INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO表查看创建的临时表的元数据信息。
例
mysql use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)mysql select * from information_schema.INNODB_SESSION_TEMP_TABLESPACES\g
------------------------------------------------------------------------
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
------------------------------------------------------------------------
| 18 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 18 | 4243767289 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | USER |
| 19 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
------------------------------------------------------------------------
10 rows in set (0.00 sec)mysql
mysql SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
TABLE_ID: 1438NAME: #sql3fec_12_11N_COLS: 4SPACE: 4243767289
1 row in set (0.00 sec)
数据文件
rootmysql-vm:/var/lib/mysql/#innodb_temp# ls -larth
total 828K
drwxr-x--- 2 mysql mysql 4.0K Dec 28 11:28 .
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Dec 28 11:28 temp_1.ibt
drwx------ 12 mysql mysql 4.0K Dec 29 14:53 ..
-rw-r----- 1 mysql mysql 96K Dec 29 23:06 temp_9.ibt临时表所在的临时表空间的数据文件temp_9.ibt会根据需要扩大。
※注临时表只在当前会话中可见并在会话关闭时临时表会被自动删除。
mysql exit
Bye例会话关闭后ID18的临时表空间变为INACTIVE 且临时表被自动删除。
mysql select * from information_schema.INNODB_SESSION_TEMP_TABLESPACES\g
------------------------------------------------------------------------
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
------------------------------------------------------------------------
| 19 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767289 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | INACTIVE | NONE |
------------------------------------------------------------------------
10 rows in set (0.00 sec)mysql SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
Empty set (0.00 sec)会话临时表空间相关的设置参数
innodb_temp_tablespaces_dir
innodb_temp_tablespaces_dir变量用于定义会话临时表空间创建的位置。
Command-Line Format–innodb-temp-tablespaces-dirdir_nameIntroduced8.0.13System Variableinnodb_temp_tablespaces_dirScopeGlobalDynamicNoSET_VAR Hint AppliesNoTypeDirectory nameDefault Value#innodb_temp
例
mysql show variables like innodb_temp_tablespaces_dir;
----------------------------------------------
| Variable_name | Value |
----------------------------------------------
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
----------------------------------------------
1 row in set (0.00 sec)
mysql show variables like datadir;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.01 sec)默认位置是数据目录中的#innodb_temp目录。
rootmysql-vm:/var/lib/mysql/#innodb_temp# pwd
/var/lib/mysql/#innodb_temp
rootmysql-vm:/var/lib/mysql/#innodb_temp# ls
temp_1.ibt temp_10.ibt temp_2.ibt temp_3.ibt temp_4.ibt temp_5.ibt temp_6.ibt temp_7.ibt temp_8.ibt temp_9.ibt
rootmysql-vm:/var/lib/mysql/#innodb_temp#全局临时表空间
全局临时表空间用于存储对用户创建的临时表所做更改的回滚段rollback segments/undo logs。
全局临时表空间在正常关闭或初始化中止时被移除并在每次服务器启动时重新创建。全局临时表空间在创建时接收一个动态生成的空间ID。如果无法创建全局临时表空间服务器将拒绝启动。如果服务器意外停止全局临时表空间不会被移除。重新启动MySQL服务器会自动移除并重新创建全局临时表空间回收全局临时表空间数据文件占用的磁盘空间。
查看全局临时表空间的信息
可以通过INFORMATION_SCHEMA.FILES查看有关全局临时表空间的元数据。
例
mysql SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAMEinnodb_temporary\G
*************************** 1. row ***************************FILE_ID: 4294967293FILE_NAME: ./ibtmp1FILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_temporaryTABLE_CATALOG:TABLE_SCHEMA: NULLTABLE_NAME: NULLLOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULLENGINE: InnoDBFULLTEXT_KEYS: NULLDELETED_ROWS: NULLUPDATE_COUNT: NULLFREE_EXTENTS: 2TOTAL_EXTENTS: 12EXTENT_SIZE: 1048576INITIAL_SIZE: 12582912MAXIMUM_SIZE: NULLAUTOEXTEND_SIZE: 67108864CREATION_TIME: NULLLAST_UPDATE_TIME: NULLLAST_ACCESS_TIME: NULLRECOVER_TIME: NULLTRANSACTION_COUNTER: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULLMAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: 6291456CREATE_TIME: NULLUPDATE_TIME: NULLCHECK_TIME: NULLCHECKSUM: NULLSTATUS: NORMALEXTRA: NULL
1 row in set (0.01 sec)mysql
mysql SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE- AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES- WHERE TABLESPACE_NAME innodb_temporary\G
*************************** 1. row ***************************FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporaryENGINE: InnoDBINITIAL_SIZE: 12582912TotalSizeBytes: 12582912DATA_FREE: 6291456MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)mysql全局临时表空间数据文件
rootmysql-vm:/var/lib/mysql# ls -lh ibtmp1
-rw-r----- 1 mysql mysql 12M Dec 28 11:28 ibtmp1
rootmysql-vm:/var/lib/mysql#默认情况下全局临时表空间数据文件是自动扩展的并根据需要增加大小。
mysql show variables like %innodb_temp_data_file_path%;
---------------------------------------------------
| Variable_name | Value |
---------------------------------------------------
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
---------------------------------------------------
1 row in set (0.01 sec)
全局临时表空间相关的设置参数
innodb_temp_data_file_path
innodb_temp_data_file_path变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。 如果未为innodb_temp_data_file_path指定值则默认行为是在innodb_data_home_dir目录中创建一个名为ibtmp1的单个自动扩展数据文件。初始文件大小略大于12MB。 另外如果要修改innodb_temp_data_file_path参数 需要重启服务器。 例
mysql show variables like %innodb_temp_data_file_path%;
---------------------------------------------------
| Variable_name | Value |
---------------------------------------------------
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
---------------------------------------------------
1 row in set (0.01 sec)mysql参考
15.6.3.5 Temporary T ablespaces https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html
15.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-temp-table-info.html
13.1.20.2 CREATE TEMPORARY TABLE Statement https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
Internal Temporary Table Storage Engine https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html#internal-temporary-tables-engines
