Note
The tablespace fragmentation discussed in this section refers to InnoDB tablespace fragmentation.
Reasons
Tablespace fragmentation may be caused by the following reasons:
A record has been deleted and the associated space cannot be reused.
A record has been updated (usually become longer) and the associated space cannot be reused.
Page splits occur when an insert operation is performed. This causes low page utilization.
Influence
The influence of tablespace fragmentation is as follows:
A waste of disk space.
Reduced I/O performance during query execution.
If the tablespace is not highly fragmented, we recommend that you do not perform any operations to remove fragmentation.
Methods to reclaim wasted space
A common method to remove fragmentation is using the OPTIMIZE TABLE command to reorganize the table. When you execute this command, write operations to the table are not allowed and the database workload increases. Exercise caution when you perform this operation. We recommend that you execute this command during off-peak hours.
You can also use the ALTER TABLE command to reclaim wasted space. Exercise caution when you perform this operation. We recommend that you execute this command during off-peak hours.