MySQL 5.7 新特性 共享临时表空间及临时表更进
发布时间：2022-04-08 16:57:42 所属栏目：MySql教程 来源：互联网
导读：在MySQL 5.6引用了独立undo tablespace之后，MySQL 5.7在temporary tablespace上做了改进。 已经实现将temporary tablespace从ibdata（系统表空间文件）中分离。 并且可以重启重置大小，避免出现像ibdata难以释放的问题。 但下面所有的讨论只针对InnoDB，
|
在MySQL 5.6引用了独立undo tablespace之后，MySQL 5.7在temporary tablespace上做了改进。 已经实现将temporary tablespace从ibdata（系统表空间文件）中分离。 并且可以重启重置大小，避免出现像ibdata难以释放的问题。 但下面所有的讨论只针对InnoDB，并且指定了innodb_file_per_table，所用版本为MySQL 5.7.x 〇 新特性 · 共享临时表空间(shared temporary tablespace)： 共享临时表空间出现于MySQL 5.7.1，为的是将临时表空间从系统表空间(system tablespace)文件中独立出来。该共享临时表空间用于存储非压缩InnoDB临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等数据。更多信息可以参考【MySQL 5.7 Reference Manual 8.4.4 Internal Temporary Table Use in MySQL】 因为存放的数据特殊性，不会参与crash recovery，因此无需记录redo log。 该共享临时表空间默认大小为12MB。在实例关闭之后，将会被删除。在实例启动时则会被创建。 默认的，该共享临时表空间存放在innodb_data_home_dir中的ibtmp1里，而innodb_data_home_dir默认为datadir。 所以一般该ibtmp1存放在datadir下，显然，其路径与共享表空间的路径一样，取决于innodb_data_home_dir。 新增参数innodb_temp_data_file_path，通过修改其值可以将该共享临时表空间的文件名，扩展大小做修改。 比如在配置文件中加上innodb_temp_data_file_path = temp_tablespace:64M:autoextend 那么在启动实例之后，会生成一个大小为64MB的temp_tablespace文件 -rw-r----- 1 root root 67108864 Jun 20 17:29 temp_tablespace 该参数默认出现于5.7.1，静态，默认值为ibtmp1:12M:autoextend。 〇 新特性 · InnoDB临时表统计信息优化 因为临时表特性，是无法在SHOW TABLES;与通过information_schema.TABLES查询到其元数据信息的。 老版本可能只能通过一些比较麻烦的方法来查看： 比如SHOW CREATE TABLE tmp_aG 5.7版本之后，在I_S里增加了一个表来统计该表的元数据信息INNODB_TEMP_TABLE_INFO。 可以通过I_S来查看该表的定义： SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; +----------+---------------+--------+-------+----------------------+---------------+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+---------------+--------+-------+----------------------+---------------+ | 68 | #sql2b79_35_0 | 4 | 37 | FALSE | FALSE | +----------+---------------+--------+-------+----------------------+---------------+ 1 row in set (0.00 sec) 字段介绍： TABLE_ID：表id NAME：表名，这个名字对应的表结构为$NAME.frm，若该表为压缩临时表，对应的数据文件为$NAME.ibd，反之则无。 N_COLS：列的数量，1个被我显示创建的列，其他3个为InnoDB的隐藏列(DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR) SPACE：临时表的表空间id，总是非0，并且随实例重启动态变化， PER_TABLE_TABLESPACE：如果为TRUE，则表明该临时表有自己的临时表空间（有自己的ibd文件），如果为FALSE，则表明该临时表用共享表空间。 IS_COMPRESSED：如果为TRUE，则表明该表被压缩，反之则未压缩。 〇 新特性 · innodb_tmpdir 出现在5.7.11以后的版本，用于在做某些Online DDL时存放临时数据。 innodb_tmpdir的值覆盖tmpdir，此特性只针对于Online DDL生效。 〇 共享临时表空间与tmpdir对比： 通过CREATE TEMPORARY TABLE ... 创建的表，该表定义会放在tmpdir下，默认为/tmp tmpdir不是个新参数，一般也不需要指定，默认值为/tmp，此处还是提及并与共享临时表空间做一个对比。 tmpdir参数用于指定临时文件(temporary files)和临时表(temporary tables)的存放目录。 可以设定为一个集合并做轮询调度（用:分割），如果要用，建议指定多个磁盘目录以提高性能。 此外，对于显式创建的临时表（create temporary table）： 与共享临时表空间不同的是，tmpdir存储的是compressed InnoDB temporary tables的临时独立表空间。 以下做一个测试，验证一下： 参数检查： SELECT @@innodb_temp_data_file_path, @@innodb_file_per_table, @@tmpdir, @@innodb_data_home_dir; +--------------------------------+-------------------------+----------+------------------------+ | @@innodb_temp_data_file_path | @@innodb_file_per_table | @@tmpdir | @@innodb_data_home_dir | +--------------------------------+-------------------------+----------+------------------------+ | ibtmp1:12M:autoextend | 1 | /tmp | NULL | +--------------------------------+-------------------------+----------+------------------------+ 1 row in set (0.00 sec) 先创建两张临时表，引擎均为默认的InnoDB，其中第一张指定行格式为COMRESSED，第二张不压缩： root@localhost [test]> CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED; Query OK, 0 rows affected (0.02 sec) root@localhost [test]> CREATE TEMPORARY TABLE uncompress_table (id int, name char(255)) ; Query OK, 0 rows affected (0.00 sec) root@localhost [test]> SHOW CREATE TABLE compress_tableG *************************** 1. row *************************** Table: compress_table Create Table: CREATE TEMPORARY TABLE `compress_table` ( `id` int(11) DEFAULT NULL, `name` char(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 1 row in set (0.00 sec) root@localhost [test]> SHOW CREATE TABLE uncompress_tableG *************************** 1. row *************************** Table: uncompress_table Create Table: CREATE TEMPORARY TABLE `uncompress_table` ( `id` int(11) DEFAULT NULL, `name` char(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 检查一下两张临时表的表定义： root@localhost [test]> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; +----------+-------------+--------+-------+----------------------+---------------+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+-------------+--------+-------+----------------------+---------------+ | 73 | #sqlb48_3_1 | 5 | 58 | FALSE | FALSE | | 72 | #sqlb48_3_0 | 5 | 59 | TRUE | TRUE | +----------+-------------+--------+-------+----------------------+---------------+ 2 rows in set (0.00 sec) 根据TABLE_ID和IS_COMPRESSED和PER_TABLE_TABLESPACE参数 可得知，#sqlb48_3_0为compress_table，#sqlb48_3_1为uncompress_table 创建好了之后，检查/tmp目录，也就是tmpdir。 # ll /tmp/ total 88 -rw-r----- 1 root root 8586 Jun 20 16:38 #sqlb48_3_0.frm -rw-r----- 1 root root 65536 Jun 20 16:38 #sqlb48_3_0.ibd -rw-r----- 1 root root 8586 Jun 20 16:39 #sqlb48_3_1.frm -rw-------. 1 root root 0 Jan 3 2014 yum.log 可以发现，两张显式创建的临时表的【表定义文件】都被放到了tmpdir下。 此外，#sqlb48_3_0也就是IS_COMPRESSED为TRUE的那张压缩表，ibd文件也放在了tmpdir文件中。 那么理论上，#sqlb48_3_1这张未压缩的表的数据放到了ibtmp1中，也就是放到了共享临时表空间中。 简单验证一下，验证思路为两张表插入大量数据。 并分别检查ibtmp1文件和#sqlb48_3_0.ibd文件的大小变化： 对compress_table表： root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 50000; Query OK, 50000 rows affected (1.20 sec) Records: 50000 Duplicates: 0 Warnings: 0 （a表为一个测试数据用表） root@localhost [test]> ! ls -l /tmp -rw-r----- 1 root root 11534336 Jun 20 16:54 #sqlb48_3_0.ibd root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 20000; Query OK, 20000 rows affected (0.53 sec) Records: 20000 Duplicates: 0 Warnings: 0 （a表为一个测试数据用表） root@localhost [test]> ! ls -l /tmp/*.ibd -rw-r----- 1 root root 14680064 Jun 20 16:55 #sqlb48_3_0.ibd 可以发现，针对压缩的InnoDB临时表，其数据放在tmpdir下的ibd文件中 再简单测试一下非压缩的InnoDB临时表： root@localhost [test]> ! ls -l /data/mysql-data/mysql57-3357/datadir/ibtmp1 -rw-r----- 1 root root 12582912 Jun 20 16:57 /data/mysql-data/mysql57-3357/datadir/ibtmp1 root@localhost [test]> INSERT INTO uncompress_table SELECT id, name FROM a limit 50000; Query OK, 50000 rows affected (0.53 sec) Records: 50000 Duplicates: 0 Warnings: 0 root@localhost [test]> ! ls -l /data/mysql-data/mysql57-3357/datadir/ibtmp1 -rw-r----- 1 root root 79691776 Jun 20 17:02 /data/mysql-data/mysql57-3357/datadir/ibtmp1 显然，非压缩的InnoDB临时表将数据存放在了共享临时表空间。 tmpdir下的东西和共享临时表空间最大的共同点以及特性就是，实例关闭之后，将会被删除。 〇 slave_load_tmpdir 该参数也不是5.7的新伙计，默认值取决于tmpdir的参数。 用于存放slave上产生的特殊的临时文件： 在master上出现LOAD DATA INFILE ... 时，被记录到binlog并发送给slave，在SQL thread从relaylog提取数据时，写入指定的目录下，然后执行LOAD DATA LOCAL INFILE ...，结束之后则会删掉这个文件。 增加这个参数是为了复制的可靠性和数据一致性。 如果默认放在tmpdir下，如果此时遭遇重启，文件丢失，则会导致复制失败。 如果master有使用这样的语句，建议将该目录指定在基于可靠存储设备上。 〇 可能遇到的问题： MySQL 5.7.6以后，开始支持32KB和64KB的page size，若将page size修改为32或者64KB，则不能使用ROW_FORMAT=COMPRESSED，该行格式能支持的最大page size为16KB。 若要保证ROW_FORMAT=COMPRESSED生效，innodb_file_format必须设置为Barracuda。 〇 参考文档： MySQL 5.7 Reference Manual 5.1.3 Server Option and Variable Reference MySQL 5.7 Reference Manual 14.4.12 Temporary Tablespace MySQL 5.7 Reference Manual 14.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Information Table。  （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