InnoDB This engine is very different from the MyISAM engine. In particular, its data storage format and so on.
For a INNODB data structure, you first need to address two conceptual issues: shared table space and exclusive tablespace.
What is shared table space and exclusive table space
Shared table spaces and exclusive tablespaces are all about how data is stored.
To share a table space:All the table data of a database, the index files are all placed in a file, default this shared tablespace file path in the data directory. The default file name is: ibdata1 initialized to 10M.
Exclusive table space:Each table will be generated in a separate file format, each with a. frm table description file and an. ibd file. This file contains the data contents of a single table and the indexed content, and by default its storage location is in the table location.
Advantages and disadvantages between the two
To share a table space:
Advantages:
You can put the table space into multiple files on each disk (the size of the table space file is not limited by the size of the table, such as a table can be distributed on a different file). Data and files are put together for easy management.
Disadvantages:
All the data and indexes are stored in a file that thinks there will be a very large file, although a large file can be divided into small files, but multiple tables and indexes in the table space mixed storage, so for a table to do a large number of deletions after the table space will have a lot of voids, especially for statistical analysis, The application of daily value system is the most unsuitable for sharing table space.
Standalone tablespace: Set in configuration file (MY.CNF):
innodb_file_per_table
Advantages:
1. Each table has its own table space.
2. The data and indexes for each table exist in the table space of their own.
3. You can implement a single table to move in a different database.
4. Space can be recycled (except for the drop table operation, table null cannot be retrieved)
A The drop table operation automatically reclaims the table space, and if you delete a large amount of data for a statistical analysis or a daily value table, you can pass: Alter table TableName Engine=innodb;The space to shrink back.
b The use of Turncate table for the InnoDB of the Innodb-plugin also shrinks the space.
C for tables that use independent tablespaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too badly, and there is an opportunity to handle it.
Disadvantages:
A single table increases too large, such as more than 100 g.
In comparison, the efficiency and performance of using exclusive table space is higher.
Transformation between shared table spaces and exclusive tablespaces
Innodb_file_per_table the conversion by this parameter, if the off description uses an exclusive table space "By default, the table space used is shared tablespace"
innodb_file_per_table value to be modified, but it will not affect the shared table space used previously, unless manually modified or
Innodb_file_per_table=1 to use exclusive table space
Innodb_file_per_table=0 to use shared table space
to modify the location of a data store that has 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 an expandable size of 10MB, named Ibdata1. The location of the file is not given, so the default is in the MySQL data directory. "Settings for initialization of data"
Innodb_data_home_dir represents the directory that is stored for the database file
Innodb_log_group_home_dir for log storage directory
Innodb_file_per_table whether to use shared and exclusive tablespace to
Several of the above parameters must be joined together.
For some of the notes on the parameters of the place
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 the 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./, which means MySQL data directory.
Therefore, when doing data migration and backup, be sure to pay attention to the integrity of the data file.
