InnoDB stand-alone table space

The students who have used MySQL have just started to touch the MyISAM table engine, the database of this engine will create three files respectively: Data file (. MYD), index file (. MYI) and the table structure file (. frm). We can migrate a database directory directly to a different database and work properly. However, when you use InnoDB, everything changes.

InnoDB default will store all database data in a shared tablespace: Ibdata1 file, so feel uncomfortable, add and delete database, ibdata1 file does not shrink automatically, a single database backup will become a problem. You can usually only export data using mysqldump and then import to resolve this issue.

In the MySQL configuration file [mysqld] section, add the innodb_file_per_table parameter. You can modify InnoDB as a stand-alone tablespace pattern, and each table in each database generates a data space.

Independent Table Space Benefits:

• Each table has a self-contained table space
• Data and indexes for each table will exist in the table space of your own
• You can implement a single table to move through different databases
• Drop table operation will automatically reclaim tablespace

Tables that use a separate tablespace, regardless of how they are deleted, do not have a significant impact on the overall performance of the database and have the opportunity to process it.

Standalone table Space Disadvantages:

• Single table increase too large, such as more than 100 g
• Tablespace files cannot be stored on different disks by multiple files

Conclusion

Shared tablespace insert operation has a slight advantage, others do not have a separate table space to perform well. When you enable stand-alone table spaces, adjust the innodb_open_files parameters appropriately.

Independent Tablespace open method, set in My.cnf [mysqld]:

innodb_file_per_table=1

To see if it is turned on:

mysql> show variables like ‘%per_table%’;

To turn off the exclusive table space:

innodb_file_per_table=0

Typically these three parameters are set together:

innodb_file_per_table=1innodb_file_format=barracudainnodb_strict_mode=1

InnoDB stand-alone table space