Innodb
Physically, InnoDB tables consist of shared tablespaces, log file groups (redo file groups), and table structure definition files.
innodb has a relatively different directory structure, divided into shared tablespaces, separate tablespaces.
The type is controlled by the parameter innodb_file_per_table. 0: Use shared tablespace;
show variables like “innodb_file_per_table”; See the file directories under
in the data_dir definition.
Tablespace independent
Separate tablespaces are enabled. Each database creates a file of the same name to store table structure files, index files, and data files. However, undo rollback logs to transactions and redo log buffers are still stored in the shared tablespace.
Table_name.frm defines the table structure.
table_name.ibd Stores table indexes and data.
Advantages:

Each table has its own independent table space, the data and index of each table will exist in its own table space, you can achieve a single table in different databases to move. Space can be reclaimed (except for the DROP TABLE operation, where a table empty cannot be reclaimed by itself). Alter table TableName engine=innodb alter table TableName engine=innodb alter table TableName engine=innodb; Retract unused space. Using Turncate Table for InnoDB with innodb-plugin also shrinks the space. For tables that use separate tablespaces, no matter how they are dropped, tablespace fragmentation will not have a significant impact on performance, and there is still a chance to handle it.

Disadvantages:

single table increase is too large, when the single table occupies too much space, the storage space is insufficient, can only think about the solution from the operating system level, the maximum limit of table space is 64TB.

SHARED TABLESPACE:
If no separate tablespaces are enabled, they are all stored in IBDATA1. You can set its size and automatically expand it when it exceeds the limit size.
Advantages:
The

table space can be split into multiple files for each disk, so the table can be split into multiple files for each disk. The size of the table is not limited by the disk size. Data and files are put together for easy management.

Disadvantages:

all data and index to a file, while it is possible to put a large file into multiple small files, but multiple tables and indexes mixed stored in table space, when is a large amount of data, made a lot of delete table after table space will have a lot of space, especially for statistical analysis, for often delete operation of this kind of application the most weak Shared table space. Can’t bounce back after sharing a table space distribution: when there is a temporary indexed or create a temporary table operating table space is enlarged, is to delete related tables didn’t also the way to retract the part space (can be understood as oracle 10 g of table space, but only use 10 m, but the operating system shows the mysql table space for 10 g), database of cold standby is slow.

MySQL has a “double write” mechanism for writing data pages.
MySQL has a “double write” mechanism for writing data pages.
MySQL has a “double write” mechanism for writing data pages. The redo log records page operations at the physical level, and now the page is only 4KB written, which is itself a “faulty” page, so the redo log records the page writes in error. Thus, there is a double write: the pages are copied to the double write buffer, then the pages are written to the shared tablespaces in order, and finally a copy is written to the corresponding tablespaces.
Tokudb
When TOKUDB is started, it reads TOKUDB.DIRECTORY, organizes the table related files according to the key information, and writes them to the INFORMATION_SCHEMA. TOKUDB_FILE_MAP table.
Tokudb. directory defines table/index file information.
tokudb. Environment tokudb version number information.
tokudb.rollback undo record .
log000000000009 tokulog27 redo records.
tokudb_lock_dont_delete_me_* file lock ensures that the same datadir can only be used by one TokuDB process.

_test_table_name_key_name_45ca56_3_1b_b_0. tokudb index file
myisam_table.MYD table data
isam_table. MYI table index