在MySQL中,varchar和char都可以用来存储字符串。从语义上看,varchar是变长的(Variable-length),char是定长的(Fixed-length)。本文基于MySQL 5.7版本,从varchar和char的语义,到存储引擎底层存储机制,探讨它们在存储空间占用、查询性能上的区别。
测试环境
- MySQL 5.7.36 版本
- 存储引擎: Innodb
- 行格式(Row format):DYNAMIC
Innodb存储引擎支持多种行格式(REDUNDANT、COMPACT、DYNAMIC、COMPRESSED),不同行格式存储方式存在差异。本文基于默认行格式DYNAMIC。
默认行格式可以通过innodb_default_row_format
变量查看:
1 | mysql> show variables like "innodb_default_row_format"; |
varchar和char在MySQL层的区别
根据MySQL的官方文档The CHAR and VARCHAR Types中的描述, varchar和char的区别主要有:
- 最大长度:char是255,varchar是65535,单位是字符(而不是字节)。
- 尾随空格:char会将尾随空格去掉,而varchar不会。
因为存储时,char会用空格填充至指定长度,所以取出时需要去除空格。如果char字段有唯一索引,a
和a
会提示唯一索引冲突。 - 存储空间占用:varchar会占用额外的1~2字节来存储字符串长度。如果最大长度超过255,就需要2字节,否则1字节。
注意:这是MySQL层的描述,具体怎么存储由存储引擎决定。
varchar和char在存储引擎层的区别
以下描述基于Innodb存储引擎、DYNAMIC行格式。
varchar如何存储
下面通过一个实验来看看,varchar在Innodb底层是如何存储的。
创建一个带有varchar字段的表格,并插入2条记录:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> drop table test_string;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test_string(
-> id int(11) unsigned not null primary key auto_increment,
-> code varchar(100) not null
-> )charset=utf8mb4;;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_string values(1, "hello");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_string values(2, "hhhhhhhhhh");
Query OK, 1 row affected (0.01 sec)找到数据文件
/var/lib/mysql/study/test_string.ibd
,使用hexdump
命令查看:# hexdump -C test.ibd ...... // 省略一堆 0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......| 0000c070 73 75 70 72 65 6d 75 6d 05 00 00 10 00 1c 00 00 |supremum........| 0000c080 00 01 00 00 00 00 08 28 c3 00 00 01 8d 01 10 68 |.......(.......h| 0000c090 65 6c 6c 6f 0a 00 00 18 ff d6 00 00 00 02 00 00 |ello............| 0000c0a0 00 00 08 2a c5 00 00 01 d1 01 10 68 68 68 68 68 |...*.......hhhhh| 0000c0b0 68 68 68 68 68 00 00 00 00 00 00 00 00 00 00 00 |hhhhh...........| 0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ...... // 省略一堆
其中,下划线部分为实际存储的数据
hello
和hhhhhhhhhh
,红色字体05
和0a
分别对应字符串的长度5和10.插入2条中文(多字节编码),分别是6个字和100个字:
1
2
3
4
5
6mysql> insert into test_string values(3, "毛毛毛毛毛毛");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_string values(4, "毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛
毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛");
Query OK, 1 row affected (0.01 sec)再次用
hexdump
命令查看:...... // 省略一堆 0000c0b0 68 68 68 68 68 12 00 00 20 00 2a 00 00 00 03 00 |hhhhh... .*.....| 0000c0c0 00 00 00 08 3f d3 00 00 01 d9 01 10 e6 af 9b e6 |....?...........| 0000c0d0 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b 2c 81 |..............,.| 0000c0e0 00 00 28 ff 8b 00 00 00 04 00 00 00 00 08 41 d5 |..(...........A.| 0000c0f0 00 00 01 9f 01 10 e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c100 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c110 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c120 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c130 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c140 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c150 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c160 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c170 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c180 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c190 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c1a0 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c1b0 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c1c0 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c1d0 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c1e0 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c1f0 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c200 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c210 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c220 af 9b 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ...... // 省略一堆
从输出中可以看到大量重复的
e6 af 9b
,这是使用utf8mb4编码的毛
字。可以使用hex()
函数来验证:1
2
3
4
5
6
7mysql> select hex('毛');
+------------+
| hex('毛') |
+------------+
| E6AF9B |
+------------+
1 row in set (0.09 sec)1个“毛”字占用3个字节,插入的第1条中文“毛毛毛毛毛毛”占用18个字节, 对应上面标红的
12
(十六进制,转换为十进制是18)。类似的,第2条中文记录,100个汉字占用300个字节,对应上面标红的2c 81
(2c 81
是逆序存储,转过来之后是81 2c
。其中第1位二进制位表示字符长度大于127字节,除去第1位后,即01 2c
对应十进制的300)。
从上面的实验可以看出,varchar类型对于短字符串、长字符串、多字节编码,都是存储了实际的字符+字符长度。
char如何存储
接下来对比char类型的字符串,看看有什么区别。
先创建一个表格:
1
2
3
4
5mysql> create table test_char(
-> id int unsigned not null primary key auto_increment,
-> code char(50)
-> ) charset=utf8mb4;
Query OK, 0 rows affected (0.03 sec)注意表格字段定义是
char(50)
,编码是utf8mb4
,意味code
字段着最多可以存储50*4=200
个字节的数据。接下来插入几条测试数据(5个字母、50个字母、50个多字节文字):
1
2
3
4
5mysql> insert into test_char values(1, "hello"),
(2, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),
(3, "毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0再次使用
hexdump
命令查看文件中的数据:0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......| 0000c070 73 75 70 72 65 6d 75 6d 32 00 00 00 10 00 4a 00 |supremum2.....J.| 0000c080 00 00 01 00 00 00 00 08 4a dc 00 00 01 dd 01 10 |........J.......| 0000c090 68 65 6c 6c 6f 20 20 20 20 20 20 20 20 20 20 20 |hello | 0000c0a0 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 | | 0000c0b0 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 | | 0000c0c0 20 20 32 00 00 00 18 00 4a 00 00 00 02 00 00 00 | 2.....J.......| 0000c0d0 00 08 4a dc 00 00 01 dd 01 1c 61 61 61 61 61 61 |..J.......aaaaaa| 0000c0e0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa| 0000c0f0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa| 0000c100 61 61 61 61 61 61 61 61 61 61 61 61 96 00 00 00 |aaaaaaaaaaaa....| 0000c110 20 ff 5d 00 00 00 03 00 00 00 00 08 4a dc 00 00 | .].........J...| 0000c120 01 dd 01 28 e6 af 9b e6 af 9b e6 af 9b e6 af 9b |...(............| 0000c130 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c140 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c150 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c160 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c170 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c180 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b |................| 0000c190 e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 |................| 0000c1a0 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af 9b e6 af |................| 0000c1b0 9b e6 af 9b e6 af 9b e6 af 9b 00 00 00 00 00 00 |................| 0000c1c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 0000c1d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
从上面内容可以看出,对于char
类型的字段,innodb同样保存了字符长度(红色字体)。对于utf8mb4 char(50)
来说,长度不够50字节的字符串,会使用空格(0x20)填充到50个字节(但不是最大长度200)。
char和varchar存储对比
- char和varchar都会存储字符串长度
- 对于
CHAR(N)
字段,如果实际存储数据小于N
字节,会填充空格到N
个字节。
性能对比
从char和varchar的存储结构对比,可以得出一个结论:char填充空格可能导致浪费存储空间,进而导致性能下降。因为char多存储一些空格,意味着需要从磁盘读写更多的数据、耗费更多内存、查找数据时删除空格可能也会耗费一些CPU性能。
那与varchar相比,char字段是不是一无是处呢?
大部分情况,是的,最好使用varchar。不过考虑一个极端的场景:某个字段的最大长度是100字节,但是会频繁修改。如果使用char(100)
,则插入记录后就分配了100个字节,后续修改不会造成页分裂、页空隙等问题,而varchar(100)
由于没有提前分配存储空间,后续修改时可能出现页分裂,进而导致性能下降。