INNODB tablespace of MySQL (shared and independent)

When using the Innodb engine, Innodb has two ways to manage and select two types of tablespaces.

• Shared tablespace: it can also be split into multiple small tablespaces.
• Independent tablespace: Each table has an independent tablespace.

In terms of performance and O & M, independent tablespaces have many advantages over shared tablespaces.

1. Concepts of shared and independent tablespaces

1. Shared tablespace

All the table data of a database is stored in one file, and the file path of the shared tablespace is under the data directory by default. The default file name is ibdata1, Which is initialized to 10 M.

Because it is the default method, it is understood as the officially recommended Mysql method for the moment. All data is relatively stored in one (or several) file, which is easier to manage. You only need to open this (or several) file during operations, relatively low cost. However, the problem is that when the data reaches G, the advantages and disadvantages are reversed. A large file is not conducive to management, and for such a huge file, reading and writing it requires a huge amount of resources. Even more puzzling, the INNODB Engine stores indexes and data in the same file, and there is still resource contention between indexes and data, which is not conducive to performance improvement. Of course, you can plan multiple tablespace files through the configuration of innodb_data_file_path. However, the MySQL logic is "Add after use", which is only a file split and cannot fundamentally separate data and indexes.

2. Exclusive tablespace

Each table is stored in an independent file. Each table has a. frm table description file and A. ibd file. This file includes the data content and index content of a separate table.

Ii. Advantages and Disadvantages of shared and independent tablespaces

1. Advantages and Disadvantages of shared tablespace

(1) Advantages

The tablespace can be divided into multiple files and stored on each disk (the tablespace file size is not limited by the table size, for example, a table can be distributed on non-synchronous files ). Put data and files together for convenient management.

(2) Disadvantages

All the data and indexes are stored in one file to store a very large file. Although a large file can be divided into multiple small files, however, multiple tables and indexes are stored in a table space in a hybrid manner. After a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, especially for statistical analysis, applications such as the daily value system do not use the most shared tablespace.

In shared tablespace management, the tablespace cannot be reduced after it is allocated. When a temporary index is created or a temporary table is created, that is, you cannot shrink the space of a table after deleting it. During disk monitoring, alarms may continue, but MySQL can still run well. In addition, when the disk occupies a large amount of space, the performance is not very good. In this case, you can only create a new Slave to Dump it from the master database and then Dump it to the Slave database. For InnoDB Hot Backup operations (or direct cold Backup), each copy is large. If there are currently GB tablespaces, but the actual data is only 50 GB, we will face the need to copy GB of data each time.

2. Advantages and Disadvantages of independent tablespace

(1) Advantages

1. Each table has its own independent tablespace.
2. Each table's data and indexes are stored in its own tablespace.
3. A single table can be moved across different databases.
4. Space can be recycled (except for the drop table operation, the tablespace cannot be recycled by itself ).

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.

The use of turncate table for innodb-plugin will also shrink the space.

No matter how you delete a table that uses an independent tablespace, The tablespace fragments will not seriously affect the performance, and there is a chance to process them.

(2) Disadvantages

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

Note: When an independent tablespace is created, the ibdata1 File Stores dictionary information and undo pages. Therefore, you still need to back up the ibdata1 file during cold backup. If the ibdata1 file is lost, the innodb table does not exist, but its data file and table structure exist.