CREATE TABLESPACE语法

最新推荐文章于 2025-09-19 14:00:07 发布

转载最新推荐文章于 2025-09-19 14:00:07 发布 · 8k 阅读

dataBase 专栏收录该内容

89 篇文章

订阅专栏

本文介绍如何使用MySQL创建InnoDB和NDBCluster表空间，包括通用表空间和NDB集群磁盘数据表空间的创建方法。文章详细解释了各种选项的作用，如数据文件的添加、表空间类型的选择等。

创建表空间 tablespace_name

  InnoDB和NDB： 
    添加数据文件'file_name'

  仅限InnoDB：
    [FILE_BLOCK_SIZE =值]

  仅限NDB： 
    使用LOGFILE GROUPlogfile_group
    [EXTENT_SIZE [=]extent_size]
    [INITIAL_SIZE [=] initial_size]
    [AUTOEXTEND_SIZE [=] autoextend_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [等待]
    [评论[=]' string']

  InnoDB和NDB： 
    [ENGINE [=]engine_name]

该语句用于创建表空间。精确的语法和语义取决于使用的存储引擎。在标准的MySQL 5.7版本中，这总是一个InnoDB表空间。MySQL NDB Cluster 7.5还支持使用NDB存储引擎的表空间 InnoDB。

InnoDB的注意事项

使用InnoDB创建的表空间 CREATE TABLESPACE称为通用表空间。这是一个共享的表空间，类似于系统表空间。它可以容纳多个表格，并支持所有表格行格式。通用表空间可以在相对于或独立于MySQL数据目录的位置创建。

创建InnoDB常规表空间后，可以使用或向表空间添加表。 CREATE TABLE tbl_name ... TABLESPACE [=]tablespace_nameALTER TABLE tbl_name TABLESPACE [=] tablespace_name

有关更多信息，请参见第14.7.9节“InnoDB常规表空间”。

NDB群集的注意事项

该语句用于创建一个表空间，该表空间可以包含一个或多个数据文件，为NDB集群磁盘数据表提供存储空间（请参见第21.5.13节“NDB集群磁盘数据表”）。使用此语句创建一个数据文件并将其添加到表空间。其他数据文件可以通过使用ALTER TABLESPACE 语句添加到表空间中（请参见第13.1.9节“ALTER TABLESPACE语法”）。

注意

所有NDB群集磁盘数据对象共享相同的名称空间。这意味着每个磁盘数据对象必须是唯一命名的（而不仅仅是给定类型的每个磁盘数据对象）。例如，您不能拥有同名的表空间和日志文件组，或者具有相同名称的表空间和数据文件。

一个或多个UNDO日志文件的日志文件组必须分配给要使用USE LOGFILE GROUP子句创建的表空间。logfile_group必须是使用创建的现有日志文件组CREATE LOGFILE GROUP（请参见第13.1.15节“CREATE LOGFILE GROUP语法”）。多个表空间可以使用相同的日志文件组进行 UNDO日志记录。

在设置EXTENT_SIZE或时 INITIAL_SIZE，您可以选择使用与英文中使用的数字类似的单字母缩写的数量级my.cnf。通常，这是字母之一M（对于兆字节）或 G（对于千兆字节）。

INITIAL_SIZE并EXTENT_SIZE 受四舍五入的影响如下：

EXTENT_SIZE 被四舍五入到32K的整数倍。
INITIAL_SIZE是圆形的向下到32K最接近的整倍数; 这个结果被四舍五入到最接近的整数倍EXTENT_SIZE（在任何四舍五入之后）。

刚刚描述的舍入是显式完成的，并且当执行任何这样的舍入时，MySQL服务器发出警告。NDB内核也使用舍入值来计算 INFORMATION_SCHEMA.FILES列值和其他用途。但是，为避免意外的结果，我们建议您始终使用32K的整数倍来指定这些选项。

何时CREATE TABLESPACE与 ENGINE [=] NDB每个群集数据节点上创建一个表空间和关联的数据文件。您可以验证数据文件是否已创建，并通过查询INFORMATION_SCHEMA.FILES表获取有关它们的信息。（请参阅本节后面的示例。）

（请参见第24.8节“INFORMATION_SCHEMA文件表”）。

选项

ADD DATAFILE：定义表空间数据文件的名称; 这个选项总是需要的。一个 InnoDB表空间仅支持单个数据文件，其名称必须包括.ibd 扩展名。NDB簇表空间支持多个数据文件，这些文件可以具有任何合法的文件名; 通过使用ALTER TABLESPACE 语句创建后，可以将更多数据文件添加到NDB Cluster表空间中。

注意

ALTER TABLESPACE不受支持 InnoDB。

要将数据文件放在MySQL数据目录（datadir）以外的位置，请包含绝对目录路径或相对于MySQL数据目录的路径。如果不指定路径，则表空间将在MySQL数据目录中创建。当在MySQL数据目录之外创建表空间时，将在MySQL数据目录中创建isl文件InnoDB。
为避免与隐式创建的每个表文件表空间冲突，不支持在MySQL数据目录下的子目录中创建常规表空间。另外，在MySQL数据目录之外创建一个通用表空间时，目录必须在创建表空间之前存在。
的file_name，包括路径（可选），必须用单或双引号引用。文件名（不包括文件的任何 “ .ibd ”扩展名InnoDB ）和目录名必须至少有一个字节的长度。零长度的文件名和目录名称不受支持。
FILE_BLOCK_SIZE：该选项 - 特定于InnoDB并且被忽略 - NDB定义表空间数据文件的块大小。如果您不指定此选项，则 FILE_BLOCK_SIZE默认为 innodb_page_size。 FILE_BLOCK_SIZE当您打算使用表空间来存储压缩InnoDB表（ROW_FORMAT=COMPRESSED）时需要。
如果FILE_BLOCK_SIZE等于 innodb_page_size，表空间可以含有具有未压缩的行格式仅表（COMPACT，REDUNDANT，或DYNAMIC）。表使用的物理页面大小COMPRESSED与未压缩的表的物理页大小不同; 这意味着压缩表和未压缩表不能共存于同一个表空间中。
对于包含压缩表的常规表空间， FILE_BLOCK_SIZE必须指定，并且该FILE_BLOCK_SIZE值必须是与该值相关的有效压缩页面大小 innodb_page_size。另外，压缩表（KEY_BLOCK_SIZE）的物理页面大小必须等于FILE_BLOCK_SIZE/1024。例如，如果 innodb_page_size=16K和 FILE_BLOCK_SIZE=8K，该 KEY_BLOCK_SIZE表必须为8欲了解更多信息，请参见第14.7.9，“InnoDB的总表空间”。
USE LOGFILE GROUP：必需的 NDB，这是以前使用的日志文件组的名称CREATE LOGFILE GROUP。不支持InnoDB，因为出现错误而失败。
EXTENT_SIZE：这个选项是特定于NDB的，InnoDB不支持它，它在失败时会出错。EXTENT_SIZE设置属于表空间的任何文件使用的区的大小（以字节为单位）。默认值是1M。最小尺寸为32K，理论最大值为2G，但实际最大尺寸取决于许多因素。在大多数情况下，更改盘区大小并不会对性能产生任何可衡量的影响，并且除了最不寻常的情况外，建议使用默认值。
的程度是磁盘空间分配的单位。一个范围充满了在使用另一个范围之前该范围可以包含的尽可能多的数据。理论上，每个数据文件最多可以使用65,535（64K）个区段; 但是，建议的最大值是32,768（32K）。单个数据文件的建议最大大小为32G，即每个区域32K扩展区大小×1 MB。另外，一旦将分区分配给了给定的分区，它就不能用于存储来自不同分区的数据; 一个盘区不能存储来自多个分区的数据。这意味着，例如，具有单个数据文件的表空间INITIAL_SIZE（在下面的项目中描述）是256 MB， EXTENT_SIZE 是128M只有两个扩展盘区，因此可用于存储来自至多两个不同磁盘数据表分区的数据。
您可以通过查询INFORMATION_SCHEMA.FILES表来查看给定数据文件中有多少数据块空闲空间，从而得出文件中剩余多少空间的估计。有关进一步的讨论和示例，请参见第24.8节“INFORMATION_SCHEMA文件表”。
INITIAL_SIZE：该选项特定于 NDB并且不受其支持 InnoDB，并且在出现错误时失败。
该INITIAL_SIZE参数设置特定使用的数据文件的总大小（以字节为单位） ADD DATATFILE。一旦创建了这个文件，它的大小就不能改变; 但是，您可以使用添加更多数据文件到表空间 ALTER TABLESPACE ... ADD DATAFILE。
INITIAL_SIZE是可选的; 其默认值是134217728（128 MB）。
在32位系统上，支持的最大值为 INITIAL_SIZE4294967296（4 GB）。
AUTOEXTEND_SIZE：目前被MySQL忽略; 保留以备将来使用。无论使用哪种存储引擎，在任何版本的MySQL 5.7或MySQL NDB Cluster 7.5中都不起作用。
MAX_SIZE：目前被MySQL忽略; 保留以备将来使用。无论使用哪种存储引擎，在任何版本的MySQL 5.7或MySQL NDB Cluster 7.5中都不起作用。
NODEGROUP：目前被MySQL忽略; 保留以备将来使用。无论使用哪种存储引擎，在任何版本的MySQL 5.7或MySQL NDB Cluster 7.5中都不起作用。
WAIT：目前被MySQL忽略; 保留以备将来使用。无论使用哪种存储引擎，在任何版本的MySQL 5.7或MySQL NDB Cluster 7.5中都不起作用。
COMMENT：目前被MySQL忽略; 保留以备将来使用。无论使用哪种存储引擎，在任何版本的MySQL 5.7或MySQL NDB Cluster 7.5中都不起作用。
ENGINE：定义使用表空间的存储引擎，其中 engine_name是存储引擎的名称。目前，只有InnoDB 标准MySQL 5.7版本支持存储引擎。MySQL的NDB簇7.5支持 NDB和InnoDB 表空间。如果未指定该选项，则default_storage_engine使用系统变量的值 ENGINE。

笔记

有关MySQL表空间命名的规则，请参见第9.2节“模式对象名称”。除了这些规则之外，不允许使用斜杠字符（“ / ”），也不能使用以innodb_前缀开头的名称，因为此前缀保留供系统使用。
表空间不支持临时表。
该TABLESPACE选项可以与被用于 CREATE TABLE或 ALTER TABLE要分配 InnoDB表分区或子分区的一般表空间，一个单独的文件，每个表的表空间，或系统表空间。TABLESPACEMySQL 5.7中添加了对表分区和子分区的选项支持。所有分区必须属于同一个存储引擎。有关更多信息，请参见第14.7.9节“InnoDB常规表空间”。
innodb_file_per_table， innodb_file_format和 innodb_file_format_max 设置对CREATE TABLESPACE操作没有影响。 innodb_file_per_table不需要启用。不管文件格式设置如何，常规表空间都支持所有表格行格式。同样，CREATE TABLE ... TABLESPACE不管文件格式设置如何，通用表空间都支持添加任何行格式的表格。
innodb_strict_mode不适用于一般的表空间。表空间管理规则严格独立执行 innodb_strict_mode。如果CREATE TABLESPACE参数不正确或不兼容，无论innodb_strict_mode设置如何，操作都会失败。当使用CREATE TABLE ... TABLESPACEor 将表添加到常规表空间时 ALTER TABLE ... TABLESPACE，innodb_strict_mode将忽略该语句，但语句的计算方式与innodb_strict_mode启用该语句时相同。
使用DROP TABLESPACE删除表空间。在删除表空间DROP TABLE之前，必须从表空间中删除所有表。在删除NDB集群表空间之前，您还必须使用一个或多个ALTER TABLESPACE ... DROP DATATFILE语句删除其所有数据文件。请参见第21.5.13.1节“NDB群集磁盘数据对象”。
InnoDB添加到InnoDB常规表空间的表的所有部分驻留在常规表空间中，包括索引和 BLOB页面。
对于NDB分配给表空间的表，只有未索引的列才存储在磁盘上，并且实际使用表空间数据文件。所有NDB表的索引和索引列始终保留在内存中。
与系统表空间类似，截断或删除存储在通用表空间中的表可在通用表空间.ibd数据文件的内部创建空闲空间，该数据文件只能用于新InnoDB数据。空间不会释放回操作系统，因为它是用于每个表文件的表空间。
常规表空间不与任何数据库或模式关联。
ALTER TABLE ... DISCARD TABLESPACE并且 ALTER TABLE ...IMPORT TABLESPACE不支持属于常规表空间的表。
服务器使用表空间级别元数据锁定引用常规表空间的DDL。相比之下，服务器使用表级别元数据锁定引用文件每表的表空间的DDL。
生成的或现有的表空间不能更改为常规表空间。
由于添加了新的表标志，只能在MySQL 5.7.6或更高版本中打开存储在常规表空间中的表。
常规表空间名称和每个文件表空间名称之间没有冲突。一般表空间名称中不允许出现在每个表文件表空间名称中的“ /”字符。

InnoDB示例

这个例子演示了如何创建一个通用的表空间并添加三个不同行格式的未压缩表。

mysql> CREATE TABLESPACE `ts1`
    - >      ADD DATAFILE 'ts1.ibd'
    - >     ENGINE=INNODB;
查询OK，0行受影响（0.01秒）

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY)
    - >      TABLESPACE ts1
    - >     ROW_FORMAT=REDUNDANT;
