Shulou(Shulou.com)05/31 Report--

How to judge whether the InnoDB table is an independent tablespace or a shared tablespace in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Advantages and disadvantages of independent tablespaces

1. Advantages:

(1) each table has its own independent table space.

(2) the data and indexes of each table will be stored in its own tablespace.

(3) it is possible to move a single table in different databases.

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

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.

two。 Shortcoming

Single table increases too much, such as more than 100 gigabytes.

Second, judge whether the data table is an independent tablespace or a shared tablespace

1. Distinguish by ibd file

If the storage engine of the table is InnoDB, and the table space (tablespace) is a shared table space, then there is no "table name .ibd" file under the corresponding directory of the database. If the table is independent of the tablespace, there is a "table name .ibd" file. It's just that this method is very stupid, for the production environment, a large number of tables are judged in this way, it is really not a good method.

-- innodb_file_per_table=1 show variables like 'innodb_file_per_table'; create table Independent_tablespace (name varchar (64)); ls-lrt independent_tablespace.*-- innodb_file_per_table=0 show variables like' innodb_file_per_table'; create table common_tablespace (name varchar (64)); ls-lrt independent_tablespace.*

2. INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES discrimination

INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES of MySQL 5.6provides information about the format and storage characteristics of the table, including row formatting, compressed page size bit-level information (if applicable), and tablespace information for INNODB.

-shared tablespaces SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, N 'shared tablespaces' AS TABLE_SPACE, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, CREATE_TIME, UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/' T.TABLE_NAME) = I.NAME WHERE I.NAME IS NULL AND T.TABLESCHEMAX innovative databases HWB 'AND T.ENGINE innovative database' Independent tablespaces SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, N 'independent tablespaces' AS TABLE_SPACE, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, CREATE_TIME, UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES T INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/' T.TABLE_NAME) = I.NAME WHERE T.TABLESCHEMAX innovative databases HWB 'AND T.ENGINE innovative database'

3. INFORMATION_SCHEMA.INNODB_SYS_TABLES Discriminant (recommended)

The INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES of MySQL 5.7contains SPACE_TYPE fields, but all of their values are Single, while INFORMATION_SCHEMA.INNODB_SYS_TABLES also has fields SPACE_TYPE, whose values are Single and System for independent and shared tablespaces, respectively.

-- independent tablespace SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE_TYPE='Single' SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, N 'independent tablespaces' AS TABLE_SPACE, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, CREATE_TIME, UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES T INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/', T.TABLE_NAME) = I.NAME WHERE T.TABLESCHEMAX independent tables HWB' AND T.ENGINE innovative DB' -shared tablespaces SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, N 'shared tablespaces' AS TABLE_SPACE, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, CREATE_TIME, UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/' T.TABLE_NAME) = I.NAME WHERE I.NAME IS NULL AND T.TABLESCHEMAX innovative databases HWB 'AND T.ENGINE innovative database'

After reading the above, have you mastered the method of determining whether the InnoDB table is an independent tablespace or a shared tablespace in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.