计算varchar列的长度
varchar类型除了存储字符串本身以外,还需要1~2个字节来存储字符串的长度信息(字符串长度小于或等于255字节时需要1字节,超过则需要2字节);另外,如果设置允许NULL
的话,会再需要多1字节空间。
假设有一个varchar(32)
字段,字符集是utf8
,允许NULL
,因为每个utf8
字符最多占用3个字节,存储字符串本身需要占用的空间是:32 * 3 = 96字节
96字节小于255,所以这一列的最大长度是:
96 + 1(存储长度信息的额外1字节) + 1(允许NULL的1字节) = 98字节
varchar(N)中的N最大能设置为多少
由于MySQL限制了每一行的数据最大长度为65535字节(不包括TEXT
类型和BLOB
类型的字段),无论用的是什么存储引擎,都不能突破这个限制。因此这个N
的计算公式为:
(65535 - 其它字段占用的字节数 - varchar存储长度信息的额外字节数 - 允许NULL的额外字节数) / 每个字符需要占用的字节数
举个例子,我们要在一个表中新增一个varchar字段,字符集是utf8mb4
,允许NULL
。其它字段总共已占用了1000字节,直接套公式,得出N
最大值为:
(65535 - 1000 - 2 - 1) / 4 = 16133
InnoDB引擎每一行记录的最大长度是多少
InnoDB每一个数据页的大小默认是16KB,每一页至少要存2行数据,除去一些页头等额外需要占用的空间,InnoDB每一行记录能使用的最大空间约为8KB,实际值是8126字节。
既然每一行最多只能占用8126个字节,那为什么上面的例子中,N可以设置为16133个字符呢?每个字符4字节,已经是16133 * 4 = 64532字节了,远远超出8126。这里是因为,对于可变长的列,如果长度超过了8126,InnoDB会把列数据存储在溢出页上,而不是存储在数据页里,数据页仅保留一个20字节的指针,用于指向数据所在的溢出页(Off Page)。
因此,对于这些超长的字符串字段,数据库在查询的时候,除了要查询数据页的数据以外,还要另外查询溢出页,增加了磁盘IO,会对性能有一定的影响,这也是不建议
SELECT *
的原因之一,而且查询所有字段也会更加占用内存。
varchar(5)和varchar(200)有区别吗
虽然varchar(5)
和varchar(200)
在存储空间上的开销是一样的,但使用更大的长度有以下缺点:
1)上面说到,每一行记录最多只能占用65535字节,如果varchar
定义得太大,留给后续新加字段使用的空间就越少
2)建立索引时如果没有限制索引的大小,索引长度会默认采用的该字段的长度,也就是说varchar(200)
建立的索引存储大小要比varchar(5)
建立索引存储大小大得多,加载索引使用的内存也更多
3)使用临时表进行排序时,会占用更多的空间。
因此最好的策略是按照真实需要来设置长度。