OPTIMIZE TABLE said, "Only when DELETE lines create a lot of wasted space."
How to interact with the InnoDB table for adults
For example, suppose DELETE and INSERT are performed the same number of times, and there is a table with a high metabolism with a constant number of rows.Do you need OPTIMIZE TABLE at this time?
I was not sure if INSERT would overwrite the wasted space generated by DELETE with a new line, or if it would never be available until OPTIMIZE TABLE, or otherwise.
For example, the explanation of the following article is detailed.To put it bluntly...
DELETE removes it temporarily, but it does not mean it will not be reused permanently.DELETE, running OPTIMIZE TABLE can improve performance and free disk space.Optimize tables with the 35th OPTIMIZE TABLE: MySQL Road Construction News
I deleted all cases using DELETE syntax. (omitted) You can see that the file size has not changed.
In addition to the DELETE syntax, there is also a TRUNCATE syntax for data deletion.If you use this to delete tables in bulk, the conversation will change and the disk space will be automatically freed.
Now, if you delete the data using the DELETE syntax in the preceding paragraph, you can see that the disk space has not been freed even though it has been deleted from the database.InnoDB actually uses and reuses the area it has, but fragmentation will inevitably occur after a large number of deletions are made at once.If you want to optimize your data area, use OPTIMIZE TABLE.
Understanding MySQL Physical Deletion Degrades and Workarounds
In the fragmented table, a missing record was selected and statistics were updated, possibly causing extreme performance degradation.
(omitted)
The performance of the query was improved by reconstructing the space in the physical file containing the table so that only real records are present.
© 2024 OneMinuteCode. All rights reserved.