MySQL INFORMATION_SCHEMA.TABLES没有添加文件大小

Question

我有一张已经长得很大的桌子的档案。我希望有大量未分配的空间需要优化，但在检查INFORMATION_SCHEMA.TABLES时，几乎没有data_free (4 MiB，0.4 %)，并且这些数字与文件的大小并不相加：

$ sudo ls -lh /var/lib/mysql/data/modnar.ibd
-rw-r----- 1 mysql mysql 7.0G Aug 23 09:52 /var/lib/mysql/data/modnar.ibd

mysql> select table_name, engine, data_length, max_data_length, index_length, data_free from information_schema.tables where table_name = "modnar"; 
+------------+--------+-------------+-----------------+--------------+-----------+
| table_name | engine | data_length | max_data_length | index_length | data_free |
+------------+--------+-------------+-----------------+--------------+-----------+
| modnar     | InnoDB |  1172307968 |               0 |            0 |   4194304 |
+------------+--------+-------------+-----------------+--------------+-----------+

该文件为7 GiB，但从INFORMATION_SCHEMA.TABLES (1172307968+4194304)/1024/1024/1024中添加的数字仅为1.1 GiB。

为什么文件如此大，是表中数据量的7倍？

Answer 1

事实证明，INFORMATION_SCHEMA.TABLES很少被更新，而且可能会有很大的影响。来自percona的详细信息：https://www.percona.com/blog/2016/01/26/finding_mysql_表格_大小_在……上面_磁盘/

INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES始终是最新的，实际上：

mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name = 'data/modnar';
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME        | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE |
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
|    54 | data/modnar |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 7470055424 |     7470059520 |
+-------+-------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+

在modnar表上运行优化确实更新了INFORMATION_SCHEMA.TABLES，对其数字加起来现在达到6.25 GiB。

查找哪个表使用最新数据中的大量未分配空间的查询：

select name, unallocated_KB, round(unallocated_KB/(fs_block_size/1024)) unallocated_blocks, unallocated_KB/(file_size/1024) unallocated_percent from (
  select name, fs_block_size, file_size, (ALLOCATED_SIZE - FILE_SIZE)/1024 unallocated_KB
  from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
  where name not like 'mysql/%' and name not like 'sys/%'
  order by unallocated_KB
  ) as tables;