查询OK，0行受影响（0.00秒）

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY)
    - >      TABLESPACE ts1
    - >     ROW_FORMAT=COMPACT;
查询OK，0行受影响（0.00秒）

mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY)
    - >      TABLESPACE ts1
    - >     ROW_FORMAT=DYNAMIC;
查询OK，0行受影响（0.00秒）

此示例演示如何创建常规表空间并添加压缩表。该示例假设默认 innodb_page_size值为16K。在FILE_BLOCK_SIZE8192要求压缩表有KEY_BLOCK_SIZE8个。

mysql> CREATE TABLESPACE`ts2`
    - > ADD DATAFILE'ts2.ibd'
    - > FILE_BLOCK_SIZE = 8192
    - > ENGINE = INNODB;
查询OK，0行受影响（0.01秒）

mysql> CREATE TABLE t4（c1 INT PRIMARY KEY）
    - > TABLESPACE ts2
    - > ROW_FORMAT = COMPRESSED
    - > KEY_BLOCK_SIZE = 8;
查询OK，0行受影响（0.00秒）

NDB示例

假设您希望创建一个名为myts使用数据文件命名的NDB群集磁盘数据表空间mydata-1.dat。一个NDB 表空间总是需要使用由一个或多个撤消日志文件，日志文件组。对于这个例子，我们首先创建一个名为的日志文件组mylg，其中包含一个名为的撤消长文件myundo-1.dat，使用下面显示的 CREATE LOGFILE GROUP语句：

