毛英东的个人博客
0%

MySQL中char与varchar的区别：存储机制、性能差异

发表于 更新于 分类于

在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
2
3
4
5
6
7
mysql> show variables like "innodb_default_row_format";
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.08 sec)

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底层是如何存储的。

  1. 创建一个带有varchar字段的表格，并插入2条记录：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> 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)

  2. 找到数据文件/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.

  3. 插入2条中文（多字节编码），分别是6个字和100个字：

    1
    2
    3
    4
    5
    6
    mysql> 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)

  4. 再次用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
    7
    mysql> 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. 先创建一个表格：

    1
    2
    3
    4
    5
    mysql> 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个字节的数据。

  2. 接下来插入几条测试数据（5个字母、50个字母、50个多字节文字）：

    1
    2
    3
    4
    5
    mysql> insert into test_char values(1, "hello"),
    (2, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),
    (3, "毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛毛");
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

  3. 再次使用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)由于没有提前分配存储空间，后续修改时可能出现页分裂，进而导致性能下降。