What is a MySQL tablespace?

The topic I want to share with you today is: "What exactly is the table space that everyone often talks about? What exactly is a data table?"

This is actually a conceptual knowledge point, which should be regarded as expanding knowledge. You just need to understand the concepts involved and leave an impression of the parameters involved.

1. What is a table?

Anyone who has used MySQL knows that, intuitively, MySQL data is stored in data tables.

For example, an Update SQL:

update user set username = 'Daydream' where id = 999;

It changes the username column of the record with id 1 in the user data table to 'Daydream'

The user here is actually a data table. Of course this is not the point. The point I want to express is that the data table is actually a logical concept. The table space discussed below is a physical concept.

2. What is a tablespace?

I don’t know if you have ever seen this sentence: “In the innodb storage engine, data is organized and stored according to tablespaces.” In fact, there is a subtext: the tablespace is a tablespace file that is an actual physical file.

You don’t need to worry about why it is called a tablespace or why the tablespace corresponds to a physical file on the disk, because MySQL is designed and set in this way. Just accept the concept directly.

MySQL has many types of tablespaces. Let’s take a look at them together.

3. sys tablespace

You can view your MySQL system tablespace like this:

The Value part consists of: name:size:attributes

By default, MySQL will initialize a file named ibdata1 with a size of 12MB, and it will automatically expand as the data increases.

This ibdata1 file is the system tablespace, the default tablespace, the default tablespace physical file, and the legendary shared tablespace.

Regarding this shared tablespace, intuitively, if this tablespace can store data for multiple tables, then it can be called a shared tablespace, so you can think of the system tablespace as a shared tablespace.

4. Configure the sys tablespace

The number and size of the system tablespace can be set by the startup parameter: innodb_data_file_path

# my.cnf
[mysqld]
innodb_data_file_path=/dir1/ibdata1:2000M;/dir2/ibdata2:2000M:autoextend

5. File per table tablespace

If you want each database table to have a separate tablespace file, you can set it through the parameter innodb_file_per_table.

This parameter is available only in MySQL 5.6 or higher.

Through the configuration file

[mysqld]
innodb_file_per_table=ON

You can also use the command

mysql> SET GLOBAL innodb_file_per_table=ON; 

Let you set it to ON, then the tables generated by the InnoDB storage engine will have their own independent tablespace files.

Independent tablespace file naming rules: table name.ibd

Notice:

An independent tablespace file only stores the data, index, and insert buffer bitmap corresponding to the table.

The rest of the information, such as undo information, insert buffer index page, double write buffer, etc., is still placed in the default tablespace, that is, the shared tablespace.

It doesn't matter if you don't understand what the undo, insert buffer, and double write buffer are. According to the outline schedule, I will share with you in the 41st and 42nd articles. Here you just need to understand that even if you set innodb_file_per_table=ON, the size of the shared tablespace will continue to grow, and even if you continue to use undo to rollback, the size of the shared tablespace will not shrink.

Check out my tablespace files:

Finally, let's briefly describe the advantages and disadvantages of this file per table:

advantage:

• Improve the fault tolerance. When the tablespace of table A is damaged, other tablespaces will not be affected. s
• Use MySQL Enterprise Backup to quickly back up or restore tables created in file-per-table tablespaces without disrupting the use of other InnoDB tables
  

shortcoming:

It is not friendly to the fsync system call. If you use a tablespace file, a single system call can complete the data flushing, but if you split the tablespace file into multiple ones. The original fsync may become an fsync executed for all tablespace files involved, increasing the number of fsyncs.

6. Temporary table space

The temporary tablespace is used to store temporary tables created by users and internal temporary tables on disk.

The parameter innodb_temp_data_file_path defines some of the name, size, and specification attributes of the temporary tablespace as shown below:

View the directory where temporary tablespace files are stored

7. Undo tablespace

I believe you must have heard of undolog. When your program wants to rollback a transaction, the underlying MySQL actually helps you roll back through these undo information.

In the MySQL settings, there is a table space that can be used specifically to store undolog log files.

However, in the MySQL settings, undolog is placed in the system tablespace by default.

If your MySQL is newly installed, you can view the usage of your MySQL undo tablespace by running the following command:

As you can see, I have two undo log tablespaces for MySQL.

That is, my undo is transferred from the default system tablespace to the undo log dedicated tablespace.

So should the undo log be placed in the system tablespace using the default configuration? Or should it be placed in the undo tablespace?

This actually depends on the type of storage volumes used by the server.

If SSD storage is used, it is recommended to store undo info in the undo tablespace.

The above is the detailed content of what MySQL tablespace is. For more information about MySQL tablespace, please pay attention to other related articles on 123WORDPRESS.COM!