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

2023年 9月 16日 88.4k 0

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

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论