InnoDB general table space

InnoDB general table space

General table space

The regular table space is

use

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.

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:

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

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,

The directory is at the same level as the data directory: The clause must be defined as thePart of the statement, or Must be defined as the default storage engine ().

Add the table to the regular table space

create

After the general table space, you can use or add tables to the table space, as shown in the following example: tbl_nametablespace_nametbl_nametablespace_name : :

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

System table space and regular table space.

For detailed syntax information, see

with.

Regular tablespace row format support

The general table space supports all table row formats (

,, ,) 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 (

) Of the regular table space, Must be specified, and the The value must be relative to theThe value of the effective compressed page size. In addition, the compressed table () The physical page size must be equal to . For example, if the tablewith ,then Must be 8.

The following table shows the allowed

, with combination. The value can also be specified in bytes. To be sure Given effective value, Please place Divide the value by 1024. Table compression does not support 32K and 64KPage size. More information about,See 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 value64KB64K (65536)Does not support compression32KB32K (32768)Does not support compression16KB16K (16384)N/A: if

equal , The table space cannot contain compressed tables.16KB8K (8192)816KB4K (4096)416KB2K (2048)216KB1K (1024)1 piece8KB8K (8192)N/A: ifequal , The table space cannot contain compressed tables.8KB4K (4096)48KB2K (2048)28KB1K (1024)1 piece4KB4K (4096)N/A: ifequal , The table space cannot contain compressed tables.4K2K (2048)24KB1K (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. in8192 requires compression table to have8.

in case

Is not specified when creating a regular table space, then The default is . whenequal , The table space may only contain the uncompressed row format (table, withRow format).

Use ALTER TABLE to move tables between table spaces

you can use it

The

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

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

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.

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

Is the tablespace name.

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

As the table space name. Even ifThe property has not changed its previous value, and the operation will always cause the full table to be rebuilt. The syntax does not support moving a table from a temporary table space to a persistent table space. Clauses are allowed, But does not support theOptions are used in combination.

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

Delete general table space

Statement is used to deleteConventional table space. Before the operation, all tables must be deleted from the table space. If the table space is not empty, thenReturns an error.

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

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 tableThe operation interrupted by the server exit is left behind. For more information, see "Troubleshooting InnoDB Data Dictionary Operations" . 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.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 theThe 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.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

data. in 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

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

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,

  ALTER TABLE
  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.
• ALTER TABLE ... DISCARD TABLESPACE
  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.