Detailed explanation of how MySQL determines whether an InnoDB table is an independent tablespace or a shared tablespace

Preface

InnoDB stores data in tablespaces. In the default configuration, there will be a file with an initial size of 10MB and the name ibdata1. This file is the default tablespace 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 Innodb storage type tables are placed in this shared tablespace. If the system variable innodb_file_per_table=1, then the InnoDB storage engine type table will generate an independent tablespace. The naming rule of the independent tablespace is: table name.idb. These separate tablespace files only store the data, index, insert buffer BITMAP and other information of the table. Other information is still stored in the shared tablespace. So how to determine which tables in the database are independent tablespaces and which tables are shared tablespaces?

InnoDB logical storage structure

Method 1: Identify through ibd file

If the storage engine of the table is InnoDB and the tablespace is a shared tablespace, there is no "table name.ibd" file in the corresponding directory of the database. For a table in an independent tablespace, there is a "table name.ibd" file. But this method is very stupid. For a production environment, it is not a good method to judge a large number of tables in this way.

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
 
mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> 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 names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> create table common_tablespace(name varchar(64));
Query OK, 0 rows affected (0.02 sec)
 
mysql> exit
Bye
[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: Use INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES to determine.

MySQL 5.6

MySQL 5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES This system table provides information about the format and storage characteristics of tables, including row format, compression page size bit level (if applicable), and INNODB tablespace information.

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

I spent some time to understand the INNODB_SYS_TABLESPACES table under the INFORMATION_SCHEMA database, and then wrote a SQL to determine whether those InnoDB engine tables are independent table spaces or shared table spaces.

Shared tablespace:

SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'Shared tablespace' 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 I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB'; 

However, this script has a small bug. For table names containing special characters, errors may occur. This is because if the table name contains special characters, the file name or NAME in INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES is escaped, as shown below

Independent table space

SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'Independent table space' 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 I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB'; 

Method 3: INFORMATION_SCHEMA.INNODB_SYS_TABLES determination

MySQL 5.7

If it is MySQL 5.7, there is one more method than MySQL 5.6. There is an additional SPACE_TYPE field in INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES of MySQL 5.7, but its values ​​are all Single. There is also an additional field SPACE_TYPE in INFORMATION_SCHEMA.INNODB_SYS_TABLES, and its values ​​are Single and System, which represent separate tablespaces and shared tablespaces respectively.

#Separate tablespace

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE SPACE_TYPE='Single';
 
 
 
SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'Independent table space' 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 I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE T.TABLE_SCHEMA='YourDatabase' AND T.ENGINE='InnoDB';

#Shared tablespace

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE SPACE_TYPE='System';
 
 
 
SELECT TABLE_SCHEMA
 ,TABLE_NAME
 ,TABLE_TYPE
 ,N'Shared tablespace' 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 I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='YourDatabase' AND T.ENGINE='InnoDB'

Method 4: INFORMATION_SCHEMA.INNODB_TABLES determination

MySQL 8.0

If it is MySQL 8.0, there is one more method, that is, to judge through INFORMATION_SCHEMA.INNODB_TABLES. This newly added system table can distinguish shared tablespaces from independent tablespaces through the SPACE_TYPE field.

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

References:

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.