This article refers to the original- http://bjbsair.com/2020-03-22/tech-info/5348/
Summary

InnoDB stores data in the form of tablespace, In the default configuration, there will be a file with the initial size of 10MB and the name of ibdata1. This file is the default tablespace file. Users can set it with the parameter InnoDB? Data? File? Path. There can be multiple data files. If InnoDB? File? Per? Table is not set, The data of the tables of InnoDB storage type are all placed in this shared table space. If the system variable InnoDB [file] per table = 1, the tables of InnoDB storage engine type will generate an independent table space. So what are the advantages and disadvantages of independent table spaces? How to judge whether XX table is a separate table space or a shared table space?

1, Advantages and disadvantages of independent tablespace

1, advantages

1) Each table has its own table space.

2) The data and index of each table will exist in its own table space.

3) Single tables can be moved in different databases.

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

• Drop table automatically reclaims the table space. For statistical analysis or daily value tables, you can use alter table TableName engine=innodb; to retract unused space after deleting a large amount of data.
• Using rotate table for Innodb with Innodb plugin also shrinks the space.
• For tables that use a separate tablespace, no matter how they are deleted, the fragmentation of the tablespace does not significantly affect performance, and there is an opportunity to deal with it.

2, shortcomings

Too large single table increase, such as more than 100 G.

2, Distinguish whether a data table is an independent table space or a shared table space

1. Judging by ibd file

If the storage engine of the table is InnoDB and the tablespace is shared, there is no "table name. ibd" file under the corresponding directory of the database. For a table in a separate table space, there is a "table name. ibd" file. But this method is very stupid. For the production environment, a large number of tables are judged in this way, which 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

MySQL 5.6's information ﹣ schema. INNODB ﹣ sys ﹣ tablespaces provides information about the format and storage characteristics of tables, including row format, compressed page size bit level information (if applicable), and INNODB's tablespace information.

--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='HWB' AND T.ENGINE='InnoDB';  
--Independent tablespace  
SELECT TABLE_SCHEMA  
    ,TABLE_NAME  
    ,TABLE_TYPE  
    ,N'Independent tablespace' 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='HWB'  AND T.ENGINE='InnoDB';

3. Information? Schema.innodb? Sys? Tables discrimination (recommended)

In MySQL 5.7, there are many space type fields in the information table schema.innodb sys tablespaces, but their values are all Single. In the information table schema.innodb sys tables, there are also many space type fields. The values of Single and System represent 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 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='HWB'  AND T.ENGINE='InnoDB';  

--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='HWB' AND T.ENGINE='InnoDB';

Feel useful friends to help forward Oh! More content about devops and DBA will be shared later. Interested friends can pay attention to it~