Mysql 如何统计估计/解决InnoDB表中页面的碎片空间量

在InnoDB上，碎片可能是一个问题。DBA希望观察这个表实际上是否有碎片。InnoDB没有一种直接的方法来估计碎片空间量。本文档描述了如何使用信息模式来估计碎片空间的大小。

information_schema.TABLES

information_schema.TABLES中的值，是存储在磁盘上的表的统计信息。

SELECT TABLE_SCHEMA as `db`, TABLE_NAME as `tbl`, 
1-(TABLE_ROWS*AVG_ROW_LENGTH)/(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) AS `fragment_pct`,
TABLE_ROWS
FROM information_schema.TABLES WHERE 
TABLE_SCHEMA = 'greatsql' AND TABLE_ROWS >= 10000 ORDER BY fragment_pct DESC, TABLE_ROWS DESC;

information_schema.INNODB_BUFFER_PAGE

information_schema.INNODB_BUFFER_PAGE只是一个估计，计算缓存页面中的值。因为如果不访问实际表空间中的全部页，就不可能准确计算碎片空间量

SELECT
    table_name, 100 - (AVG(data_size) / @@innodb_page_size * 100) AS FRAGMENT_PCT, COUNT(*)
FROM
    information_schema.INNODB_BUFFER_PAGE
WHERE
    table_name IS NOT NULL
GROUP BY table_name
HAVING COUNT(*) > 1000
ORDER BY AVG(data_size) ASC
LIMIT 10;

如果给定表的所有页面都被缓存，结果是100%准确的。如果一个给定表的10%的页面被缓存，那么从统计角度来看，结果是可以的，因为它有足够的样本。为了做一个好的估计，上面的查询有HAVING COUNT(*) > 1000子句。这意味着查询会忽略缓存页面不超过1000个的表。您可以根据自己的喜好调整该值。

如果您想对一个没有被很好地缓存的表进行估计，请预先访问表。要缓存整个表，必须对辅助索引和聚集索引运行查询。

SELECT * FROM table_name;
SELECT column_list_in_the_secondary_index FROM table_name;

如何解决表碎片

ALTER TABLE ... ENGINE=InnoDB;

所需的锁定量取决于您使用的MySQL版本:

MySQL 5.5和更早版本:该表在重建期间是只读的。

5.6.16及更早版本:该表在重建期间是只读的。

MySQL 5.6.17及更高版本:在重建期间，表处于在线状态，但在重建表移动到位时，需要在最后短暂锁定。

MySQL 5.7和更高版本:在重建期间，表是在线的，但是在重建表移动到位时，需要在末尾有一个短暂的锁定。