mysql - 为什么一张被骗的 table 只有一半大？

Question

我复制了一个像这样的大表:

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

之后我像这样检查了行:

select count(*) from oldtable;
select count(*) from newtable;

确保它们有相同的行数

但是在 Navicat(我不相信它的计数)中，数据长度似乎有很大差距，所以我运行了一个大小查询，如下所示:

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$MY_DB"
    AND table_name = "#MY_TABLE"

而且被欺骗的表格确实几乎是大小的 1/2。我现在知道这是一些稀疏的信息，但我现在想知道是否:

1. 复制有一些错误(抽查表明没有)
2. 以某种方式复制以某种方式优化(d)(s)数据/索引

Answer 1

在 InnoDB 表上，随着时间的推移，索引会积累一些类似于硬盘碎片的东西，其中 INSERT 和 DELETE 操作会在索引文件中留下未创建的空间可供操作系统重用。

通过 INSERT INTO...SELECT *... 复制表，新表本质上获得了一个更干净的索引，旧表的碎片更少。您可以通过 running an OPTIMIZE TABLE statement 回收旧表上的空间。 .

Use OPTIMIZE TABLE in these cases, depending on the type of table:

After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

所以在你的环境中:

OPTIMIZE TABLE oldtable

之后，再次检查information_schema.TABLES中的index_length，它的大小可能会更接近于复制表的大小。

注意:在大表上这可能需要很长时间，所以当锁定会成为问题时您不应该尝试这样做。对于 InnoDB，MySQL 实际上可能会删除并重新创建表，而不是就地优化现有索引。

一些测试后的附录:

即使在我的测试中复制一个大表时，尽管新表的 index_length 比旧表小得多，但它仍然有可以通过 OPTIMIZE TABLE 回收的空间>。优化新复制的表后，(没有中间插入/删除)旧表和新表的大小相同。