When using mysql, you may find that although a table deletes a lot of data, the data files and index files of the table are strangely small. This is because mysql will leave many data holes when deleting data (especially Text and BLOB), which will occupy the space of the original data, so the file size does not change. These holes may be reused in the future when data is inserted, and of course they may remain there. This hole not only increases the storage cost, but also reduces the scanning efficiency of the table due to data fragmentation.
Usage Scenarios:If you have deleted a large part of the table, or if you have made a lot of changes to a table with variable-length rows (one with VARCHAR, BLOB, or TEXT columns), you should use OPTIMIZE TABLE. Deleted records are kept in the linked list, and subsequent INSERT operations reuse the old record location. You can use OPTIMIZE TABLE to repurpose unused space and organize fragments of data files. [When a large amount of data is deleted from your library, you may find that the data file size has not decreased. This is due to the fragmentation left in the data file after the deletion operation.
In most settings, you don't need to run OPTIMIZE TABLE at all. Even if you make a lot of updates to variable-length rows, you don't need to run them often, once a week or once a month, only for specific tables.
OPTIMIZE TABLE only works for MyISAM, BDB, and InnoDB tables.
For BDB tables, the OPTIMIZE TABLE is currently mapped to the ANALYZE TABLE. For InnoDB tables, the OPTIMIZE TABLE is mapped to the ALTER TABLE, which rebuilds the table. Rebuild operations update index statistics and free up unused space in clustered indexes.
Note: MySQL locks the table during the OPTIMIZE TABLE run.
For myisam, you can directly use the optimize table table.name,When it is the InnoDB engine, it will report "Table does not support optimize, doing recreate + analyze instead", and under normal circumstances, if you convert from myisam to innodb, you will use alter table table.name engine='innodb' to convert, and you can also use this for optimization.Therefore, when it is the InnoDB engine, we can use alter table table.name engine='innodb' instead of optimize for optimization。
To view the before and after effects, you can use the show table status command, such as show table status from [database] like '[table_name]'; The data_free in the return result is the storage space occupied by the void.
The shell script that implements MySQL timed batch check table repair and optimize table optimize table is as follows:
Original:
The hyperlink login is visible.
The hyperlink login is visible.
|
Posted on 6/17/2023 11:40:34 AM
|
|
|
Landlord