mysql中,会涉及到各种表空间的概念,虽然,很多方面这些概念和Oracle有相似性,但也有很多不同的地方,初学者很容易被这些概念弄的晕头转向,从而,混淆这些概念的区别和理解,下面,就简要介绍和说明一下这些表空间的概念。

独立表空间与共享表空间

在 MySQL InnoDB 存储引擎中,存在独立表空间(Individual Tablespaces)和共享表空间(shared Tablespace)这两种类型的表空间。

独立表空间

每个表都有独立的表空间,用于存储该表的数据和索引,每个表空间对应一个独立的 ibd 文件。可以对单个表进行备份,还原和优化,还可以在不同的磁盘上存储,管理起来比较灵活。

共享表空间

所有的表共用一个表空间,索引和数据都存放到共享的 ibd 文件里,无法对单表进行备份,但有助于节省磁盘空间。

系统表空间(System Tablespace)

innodb系统表空间包含innodb数据字典(innodb相关对象的元数据),同时,双写缓冲(doublewrite buffer)、改变缓冲(change buffer)和undo日志(undo logs)等也存储于系统表空间中。

此外,系统表空间也包含用户在该表空间创建的表和索引等数据。由于系统表空间可以存储多张表,因此,其为一个共享表空间。系统表空间由一个或多个数据文件组成,默认情况下,其包含一个叫ibdata1的系统数据文件,位于mysql数据目录(datadir)下。属于共享表空间,存储多张表的数据。

系统表空间数据文件的位置、大小和数目由innodb_data_home_dir和innodb_data_file_path启动选项控制。针对不同场景,具体举例如下:

1)参数innodb_data_home_dir未配置,只配置参数

innodb_data_file_path:innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend

--注:系统表空间包含ibdata1和ibdata2两个数据文件,二者均位于datadir目录下。

2)参数innodb_data_home_dir和innodb_data_file_path均进行了配置:

innodb_data_home_dir = /data

innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend

--注:系统表空间包含ibdata1和ibdata2两个数据文件,二者均位于/data目录下。

3)参数innodb_data_home_dir位置为空串,只配置参数innodb_data_file_path:

innodb_data_home_dir =

innodb_data_file_path=/d1/ibdata1:1024M;/d2/ibdata2:1024M:autoextend

--注:系统表空间包含ibdata1和ibdata2两个数据文件,ibdata1位于/d1目录下,ibdata2位于/d2目录下。

表文件表空间(File-Per-Table Tablespaces)

表文件表空间是一个独立表空间,该表创建于自己的数据文件中,而非创建于系统表空间中。当innodb_file_per_table选项开启时,表将被创建于表文件表空间中。否则,innodb将被创建于系统表空间中。

每个表文件表空间由一个.ibd数据文件代表,该文件默认被创建于相应数据库目录中。表文件表空间支持动态(DYNAMIC)和压缩(commpressed)行格式。

通用表空间(General Tablespaces)

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间,其可以容纳多张表,且其支持所有的行格式。

CREATE TABLESPACE tablespace_name [ADD DATAFILE ‘file_name’] [FILE_BLOCK_SIZE = [value] [ENGINE [=] engine_name]

#指定表空间文件名

create tablespace 'gt1' add datafile 'gt1.ibd' engine=InnoDB

如果不指定add datafile 'gt1.ibd',则自动创建一个以 UUID 为文件名的表空间数据文件。

如何在数据目录之外创建通用表空间

mysql> CREATE TABLESPACE user_defined_general_tablespace
    -> ADD DATAFILE '/var/lib/var/lib/mysql_user_defined/user_defined_general_tablespace.ibd'
    -> Engine=InnoDB;
ERROR 3121 (HY000): The DATAFILE location must be in a known directory.

错误 3121 (HY000):数据文件位置必须位于已知目录中。 提示 MySQL 无法在指定目录中创建表空间,因为该目录未配置为数据文件的有效位置。

要解决此错误,请按照下列步骤操作:使用 SHOW VARIABLES LIKE 'innodb_directories' 检查配置的目录;如果 /var/lib/mysql_user_define未列出,请继续添加该目录。

root@mysql8:/etc/mysql/mysql.conf.d# grep -i innodb_directories mysqld.cnf
innodb_directories=/var/lib/mysql_user_defined
root@mysql8:/etc/mysql/mysql.conf.d# service mysql restart
root@mysql8:/etc/mysql/mysql.conf.d

mysql> CREATE TABLESPACE user_defined_general_tablespace
    -> ADD DATAFILE '/var/lib/mysql_user_defined/user_defined_general_tablespace.ibd'
mysql> CREATE TABLE my_table (
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(50)
    -> ) TABLESPACE = my_general_tablespace
    ->   ENCRYPTION='Y';

将表迁移到通用表空间

如果您有现有表并希望将它们移动到通用表空间,则可以使用 ALTER TABLE 语句。例如:

mysql> show create table authors\G;
*************************** 1. row ***************************
       Table: authors
Create Table: CREATE TABLE `authors` (
  `id` int DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE authors
    -> TABLESPACE = my_general_tablespace;
ERROR 3825 (HY000): Request to create 'unencrypted' table while using an 'encrypted' tablespace.

mysql> ALTER TABLE authors ENCRYPTION='Y';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE authors
    -> TABLESPACE = my_general_tablespace;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

要将表从通用表空间转移到独立表空间,请指定 “innodb_file_per_table”作为目标表空间名称。

mysql> ALTER TABLE authors
    -> TABLESPACE = innodb_file_per_table ENCRYPTION = 'Y';

删除通用表空间先需要先把表空间的表删除,通过下方的语句进行查询

SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a, INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE'ts1';

使⽤通⽤表空间时要注意什么?

  • 使⽤ TRUNCATE 或 DROP 语句截断或删除表时,通⽤表空间的空闲容量并不会释放,并且只能⽤于新的InnoDB表;

  • 通⽤表空间不属于任何数据库,使⽤ DROP DATABASE 操作数据库和属于该数据库所有的表时,并不会删除通⽤表空间。

  • tablespace_name 表空间名区分⼤⼩写

undo表空间(undo tablespace)

undo表空间由一个或多个包含undo日志的文件组成。innodb_undo_tablespace配置选项控制undo表空间的数目。

undo表空间创建于innodb_undo_directory配置选项确定的位置,该选项典型被用于将undo日志放于不同的

存储设备上。

如果该选项没有确定任何路径,undo表空间则被默认创建于mysql数据目录(datadir)下。

临时表空间(Temporary Tablespace)

用户创建的临时表和磁盘内部临时表创建于共享临时表空间中。

innodb_temp_data_file选项确定临时表空间数据文件的相对路径、名字、大小和属性等。

如果该选项未确定任何值,默认情况下,系统将在innodb_data_home_dir确定的目录下创建一个叫ibtmp1的自动扩展的数据文件,该文件将稍大于12m。

mysql服务器正常关闭或异常终止初始化时,临时表空间将被移除,并且,mysql服务器每次启动时会被重新创建。

当临时表空间被创建时,其被赋予一个动态产生的空间ID(space ID)。如果不能创建临时表空间,mysql服务器启动将被拒绝。mysql服务器异常终止的情况下,临时表空间将不被移除。