How to Detect and Fix Table Fragmentation in MySQL for Better Performance
This article explains how to identify table and index fragmentation in MySQL using the Data_free column, and provides two effective repair methods—altering the table engine and running OPTIMIZE TABLE—while warning about resource consumption and recommending a monthly maintenance schedule.
During prolonged data modifications, both index and data files can develop holes and fragmentation, which degrades index performance.
To check for fragmentation, run SHOW TABLE STATUS LIKE 'table_name'; and examine the Data_free column; a value greater than 0 indicates fragmentation.
Two common repair methods are:
Execute ALTER TABLE xxx ENGINE=InnoDB; (or MyISAM). Even if the engine is already InnoDB, this command rebuilds the table and removes fragmentation.
Run OPTIMIZE TABLE table_name; to reorganize the data files.
Both approaches rewrite the data files to align them, but they consume considerable resources, so they should not be performed frequently; a monthly schedule is recommended.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Java High-Performance Architecture
Sharing Java development articles and resources, including SSM architecture and the Spring ecosystem (Spring Boot, Spring Cloud, MyBatis, Dubbo, Docker), Zookeeper, Redis, architecture design, microservices, message queues, Git, etc.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.