InnoDB general table space

InnoDB general table space

General table space

The regular table space is

InnoDB
use
CREATE TABLESPACE
The shared table space created by the syntax. The following topics in this section describe general tablespace functions below:

Conventional table space functions

The general table space function provides the following functions:

  • Similar to the system table space, the regular table space is a shared table space that can store data from multiple tables.
  • Conventional table spaces have potential memory advantages over per-table file table spaces. The server keeps the table space metadata in memory during the lifetime of the table space. Compared with the same number of tables in a single file table space per table, multiple tables in a fewer conventional table space consume less memory for table space metadata.
  • General tablespace data files can be placed in a directory relative to the MySQL data directory or independent of the MySQL data directory. This directory provides you with many data files and storage management functions for the tablespace of each table file . As with the tablespace for each table file, the ability to place data files outside the MySQL data directory allows you to manage the performance of key tables separately, set up RAID or DRBD for specific tables, or bind tables to specific disks.
  • Conventional table space supports Antelope and Barracuda file formats, so all table row formats and related functions are supported. Support two file formats, universal table space does not depend on
    innodb_file_format
    or
    innodb_file_per_table
    Settings, these variables do not affect the general table space.
  • The
    TABLESPACE
    Options are available for
    CREATE TABLE
    In a regular table space, a table is created in each table file table space or system table space.
  • The
    TABLESPACE
    Options are available for
    ALTER TABLE
    In a regular table space, move tables between each table file table space and the system table space. Previously, it was impossible to move tables from each table file table space to the system table space. Using regular tablespace features, you can do this now.
Create a general table space

Conventional table space is to use

Grammar created.

CREATE TABLE SPACE tablespace_name ADD DATAFILE'file_name ' [FILE_BLOCK_SIZE = value ] [ENGINE [ = ] engine_name] Copy code

The general table space can be created in the data directory or outside of it. In order to avoid conflicts with the implicitly created table space of each table file, it is not supported to create a regular table space in a subdirectory under the data directory. When creating a regular table space outside of the data directory, the directory must exist before the table space is created.

When creating a regular tablespace outside of the MySQL data directory, an .isl file is created in the MySQL data directory .

example:

Create a regular table space in the data directory:

MySQL > the CREATE TABLE the SPACE `ts1` the ADD DATAFILE 'ts1.ibd' Engine = the InnoDB; duplicated code

Create a regular tablespace in a directory other than the data directory:

MySQL > the CREATE TABLE the SPACE `ts1` the ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine = the InnoDB; duplicated code

You can specify the path relative to the data directory, as long as the tablespace directory is not in the data directory. In this example,

my_tablespace
The directory is at the same level as the data directory:

MySQL > the CREATE TABLE the SPACE `ts1` the ADD DATAFILE '../my_tablespace/ts1.ibd' Engine = the InnoDB; duplicated code

note

The

ENGINE = InnoDB
The clause must be defined as the
CREATE TABLESPACE
Part of the statement, or
InnoDB
Must be defined as the default storage engine (
default_storage_engine=InnoDB
).

Add the table to the regular table space

create

InnoDB
After the general table space, you can use or add tables to the table space, as shown in the following example:
CREATE TABLE *
tbl_name
* ... TABLESPACE [=\] *
tablespace_name
*
ALTER TABLE *
tbl_name
* TABLESPACE [=\] *
tablespace_name
*

:

MySQL > the CREATE TABLE T1 (C1 the INT a PRIMARY KEY) TABLESPACE tsl; duplicated code

:

MySQL > the ALTER TABLE T2 TABLESPACE tsl; duplicated code

note

Support for adding table partitions to shared tablespaces is deprecated in MySQL 5.7.24, and this support will be removed in a future MySQL version. Shared table space includes

InnoDB
System table space and regular table space.

For detailed syntax information, see

with
ALTER TABLE
.

Regular tablespace row format support

The general table space supports all table row formats (

REDUNDANT
,
COMPACT
,
DYNAMIC
,
COMPRESSED
) It should be reminded that compressed and uncompressed tables cannot generally coexist in the same tablespace due to different physical page sizes.

