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

Preface

InnoDB stores data by table space (tablespace). In the default configuration, there will be a file with an initial size of 10MB and a name of ibdata1. This file is the default table space file (tablespce file). Users can set it through the parameter innodb_data_file_path. There can be multiple data files, if innodb_file_per_table is not set. The data of those tables of Innodb storage type are placed in this shared tablespace, and if the system variable innodb_file_per_table=1, then tables of InnoDB storage engine type will produce a separate tablespace, the naming convention of independent tablespace is: table name .idb. These separate table space files only store the data, index and insert buffer BITMAP of the table, and other information is still stored in the shared table space, so how to distinguish which tables in the database are independent table spaces and which tables are shared table spaces?

InnoDB logical storage structure

Method 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.

Mysql > show variables like 'innodb_file_per_table' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_file_per_table | ON | +-- + 1 row in set (0.01sec) mysql > use MyDB Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-A Database changedmysql > create table Independent_tablespace (name varchar (64)) Query OK, 0 rows affected (0.03 sec) mysql > exit [root@DB-Server ~] # cd / data/mysql/MyDB/ [root@DB-Server MyDB] # ls-lrt Independent_tablespace*-rw-rw---- 1 mysql mysql 8560 Aug 21 22:05 Independent_tablespace.frm-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_ tablespace.ibd [root @ DB-Server MyDB] #

Set innodb_file_per_table=0 in the configuration file my.cnf, restart the MySQL service, create the table common_tablespace, and you will see only the common_tablespace.frm file in the data directory.

Mysql > show variables like 'innodb_file_per_table' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_file_per_table | OFF | +-- + 1 row in set (0.00 sec) mysql > use MyDB Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-A Database changedmysql > create table common_tablespace (name varchar (64)); Query OK, 0 rows affected (0.02 sec) mysql > exitBye [root@DB-Server MyDB] # ls-lrt common_tablespace*-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_ tablespace.frm [root @ DB-Server MyDB] #

Method 2: INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES was used to distinguish.

MySQL 5.6

MySQL 5.6INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES about this system table provides 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.

The INNODB_SYS_TABLESPACES table provides metadata about InnoDB tablespaces, equivalent to the information in the SYS_TABLESPACES table in the InnoDB data dictionary.

It took some time to understand the INNODB_SYS_TABLESPACES table under the INFORMATION_SCHEMA database, so I wrote a SQL to determine whether those InnoDB engine tables were independent empty or shared tablespaces.

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_TIMEFROM INFORMATION_SCHEMA.TABLES TLEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/', T.TABLE_NAME) = I.NAMEWHERE I.NAME IS NULL AND T. TABLESCHEMAT shared table spaces MyDB' AND T.ENGINE shared innovative DB'

However, this script has a small bug, and an error may occur for a table name that contains special characters, because if the table name contains special characters, then the NAME in the file name or INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES is escaped, as shown below

Independent tablespace

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, N 'independent tablespaces' AS TABLE_SPACE, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, CREATE_TIME, UPDATE_TIMEFROM INFORMATION_SCHEMA.TABLES TINNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/', T.TABLE_NAME) = I.NAMEWHERE T. TABLESCHEMAX independent tables MyDB' AND T. engineer

Method 3: INFORMATION_SCHEMA.INNODB_SYS_TABLES discrimination

MySQL 5.7

If it is MySQL 5.7.There is one more way than MySQL 5.6.The INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES of MySQL 5.7contains more SPACE_TYPE fields, but all of their values are Single, and there are also more fields SPACE_TYPE in INFORMATION_SCHEMA.INNODB_SYS_TABLES. The values of Single and System represent separate and shared tablespaces, respectively.

# separate tablespace

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESWHERE 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_TIMEFROM INFORMATION_SCHEMA.TABLES TINNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/', T.TABLE_NAME) = I.NAMEWHERE T.TABLESCHEMAXYourDatabase 'AND T.ENGINE innovative database'

# shared tablespace

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESWHERE SPACE_TYPE='System'; SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, N 'shared tablespaces' AS TABLE_SPACE, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, CREATE_TIME, UPDATE_TIMEFROM INFORMATION_SCHEMA.TABLES TLEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES ION CONCAT (T.TABLE_SCHEMA,'/', T.TABLE_NAME) = I.NAMEWHERE I.NAME IS NULL AND T.TABLESCHEMAXYourDatabase 'AND T.ENGINE shared InnoDB'

Method 4: INFORMATION_SCHEMA.INNODB_TABLES discrimination

MySQL 8.0

If it is MySLQ 8.0, there is another way to judge by INFORMATION_SCHEMA.INNODB_TABLES. This new system table can distinguish between shared table space and independent table space through the SPACE_TYPE field.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE_TYPE='Single'

Reference:

Https://dev.mysql.com/doc/refman/8.0/en/innodb-tables-table.html

Https://dev.mysql.com/doc/refman/5.6/en/innodb-i_s-tables.html

Https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablespaces-table.html#innodb-sys-tablespaces-table-flag-column

Https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablespaces-table.html

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.