MySQL8.0自定义表空间

MySQL8.0自定义表空间

从MySQL 8.0开始允许用户自定义表空间，不同表的表空间可以设置使用不同磁盘，可以做到冷热数据的分离。

语法

创建表空间语法

CREATE [UNDO] TABLESPACE tablespace_name

  InnoDB and NDB:
    [ADD DATAFILE 'file_name'] -- 定义表空间数据文件的名称。创建NDB表空间时始终需要此选项。
                               -- 一个InnoDB表空间仅支持单个数据文件。
                               -- 如果需要修改undo表空间默认路径，需要修改innodb_undo_directory变量。
                               -- 如果是创建通用表空间且不在默认路径下，需要配置innodb_directories变量
                               -- 不支持循环路径引用，例如'any_directory/../ts1.ibd'
    [AUTOEXTEND_SIZE [=] value] -- 自动扩展大小

  InnoDB only: -- 仅innodb支持
    [FILE_BLOCK_SIZE = value]  -- 定义了表空间数据文件的块大小。值可以以字节或千字节为单位指定。
    [ENCRYPTION [=] {'Y' | 'N'}] 

  NDB only: -- 仅ndb支持
    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]  -- 重命名undo表空间
  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;
-- 以上的ROW_FORMAT非必要，这里只是为了演示同一个表空间可以提供多个表使用不同的row_format参数

多个表使用不同表空间

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