MySQL8.0自定义表空间
从MySQL 8.0开始允许用户自定义表空间,不同表的表空间可以设置使用不同磁盘,可以做到冷热数据的分离。
语法
创建表空间语法
CREATE [UNDO] TABLESPACE tablespace_name
InnoDB and NDB:
[ADD DATAFILE 'file_name']
[AUTOEXTEND_SIZE [=] value]
InnoDB only:
[FILE_BLOCK_SIZE = value]
[ENCRYPTION [=] {'Y' | 'N'}]
NDB only:
USE LOGFILE GROUP logfile_group
[EXTENT_SIZE [=] extent_size]
[INITIAL_SIZE [=] initial_size]
[MAX_SIZE [=] max_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] 'string']
InnoDB and NDB:
[ENGINE [=] engine_name]
Reserved for future use:
[ENGINE_ATTRIBUTE [=] 'string']
修改表空间语法
ALTER [UNDO] TABLESPACE tablespace_name
NDB only:
{ADD | DROP} DATAFILE 'file_name'
[INITIAL_SIZE [=] size]
[WAIT]
InnoDB and NDB:
[RENAME TO tablespace_name]
InnoDB only:
[AUTOEXTEND_SIZE [=] 'value']
[SET {ACTIVE | INACTIVE}]
[ENCRYPTION [=] {'Y' | 'N'}]
InnoDB and NDB:
[ENGINE [=] engine_name]
Reserved for future use:
[ENGINE_ATTRIBUTE [=] 'string']
示例
修改innodb_directories
变量,语法为:
innodb_directories="directory_path_1;directory_path_2"
改变量为只读变量,需要重启生效,新增/tmp作为存放表空间文件目录
innodb_directories="/tmp"
多个表使用相同表空间
mysql> CREATE DATABASE tbs_test;
mysql> USE tbs_test;
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=REDUNDANT;
mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;
多个表使用不同表空间
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' ENGINE=INNODB;
mysql> CREATE TABLESPACE `ts3` ADD DATAFILE '/tmp/ts3.ibd' ENGINE=INNODB;
mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ;
mysql> CREATE TABLE t5 (c1 INT PRIMARY KEY) TABLESPACE ts3 ;
表空间文件目录必须是已知
创建undo表空间
mysql> CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
查看表空间文件信息
mysql> SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME like 'ts%';
+
| FILE_NAME | FILE_TYPE | LOGFILE_GROUP_NAME | STATUS | EXTRA |
+
| ./ts1.ibd | TABLESPACE | NULL | NORMAL | NULL |
| ./ts2.ibd | TABLESPACE | NULL | NORMAL | NULL |
| /tmp/ts3.ibd | TABLESPACE | NULL | NORMAL | NULL |
+
查看innodb表空间信息
mysql> select * from INNODB_TABLESPACES;
mysql> select * from INNODB_TABLESPACES_BRIEF;
+------------+-----------------+----------------------+-------+------------+
| SPACE | NAME | PATH | FLAG | SPACE_TYPE |
+------------+-----------------+----------------------+-------+------------+
| 0 | innodb_system | ibdata1 | 18432 | System |
| 4294967279 | innodb_undo_001 | ./undo_001 | 0 | Single |
| 4294967278 | innodb_undo_002 | ./undo_002 | 0 | Single |
| 1 | sys/sys_config | ./sys/sys_config.ibd | 16417 | Single |
| 2 | ts1 | ts1.ibd | 18432 | General |
| 3 | ts2 | ts2.ibd | 18432 | General |
| 4 | ts3 | /tmp/ts3.ibd | 18432 | General |
| 4294967150 | undo_003 | undo_003.ibu | 0 | Single |
+------------+-----------------+----------------------+-------+------------+
停用UNDO表空间
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
启用UNDO表空间
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
参考文档
- https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html
- https://dev.mysql.com/doc/refman/8.0/en/alter-tablespace.html
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)