MySQL shared table space and stand-alone table space

Data structures for InnoDB tables

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.

Shared tablespace: All the table data for a database, the index file is all in one file, the file path of the shared tablespace is in the data directory by default. The default file name is: Ibdata1 is initialized to 10M.

Exclusive tablespace: Each table will be generated in a separate file way for storage, 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, if for statistical analysis or a daily value table, delete a large amount of data can pass : ALTER TABLE TableName ENGINE=INNODB;
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"

650) this.width=650; "title=" image "border=" 0 "alt=" image "src=" http://images.cnblogs.com/cnblogs_com/myjavawork/ 201104/201104011600232470.png "width=" 428 "height=" style= "border:0px;background-image:none;padding-left:0px"; padding-right:0px;padding-top:0px; "/>

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 verify that the configured path directory does exist before you start the server. 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 in my.cnf at all, the default value is "dot" directory./, this means MySQL data directory.

So when doing data migration and backup, be sure to pay attention to the integrity of the data file.

This article is from the "believe it or not you" blog, please be sure to keep this source http://312461613.blog.51cto.com/965442/1407220