表空间

普通建表语句

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

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

mysql> show create table table1;
+--------+-------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                              |
+--------+-------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看数据目录内容

•auto.cnf文件是MySQL第一次启动时生成的uuid值
•ib_buffer_pool文件是MySQL启动时加载缓存数据到内存区域
•ibdata1文件时存放系统表空间数据，在5.7版本及之前undo log也存放与该文件
•ib_logfile0 ib_logfile1文件是MySQL的重做日志存放文件，默认两个，循环使用
•ibtmp1是存放临时表数据的，比如join操作结束后就会删除临时表
•mysql为MySQL用户信息与权限信息等、performance_schema为MySQL性能库 、sys为MySQL系统信息库
•test库为用户自行创建的库

[root@db03 ~]# ls  /usr/local/mysql/data
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql_upgrade_info  performance_schema  sys  test

指定表空间创建表

mysql> create tablespace general add datafile 'general.ibd';
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| table1         |
+----------------+
1 row in set (0.00 sec)

mysql> create table table2(b int) tablespace=general;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table table2;
+--------+-----------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                   |
+--------+-----------------------------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
  `b` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `general` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

查看数据目录内容

•InnoDB存储引擎：frm文件是表结构元数据文件
•InnoDB存储引擎：ibd文件是存储数据的文件（独立表空间模式），如果是共享表空间模式将存储在ibdata文件

[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf  general.ibd  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql_upgrade_info   
performance_schema  sys  test

[root@db03 ~]# ls /usr/local/mysql/data/test/
db.opt  table1.frm  table1.ibd  table2.frm

指定外部表空间创建表

[root@db03 ~]# mkdir /tablespace
[root@db03 ~]# chown -R mysql.mysql /tablespace

mysql> create tablespace external add datafile '/tablespace/external.ibd';
Query OK, 0 rows affected (0.01 sec)

mysql> create table table3(c int) tablespace=external;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table table3;
+--------+-------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                   |
+--------+-------------------------------------------------------------------------------------------------------------------+
| table3 | CREATE TABLE `table3` (
  `c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `external` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看数据目录内容

•isl文件是一个链接文件，里面记录表空间ibd文件存放的路径

[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf  external.isl  general.ibd  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql_upgrade_info  
performance_schema  sys  test

[root@db03 ~]# ls /usr/local/mysql/data/test/
db.opt  table1.frm  table1.ibd  table2.frm  table3.frm

[root@db03 ~]# ls /tablespace/
external.ibd

[root@db03 ~]# cat /usr/local/mysql/data/external.isl
/tablespace/external.ibd

指定表空间目录创建表

mysql> create table table4(d int) data directory='/tablespace';
Query OK, 0 rows affected (0.01 sec)

mysql> show create table table4;
+--------+----------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                      |
+--------+---------------------------------------------------------------------------------------------------------------+
| table4 | CREATE TABLE `table4` (
  `d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/tablespace/' |
+--------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看数据目录内容

[root@db03 ~]# ls /usr/local/mysql/data/test/
db.opt  table1.frm  table1.ibd  table2.frm  table3.frm  table4.frm  table4.isl

[root@db03 ~]# ls /tablespace/test/
table4.ibd

[root@db03 ~]# cat /usr/local/mysql/data/test/table4.isl
/tablespace/test/table4.ibd

使用系统表空间创建一张表

mysql> create table table5(e int) tablespace=innodb_system;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table table5;
+--------+--------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                               |
+--------+--------------------------------------------------------------------------------------------------------------------+
| table5 | CREATE TABLE `table5` (
  `e` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看数据目录内容

[root@db03 ~]# ls /usr/local/mysql/data/test
db.opt  table1.frm  table1.ibd  table2.frm  table3.frm  table4.frm  table4.isl  table5.frm

[root@db03 ~]# ls /tablespace/test
table4.ibd

分别删除库和表空间并且查看原始数据

•最终效果：表空间存放路径可以自由设置，某一张大数据量的表也可以指定存放某个表空间或者某一个挂载到系统目录中的大磁盘存储。

mysql> drop database test;
Query OK, 5 rows affected (0.01 sec)

[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf  external.isl  general.ibd  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql_upgrade_info  
performance_schema  sys

[root@db03 ~]# ls /tablespace/test/

[root@db03 ~]# ls /tablespace
external.ibd  test

mysql> drop tablespace general;
Query OK, 0 rows affected (0.00 sec)

mysql> drop tablespace external;
Query OK, 0 rows affected (0.00 sec)

[root@db03 ~]# ls /tablespace
test

[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql_upgrade_info  performance_schema  sys

[root@db03 ~]# rm -rf /tablespace

来源：https://dbawsp.com/1853.html