MySQL disk fragmentation cleaning

Cause of disk fragmentation

Why does disk fragmentation occur? That’s because a table if often insert and delete data, is bound to generate a lot of unused blank space, these pieces of blank space is discontinuous, so over time, the table will occupy a large space, but in fact the record number is little, so will not only waste of space, and query speed is slower.

View information about all tables whose disk fragmentation space usage [date_free] is greater than 0 except system tables

-- Query information about all tables except system tables whose datA_FREE value is greater than 0

SELECT
    concat(TABLE_SCHEMA,'. ',TABLE_NAME)                                         AS database_name,
    concat(TRUNCATE(SUM(DATA_LENGTH+DATA_FREE+INDEX_LENGTH)/1024/1024.2),' MB') AS total_size,
    concat(TRUNCATE(SUM(DATA_LENGTH)/1024/1024.2),' MB')                        AS data_size,
    concat(TRUNCATE(SUM(DATA_FREE)/1024/1024.2),' MB')                          AS data_free,
    concat(TRUNCATE(SUM(INDEX_LENGTH)/1024/1024.2),'MB')                        AS index_size,
    ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema'.'mysql')
    AND data_free>0
GROUP BY
    TABLE_NAME,
    TABLE_SCHEMA,
    ENGINE
ORDER BY
    data_free DESC
;
Copy the code

Two ways to clean up disk debris

  • OPTIMIZE TABLE name;
OPTIMIZE TABLE only works with MyISAM, BDB and InnoDB tables
OPTIMIZE TABLE tb_task_log;
Copy the code

The result will be “Table does not support optimize, Doing math + Analyze instead”, which is fine, actually the disk fragmentation has been removed.

  • ALTER TABLE ENGINE = Innodb;(justInnoDBThe table can do this
ALTER TABLE tb_operational_log engine=InnoDB;
Copy the code

* * attention! The OPTIMIZE operation temporarily locks tables, and the larger the volume, the longer it takes, it’s not a simple query. ** So putting the OPTIMIZE command into an application is not a good idea, no matter how low the hit rate is, it will increase as the number of visits increases, which will certainly affect the efficiency of the application. A good way to do this is to run a shell that periodically checks the information_schema.TABLES field in mysql, check the DATA_FREE field, and start the script if it is greater than 0.