Shulou(Shulou.com)06/01 Report--

When learning mysql, it is always customary to compare with oracle database. When learning the storage structure of mysql InnoDB, it is inevitable to compare it with oracle. Oracle data stores include table spaces, segments, extents, blocks, and data files; mysql InnoDB storage management is similar, but mysql adds the concept of shared table spaces and independent table spaces

I. concept

Shared tablespaces: all data in Innodb is stored in a single tablespace, and this tablespace can be composed of many files, and a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limit. As can be seen from the official documentation of Innodb, the maximum tablespace limit of Innodb is 64TB, that is, the single table limit of Innodb is basically around 64TB, of course, this size includes all indexes of this table and other related data.

Independent tablespaces:

Second, 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 10m. When each file is full, ibdata4 automatically expands.

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, when InnoDB uses up all the table free space, the last data file will be automatically expanded by 8 MB each time. Example:

Innodb_data_file files exist for both shared and independent tablespaces, because they not only hold data, but also act as UNDO tablespaces similar to ORACLE.

III. Advantages and disadvantages of shared tablespaces

Since Innodb has two types of shared tablespaces and independent tablespaces, it is reasonable to have scenarios in which both tablespaces must have their own applications. Here are some official introductions from mysql:

3.1 advantages of shared tablespaces

Tablespaces can be divided into multiple files to be stored on each disk, so tables can be divided into multiple files to be stored on disk, and the size of the table is not limited by the disk size (there is something wrong with the description of many documents).

Data and files are put together for easy management.

3.2 disadvantages of shared tablespaces

All the data and indexes are stored in one file, although a large file can be divided into multiple small files, multiple tables and indexes are mixed and stored in the tablespace, when the amount of data is very large, there will be a lot of gaps in the tablespace after a large number of table deletions, especially for statistical analysis, shared tablespaces are the least suitable for applications such as frequent deletion operations.

Shared table space cannot be retracted after allocation: when the table space is temporarily indexed or the operation table space for creating a temporary table is expanded, there is no way to shrink that part of the space even if the related table is deleted (it can be understood as oracle table space 10G, but only 10m is used, but the operating system shows that mysql table space is 10G), cold backup of the database is very slow

IV. Advantages and disadvantages of independent table space

4.1 advantages of independent tablespaces

Each table has its own independent table space, and the data and indexes of each table will be stored in its own table space, so that a single table can be moved in different databases.

Space can be recycled (table emptiness cannot be recycled by itself except for drop table operation)

The Drop table operation automatically reclaims tablespaces. For statistical analysis or daily value tables, you can delete a large amount of data through: alter table TableName engine=innodb; to retract unused space.

Using turncate table for the Innodb of innodb-plugin also shrinks space.

For tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of the tablespace will not seriously affect performance, and there is still a chance to deal with it.

4.2 disadvantages of independent tablespaces

The increase of single table is too large, when the space of single table is too large, there is not enough storage space, so we can only think about the solution from the level of operating system.

Conversion between shared and independent tablespaces

5.1 View the tablespace management types 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, OFF represents shared tablespace management; (to view the tablespace management of a single table, you need to check whether each table has a separate data file)

5.2 modify the tablespace management of the database

You can modify the parameter values of innodb_file_per_table, but the changes cannot affect the shared and independent tablespaces that have been used before.

Innodb_file_per_table=1 to use exclusive tablespaces

Innodb_file_per_table=0 for using shared tablespaces

5.3 method of converting shared tablespaces to independent tablespaces (parameter innodb_file_per_table=1 needs to be set)

Conversion operation for a single table, script: alter table table_name engine=innodb

When there are a large number of tables to operate, export the database first, then delete the data and then import it, which can be operated with mysqldump (http://blog.itpub.net/12679300/viewspace-1259451/)

Summary: after the above operations to complete the conversion of the storage space of the database, to understand the technology is to make better use of the technology, when the amount of data is very small, it is recommended to use the management mode of shared table space. When the amount of data is large, it is recommended to use independent tablespace management.

*

Author: JOHN QQ:1916066696 (please note the database)

ORACLE Technology blog:

ORACLE Hunter's Note http://blog.itpub.net/12679300/

*

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.