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服务器异常终止的情况下,临时表空间将不被移除。
评论