Shared tablespace and standalone tablespace, Frm,myd,myi.idb,par file description

I. Shared tablespace and stand-alone table space
MySQL5.5 default is a shared tablespace, 5.6, which is a standalone table space by default.

To share a table space:
Ibdata1 is a shared tablespace for InnoDB, and the default configuration is to store all tablespaces in ibdata1, resulting in an unlimited growth of ibdata1 files to be released.
Advantages:
You can put the table space into multiple files on each disk (the Tablespace file size is not limited by the size of the table, such as a table can be spread over the files on the different steps). Data and documents are easily managed together.
Disadvantages:
1. There will be a lot of voids in the table space after a large number of deletions, especially for statistical analysis, such as day-value systems, which are most unsuitable for shared table spaces.
2. Shared table space management will not be able to shrink the table space after the problem, when the temporary build index or create a temporary table of the Operation table space expanded, is to delete the related table and can not shrink back that part of the space. This can only be done by building a new slave dump from the main repository, and then in the dump to the slave library, the action is larger.
3. For InnoDB Hot backup operations (or direct cold), the file that requires CP is larger each time. If there is now a 180G tablespace, but the actual data is only more than 50 g, then we will face each need to copy 180G data.

Stand-alone table space:
Set in configuration file (MY.CNF): Innodb_file_per_table is on
Advantages:
1. Each table has a self-contained table space.
2. You can use the file system (for example, the LS command) to evaluate the disk space occupied by a data table.
3. You can implement a single table to move through different databases.
4. When this parameter is on, the compression characteristics of the InnoDB engine
4. Drop table automatically reclaims the tablespace, delete a large amount of data can be through ALTER TABLE XX engine = InnoDB; Reclaim space
Disadvantages:
Single table increased too large, such as more than 100 g.
For single-table growth problems, if you use shared tablespace can separate files, but there is also a problem, if the scope of access is too large to access multiple files, it will be slower. There is also a workaround for stand-alone table spaces: Using partitioned tables, you can also move that large table space to a different space and then make a connection. In fact, from a performance point of view, when a table more than 100 g may respond is also slower, for the independent table space is also easy to find problems early processing.

Ii. Description of Frm,myd,myi.idb,par documents
such as database A, table B.
1. If Table B uses Myisam,data\a, 3 files will be generated:
B.FRM: Description Table structure file, field length, etc.
B.myd (MYData): Data information file, storing data information (if independent table storage mode is used)
B.myi (myindex): Index information file.

2. If Table B uses innodb,data\a, it will produce 1 or 2 files:
B.FRM: Description Table structure file, field length, etc.
B.IBD files (storing data information and index information) are also generated in data\a if the standalone table storage mode is used
Data information and index information are stored in IBDATA1 if shared-storage mode is used

If partitioned storage is used, there will also be a B.par file (for storing partition information) in Data\a

Shared tablespace and standalone tablespace, Frm,myd,myi.idb,par file description