MySQLInnoDB shared tablespace and independent tablespace

When learning MySQL, it is always customary to compare it with the Oracle database. When learning the storage structure of MySQLInnoDB, you can easily compare it with Oracle. Oracle count

When learning MySQL, it is always customary to compare it with the Oracle database. When learning the storage structure of MySQL InnoDB, it is inevitable to compare it with Oracle. Oracle count

MySQL is always used to be compared with Oracle databases. When learning the storage structure of MySQL InnoDB, it is inevitable to compare it with Oracle. Oracle Data Storage includes tablespaces, segments, zones, blocks, and data files. The storage and management of MySQL InnoDB is similar. However, MySQL adds the concept of a shared tablespace and an independent tablespace;

I. Concepts

Shared tablespace: All data in Innodb is stored in a single tablespace, which can be composed of multiple files. A table can exist across multiple files, therefore, its size limit is no longer a file size limit, but its own limit. From the official Innodb documentation, we can see that the maximum tablespace limit is 64 TB. That is to say, the single table limit of Innodb is about 64 TB, of course, this size includes all indexes of the table and other related data.

Independent tablespace:

2. view the tablespace of the database

Mysql> show variables like 'innodb _ data % ';

The tablespace consists of four files: ibdata1, ibdata2, ibdata3, and ibdata4. The size of each file is 10 MB. When each file is full, ibdata4 will automatically expand;

When the current storage space is full, you can add data files to other disks. The syntax is as follows:

Pathtodatafile: sizespecification; pathtodatafile: sizespec; ..; pathtodatafile: sizespec [: autoextend [: max: sizespecification]

If you use the autoextend option to describe the last data file, InnoDB will automatically expand the last data file after InnoDB uses up all the free space of the table, each increment is 8 MB. Example:

Whether it is a shared tablespace or an independent tablespace, there will be innodb_data_file files, because these files not only need to store data, but also act as ORACLE-like UNDO tablespace and other roles.

Iii. Advantages and Disadvantages of shared tablespace

Since Innodb has two types of table space: Shared table space and independent table space, the two types of table space are certainly applicable in some scenarios, which is reasonable. The following are some official introductions from mysql:

3.1 advantages of table space sharing

Table spaces can be divided into multiple files and stored on each disk. Therefore, tables can be divided into multiple files and stored on the disk. The table size is not limited by the disk size (many documents may be difficult to describe ).

Put data and files together for convenient management.

3.2 disadvantages of table space sharing

All data and indexes are stored in one file. Although a large file can be divided into multiple small files, multiple tables and indexes are stored in a table space, when the data size is very large, there will be a lot of gaps in the table space after a large number of deletion operations, especially for statistical analysis, such applications that frequently delete operations are the most unsuitable for sharing tablespaces.

Shared tablespaces cannot be reduced after they are allocated: when temporary indexes are created or a temporary table is created to expand the tablespace, that is to say, you cannot shrink the space of a table by deleting the table. (it can be understood that the tablespace in oracle is 10 Gb, but only 10 MB is used, but the tablespace in mysql is 10 Gb in the operating system ), database cold backup is slow;

Iv. Advantages and Disadvantages of independent tablespace

4.1 advantages of independent tablespace

Each table has its own independent tablespace, and the data and indexes of each table are stored in its own tablespace. This allows a single table to be moved across different databases.

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

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.

4.2 disadvantages of independent tablespace

A single table is too large. When the space occupied by a single table is too large, the storage space is insufficient. You can only think about the solution from the operating system level;

5. Conversion Between the shared tablespace and the independent tablespace

5.1 view the tablespace management type of the current database

Script: show variables like "innodb_file_per_table ";

Mysql> show variables like "innodb_file_per_table ";

ON represents independent tablespace management, and OFF represents shared tablespace management. (To view the tablespace management mode of a single table, check whether each table has a separate data file)

5.2 modify the tablespace management mode of the database

Modify the parameter value of innodb_file_per_table, but the modification cannot affect the previously used shared and independent tablespaces;

Innodb_file_per_table = 1 is the use of exclusive tablespace

Innodb_file_per_table = 0 indicates the use of the shared tablespace.

5.3 convert a shared tablespace to an independent tablespace (the innodb_file_per_table parameter must be set)
Single table conversion operation, script: alter table table_name engine = innodb;

When a large number of tables need to be operated, first export the database, then delete the data, and then import the data. This operation can be performed using mysqldump ()

Conclusion: After the above operations, the storage space of the database is converted. The knowledge of this technology is to make better use of the technology. When the data volume is small, we recommend that you use the management of the shared tablespace. When the data volume is large, we recommend that you use the independent tablespace management method.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration