A general tablespace is a shared InnoDB
tablespace that is created using CREATE
TABLESPACE
syntax. General tablespace capabilities and
features are described under the following topics in this section:
General tablespaces provide the following capabilities:
Similar to the system tablespace, general tablespaces are shared tablespaces capable of storing data for multiple tables.
General tablespaces have a potential memory advantage over file-per-table tablespaces. The server keeps tablespace metadata in memory for the lifetime of a tablespace. Multiple tables in fewer general tablespaces consume less memory for tablespace metadata than the same number of tables in separate file-per-table tablespaces.
General tablespace data files can be placed in a directory relative to or independent of the MySQL data directory, which provides you with many of the data file and storage management capabilities of file-per-table tablespaces. As with file-per-table tablespaces, the ability to place data files outside of the MySQL data directory allows you to manage performance of critical tables separately, setup RAID or DRBD for specific tables, or bind tables to particular disks, for example.
General tablespaces support all table row formats and associated features.
The
TABLESPACE
option can be used withCREATE TABLE
to create tables in a general tablespaces, file-per-table tablespace, or in the system tablespace.The
TABLESPACE
option can be used withALTER TABLE
to move tables between general tablespaces, file-per-table tablespaces, and the system tablespace.
General tablespaces are created using
CREATE TABLESPACE
syntax.
CREATE TABLESPACE tablespace_name
[ADD DATAFILE 'file_name']
[FILE_BLOCK_SIZE = value]
[ENGINE [=] engine_name]
A general tablespace can be created in the data directory or
outside of it. To avoid conflicts with implicitly created
file-per-table tablespaces, creating a general tablespace in a
subdirectory under the data directory is not supported. When
creating a general tablespace outside of the data directory, the
directory must exist and must be known to
InnoDB
prior to creating the tablespace. To
make an unknown directory known to InnoDB
,
add the directory to the
innodb_directories
argument
value. innodb_directories
is a
read-only startup option. Configuring it requires restarting the
server.
Examples:
Creating a general tablespace in the data directory:
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
or
mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;
The ADD DATAFILE
clause is optional as of
MySQL 8.0.14 and required before that. If the ADD
DATAFILE
clause is not specified when creating a
tablespace, a tablespace data file with a unique file name is
created implicitly. The unique file name is a 128 bit UUID
formatted into five groups of hexadecimal numbers separated by
dashes
(aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
).
General tablespace data files include an
.ibd
file extension. In a replication
environment, the data file name created on the source is not the
same as the data file name created on the replica.
Creating a general tablespace in a directory outside of the data directory:
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;
You can specify a path that is relative to the data directory as
long as the tablespace directory is not under the data
directory. In this example, the
my_tablespace
directory is at the same
level as the data directory:
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;
The ENGINE = InnoDB
clause must be defined
as part of the CREATE
TABLESPACE
statement, or InnoDB
must be defined as the default storage engine
(default_storage_engine=InnoDB
).
After creating a general tablespace,
CREATE TABLE
or
tbl_name
... TABLESPACE [=]
tablespace_name
ALTER TABLE
statements
can be used to add tables to the tablespace, as shown in the
following examples:
tbl_name
TABLESPACE [=]
tablespace_name
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
mysql> ALTER TABLE t2 TABLESPACE ts1;
Support for adding table partitions to shared tablespaces was
deprecated in MySQL 5.7.24 and removed in MySQL 8.0.13. Shared
tablespaces include the InnoDB
system
tablespace and general tablespaces.
For detailed syntax information, see CREATE
TABLE
and ALTER TABLE
.
General tablespaces support all table row formats
(REDUNDANT
, COMPACT
,
DYNAMIC
, COMPRESSED
) with
the caveat that compressed and uncompressed tables cannot
coexist in the same general tablespace due to different physical
page sizes.
For a general tablespace to contain compressed tables
(ROW_FORMAT=COMPRESSED
), the
FILE_BLOCK_SIZE
option must be specified, and
the FILE_BLOCK_SIZE
value must be a valid
compressed page size in relation to the
innodb_page_size
value. Also,
the physical page size of the compressed table
(KEY_BLOCK_SIZE
) must be equal to
FILE_BLOCK_SIZE/1024
. For example, if
innodb_page_size=16KB
and
FILE_BLOCK_SIZE=8K
, the
KEY_BLOCK_SIZE
of the table must be 8.
The following table shows permitted
innodb_page_size
,
FILE_BLOCK_SIZE
, and
KEY_BLOCK_SIZE
combinations.
FILE_BLOCK_SIZE
values may also be specified
in bytes. To determine a valid KEY_BLOCK_SIZE
value for a given FILE_BLOCK_SIZE
, divide the
FILE_BLOCK_SIZE
value by 1024. Table
compression is not support for 32K and 64K
InnoDB
page sizes. For more information about
KEY_BLOCK_SIZE
, see
CREATE TABLE
, and
Section 17.9.1.2, “Creating Compressed Tables”.
Table 17.3 Permitted Page Size, FILE_BLOCK_SIZE, and KEY_BLOCK_SIZE Combinations for Compressed Tables
InnoDB Page Size (innodb_page_size) | Permitted FILE_BLOCK_SIZE Value | Permitted KEY_BLOCK_SIZE Value |
---|---|---|
64KB | 64K (65536) | Compression is not supported |
32KB | 32K (32768) | Compression is not supported |
16KB | 16K (16384) | None. If innodb_page_size is equal to
FILE_BLOCK_SIZE , the tablespace cannot
contain a compressed table. |
16KB | 8K (8192) | 8 |
16KB | 4K (4096) | 4 |
16KB | 2K (2048) | 2 |
16KB | 1K (1024) | 1 |
8KB | 8K (8192) | None. If innodb_page_size is equal to
FILE_BLOCK_SIZE , the tablespace cannot
contain a compressed table. |
8KB | 4K (4096) | 4 |
8KB | 2K (2048) | 2 |
8KB | 1K (1024) | 1 |
4KB | 4K (4096) | None. If innodb_page_size is equal to
FILE_BLOCK_SIZE , the tablespace cannot
contain a compressed table. |
4KB | 2K (2048) | 2 |
4KB | 1K (1024) | 1 |
This example demonstrates creating a general tablespace and
adding a compressed table. The example assumes a default
innodb_page_size
of 16KB. The
FILE_BLOCK_SIZE
of 8192 requires that the
compressed table have a KEY_BLOCK_SIZE
of 8.
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
If you do not specify FILE_BLOCK_SIZE
when
creating a general tablespace,
FILE_BLOCK_SIZE
defaults to
innodb_page_size
. When
FILE_BLOCK_SIZE
is equal to
innodb_page_size
, the
tablespace may only contain tables with an uncompressed row
format (COMPACT
,
REDUNDANT
, and DYNAMIC
row
formats).
ALTER TABLE
with the
TABLESPACE
option can be used to move a table
to an existing general tablespace, to a new file-per-table
tablespace, or to the system tablespace.
Support for placing table partitions in shared tablespaces was
deprecated in MySQL 5.7.24 and removed MySQL 8.0.13. Shared
tablespaces include the InnoDB
system
tablespace and general tablespaces.
To move a table from a file-per-table tablespace or from the
system tablespace to a general tablespace, specify the name of
the general tablespace. The general tablespace must exist. See
ALTER TABLESPACE
for more
information.
ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;
To move a table from a general tablespace or file-per-table
tablespace to the system tablespace, specify
innodb_system
as the tablespace name.
ALTER TABLE tbl_name TABLESPACE [=] innodb_system;
To move a table from the system tablespace or a general
tablespace to a file-per-table tablespace, specify
innodb_file_per_table
as the tablespace name.
ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;
ALTER TABLE ... TABLESPACE
operations cause a
full table rebuild, even if the TABLESPACE
attribute has not changed from its previous value.
ALTER TABLE ... TABLESPACE
syntax does not
support moving a table from a temporary tablespace to a
persistent tablespace.
The DATA DIRECTORY
clause is permitted with
CREATE TABLE ...
TABLESPACE=innodb_file_per_table
but is otherwise not
supported for use in combination with the
TABLESPACE
option. As of MySQL 8.0.21, the
directory specified in a DATA DIRECTORY
clause must be known to InnoDB
. For more
information, see
Using the DATA DIRECTORY Clause.
Restrictions apply when moving tables from encrypted tablespaces. See Encryption Limitations.
Renaming a general tablespace is supported using
ALTER
TABLESPACE ... RENAME TO
syntax.
ALTER TABLESPACE s1 RENAME TO s2;
The CREATE TABLESPACE
privilege
is required to rename a general tablespace.
RENAME TO
operations are implicitly performed
in autocommit
mode regardless
of the autocommit
setting.
A RENAME TO
operation cannot be performed
while LOCK TABLES
or
FLUSH TABLES WITH READ
LOCK
is in effect for tables that reside in the
tablespace.
Exclusive metadata locks are taken on tables within a general tablespace while the tablespace is renamed, which prevents concurrent DDL. Concurrent DML is supported.
The DROP TABLESPACE
statement is
used to drop an InnoDB
general tablespace.
All tables must be dropped from the tablespace prior to a
DROP TABLESPACE
operation. If the
tablespace is not empty, DROP
TABLESPACE
returns an error.
Use a query similar to the following to identify tables in a general tablespace.
mysql> 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';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1 | test/t1 |
| ts1 | test/t2 |
| ts1 | test/t3 |
+------------+------------+
A general InnoDB
tablespace is not deleted
automatically when the last table in the tablespace is dropped.
The tablespace must be dropped explicitly using
DROP TABLESPACE
.
tablespace_name
A general tablespace does not belong to any particular database.
A DROP DATABASE
operation can
drop tables that belong to a general tablespace but it cannot
drop the tablespace, even if the DROP
DATABASE
operation drops all tables that belong to the
tablespace.
Similar to the system tablespace, truncating or dropping tables
stored in a general tablespace creates free space internally in
the general tablespace .ibd data
file which can only be used for new
InnoDB
data. Space is not released back to
the operating system as it is when a file-per-table tablespace
is deleted during a DROP TABLE
operation.
This example demonstrates how to drop an
InnoDB
general tablespace. The general
tablespace ts1
is created with a single
table. The table must be dropped before dropping the tablespace.
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;
mysql> DROP TABLE t1;
mysql> DROP TABLESPACE ts1;
is a case-sensitive identifier in MySQL.
tablespace_name
A generated or existing tablespace cannot be changed to a general tablespace.
Creation of temporary general tablespaces is not supported.
General tablespaces do not support temporary tables.
Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace .ibd data file which can only be used for new
InnoDB
data. Space is not released back to the operating system as it is for file-per-table tablespaces.Additionally, a table-copying
ALTER TABLE
operation on table that resides in a shared tablespace (a general tablespace or the system tablespace) can increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes. The additional space required for the table-copyingALTER TABLE
operation is not released back to the operating system as it is for file-per-table tablespaces.ALTER TABLE ... DISCARD TABLESPACE
andALTER TABLE ...IMPORT TABLESPACE
are not supported for tables that belong to a general tablespace.Support for placing table partitions in general tablespaces was deprecated in MySQL 5.7.24 and removed in MySQL 8.0.13.
The
ADD DATAFILE
clause is not supported in a replication environment where the source and replica reside on the same host, as it would cause the source and replica to create a tablespace of the same name in the same location, which is not supported. However, if theADD DATAFILE
clause is omitted, the tablespace is created in the data directory with a generated file name that is unique, which is permitted.As of MySQL 8.0.21, general tablespaces cannot be created in the undo tablespace directory (
innodb_undo_directory
) unless that directly is known toInnoDB
. Known directories are those defined by thedatadir
,innodb_data_home_dir
, andinnodb_directories
variables.