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

This article is about how MySQL implements tablespace recycling. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Pre-instruction

At present, most MySQL databases use the InnoDB engine, so unless otherwise specified, the examples in this paper are based on the InnoDB engine.

There is a configuration item in the MySQL configuration called innodb_file_per_table after it is set to 1

The data for each table is stored separately in a file with the suffix .ibd.

If innodb_file_per_table is not turned on

The data of the table is stored in the shared tablespace of the system, so that even if the table is deleted, the shared tablespace will not release this part of the space.

Therefore, in general, the innodb_file_per_table option is set to 1, and in order to visually see the size change of the table data file, the examples in this paper are also based on the fact that this option is enabled.

Problem recurrence

Create a new table, ta, with the following structure

Mysql > show create table ta\ gateway * 1. Row * * Table: taCreate Table: CREATE TABLE `ta` (`id` int (11) NOT NULL, `ia` int (11) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Use the following stored procedure to bulk insert data into ta

Delimiter / / create procedure multinsert (in beg int,in cnt int) begin declare icnt int default 0; declare tmp int default 0; while icnt

< cnt do set icnt = icnt + 1; set tmp = beg + icnt; insert into ta(id,ia) values(tmp,tmp); end while;end//delimiter ; 在MySQL控制台执行 call multinsert(0,100000) 命令，往 ta表插入10万条数据 mysql>

Call multinsert (0Jing 100000); mysql > select count (*) from ta;+-+ | count (*) | +-+ | 100000 | +-+ 1 row in set (0.02 sec)

View the size of the data file ta.ibd of the ta table on disk

[root@ecs-centos-7 test] # cd / var/lib/mysql/test/ [root@ecs-centos-7 test] # ls-l ta.ibd-rw-r- 1 mysql mysql 11534336 January 3 23:14 ta.ibd

As you can see from the above results, after inserting 100000 pieces of data into the ta table, the ta.ibd size is 11534336 bytes (about 11m).

Now we use the delete command to delete half of the child table data (50, 000 rows of records)

Mysql > delete from ta where id between 1 and 50000 row in set query OK, 10000 rows affected (0.03 sec) mysql > select count (*) from ta;+-+ | count (*) | +-+ | 50000 | +-+ 1 row in set (0.02 sec)

After the delete operation is complete, check the size of the ta.ibd on the disk again

[root@ecs-centos-7 test] # cd / var/lib/mysql/test/ [root@ecs-centos-7 test] # ls-l ta.ibd-rw-r- 1 mysql mysql 11534336 January 3 23:14 ta.ibd

As you can see from the above results, after half of the ta table is deleted, that is, 50, 000 rows of data, the size of the ta.ibd is 11534336 bytes (about 11m).

That is to say, before and after the ta table deletes the data, the table data file on the disk has not shrunk.

To understand why the data file is not shrunk, you need to have an in-depth understanding of how to delete the data.

Principle of deleting data

As we all know, the data in InnoDB is organized by B + tree. For knowledge of B + tree, please refer to understanding B + tree.

Figure (1)

Above is the index diagram of InnoDB, in which the nodes framed by dotted lines belong to Page1 data pages, and the leaf nodes store the corresponding data of the index, which form an ordered array according to the order of the index from small to large.

If we want to delete the data in the Page1 page with an index key value of 13, that is, the red part of the figure above

The InnoDB engine marks the node with an index key value of 13 as deleted. It does not reclaim the real physical space of the node, but marks it as the deleted node, which can be reused later. Therefore, if the table record is deleted, the data file on the disk will not shrink.

You might say that the above only deletes the data of one node in the Page1 page, so if you delete all the node data in the Page1 page, it should reclaim the space in the Page1 page, right?

The answer is, no recycling.

When all the data on the Page1 page is deleted, the entire data page is marked as deleted and the entire data page can be reused, so in this case, the data file on disk still does not shrink.

Data reuse

Data reuse involves operations such as insertion, deletion, transfer of data nodes and merging of data pages. For details related to the operation process, please refer to understanding B + Tree, which will not be repeated here.

Reuse of data nodes

In figure (1) above, when a node with an index key value of 13 is deleted, it is marked as reusable

If a record with an index key value between 7 and 18 is then inserted, the data node with the original index key value of 13 will be reused

However, if the index key value of the inserted record is not between 7 and 18, the data node with the original index key value of 13 may not be reused.

In other words, the reuse of data nodes requires the index key value to meet certain range conditions.

Reuse of data pages

In figure 1, when all the data nodes of the Page1 data page are deleted, the whole Page1 page is reusable, and when the inserted record needs to use a new page, the Page1 can be reused.

When the utilization of adjacent data pages is low, it is possible to merge them into one of the data pages. At this time, the other data page is empty and the empty data page becomes reusable.

What actions will cause data holes?

After we delete a record with the delete command, InnoDB simply marks the corresponding data nodes as deleted and reusable, and these vacant data nodes waiting to be used can be seen as data holes.

Delete data

When you delete data, it will create a data hole, which has been explained earlier, and I will not repeat it here.

Insert data

If the data is inserted in order of index size, the data page is compact and there are no data holes.

If it is inserted from the middle of the index, it may cause the page to split, and the split page may have data holes. The following figure is an example of page splitting caused by insertion.

As shown in the figure, the leaf page is full before the split, and the data is arranged very tightly.

Now insert a data with an index key value of 15, and after insertion, the Page1 page is split into two Page1,Page2 pages in the figure above

After the split, there are two holes in the Page1 page, the two data nodes are reusable, and the Page2 page is just full

Update data

Updating data can be seen as deleting and then inserting, and it is also possible to create data holes.

For example, id is the primary key of the table ta, and the statement update ta set id = 10 where id = 1 changes id = 1 to id = 10, which is equivalent to deleting the record of id = 1 and then inserting the record of id = 10. This situation will create data holes.

However, statements like update ta set ia = ia + 1 where id = 1 that do not change the primary key value will not create a hole.

Therefore, updating data may create data holes.

To sum up, table addition, deletion and modification operations may cause data holes, while online services will do a large number of table additions, deletions and changes, and data holes are more likely to exist.

How to shrink tablespaces

Since a table, after a large number of irregular addition, deletion and modification operations, it will produce a large number of data holes.

So if we create a new table with the same structure as the original table with empty data, and then insert the data from the old table into the new table in ascending order of index, after all the data from the old table is inserted into the new table, delete the old table and rename the new table to the name of the old table.

Because the leaf node data in the new table is added sequentially, the page is very compact, the page utilization is very high, and fewer pages are needed than the old table, so the holes in the index in the old table do not exist in the new table. the disk space occupied by the new table data file will naturally be reduced, thus achieving the purpose of shrinking the table space.

The following methods for shrinking table spaces are different, but the basic principles are all achieved by rebuilding the table.

Truntace table table name

This operation is equal to drop + create. Delete the table first, and then create a new table with the same name. Of course, you need to save the data of an old table before executing the truncate table command, and then import this data into the new table after the command is executed.

Alter table table name engine=InnoDB

The operation is to traverse the data page of the old table primary key index, generate a B + tree structure of the records in the data page, store it in a temporary file on disk, and replace the data file of the old table with a temporary file after traversing the data page.

Since the MySQL5.6 version, this operation is Online DDL. It should be noted that this method requires scanning table data files, which is very time-consuming for large tables. If it is for online services, you need to avoid the business peak and be careful.

Note:

When rebuilding the table, InnoDB will not fill the whole table, leaving about 10% of the data nodes on each page for subsequent updates, that is to say, it is not the most compact after rebuilding the table.

Suppose there is a process of rebuilding table t

Insert part of the data, but the inserted data uses up part of the reserved space

In this case, if table t is rebuilt again, it may take up more space after rebuilding the table than before.

Thank you for reading! This is the end of the article on "how to achieve tablespace recycling in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.