[HowTo]CHARvsVARCHAR-何去何从

字符串数据类型,CHAR与VARCHAR二者的选择问题,参考资料如下

CHAR

固定长度.MySQL总是为特定数量的字符分配足够的空间.当保存CHAR值的时候,MySQL去掉末尾的空格.进行比较的时候,空格会被填充到字符串末尾.

CHAR在存储很短的字符串(CHAR类型最大能保存255个字符,4.1以后版本,统一改成了字符)或者长度近似相同的字符串的时候很有用.例如密码的MD5值.对于经常改变的值,CHAR也好于VARCHAR,因为固定长度的行不容易产生碎片.

VARCHAR

VARCHAR保存了可变长度的字符串.它能比固定类型占用更少的存储空间,因为它只占用了自己需要的空间.

VARCHAR使用额外的1到2个字节来存储值的长度.如果列的最大长度小于或等于255个字符,则使用一个字节,否则使用两个字节.在latin1字符集下,VARCHAR(10)占用11个字节的存储空间,VARCHAR(1000)占用1002个字节的存储空间.

VARCHAR能节约存储空间,所以对性能有帮助.然而,由于行的长度是可变的,他们在更新的时候可能会发生变化,这会引起额外的工作.如果行的长度增加并不再适合于原始的位置时,具体的行为则会与存储引擎相关.

当最大长度远远大于平均长度,并且很少发生更新的时候,通常适合使用VARCHAR.这时候碎片就不会成为问题.

 

通过上面的CHAR和VARCHAR的简单说明,大家可能得到下面的结论:固定长度用CHAR,变长用VARCHAR.这样的选择在latin1等单字符集下,可能是比较好的.但是,在流行的InnoDB引擎以及通用的UTF-8字符集下,是否还要这样选择呢?

 

Storage Required

下面通过占用的存储空间进行分析.

在latina1字符集下

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte
'ab'	'ab  '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

在utf8字符集下

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	12 bytes	''	2 byte
'ab'	'ab  '	12 bytes	'ab'	3 bytes
'abcd'	'abcd'	12 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	12 bytes	'abcd'	5 bytes

通过上表可以分析,在多字符集环境下,CAHR的存储空间通常是比VARCHAR大的.

如何验证这一结论,单个值不好测试,而且暂时也没有找到合适的MySQL函数,想到的办法是分别向CHAR和VARCHAR的表,插入相同的数据,观察.ibd文件的大小.

注:下面这两个函数,返回的值,并非所需的存储空间.

CHAR_LENGTH(str),返回字符串str的长度,以字符为单位.

LENGTH(str),返回字符串str的长度,以字节为单位.

 

Test

UTF-8字符集,MyISAM引擎,CHAR vs VARCHAR

环境

创建两个表tc,tv.仅有一个字段CHAR,VARCHAR

 

DROP TABLE IF EXISTS tc ;
CREATE TABLE `tc` (
  `a1` char(200) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS tv ;
CREATE TABLE `tv` (
  `a1` varchar(200) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

存储过程

drop procedure if exists load_data;
delimiter //
create procedure load_data(count int unsigned)
begin
declare s int unsigned default 1;
declare c char(200) default repeat('a', 200);
declare v varchar(200) default repeat('a', 200);
while s<= count do
insert into tc select c;
insert into tv select v;
set s = s+1;
end while;
end;
//
delimiter ;

导入前存储文件大小

导入测试数据

call load_data(200);

导入后存储文件大小

分析

在tc和tv两个表,分别插入200条数据后,对比存储文件,CHAR类型的占了120200个字节,而VARCHAR类型仅占了41600个字节.这说明,MyISAM存储引擎,UTF-8字符集的环境下, CHAR类型远大于VARCHAR类型的磁盘空间占用量.

为什么呢?我们可以在官方文档的Unicode章节找到答案:

保存UTF-8字符集数据,用VARCHAR代替CHAR,否则,MySQL必须为保存每一个CHAR数据类型的值,需要消耗3个字节的存储空间.utf8mb4会消耗4个字节的存储空间.例如:CHAR(10)在UTF-8字符集下,会占用30个字节的存储空间.

然而,在InnoDB引擎下,又是什么样的情况?CHAR类型是否比VARCHAR有优势呢?

UTF-8字符集, InnoDB引擎,CHAR vs VARCHAR

涉及知识:InnoDB行记录格式: Compact

环境

DROP TABLE IF EXISTS tc ;
CREATE TABLE `tc` (
  `a1` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS tv ;
CREATE TABLE `tv` (
  `a1` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

存储过程

(同上)

导入前存储文件大小

导入测试数据

call load_data(200);

导入后存储文件大小

分析

CHAR类型与VARCAHR类型占用了相同的存储空间,接下来再做一个测试,修改存储过程:

declare c char(200) default repeat('a', 100);

declare v varchar(200) default repeat('a', 100);

...

导入测试数据后,存储文件大小为:

通过插入200个字符a和100个字符a后的存储文件大小可以看出,数据等长的情况下,CHAR与VARCHAR的存储文件大小相同.然而在可变的数据类型中,VARCHAR占用更少的存储空间,如果设置得当的话,会占用更少的内存处理资源.

 

etc

在latin1字符集下,分别对MyISAM,InnoDB引擎做测试.

MyISAM,CHAR比VARCHAR占用的存储空间少.

InnoDB,CHAR与VARCHAR占用的存储空间相同.

Reference

<高性能MySQL(2)>

http://dev.mysql.com/doc/refman/5.5/en/char.html

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

http://dev.mysql.com/doc/internals/en/innodb-page-structure.html

附:

根据技术内部-InnoDB存储引擎>关于innodb行记录格式的说明,得到的结论是:在多字节字符集的情况下,CHAR和VARCHAR的行存储基本是没有区别的.也就是说,在UTF-8字符集下,CHAR不再代表固定长度的字符串.

查看二进制文件

hexdump -C -v tc.ibd |less

二进制文件解读:

c8表示变长字段的长度,建表的时候设置的为200,把它换成16进制,则是c8

61代表字符串a

20代表未占满的长度.(仍然用0x20填充)