1 、 外部表介绍

MySQL 中所谓的外部表，就是将 innodb 表创建在变量 datadir 代表的数据目录之外。出于扩容，空间管理， IO 优化等原因。

2 、支持版本

MySQL 5.7及以上。

3 、创建外部表

这里可以使用如下3种方式创建外部表：

● 使用 DATA DIRECTORY 子句

● 使用 CREATE TABLE ... TABLESPACE 语法

● 在外部通用表空间中创建表

1)         使用 DATA DIRECTORY 子句

前提1：如果使用DATA DIRECTORY子句创建，前提是开启File-Per-Table表空间属性，即启动innodb_file_per_table变量（默认启用）。

前提2：由于实例崩溃恢复需要确定表空间文件位置目录，因此DATA DIRECTORY指定的数据目录位置则必须在提前定义好，无法随意指定，不然mysql它本身不知道都有哪些目录。MySQL 8.0开始新增条件。

禁用innodb_file_per_table，会出现如下报错

CREATE TABLE `ext_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `county_code` char(3) DEFAULT NULL,

    ->   `county_name` varchar(10) DEFAULT NULL,

    ->   `level_city_code` char(3) DEFAULT NULL,

    ->   PRIMARY KEY (`id`),

    ->   KEY `idx_county_code` (`county_code`),

    ->   KEY `idx_level_city_code` (`level_city_code`)

    -> )DATA DIRECTORY = '/home/mysql/';

5.7 ：ERROR 1031 (HY000): Table storage engine for 'ext_table' doesn't have this option

8.0 ：ERROR 3121 (HY000): The DATA DIRECTORY location must be in a known directory.

开启innodb_file_per_table后，MySQL5.7创建成功

CREATE TABLE `ext_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `county_code` char(3) DEFAULT NULL,

    ->   `county_name` varchar(10) DEFAULT NULL,

    ->   `level_city_code` char(3) DEFAULT NULL,

    ->   PRIMARY KEY (`id`),

    ->   KEY `idx_county_code` (`county_code`),

    ->   KEY `idx_level_city_code` (`level_city_code`)

    -> )DATA DIRECTORY = '/home/mysql/mysql57/';

Query OK, 0 rows affected (0.23 sec)

shell> ll /home/mysql/mysql57/wg_db01/

total 128

-rw-r-----. 1 mysql mysql 131072 Aug 22 15:01 ext_table.ibd

MySQL 8.0还是报同样的错误

ERROR 3121 (HY000): The DATA DIRECTORY location must be in a known directory.

说明在8.0版本开始，外部表的创建，DATA DIRECTORY指定的数据目录外的位置必须在提前定义好。会检测外部表目录是否存在，然后在检测innodb_file_per_table是否开启

MySQL 8.0以上版本，配置外部表目录参数，如下：

[mysqld]

innodb_directories="/home/mysql/mysql8/"

重启mysql服务。

CREATE TABLE `ext_table` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `county_code` char(3) DEFAULT NULL,

  `county_name` varchar(10) DEFAULT NULL,

  `level_city_code` char(3) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_county_code` (`county_code`),

  KEY `idx_level_city_code` (`level_city_code`)

)DATA DIRECTORY = '/home/mysql/mysql8/';

Query OK, 0 rows affected (0.23 sec)

2)         使用 CREATE TABLE ... TABLESPACE 语法

create table ... tablespace需要和data directory结合，可以不需要开启File-Per-Table表空间属性，即不需要启动innodb_file_per_table变量（虽然是默认启动），为此需要指定 "innodb_file_per_table" 为表空间名称。

8.0版本开始，外部表的创建，DATA DIRECTORY指定的数据目录外的位置必须在提前定义好

CREATE TABLE `ext_table2` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `county_code` char(3) DEFAULT NULL,

    ->   `county_name` varchar(10) DEFAULT NULL,

    ->   `level_city_code` char(3) DEFAULT NULL,

    ->   PRIMARY KEY (`id`),

    ->   KEY `idx_county_code` (`county_code`),

    ->   KEY `idx_level_city_code` (`level_city_code`)

    -> )TABLESPACE = innodb_file_per_table DATA DIRECTORY = '/home/mysql/mysql57/';

Query OK, 0 rows affected (0.52 sec)

shell> ll /home/mysql/mysql57/wg_db01/

total 256

-rw-r-----. 1 mysql mysql 131072 Aug 22 15:19 ext_table2.ibd

-rw-r-----. 1 mysql mysql 131072 Aug 22 15:01 ext_table.ibd

3)         在外部通用表空间中创建表

通用表空间使用的非datadir新目录，8.0需要出现在innodb_directories来指定，该变量是只读的需要设置重启生效。

MySQL 8.0以上版本，配置外部表目录参数，如下：

[mysqld]

innodb_directories="/home/mysql/mysql8/;/home/mysql/shared_tb"

重启mysql服务。

我们设置一个新的目录/home/mysql/shared_tbs，显示CREATE TABLESPACE并进行测试，默认的ENGINE = InnoDB，也可显示指定必须是ENGINE = InnoDB为子句，如下：

create tablespace test_share_tbs add datafile '/home/mysql/shared_tbs/comm_tbs01.ibd' engine=innodb;

Query OK, 0 rows affected (0.10 sec)

shell> ll /home/mysql/shared_tbs/

total 32

-rw-r-----. 1 mysql mysql 65536 Aug 22 15:28 comm_tbs01.ibd

用通用表空间创建外部表

CREATE TABLE `ext_table3` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `county_code` char(3) DEFAULT NULL,

    ->   `county_name` varchar(10) DEFAULT NULL,

    ->   `level_city_code` char(3) DEFAULT NULL,

    ->   PRIMARY KEY (`id`),

    ->   KEY `idx_county_code` (`county_code`),

    ->   KEY `idx_level_city_code` (`level_city_code`)

    -> )TABLESPACE = test_share_tbs;

Query OK, 0 rows affected (0.42 sec)

shell> ll /home/mysql/shared_tbs/

total 128

-rw-r-----. 1 mysql mysql 131072 Aug 22 15:33 comm_tbs01.ibd

4)         外部表注意事项

主从复制或者MHA、MGR等高可用，如果创建外部表，一定要在所有的实例创建相同的外部表目录，并授权chown mysql:mysql 以便可以正常访问。