Shulou(Shulou.com)06/01 Report--

By default, InnoDB stores all the table data of the database InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. When adding or deleting a database, the ibdata1 file will not automatically shrink, and the backup of a single database will become a problem. Usually the data can only be exported using mysqldump and then imported to solve this problem.

In the [mysqld] section of the MySQL configuration file, add the innodb_file_per_table parameter.

You can change the InnoDB to an independent tablespace schema, and each table in each database generates a data space.

Independent tablespaces:

Advantages:

1. Each table has its own independent tablespace.

2. The data and indexes of each table will be stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be recycled (table emptiness cannot be recycled by itself except for drop table operation)

A) the Drop table operation automatically reclaims tablespaces. For statistical analysis or daily value tables, you can delete a large amount of data through: alter table TableName engine=innodb; to retract unused space.

B) using turncate table for Innodb of innodb-plugin also shrinks the space.

C) for tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of the tablespaces will not seriously affect performance, and there is still a chance to deal with them.

Disadvantages:

Single table increases too much, such as more than 100 gigabytes.

Conclusion:

Shared tablespaces have few advantages in Insert operations. Nothing else performs as well as independent tablespaces. When enabling independent tablespaces, make a reasonable adjustment: innodb_open_files.

InnoDB Hot Backup (cold standby) tablespace cp will not face a lot of useless copy. And the use of innodb hot backup and table space management commands can achieve single-show movement.

1.innodb_file_per_table settings. Opening method:

Set under [mysqld] in my.cnf

Innodb_file_per_table=1

two。 Check to see if it is on:

Mysql > show variables like'% per_table%'

3. Turn off exclusive tablespace

Innodb_file_per_table=0 closes separate tablespaces

Mysql > show variables like'% per_table%'

Restrictions on Innodb tables

A table cannot contain more than 1000 columns.

The internal maximum key length is 3500 bytes, but MySQL itself limits this to 1024 bytes.

Except for the VARCHAR, BLOB, and TEXT columns, the maximum row length is slightly less than half of the database page. That is, the maximum line length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, the total row length, the page includes BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 768 bytes of the VARCHAR,BLOB or TEXT column in the row, and the rest of the stored scattered pages.

Although InnoDB internally supports a row size greater than 65535, you cannot define a row with a VARCHAR column that merges a size greater than 65535.

The merge size of the nnoDB log file must be less than 4GB.

The smallest tablespace size is 10MB. The largest table space size is 4000000000 database pages (64TB). This is also the maximum size of a watch.

InnoDB table does not support FULLTEXT index

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.