The engine bitsCN. cominnodb is different from the MYISAM engine. In particular, its data storage format.
For the innodb data structure, we must first solve two conceptual problems: the shared tablespace and the exclusive tablespace.
What are shared and exclusive tablespaces?
Shared and exclusive tablespaces are used for data storage.
Shared tablespace:All the table data of a database is stored in one file, and the file path of the shared tablespace is under the data Directory by default. The default file name is: ibdata1 initialized to 10 M.
Exclusive tablespace:Each table is generated and stored in an independent file. Each table has a. frm table description file and a. ibd file. The file contains the data content and index content of a single table. by default, the file is stored in the table.
Advantages and disadvantages
Shared tablespace:
Advantages:
The tablespace can be divided into multiple files and stored on each disk (the tablespace file size is not limited by the table size, for example, a table can be distributed on non-synchronous files ). Put data and files together for convenient management.
Disadvantages:
All the data and indexes are stored in one file to store a very large file. although a large file can be divided into multiple small files, however, multiple tables and indexes are stored in a table space in a hybrid manner. after a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, especially for statistical analysis, applications such as the daily value system do not use the most shared tablespace.
Independent tablespace: in the configuration file (my. cnf), set:
Innodb_file_per_table
Advantages:
1. Each table has its own independent tablespace.
2. Data and indexes of each table are stored in its own tablespace.
3. a single table can be moved in different databases.
4. space can be recycled (except for the drop table operation, the table cannot be recycled if it is empty)
A) the Drop table operation automatically recycles tablespaces. for statistical analysis or daily value tables, you can use alter table TableName engine = innodb to delete a large amount of data and reduce unnecessary space.
B) using turncate table for innodb-plugin will also shrink the space.
C) for tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.
Disadvantages:
The increase in a single table is too large, for example, more than 100 GB.
In comparison, the efficiency and performance of using an exclusive tablespace are higher.
Conversion between shared and exclusive tablespaces
Innodb_file_per_table is converted using this parameter. if it is OFF, it indicates that the exclusive tablespace is used. [by default, the tablespace used is a shared tablespace]
The innodb_file_per_table value can be modified, but it does not affect the previously used shared tablespace unless it is manually modified or
Innodb_file_per_table = 1 is the use of exclusive tablespace
Innodb_file_per_table = 0 indicates the use of the shared tablespace.
Modify the data storage location of an exclusive empty tablespace
Innodb_data_home_dir = "C:/mysql/data /"
Innodb_log_group_home_dir = "C:/mysql/data /"
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_file_per_table = 1
Parameter description:
This configuration configures a single file with an expandable size of 10 Mb, named ibdata1. No file location is provided, so it is in the MySQL data directory by default. [Data initialization settings]
Innodb_data_home_dir indicates the directory where database files are stored.
Innodb_log_group_home_dir is the log storage directory.
Whether innodb_file_per_table uses shared and exclusive tablespace
The preceding parameters must be added together.
Notes for parameters
InnoDB does not create a directory. Therefore, before starting the server, make sure that the "configured path directory" exists. This is true for any log file directory you configured. Use the mkdir command of Unix or DOS to create any required directories.
By deploying the value of innodb_data_home_dir to the data file name in the original format, and adding a slash or backslash as needed, InnoDB forms a directory path for each data file.
If the innodb_data_home_dir option is not mentioned in my. cnf, the default value is the "dot" directory./, which means the MySQL data directory.
Therefore, when porting and backing up data, you must pay attention to the integrity of data files.
BitsCN.com
