MySQL Shared table space concept

1. Table Space Concepts

The InnoDB storage engine can store all of the data in a shared tablespace of ibdata*, or it can store each table in a separate table space in a separate. ibd file.
Shared tablespaces and stand-alone table spaces are all about how data is stored.

• Shared table Spaces

All the table data of a database, the index file is placed in a file, the default file path for this shared tablespace is in the data directory. The default file name is: Ibdata1 is initialized to 10M.

• Stand-alone table space

Each table will be generated in a separate file way for storage, and each table has a. frm table description file, and an. ibd file. This file includes the data content of a single table and the index content, which, by default, is stored in the table's location.

2. Advantages and Disadvantages

• Shared table Spaces

Pros: You can divide a tablespace into multiple files on separate disks (the tablespace file size is not limited by the size of the table, such as a table can be spread over different files). Data and documents are easily managed together.

Disadvantage: all the data and indexes are stored in a file, although a large file can be divided into small files, but multiple tables and indexes in the table space mixed storage, so for a table to do a large number of delete operations in the table space will have a large number of gaps, especially for statistical analysis, Such applications as day-value systems are most unsuitable for sharing table spaces.

• Stand-alone table space

Advantages:
1. Each table has its own self-contained table space.
2. The data and index for each table will exist in the table space itself.
3. You can implement a single table to move in different databases.
4. The space can be recycled (except for the drop table operation, the meter is empty)
The table space is automatically reclaimed by the A.drop table operation, and if you delete large amounts of data for statistical analysis or daily value tables, you can: alter table TableName engine=innodb;Shrink the unused space.
B. The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.
C. For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too severely, and there is a chance to process it.
Disadvantages:
1. Single table increased too large, such as more than 100 g.
2. By comparison, the efficiency and performance of using exclusive tablespaces are a bit higher. 3. Independent table Space parameter innodb_file_per_table detailedThis parameter is turned on by default in mysql5.6.6 and subsequent versions, and when this parameter is turned on, InnoDB stores the data and indexes of each newly created table in a separate. ibd file instead of the system tablespace. When these InnoDB tables are deleted or emptied, the storage space is recycled.
When Innodb_file_per_table is turned on, the InnoDB table is moved from the System shared table space to the standalone. ibd file in the event that the ALTER TABLE operation rebuilds the tables.
Not opening INNODB_FILE_PER_TABLE,INNODB will store all table and indexed data in the Ibdata file that makes up the system tablespace. This reduces the performance overhead of file system operations, such as drop table or TRUNCATE table. It is best suited to the case where the consolidated disk is used to store MySQL data because the system tablespace does not shrink, and all the databases are in one space instance. When Innodb_file_per_table=off, avoid importing large amounts of temporary data in space-constrained system tablespaces.
The innodb_file_per_table can be changed to on or off via set global dynamics, or it can be permanently modified in my.cnf, and the mysqld service needs to be restarted if the MY.CNF is modified.

Mysql> SetGlobal innodb_file_per_table= on; MySQL>Show variables like '%per_table%'; +-----------------------+-------+  |Variable_name|Value|  +-----------------------+-------+  |Innodb_file_per_table|  on    |  +-----------------------+-------+

Note: Dynamic modification only takes effect for subsequent operations, such as the original shared tablespace, and only newly created tables are stand-alone table spaces after dynamic modification to a separate table space.

4. InnoDB shared table space into a separate table space

To view the current table space situation:

Mysql>Show variables like '%per_table'; +-----------------------+-------+  |Variable_name|Value|  +-----------------------+-------+  |Innodb_file_per_table| OFF   |  +-----------------------+-------+

The innodb_file_per_table parameter value is off to indicate that the table space is currently shared.
There are two ways to convert a shared table space into a stand-alone table space:

1. First logical backup, then modify the parameters in the configuration file my.cnf innodb_file_per_table = 1, you can import a logical backup after restarting the service.
2. Modify the parameters in the configuration file my.cnf innodb_file_per_table = 1, all InnoDB tables that need to be modified are executed once after the service is restarted: alter table table_name engine=innodb;
Note: After modifying in the second way, the data in the table in the original library will continue to be stored in ibdata1, and the new table will use a separate table space.

MySQL Shared table space concept