在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和更高版本:在重建期间,表是在线的,但是在重建表移动到位时,需要在末尾有一个短暂的锁定。