Shulou(Shulou.com)05/31 Report--

This article is about MySQL innoDB independent tablespaces and shared tablespaces what are the advantages and disadvantages, the editor feels very practical, so share to you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

Advantages of independent tablespaces:

1. Each table has its own independent table space.

two。 The data and indexes of each table are stored in its own tablespace.

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

4. Space can be reclaimed (tablespaces can not be automatically recycled in drop/truncate table mode)

5. For tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of the tablespace will not seriously affect performance, and there is still a chance to deal with it.

Disadvantages:

The increase of a single table is greater than that of shared space.

Advantages of shared tablespaces:

1) the tablespace can be divided into multiple files and stored on each disk (the size of the tablespace file is not limited by the table size, for example, a table can be distributed on different files).

So its size limit is no longer a file size limit, but its own limit. As you can see from the official documentation of Innodb, the maximum limit of its tablespace is 64TB

In other words, the single table limit of Innodb is basically around 64TB, and of course this size includes all indexes of the table and other related data.

2) Table data and table description are put together for easy management.

Disadvantages:

All the data and indexes are stored in one file, and there will be a very large file. Although a large file can be divided into multiple small files, multiple tables and indexes are in the table space.

Mixed storage in, so that there will be a lot of gaps in the tablespace after a large number of deletions for a table, especially for statistical analysis, applications such as logging systems are the least suitable to use shared tablespaces.

Summary: MySQL database is composed of N multiple tables, each table has its own table definition file .frm file, table index file, table data file. The index file and data file of INNODB are together, and the MYISAM is separate.

For tablespaces, INNODB is divided into shared and exclusive tablespaces. In the case of sharing, the data of all tables are stored in a file, and in the exclusive case, each table stores the data of this table in a separate file.

These are the advantages and disadvantages of MySQL innoDB independent tablespaces and shared tablespaces. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.