MySQL's InnoDB engine's shared tablespace and stand-alone table space

For INNODB data storage files, there are two conceptual issues to be solved first: shared tablespace and exclusive table space. (The InnoDB engine differs greatly from the MyISAM engine.) Especially the way it stores data.)

1. Introduction to shared table spaces and exclusive table spaces

Shared tablespaces and exclusive tablespaces are all about how data is stored.

Shared tablespace: All the table data for each database, the index files are all in one file, the file path of this shared tablespace is in the data directory by default. The default file name is: Ibdata1 is initialized to 10M.

Exclusive tablespace: Each table will be generated as a separate file to be stored, 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).

2. Differences between shared and exclusive table spaces

To share a table space:
Advantages:
1) You can put the table space into multiple files on each disk (the Tablespace file size is not limited by the size of the table, such as a table can be distributed on different files). Table data and table descriptions are put together for easy management.
Disadvantages:
1) All the data and indexes are stored in a file, there will be a very large file, although a large file can be divided into several small files, but multiple tables and indexes in the table space mixed storage, so for a table to do a lot of delete operations in the table space will have a large number of gaps, especially for statistical analysis, Log systems Such applications are most unsuitable for shared tablespaces.

Stand-alone tablespace (set innodb_file_per_table=1in configuration file (MY.CNF)):

Advantages:
1) Each table has a self-contained table space.
2) data and indexes for each table will exist in the table space themselves.
3) You can implement a single table in a different database to move.
4) The space can be recycled (except for the drop table operation, the meter is not able to recycle):
The drop table operation automatically reclaims the tablespace, and if you delete large amounts of data for statistical analysis or log tables, you can pass : ALTER TABLE TableName ENGINE=INNODB;
The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.
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.
5) The efficiency and performance of using exclusive tablespaces are a bit higher.

Disadvantages:
1) Single table increased too large, such as more than 100 g.

3. Conversion between shared table spaces and exclusive table spaces

To modify an exclusive empty tablespace configuration, the following parameters must be joined together

Innodb_data_home_dir = directory where "/usr/local/mysql/var/" database files are stored

Innodb_log_group_home_dir = "/usr/local/mysql/var" Log storage directory

The Innodb_data_file_path=ibdata1:10m:autoextend setting configures a single file (shared data file) with an expandable size of 10MB, named Ibdata1. The location of the file is not given, so the default is within the MySQL data directory (such as/DB/MYSQL/IBDATA1).

Innodb_file_per_table=1 whether to use shared and exclusive tablespace (1 for using exclusive tablespace, 0 for using shared table space)

innodb_file_per_table conversions that are implemented by this parameter, if the off description is used for shared table spaces "By default, the tablespace used is a shared table space"

Innodb_file_per_table values to be modified, but not for shared table spaces that were previously used, unless manually modified or

Attention:

InnoDB does not create a directory, so verify that the configured path directory does exist before you start the server.

When doing data migration and backup, be aware of the integrity of the data files.

MySQL's InnoDB engine's shared tablespace and stand-alone table space