一、背景

云上磁盘io饱和,为了缓解MySQL8压力，可以把指定表迁移到指定表空间目录，创建语法如下：

CREATE [UNDO] TABLESPACE tablespace_name
		[ADD DATAFILE 'file_name']
    [AUTOEXTEND_SIZE [=] value]
 		[FILE_BLOCK_SIZE = value]
    [ENCRYPTION [=] {'Y' | 'N'}]
    [ENGINE [=] engine_name]
- ADD DATAFILE：指定表空间的关联文件和路径，注意必须已.ibd结尾
- AUTOEXTEND_SIZE：8.0.13以后有用，当表空间满时扩容的大小，必须是4MB的倍数，最大为4G（8.0.24开始）。默认值为0，默认规则为：表空间小于1M，每次增加1页；表空间大于1M小于32M,每次增加1M；表空间大于32M，每次增加4M；
- FILE_BLOCK_SIZE：定义表空间数据文件的块大小。默认为innodb_page_size，如使用默认值则只能存储未压缩的行格式（COMPACT, REDUNDANT, and - DYNAMIC）。如存储压缩的表（ROW_FORMAT=COMPRESSED）则表的KEY_BLOCK_SIZE需等于FILE_BLOCK_SIZE/1024；
ENCRYPTION：是否静态加密

二、配置格外磁盘目录

SELECT @@datadir,@@innodb_data_home_dir,@@innodb_directories;

默认没有增加，需要在my.cnf 增加新增磁盘的目录，我们这边以/data1,/data2 为新增磁盘
在[mysqld]新增如下配置

innodb_directories="/data1/mysql3306/data;/data2/mysql3306/data"

重启数据库

查询是否成功

三、创建例子

root@localhost [(none)]>CREATE TABLESPACE test ADD DATAFILE '/data1/mysql3306/data/test.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0.31 sec)
root@localhost [(none)]>create database test;
Query OK, 1 row affected (0.05 sec)

root@localhost [(none)]>use test
Database changed
root@localhost [test]> create table test (id int) tablespace=test;
Query OK, 0 rows affected (0.20 sec)

root@localhost [test]>create table test (id int ,name varchar(255)) tablespace=test;
Query OK, 0 rows affected (0.18 sec)

root@localhost [test]> select * from information_schema.innodb_tablespaces where name='test'\G
*************************** 1. row ***************************
          SPACE: 431
           NAME: test
           FLAG: 18432
     ROW_FORMAT: Any
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: General
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 131072
 ALLOCATED_SIZE: 131072
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.28
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: normal
1 row in set (0.38 sec)

root@localhost [test]>select * from information_schema.files where tablespace_name='test'\G
*************************** 1. row ***************************
             FILE_ID: 431
           FILE_NAME: /data1/mysql3306/data/test.ibd
           FILE_TYPE: TABLESPACE
     TABLESPACE_NAME: ts_test
       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: 0
       TOTAL_EXTENTS: 0
         EXTENT_SIZE: 1048576
        INITIAL_SIZE: 114688
        MAXIMUM_SIZE: NULL
     AUTOEXTEND_SIZE: 1048576
       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: 0
         CREATE_TIME: NULL
         UPDATE_TIME: NULL
          CHECK_TIME: NULL
            CHECKSUM: NULL
              STATUS: NORMAL
               EXTRA: NULL
1 row in set (0.00 sec)