mysql 碎片弊端_Mysql：innodb：File-Per-Table Tablespace：最大的弊病（每次增长仅仅4M）：碎片、慢！...

Disk space is returned to the operating system after truncating or dropping a table created in a file-per-table tablespace. Truncating or dropping a table stored in a shared tablespace creates free space within the shared tablespace data file, which can only be used for InnoDB data. In other words, a shared tablespace data file does not shrink in size after a table is truncated or dropped.

A table-copying ALTER TABLE operation on a table that resides in a shared tablespace can increase the amount of disk space occupied by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. This space is not released back to the operating system as it is for file-per-table tablespaces.

TRUNCATE TABLE performance is better when executed on tables that reside in file-per-table tablespaces.

File-per-table tablespace data files can be created on separate storage devices for I/O optimization, space management, or backup purposes. See Section 15.6.1.2, “Creating Tables Externally”.

You can import a table that resides in file-per-table tablespace from another MySQL instance. See Section 15.6.1.3, “Importing InnoDB Tables”.

Tables created in file-per-table tablespaces support features associated with DYNAMIC and COMPRESSED row formats, which are not supported by the system tablespace. See Section 15.10, “InnoDB Row Formats”.

Tables stored in individual tablespace data files can save time and improve chances for a successful recovery when data corruption occurs, when backups or binary logs are unavailable, or when the MySQL server instance cannot be restarted.

You can backup or restore tables created in file-per-table tablespaces quickly using MySQL Enterprise Backup, without interrupting the use of other InnoDB tables. This is beneficial for tables on varying backup schedules or that require backup less frequently. See Making a Partial Backup for details.

File-per-table tablespaces permit monitoring table size on the file system by monitoring the size of the tablespace data file.

Common Linux file systems do not permit concurrent writes to a single file such as a shared tablespace data file when innodb_flush_method is set to O_DIRECT. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with this setting.

Tables in a shared tablespace are limited in size by the 64TB tablespace size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides plenty of room for individual tables to grow in size.