由最佳化资料表功能而引出的大坑(DataFree)

2023-12-11 20:31:00 浏览数 (2)

之前使用PHP编写最佳化资料表功能，发现一个关于InnoDB DataFree的问题，供大家参考。

名词解释

资料分散

资料分散其实就是产生了碎片空间，MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。 每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

这种额外的破碎的存储空间在读取效率方面比正常占用的空间要低得多，我们也称其为碎片空间，他的大小就是数据库中显示的资料分散的大小。

最佳化资料表

当出现了碎片空间后，我们可以通过phpmyadmin来进行最佳化资料表操作去清除碎片空间，对应的语句如下参考SQL总结：

代码语言：javascript复制
OPTIMIZE TABLE `tablename1`,`dbname`.`tablename2`;
ANALYZE TABLE `tablename1`,`dbname`.`tablename2`;

MySQL列表，包括MyISAM和InnoDB这两种最常见的类型，而根据经验来说，其碎片的产生及消除都是随机的。碎片会在你的表格中留下明显的空白，而这会给列表扫描工作带来相当大的困扰。对你的列表进行优化，这样会使列表的全面及分区扫描工作进行得更有效率。(之前InnoDB是不能最佳化的，后面MySQL开放了对他的支持。)

查询表的碎片空间

一般我们通过查询information_schema.TABLES 中 DataFree 来查看表的碎片空间大小(MyISAM/InnoDB)

  但是问题来了，一次偶然的机会发现，数据库中的某些表虽然现实的碎片空间为0，但是DataFree的栏位并不是0。后面检查发现这些表全都是InnoDB表。

  所以我又跑到MySQL文档查询发现：

DataFree表示

  1. 已分配但未使用的字节数。
  2. InnoDB表报告表所属的表空间的可用空间。对于位于共享表空间中的表，这是共享表空间的可用空间。如果您使用多个表空间，并且表具有自己的表空间，则可用空间仅用于该表。可用空间是指完全可用范围中的字节数减去安全容量。即使可用空间显示为0，只要不需要分配新的盘区，也可以插入行。对于NDB群集，DATA_FREE显示磁盘上为磁盘数据表或磁盘上的碎片分配但未使用的空间。 （内存数据资源的使用情况由DATA_LENGTH列报告。）对于分区表，此值仅是估计值，可能不是绝对正确。在这种情况下，获取此信息的一种更准确的方法是查询INFORMATION_SCHEMA PARTITIONS表，如本例所示：从INFORMATION_SCHEMA.PARTITIONS中选择SELECT SUM（DATA_FREE），其中TABLE_SCHEMA =’mydb’并且TABLE_NAME =’mytable’;

错误原因

因此我们的错误原因就找到了

  • Innodb有共享表空间和独立表空间两种类型。
  • 若表的表空间管理方式是共享表空间时：information_schema.TABLES 中 DataFree 表示共享表空间的大小

表空间管理方式查询

查看当前数据库的表空间管理类型
代码语言：javascript复制
//MySQL现在InnoDB默认为独立表空间
show variables like "innodb_file_per_table";
ON代表独立表空间管理，OFF代表共享表空间管理；
查看单表的表空间管理方式
代码语言：javascript复制
查看单表的表空间管理方式，需要查看每个表是否有单独的数据档。(.ibd档)
或者查看 information_schema 该表中 DATA_LENGTH 的值是否与另外的表相同的情况(不推荐)

如何改变表空间管理方式

修改数据库的表空间管理方式
代码语言：javascript复制
修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间。
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间
共享表空间转化为独立表空间的方法
代码语言：javascript复制
(参数innodb_file_per_table=1需要先设置)
单个表的转换操作：
ALTER TABLE `table_name` ENGINE=InnoDB;
//也可用于清理碎片空间
查看数据库目录下相应的InnoDB表是不是有自己的.ibd档，如果有则修改完成。

总结

所以，我们如果要实现最佳化资料表的功能，查询information_schema.TABLES 中 DataFree只能查询出MyISAM表的分散程度，MyISAM 因为索引和资料是分开的，所以 OPTIMIZE 可以整理资料档案，并重排索引。而InnoDB表最好通过其他方式查询其碎片空间，再决定是否最佳化。

数据库 磁盘 工作 管理 数据

0 人点赞