InnoDB this kind of engine, and the MyISAM engine difference is very big. In particular, its data storage format and so on.
For the data structure of INNODB, there are two conceptual problems to be solved first: the shared tablespace and the exclusive table space.
What are shared table spaces and exclusive table spaces
Shared tablespaces and exclusive tablespaces are all about how data is stored.
To share a table space:All the table data of a database, the index file is placed in a file, the default file path for this shared tablespace is in the data directory. The default file name is: Ibdata1 is initialized to 10M.
Exclusive table spaces:Each table will be generated in a separate file way for storage, and each table has a. frm table description file, and an. ibd file. This file includes the data content of a single table and the index content, which, by default, is stored in the table's location.
The pros and cons of the two
To share a table space:
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:
All the data and indexes are stored in a file that will have a very large file, although it is possible to divide a large file into smaller files, but multiple tables and indexes are stored in the table space, so there will be a lot of voids in the table space after a large number of deletions for a table, especially for statistical analysis, Such applications as day-value systems are most unsuitable for sharing table spaces.
Stand-alone tablespace: Set in the configuration file (MY.CNF):
innodb_file_per_table
Advantages:
1. Each table has a self-contained table space.
2. The data and indexes for each table will exist in the table space themselves.
3. You can implement a single table to move through different databases.
4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)
A) The drop table operation automatically reclaims the tablespace, and if you delete a large amount of data for a statistical analysis or a daily value table, you can pass: Altertable TableName Engine=innodb;Shrink the unused space.
b) The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.
c) For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to process it.
Disadvantages:
Single table increased too large, such as more than 100 g.
By comparison, the efficiency and performance of using exclusive tablespaces are a bit higher.
Conversion between shared tablespace and exclusive tablespace
Innodb_file_per_table conversions that are implemented by this parameter, if the off description uses exclusive tablespace "by default, the tablespace used is a shared table space"
innodb_file_per_table values to be modified, but not for shared table spaces that were previously used, unless manually modified or
Innodb_file_per_table=1 to use exclusive tablespace
Innodb_file_per_table=0 to use shared table spaces
Modify the data storage location for exclusive empty table spaces
Innodb_data_home_dir = "C:\mysql\data\"
Innodb_log_group_home_dir = "C:\mysql\data\"
Innodb_data_file_path=ibdata1:10m:autoextend
Innodb_file_per_table=1
Parameter Description:
This setting configures a single file with a scalable size of 10MB, named Ibdata1. The location of the file is not given, so the default is within the MySQL data directory. "Setting the data for initialization"
Innodb_data_home_dir represents the directory in which the database file is stored
Innodb_log_group_home_dir Directory for log storage
Innodb_file_per_table whether to use shared and exclusive table spaces to
Several of the above parameters must be joined together.
For some notes on the parameters where
InnoDB does not create a directory,so before you start the server, verify that the configured path directory does exist. This is true for any log file directory you have configured. Use the UNIX or DOS mkdir command to create any required directories.
InnoDB a directory path for each data file by simply deploying the value of Innodb_data_home_dir to the data file name and adding a slash or backslash where needed.
If the Innodb_data_home_dir option is not mentioned at all in My.cnf,The default value is the "dot" directory./, this means MySQL data directory.
A brief analysis of MySQL shared table space and the exclusive table space and the conversion between them
