InnoDB engine independent tablespace innodb_file_per_table_MySQL

InnoDB engine independent tablespace innodb_file_per_table bitsCN.com

InnoDB engine independent tablespace innodb_file_per_table

If you have used MySQL, the MyISAM table engine is the most frequently used at the beginning. the database of this engine creates three files: table structure, table index, and table data space. You can migrate a Database Directory to another database. However, when you use InnoDB, everything changes.

By default, InnoDB stores all the table data of the InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. when adding or deleting databases, the ibdata1 file does not automatically contract, the backup of a single database will also become a problem. Data can only be exported using mysqldump and then imported to solve this problem.

Add the innodb_file_per_table parameter to the MySQL configuration file [mysqld.

You can change InnoDB to The Independent tablespace mode. each table in each database generates a data space.

Independent tablespace:

Advantages:

1. Each table has its own independent tablespace.

2. Data and indexes of each table are stored in its own tablespace.

3. a single table can be moved in different databases.

4. space can be recycled (except for the drop table operation, the table cannot be recycled if it is empty)

A) the Drop table operation automatically recycles tablespaces. for statistical analysis or daily value tables, you can use alter table TableName engine = innodb to delete a large amount of data and reduce unnecessary space.

B) using turncate table for innodb-plugin will also shrink the space.

C) for tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.

Disadvantages:

The increase in a single table is too large, for example, more than 100 GB.

Conclusion:

Shared tablespace has few advantages in Insert operations. Other independent tablespaces do a good job. When the independent tablespace is enabled, properly adjust innodb_open_files.

The tablespace cp of InnoDB Hot Backup (cold Backup) won't face a lot of useless copies. In addition, innodb hot backup and tablespace management commands can be used to achieve single-current movement.

BitsCN.com