MySQL导入导出数据表容量的一个问题场景

December 17, 2023
测试
测试
测试
测试
4 分钟阅读

朋友提了一个MySQL数据导出导入的问题。

问题描述:从源库(兼容MySQL协议的TDSQL,select version()=5.7,test表字符集是utf8,test是个分区表)通过如下指令,导出一份数据,SQL格式的,文件6G,

mysqldump -hx.x.x.x -P3306 -uroot -proot 
--databases dbtest --tables test  
--complete-insert --single-transaction  --add-drop-table 
--skip-add-locks >test.sql

通过执行mysql将数据导入目标库(docker下的MySQL 8.0,test表字符集是Utf8mb),

mysql -hx.x.x.x -P3306 -uroot -proot test < test.sql

源库test通过dbeaver看到数据的长度是10G(图形界面),但是前两次导入,发现导入的目标库中test显示长度只有400MB,再导入几次,发现长度是7G,都和源库10G有些不太相同。

一开始怀疑源库中可能有很多碎片,因为是通过SQL导入到的目标库,所以顺序插入数据块中,数据都是相邻的、紧凑的,所以容量会小。但实际优化源库的表,发现表的大小,还是和之前相同,

(1)optimizer table test;(Innodb的表会提示Table does not support optimize, doing recreate + analyze instead)
(2)alter table test engine='innodb';(Innodb可直接使用)

通过show table status指令,源库就是10G,目标库就是7G,说明不是碎片导致的,

show table status like 'test';

至于客户端工具dbeaver显示的长度数据,估计封装的就是这种操作,因为通过计算(rows * avg_row_length),得到的就是上面提到的值。

但是,官方文档提到,针对分区表,"show table status"的很多字段值,都只是个预估的,不是一个准确值,更精确的方式,是通过查询information_schema的partitions表相关字段,

https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

[Rows] For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)


[Data_free] For partitioned tables, this value is only an estimate and may not be absolutely correct. A more accurate method of obtaining this information in such cases is to query the INFORMATION_SCHEMA PARTITIONS table, as shown in this example:
SELECT SUM(DATA_FREE)
    FROM  INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = 'mydb'
    AND   TABLE_NAME   = 'mytable';

通过partitions,可以看到目标库的每个分区数据长度,加起来就是10G,和源库的容量,其实是相同的,

98b97dbda40c5e975c798be12abd6195.png
98b97dbda40c5e975c798be12abd6195.png

目标库的数据文件路径中,每个分区会单独存储到一个".ibd"文件中,例如"test#p#p0.ibd"这种格式的,可以看到每个文件的大小,和partitions中每个分区的data_length是对应的。

因此,从数据上来看,目标库的导入,是正确的,但是这种显示数据容量的检索方式可能会产生误解,或许TDSQL、MySQL不同的封装,也会存在一些不同的统计逻辑,因此,若需要可能还得进一步探索。

继续阅读

更多来自我们博客的帖子

如何安装 BuddyPress
由 测试 December 17, 2023
经过差不多一年的开发,BuddyPress 这个基于 WordPress Mu 的 SNS 插件正式版终于发布了。BuddyPress...
阅读更多
Filter如何工作
由 测试 December 17, 2023
在 web.xml...
阅读更多
如何理解CGAffineTransform
由 测试 December 17, 2023
CGAffineTransform A structure for holding an affine transformation matrix. ...
阅读更多