朋友提了一个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,和源库的容量,其实是相同的,
目标库的数据文件路径中,每个分区会单独存储到一个".ibd"文件中,例如"test#p#p0.ibd"这种格式的,可以看到每个文件的大小,和partitions中每个分区的data_length是对应的。
因此,从数据上来看,目标库的导入,是正确的,但是这种显示数据容量的检索方式可能会产生误解,或许TDSQL、MySQL不同的封装,也会存在一些不同的统计逻辑,因此,若需要可能还得进一步探索。