In this lesson, you will learn how to identify and remediate index fragmentation in MySQL and MariaDB to recover storage and restore query performance.
The Mechanics of Index Bloat
In MySQL/MariaDB (using the InnoDB storage engine), indexes are structured as B-Trees. When you perform frequent INSERT, UPDATE, or DELETE operations on a table, the B-Tree pages must be split or shifted to accommodate new data. Over time, this leaves "empty" holes within the index pages—a phenomenon known as index bloat or fragmentation. Even if your logical row count stays the same, the physical size of your indexes grows, consuming unnecessary disk I/O and cache space.
Identifying the Problem
You can check for potential bloat by querying the information_schema.tables table. If the DATA_FREE column (which shows the number of allocated but unused bytes) is significantly high, your indexes likely need a cleanup.
SELECT TABLE_NAME, DATA_FREE / 1024 / 1024 AS data_free_mb
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'your_database_name';
Rebuilding Indexes
To eliminate fragmentation, you must perform an "index rebuild." The most effective way to do this in MySQL is to run an OPTIMIZE TABLE command. This command rebuilds the table and its associated indexes by recreating the B-Tree structure, effectively packing the data tightly and reclaiming the empty space.
-- Rebuild the table and all its indexes
OPTIMIZE TABLE your_table_name;
A note on performance: In production environments, OPTIMIZE TABLE can be resource-intensive and may lock your table depending on your configuration. In modern versions of MySQL/MariaDB, this operation is often performed using the ALGORITHM=INPLACE method, which allows for concurrent reads and writes, but you should always monitor your I/O and replication lag before running this on high-traffic tables. If you are using a clustered environment, remember that these maintenance tasks should be managed via your established traffic shaping procedures to prevent performance degradation across your nodes.
Key Takeaway: Index bloat occurs when data modifications leave sparse pages in your B-Trees; regular maintenance via OPTIMIZE TABLE reclaims this space and optimizes B-Tree page density for better performance.