mysql - 如何在 MySql 中显示行定义的大小

Question

我在创建表定义时达到了 65K 的最大大小。所以我将一些 varchars(1000) 更改为文本以减小大小。

我正在尝试查找表定义的当前大小，以便在添加更多字段时跟踪我与 65K 限制的接近程度。我所有的在线搜索都显示了如何获取平均行大小，但这是基于行中的实际数据使用情况而不是行定义。

是否可以运行一个查询来显示我与表行大小的 65K 限制有多接近？

Answer 1

我做了这个测试:

mysql> create table test.foo ( i int, bi bigint, si smallint, d date, dt datetime, dt6 datetime(6), v varchar(64), f float);

mysql> select coalesce(column_name, 'TOTAL =') as column_name, data_type,
  sum(case data_type
    when 'tinyint' then 1
    when 'smallint' then 2 
    when 'int' then 4 
    when 'bigint' then 8 
    when 'float' then 4 
    when 'date' then 3 
    when 'datetime' then 8 
    when 'timestamp' then 4 
    when 'time' then 3 
    when 'decimal' then 4 
    else least(character_octet_length, 768) 
  end) as size
from information_schema.columns where table_schema='test' and table_name='foo'
group by column_name with rollup;

这是一个粗略的估计，并不打算是完整的。例如，它不处理带有额外字节的日期时间/时间/时间戳以确保精度。并且它假定十进制始终为 4 个字节。

参见 https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html有关不同数据类型存储的更多详细信息。

输出是:

+-------------+-----------+------+
| column_name | data_type | size |
+-------------+-----------+------+
| bi          | bigint    |    8 |
| d           | date      |    3 |
| dt          | datetime  |    8 |
| dt6         | datetime  |    8 |
| f           | float     |    4 |
| i           | int       |    4 |
| si          | smallint  |    2 |
| v           | varchar   |  256 |
| TOTAL =     | varchar   |  293 |
+-------------+-----------+------+

请注意，varchar 被计算为 varchar 长度的 4 倍。这是因为我使用 utf8mb4 作为我的默认字符集，所以 octet_character_length 假定每个字符最多 4 个字节。

---

在 InnoDB 中，您会在达到 MySQL 存储独立层强加的 64KB 行大小限制之前很久就遇到 8KB 大小限制。

Varchar、varbinary、blob、text 和它们的所有同类在 InnoDB 的 COMPACT 行格式中的行为方式相同:

任何这些字符串类型的前 768 个字节都存储在行中。超过 768 字节的数据存储在数据库中其他地方的其他页面上，并且不计入行大小限制。

因此您可以使用 InnoDB 的 DYNAMIC 行格式以不同方式存储这些字符串类型。如果行变得太大，它会将字符串列完全移动到溢出页面，并在行中为每个字符串只留下一个 20 字节的指针。

参见 https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html有关行格式的更多文档。