Arlen's Blog

MySQL行长度限制 ​

MySQL对于每个表都有一些硬限制，例如不超过4096列，但是实际上有效值是多少，取决于几个因素：

  1. 表的最大行大小限制了列数量，所有的列的总长度不能超过行的最大值；
  2. 列的存储字段类型限制，例如存储格式、字符集等；
  3. 存储引擎的限制，例如InnoDB要求每个表不能超过1017列；
  4. 功能部分实现为被隐藏的列；

行大小限制 ​

由以下因素限制：

  1. 表内部限制最大为65535字节，存储引擎最大支持也是这个值，BLOB、TEXT类型的值超长时会将内容存储到其他page，让page可以存储更多的行；
  2. 对于4KB，8KB，16KB和32KB的innodb_page_size设置，表的最大行大小(本地存储)略小于page的一半。例如默认的16KB page，最大行大小略小于8KB，64KB page的最大行大小限制为16KB；
  3. 可变长度字段类型，当可变类型字段值得长度超过行大小，则将超出的内容存储到外部page；
  4. 不同存储格式使用了不同数量的page元数据，这也会影响行大小；

COMPACT行格式 ​

COMPACT行格式按顺序包含：

  1. 固定长度为5字节的记录头信息：描述本行记录的相关信息，如是否删除、下一个记录的位置，是否叶子节点等；
  2. 可变字段长度列表：用于存储变长字段所占的字节数，其中超过255字节的字段使用2字节表示，未超过255则使用1字节表示；
  3. 空值列表；
  4. 字段值列表存储列本身的数据外，还存储6字节长度的事务id以及7字节长度的roll point，未设置主键的表数据还会存储6字节长度的行id。超出768字节的字符串，超出的部分会存储在溢出页中，并生成20字节的溢出页地址用于指向溢出页；

DYNAMIC行格式不存储可变长度字段的数据，只存储数据所在地址；

数据类型 ​

CHAR ​

最长255字符，真实长度据取决于字符集；

VARCHAR ​

最大长度 = （最大行大小(默认为65535字节) - NULL标识列占用字节数 - 长度标识字节数）/ 字符集单字符最大字节数。

NULL标识字段 ​

如果有一个列允许为空，则需要1 bit来标识，每8 bits的标识会组成一个字段，该字段会存放在每行最开始的位置。如果有N个NULL 字段，则标识符占用的长度就是N/8 向上取整。

  1. 根据存储的实现： 可以考虑用varchar替代tinytext
  2. 如果需要非空的默认值，就必须使用varchar
  3. 如果存储的数据大于64K，就必须使用到mediumtext , longtext varchar(255+)和text在存储机制是一样的
  4. 需要特别注意varchar(255)不只是255byte ,实质上有可能占用的更多。
  5. 特别注意，varchar大字段一样的会降低性能，所以在设计中还是一个原则大字段要拆出去，主表还是要尽量的瘦小.

长度表示位 ​

存储开销是小于255只要1字节、大于255后使用两字节。是因为按照可能的数据大小，分为0 - 255(28)、256 - 65535(216)，刚好对应1字节和2字节。 但要注意，其计算根据的是字段声明的字符长度、计算可能的字节数(取编码的最大长度，如utf8 是3字节，而utf8mb4 是4字节)，再决定长度标志的字节数。如VARCHAR(100)，字符集为UTF8，可能的字节数为300，长度标识则为2字节。 MySQL中声明的类型长度都是字符数，而计算长度时需要转换成字节。

TEXT ​

TEXT最大长度为65535（2^16 − 1）个字符。如果是多字节字符，则有效最大长度会更少。存储时会增加2字节的前缀、标识长度。

TEXT无法使用临时表。因为TEXT列可能很大，临时表空间会膨胀的非常快，所以MYSQL的MEMORY引擎不支持这类大的数据类型。 如果临时表列包括了TEXT类型，MySQL会直接用磁盘上的表、而不是内存中的表。 磁盘比内存的I/O效率低很多，这就意味着性能急剧降低。 建议避免使用select *，它会选择所有列，而是在已经确定结果集范围后、左联获取对应的TEXT字段；可以将TEXT单独拆出一个表，这样读写时减少与该列发生关系的可能，性能也会提升。

InnoDB索引长度限制 ​

索引最大长度为767字节，索引会限制单独Key的最大长度为 767 字节，超过这个长度必须建立小于等于 767 字节的前缀索引。如果是utf-8 就是 255个字符，这也恰恰是能建索引情况下的最大值。如果是utf8mb4 , 则只能767 / 4 = 191 个字符。 修改索引限制长度需要在my.ini配置文件中添加以下内容(innodb_large_prefix=1，修改单列索引字节长度为767的限制，单列索引的长度变为307 )，并重启。但是开启该参数后还需要开启表的动态存储或压缩： 系统变量innodb_file_format为Barracuda，并且ROW_FORMAT为DYNAMIC或COMPRESSED。

VARCHAR和TEXT ​

VARCHAR是标准类型，但是TEXT不是。TEXT需要进行overflow存储，即768字节数据存在行中，其余的数据存储在其他page中。这种做法是为了让一个page可以容纳更多的行。 两者的区别还有默认值上的区别(TEXT不允许有默认值)，以及字段额外开销的区别：

# overhead是指需要几个字节用于记录该字段的实际长度
varchar 小于255byte  1byte overhead
varchar 大于255byte  2byte overhead

tinytext 0-255 1 byte overhead
text 0-65535 byte 2 byte overhead
mediumtext 0-16M  3 byte overhead
longtext 0-4Gb 4byte overhead

TEXT如果不超过大约8KB时，超出768字节的部分不会存在别的page上。