数据库varchar长度最佳实践

http://dba.stackexchange.com/questions/76469/mysql-varchar-length-and-performance

主要以 MySQL 的 InnoDB 或 MyISAM 为例, 其他数据库中基本也有 VARCHAR 类型并需要提供长度的参数.

需要说明的是, 例如 VARCHAR(3) 表示的是这一列最多存 3 个字符而不是 3 个字节, 比如可以存 "一二三", 实际存储时是编码为 utf-8 的.

在 MySQL 中, VARCHAR(3) 和 VARCHAR(255) 在存储方式上是没有区别的, 都是 1 个字节表示字符串长度和字符串经 utf-8 编码后的字节. MySQL 5.0.3 以前的版本 VARCHAR 的最大长度就是 255, 之后是 65535. 而 VARCHAR(256) 之后表示长度的字节数会变成 2 个. 其实在今天来说多一个字节也没什么区别, 但为了兼容性, 通常的数据库设计中还是会出现很多 VARCHAR(255).

但事实上, 把所有较短的字符串列都设为 VARCHAR(255) 并不是最好的做法. 尽管 InnoDB 是动态存储的, 但别的数据库引擎不一定是如此. 有的可能会使用固定长度的行, 或者固定大小的内存表. 内存表即为 SQL 查询中产生的临时表. 它通常会为 varchar 类型分配最大的空间, 比如 utf-8 编码下, 内存表可能要为 VARCHAR(255) 分配 2+3×255 字节 (2 是因为存的是字节长度而不是字符长度), 如果行数非常多, 这也会带来性能问题. 不管其中每一行存储的数据是长还是短. 另外也注意到 InnoDB 的单列索引每个结点的最大是 767 字节 (即 2+3×255).

InnoDB 最大的行的大小是半个 database page (大约 8000 字节), 如果可变长的列 (如 varbinary, varchar, text, blob) 超过了这个大小会被存到外面去, 行里面只是存一个指针. 这会比存 inline 慢很多. 提到这个不得不说一下 text 类型, text 的存储方法应该和 varchar 也没什么区别, 就是没有长度的限制, 因此它在有 join 等产生中间结果的查询中会非常慢.

所以结论是, 我们应该用尽可能小的类型而不是统一用 VARCHAR(255).

原文: https://segmentfault.com/a/1190000002736763

How are VARCHAR columns implemented? Are they actual character arrays?

varchar 和字符集

首先要了解字符集 (Character Set).

ascii 每个字符占一个字节;
latin1 也是每个字符一个字节;
utf8 每个字符可能用 1, 2 或者 3 个字节表示
utf8mb4 每个字符可能用 1, 2, 3 或者 4 个字节表示

varchar(255) 声明中的 255 是字符数而不是字节数.

char(10) 在给定了字符集之后, 可以存储 10 个字符. 对于 utf8mb4, 它总是占用 40 个字节.

永远不要使用 char, 而是使用 varchar, 或者
显式地为 char 指定字符集为 ascii, 比如 Y/N, 'M/F', 或者国家编码, 邮政编码

varchar(10) character set utf8mb4 最多能存储 10 个字符, 英文字符用 1 个字节表示, 而每个中文字符可能要用到 3 到 4 个字节.

SELECT 中的临时表

对于有 ORDER BY 或者 GROUP BY 或者 UNION 的 SELECT, 可能为了中间的处理过程会构建临时表.

开始会考虑使用 MEMORY 引擎在内存中构建临时表.

在这个处理过程中, 会将所有的 VARCHAR 转为 CHAR.

last_name VARCHAR(255) CHARACTER SET utf8 这样的声明非常常见, 但是如果使用到了临时表, 它就会变成了 765 字节, 导致效率不高. 但是你何时发现 last_name 会长达 255 个字符? 所以:

不要总是使用 (255), 应该更加合理规划
在适当的时候使用 ascii/latin1 编码

另一个回答

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

255 个字节就需要一个字节来记录字节数, 超过了 255 个字节则需要 2 个字节来存储 VARCHAR 的字节数.