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

Today, I will talk to you about the example analysis that MySQL takes up more and more space, which may not be understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Query back the table in your MySQL.

One day, I had a whim. Log in to Aliyun and find that RDS takes up a lot of space. Think of a table, the original design is not good, a lot of useless data. Just want to delete and clean up part of the data to save space.

Just do it, a delete statement, as follows:

Unexpectedly, after deleting hundreds of thousands of pieces of data, I went to check the data space occupied by RDS, which not only did not fall, but also increased. What's going on?

This problem may not have been noticed by many people. At ordinary times, everyone's attention may not be in this area.

After I deleted this data, Aliyun immediately sent an email to the police, saying that our RDS storage space was too large, there was no way, and the level of knowledge was limited. And then spent the company's money to upgrade the storage space.

This question puzzled me until I saw "High performance MySQL" two years ago. And today, another netizen wrote to me privately, and I thought of it. So, today, I would like to explain to you why this happened.

The first is what many people say on the Internet, this is a BUG of MySQL, which was not solved until version 5.7 of MySQL. Since we originally chose the version of MySQL 5.6, after this problem occurred, we also consulted Aliyun. However, the answer given by Aliyun is that data migration can only be carried out. Although the MySQL5.7 version can solve this problem, the MySQL5.6 version of Aliyun cannot be directly upgraded to 5.7. And the migration of the database is also a waste of time and energy, otherwise, the main data will be lost. BOSS has to chop me up.

The second thing I want to say is that independent tablespaces are the default in MySQL 5.6. if the independent table storage mode is used, files such as report_site_day.ibd (storing data information and index information) will also be generated in data. When it is deleted, it will not be deleted.

The third is that the underlying implementation of the deleted function in MySQL is not what people think, and the execution of delete is really deleted. But when you use delete to delete, MySQL does not delete the data file, but removes the identification bit of the data file, does not organize the file, so it will not completely free up space. The deleted data will be saved in a list of links, and when new data is written, MySQL will use the deleted space to rewrite. That is, the delete operation will bring some data fragments, which are taking up the hard disk space.

There are also some problems with data migration, which we'll talk about in the next chapter. Today I'll start with the OPTIMIZE TABLE command officially recommended by MySQL to clean up and optimize the table InnoDB space.

The format of the OPTIMIZE TABLE command is as follows:

The specific usage is as follows:

Alternatively, you can batch generate and optimize the SQL of all tables, and then copy them for execution:

In addition, we can also set the table to be stored in a single table, so that the data in each table is stored separately, and when the data is deleted, the storage space is released as the data is deleted.

As for data migration, we can back up, delete, and then import.

After reading the above, do you have any further understanding of the example analysis in which MySQL takes up more and more space? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.