For containing compressed tables (

ROW_FORMAT=COMPRESSED
) Of the regular table space,
FILE_BLOCK_SIZE
Must be specified, and the
FILE_BLOCK_SIZE
The value must be relative to the
innodb_page_size
The value of the effective compressed page size. In addition, the compressed table (
KEY_BLOCK_SIZE
) The physical page size must be equal to
FILE_BLOCK_SIZE/1024
. For example, if the table
innodb_page_size=16KB
with
FILE_BLOCK_SIZE=8K
,then
KEY_BLOCK_SIZE
Must be 8.

The following table shows the allowed

,
FILE_BLOCK_SIZE
with
KEY_BLOCK_SIZE
combination.
FILE_BLOCK_SIZE
The value can also be specified in bytes. To be sure
KEY_BLOCK_SIZE
Given effective value
FILE_BLOCK_SIZE
, Please place
FILE_BLOCK_SIZE
Divide the value by 1024. Table compression does not support 32K and 64K
InnoDB
Page size. More information about
KEY_BLOCK_SIZE
,See
CREATE TABLE
And "Create Compression Table" .

The allowed page size of the compressed table, the combination of FILE_BLOCK_SIZE and KEY_BLOCK_SIZE

InnoDB page size (innodb_page_size)Allowed FILE_BLOCK_SIZE valueAllowed KEY_BLOCK_SIZE value
64KB64K (65536)Does not support compression
32KB32K (32768)Does not support compression
16KB16K (16384)N/A: if
innodb_page_size
equal
FILE_BLOCK_SIZE
, The table space cannot contain compressed tables.
16KB8K (8192)8
16KB4K (4096)4
16KB2K (2048)2
16KB1K (1024)1 piece
8KB8K (8192)N/A: if
innodb_page_size
equal
FILE_BLOCK_SIZE
, The table space cannot contain compressed tables.
8KB4K (4096)4
8KB2K (2048)2
8KB1K (1024)1 piece
4KB4K (4096)N/A: if
innodb_page_size
equal
FILE_BLOCK_SIZE
, The table space cannot contain compressed tables.
4K2K (2048)2
4KB1K (1024)1 piece

This example demonstrates how to create a regular table space and add compressed tables. This example assumes the default

The value is 16KB. in
FILE_BLOCK_SIZE
8192 requires compression table to have
KEY_BLOCK_SIZE
8.

mysql > CREATE TABLE SPACE `ts2` ADD DATAFILE'ts2.ibd ' FILE_BLOCK_SIZE = 8192 Engine = InnoDB; MySQL > the CREATE TABLE T4 (C1 the INT a PRIMARY KEY) the ROW_FORMAT TABLESPACE TS2 = the COMPRESSED KEY_BLOCK_SIZE = . 8 ; duplicated code

in case

FILE_BLOCK_SIZE
Is not specified when creating a regular table space, then
FILE_BLOCK_SIZE
The default is
innodb_page_size
. when
FILE_BLOCK_SIZE
equal
innodb_page_size
, The table space may only contain the uncompressed row format (table
COMPACT
,
REDUNDANT
with
DYNAMIC
Row format).

Use ALTER TABLE to move tables between table spaces

you can use it

The
TABLESPACE
Option to move the table to the existing regular tablespace, the new per-table file tablespace or system tablespace.

note

Support for placing table partitions in shared tablespaces has been deprecated in MySQL 5.7.24, and this support will be removed in a future version of MySQL. Shared table space includes

InnoDB
System table space and regular table space.

To move a table from a per-file file table space or system table space to a regular table space, specify the name of the regular table space. The regular table space must exist. See

For more information.

ALTER TABLE tbl_name TABLESPACE [ = ] tablespace_name; Copy code

To move the table from the regular tablespace or the per-table file tablespace to the system tablespace, specify

innodb_system
Is the tablespace name.

The ALTER TABLE tbl_name TABLESPACE [ = ] innodb_system; duplicated code

To move the table from the system table space or regular table space to each table file table space, specify

innodb_file_per_table
As the table space name.

