MySQL 数据库碎片优化、整理方法详细步骤图解教程

起因：查看线上数据库中Table Information时发现有一个日志表数据大小和索引大小有915M，但实际行数只有92行。该表需要频繁插入并且会定时去删掉旧的记录。表类型为Myisam，已建立一个索引，所以应该是产生了大量碎片，使用 Optimize table 表名 优化后大小变为2.19M，少了很多， 同时可以看出该表上的索引建的多余，因为插入操作比查询操作要多很多，而且查询不多，查询的数据量也一般比较小。借此延伸下MYSQL中Myisam、InnoDB碎片优化方式：Myisam清理碎片 OPTIMIZE TABLE table_nameInnoDB碎片优化 if you frequently delete rows (or update rows with variable-length data types), you can end up with a lot of wasted space in your data file(s), similar to filesystem fragmentation.If you’re not using the innodb_file_per_table option, the only thing you can do about it is export and import the database, a time-and-disk-intensive procedure.But if you are using innodb_file_per_table, you can identify and reclaim this space!Prior to 5.1.21, the free space counter is available from the table_comment column of information_schema.tables. Here is some SQL to identify tables with at least 100M (actually 97.65M) of free space:SELECT table_schema, table_name, table_comment FROM information_schema.tables WHERE engine LIKE ‘InnoDB’ AND table_comment RLIKE ‘InnoDB free: ([0-9]{6,}).*‘;Starting with 5.1.21, this was moved to the data_free column (a much more appropriate place):SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE ‘InnoDB’ AND data_free > 100*1024*1024;