【MySQL】在数据目录之外创建InnoDB 表（Creating Tables Externally）

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|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) 考点精析-备份与恢复考点1：MySQL Enterprise Backup概要
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1：sys.statement_analysis视图
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点3：EXPLAIN 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) 考点精析-性能优化考点6：MySQL 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) 考点精析-架构考点6：InnoDB 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中在InnoDB中创建表的时候默认使用File-per-table tablespaces（单独表空间），即创建一个InnoDB 表会创建一个独立的表空间用于保存表的数据、索引以及元数据。
表空间的数据文件会保存在MySQL data目录中 ，并且.ibd文件的命名以表的名称命名（table_name.ibd）。

根据业务或者是磁盘优化等需要，也可以在数据目录之外创建InnoDB 表，也就是将表空间的数据文件放置在数据目录之外的位置，具体方法包括：

- 创建表时使用DATA DIRECTORY选项（单独表空间）
- 创建表时指定表空间（使用通用表空间）

注意，数据文件可以放置在数据目录之外的位置，但是路径必须是innodb_directories的值或者附加到innodb_directories值的变量（innodb_data_home_dir、innodb_undo_directory和datadir）之一。

例：

mysql> SELECT @@datadir,@@innodb_data_home_dir,@@innodb_directories;
+-----------------+------------------------+----------------------+
| @@datadir       | @@innodb_data_home_dir | @@innodb_directories |
+-----------------+------------------------+----------------------+
| /var/lib/mysql/ | NULL                   | /var/lib/mysql-files |
+-----------------+------------------------+----------------------+
1 row in set (0.00 sec)

从上面的内容可以看到数据目录是/var/lib/mysql/，在数据目录之外，可以放置数据文件的其他目录为/var/lib/mysql-files。

创建表时使用DATA DIRECTORY选项（单独表空间）

使用DATA DIRECTORY选项可以指定创建表的数据文件目录。

innodb_file_per_table设置为开启状态（默认）

例1：默认创建表的表空间

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create table tb1_external (a int);
Query OK, 0 rows affected (0.03 sec)

mysql> select
    ->      name as 'table_name',
    ->      space_type
    ->  from
    ->      information_schema.innodb_tables
    ->  where
    ->      name like '%tb1_external%' \g
+---------------------+------------+
| table_name          | space_type |
+---------------------+------------+
| testdb/tb1_external | Single     |
+---------------------+------------+
1 row in set (0.00 sec)
mysql> select * from
    ->  information_schema.innodb_tablespaces t
    ->  join information_schema.innodb_datafiles d
    ->  on t.SPACE=d.SPACE
    ->  where t.NAME ='testdb/tb1_external'\G
*************************** 1. row ***************************
          SPACE: 98
           NAME: testdb/tb1_external
           FLAG: 16417
     ROW_FORMAT: Dynamic
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Single
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 114688
 ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.35
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: normal
          SPACE: 0x3938
           PATH: ./testdb/tb1_external.ibd
1 row in set (0.01 sec)

mysql>

例2：使用DATA DIRECTORY选项可以指定创建表的数据文件目录

mysql> create table tb2_external (a int) DATA DIRECTORY='/var/lib/mysql-files';
Query OK, 0 rows affected (0.04 sec)

mysql> select
    ->      name as 'table_name',
    ->      space_type
    ->  from
    ->      information_schema.innodb_tables
    ->  where
    ->      name like '%tb2_external%' \G
*************************** 1. row ***************************
table_name: testdb/tb2_external
space_type: Single
1 row in set (0.00 sec)

mysql> select * from
    ->  information_schema.innodb_tablespaces t
    ->  join information_schema.innodb_datafiles d
    ->  on t.SPACE=d.SPACE
    ->  where t.NAME ='testdb/tb2_external'\G
*************************** 1. row ***************************
          SPACE: 99
           NAME: testdb/tb2_external
           FLAG: 17441
     ROW_FORMAT: Dynamic
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Single
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 114688
 ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.35
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: normal
          SPACE: 0x3939
           PATH: /var/lib/mysql-files/testdb/tb2_external.ibd
1 row in set (0.00 sec)

mysql>

使用DATA DIRECTORY选项可以指定创建表的数据文件目录，创建的表的数据文件放在了/var/lib/mysql-files/testdb/tb2_external.ibd。

innodb_file_per_table设置为关闭状态

innodb_file_per_table设置为关闭状态时，创建表时候需要指定TABLESPACE = innodb_file_per_table，否则报错。

例：

mysql> set global innodb_file_per_table='OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create table tb4_external (a int) DATA DIRECTORY='/var/lib/mysql-files';
ERROR 1031 (HY000): Table storage engine for 'tb4_external' doesn't have this option
mysql>

指定TABLESPACE = innodb_file_per_table后创建数据目录之外的InnoDB 表。

例：

mysql> select
    ->      name as 'table_name',
    ->      space_type
    ->  from
    ->      information_schema.innodb_tables
    ->  where
    ->      name like '%tb4_external%' \G
*************************** 1. row ***************************
table_name: testdb/tb4_external
space_type: Single
1 row in set (0.00 sec)

mysql> select * from
    ->  information_schema.innodb_tablespaces t
    ->  join information_schema.innodb_datafiles d
    ->  on t.SPACE=d.SPACE
    ->  where t.NAME ='testdb/tb4_external'\G
*************************** 1. row ***************************
          SPACE: 100
           NAME: testdb/tb4_external
           FLAG: 17441
     ROW_FORMAT: Dynamic
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: Single
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 114688
 ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.35
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: normal
          SPACE: 0x313030
           PATH: /var/lib/mysql-files/testdb/tb4_external.ibd
1 row in set (0.01 sec)

mysql>

创建表时指定表空间（使用通用表空间）

创建datadir路径外的通用表空间，然后创建表时指定通用表空间。

例：

mysql> create tablespace tbs_external add datafile '/var/lib/mysql-files/tbs_external.ibd';
Query OK, 0 rows affected (0.02 sec)

mysql> create table tb5_external (a int)
    -> TABLESPACE = tbs_external;
Query OK, 0 rows affected (0.02 sec)

mysql> select
    ->      name as 'table_name',
    ->      space_type
    ->  from
    ->      information_schema.innodb_tables
    ->  where
    ->      name like '%tb5_external%' \G
*************************** 1. row ***************************
table_name: testdb/tb5_external
space_type: General
1 row in set (0.00 sec)

mysql> select * from
    ->  information_schema.innodb_tablespaces t
    ->  join information_schema.innodb_datafiles d
    ->  on t.SPACE=d.SPACE
    ->  where t.NAME  like '%tbs_external%'\G
*************************** 1. row ***************************
          SPACE: 101
           NAME: tbs_external
           FLAG: 18432
     ROW_FORMAT: Any
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: General
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 114688
 ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.35
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: normal
          SPACE: 0x313031
           PATH: /var/lib/mysql-files/tbs_external.ibd
1 row in set (0.00 sec)

mysql>

参考

15.6.1.2 Creating Tables Externally
https://dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html