mysql> CREATE LOGFILE GROUP myg1
    - >      ADD UNDOFILE 'myundo-1.dat'
    - >     ENGINE=NDB;
查询OK，0行受影响（3.29秒）

现在您可以使用以下语句创建之前描述的表空间：

mysql> CREATE TABLESPACE myts
    - >      ADD DATAFILE 'mydata-1.dat'
    - >      USE LOGFILE GROUP mylg
    - >     ENGINE=NDB;
查询OK，0行受影响（2.98秒）

您现在可以使用CREATE TABLE带有TABLESPACE和STORAGE DISK 选项的语句来创建磁盘数据表，如下所示：

mysql> CREATE TABLE mytable (
    - >      id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    - >      lname VARCHAR(50) NOT NULL,
    - >      fname VARCHAR(50) NOT NULL,
    - >      dob DATE NOT NULL,
    - >      joined DATE NOT NULL,
    - >      INDEX(last_name, first_name)
    - > )
    - >      TABLESPACE myts STORAGE DISK
    - >     ENGINE=NDB;
查询OK，0行受影响（1.41秒）

需要注意的是只有这一点非常重要dob和 joined列从mytable 实际存储在磁盘上，由于事实 id，lname和 fname列所有索引。

如前所述，当CREATE TABLESPACE 与ENGINE [=] NDB每个NDB群集数据节点一起使用时，会创建一个表空间和关联的数据文件。您可以通过查询INFORMATION_SCHEMA.FILES表来验证数据文件是否已创建并获取有关它们的信息，如下所示：

mysql> SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
    - >      FROM INFORMATION_SCHEMA.FILES
    - >     WHERE TABLESPACE_NAME = 'myts';

+ -------------- + ------------ + -------------------- + -------- + ---------------- +
| file_name | file_type | logfile_group_name | 状态| 额外|
+ -------------- + ------------ + -------------------- + -------- + ---------------- +
| mydata-1.dat | DATAFILE | mylg | 正常| CLUSTER_NODE = 5 |
| mydata-1.dat | DATAFILE | mylg | 正常| CLUSTER_NODE = 6 |
| NULL | TABLESPACE | mylg | 正常| NULL |
+ -------------- + ------------ + -------------------- + -------- + ---------------- +
3行（0.01秒）