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 DIRECTORYClause - Using
CREATE TABLE ... TABLESPACESyntax - 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;