The ALTER TABLE tbl_name TABLESPACE [ = ] the innodb_file_per_table; duplicated code

ALTER TABLE ... TABLESPACE
Even if
TABLESPACE
The property has not changed its previous value, and the operation will always cause the full table to be rebuilt.

ALTER TABLE ... TABLESPACE
The syntax does not support moving a table from a temporary table space to a persistent table space.

The

DATA DIRECTORY
Clauses are allowed,
CREATE TABLE ... TABLESPACE=innodb_file_per_table
But does not support the
TABLESPACE
Options are used in combination.

There are restrictions when moving tables from an encrypted tablespace. See encryption restrictions .

Delete general table space

The

Statement is used to delete
InnoDB
Conventional table space.

Before the operation, all tables must be deleted from the table space. If the table space is not empty, then
DROP TABLESPACE
Returns an error.

Use a query similar to the following to identify the tables in the regular tablespace.

mysql > SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a, B INFORMATION_SCHEMA.INNODB_TABLES the WHERE a.SPACE = b.SPACE the AND a.name the LIKE 'tsl' ; + ----------------------- + ----------------------- + | SPACE_NAME | table_name | + ------------+------------+ | ts1 | test / t1 | | ts1 | test / t2 | | ts1 | test / t3 | + ------------+------------+ Copy code

in case

The operation of the empty regular table space returns an error, the table space may contain orphaned temporary tables or intermediate tables, the table
ALTER TABLE
The operation interrupted by the server exit is left behind. For more information, see "Troubleshooting InnoDB Data Dictionary Operations" .

InnoDB
When deleting the last table in the table space, the general table space will not be deleted automatically. You must use an explicit drop table space.
DROP TABLESPACE *
tablespace_name
*

The regular table space does not belong to any particular database. One

The operation can delete a table belonging to a regular table space, but even if the
DROP DATABASE
The operation deletes all tables belonging to the table space, and the table space cannot be deleted. You must use an explicit delete regular table space.
DROP TABLESPACE *
tablespace_name
*

Similar to the system table space, truncating or deleting a table stored in a conventional table space will create free space inside the conventional table space .ibd data file , which can only be used for new

InnoDB
data. in
DROP TABLE
When the tablespace of each table file is deleted during the operation, the space will not be released back to the operating system.

This example shows how to delete

InnoDB
Conventional table space. Conventional table space
ts1
It was created with a single table. You must delete the table first, and then delete the table space.

mysql > CREATE TABLE SPACE `ts1` ADD DATAFILE'ts1.ibd ' Engine = InnoDB; mysql > CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine = InnoDB; mysql > DROP TABLE t1; MySQL > the DROP TABLESPACE tsl; duplicated code

note

*
tablespace_name
*
Is a case-sensitive identifier in MySQL.

Conventional table space restrictions
  • The generated table space or existing table space cannot be changed to a regular table space.

  • The creation of temporary general tablespaces is not supported.

  • Regular table spaces do not support temporary tables.

  • The tables stored in the general table space can only be opened in the MySQL version that supports the general table space.

  • Similar to the system table space, truncating or deleting a table stored in a conventional table space will create free space inside the conventional table space .ibd data file , which can only be used for new

    InnoDB
    data. The space of each table file table space will not be released back to the operating system.

    In addition,

    Table copy operations on tables residing in a shared table space (general table space or system table space) may increase the amount of space used by the table space. This type of operation requires as much extra space as the data in the table and the index. Table replication
    ALTER TABLE
    The extra space required for the operation will not be released back to the operating system like each table file table space.

  • and
    ALTER TABLE ...IMPORT TABLESPACE
    Tables belonging to general tablespaces are not supported.

  • Support for placing table partitions in regular tablespaces has been deprecated in MySQL 5.7.24, and this support will be removed in a future version of MySQL.

  • ADD DATAFILE
    In a replication environment where the source and replica are on the same host, this clause is not supported because it will cause the source and replica to create tablespaces with the same name at the same location.

For more content, please pay attention to my personal public account "Brother Han has something to say", 100G artificial intelligence learning materials, and a lot of back-end learning materials are waiting for you.