Mysql 创建外部表

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

为什么要创建外部表？

创建外部表,也就是在数据目录之外创建表
原因可能包括空间管理、I/O 优化或将表放置在具有特定性能或容量特征的存储设备上。
创建外部表,有三种方式：

• Using the DATA DIRECTORY Clause
• Using CREATE TABLE ... TABLESPACE Syntax
• Creating a Table in an External General Tablespace

前提条件：
innodb_file_per_table=1

这里验证第一种

1.创建数据目录
mkdir /opt/mysql/data1
chown mysql.mysql /opt/mysql/data1

2.创建外部表
-- data directory
mysql -uroot -proot cym1
SQL> create table t1(id int primary key);
SQL> create table t2(id int primary key) data directory='/opt/mysql/data1';

3.查看表空间文件
[root@mysql]# ls /opt/mysql/data/cym1		-- 默认目录下，保存了t2 表的frm 和 isl 文件
db.opt  t1.frm  t1.ibd  t2.frm  t2.isl
[root@mysql]# ls /opt/mysql/data1/cym1		-- 外部目录只有 ibd 文件
t2.ibd
[root@mysql]# more /opt/mysql/data/cym1/t2.isl  -- isl文件接指向了t2的表空间文件。
/opt/mysql/data1/cym1/t2.ibd

能否改动外部表位置？

mkdir /opt/mysql/data2
chown mysql.mysql /opt/mysql/data2
mysql -uroot -proot cym1
SQL> alter table t2  data directory='/opt/mysql/data2';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1
-- 查看数据文件
[root@mysql]# ls /opt/mysql/data2/
[root@mysql]# ls /opt/mysql/data1/
cym1
[root@mysql]# more /opt/mysql/data/cym1/t2.isl
/opt/mysql/data1/cym1/t2.ibd

以上，改动位置失败，不支持改动。

能否手工修改文件isl文件？

mkdir /opt/mysql/data2/cym1
chown mysql.mysql /opt/mysql/data2/cym1
vi /opt/mysql/data/cym1/t2.isl
/opt/mysql/data1/cym1/t2.ibd	->	/opt/mysql/data2/cym1/t2.ibd
mv /opt/mysql/data1/cym1/t2.ibd /opt/mysql/data2/cym1/t2.ibd
mysql -uroot -proot cym1
SQL> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

SQL> insert into t2 select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SQL> select * from t2;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

以上，手工可以修改表空间文件的位置，不需要重启数据库，可立刻生效。

修改表空间文件位置是否影响数据库启动关闭？

SQL> SHUTDOWN;
Query OK, 0 rows affected (0.01 sec)
[root@mysql]# mysqld &
[1] 28930
[root@mysql]# mysql -uroot -proot cym1
SQL> select * from t2;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

数据库正常启动关闭。再确认下表空间文件位置：
[root@mysql]# ls /opt/mysql/data1/cym1/
[root@mysql]# ls /opt/mysql/data2/cym1/t2.ibd
/opt/mysql/data2/cym1/t2.ibd
[root@mysql]# more /opt/mysql/data/cym1/t2.isl
/opt/mysql/data2/cym1/t2.ibd

数据文件位置的确变了。

生产环境中可能需要先把表设为只读才可以修改位置，请测试后操作，修改后不需要重启数据库即可生效。

flush table t2 with read lock;
unlock tables;