字符串数据类型,CHAR与VARCHAR二者的选择问题,参考资料如下
固定长度.MySQL总是为特定数量的字符分配足够的空间.当保存CHAR值的时候,MySQL去掉末尾的空格.进行比较的时候,空格会被填充到字符串末尾.
CHAR在存储很短的字符串(CHAR类型最大能保存255个字符,4.1以后版本,统一改成了字符)或者长度近似相同的字符串的时候很有用.例如密码的MD5值.对于经常改变的值,CHAR也好于VARCHAR,因为固定长度的行不容易产生碎片.
VARCHAR保存了可变长度的字符串.它能比固定类型占用更少的存储空间,因为它只占用了自己需要的空间.
VARCHAR使用额外的1到2个字节来存储值的长度.如果列的最大长度小于或等于255个字符,则使用一个字节,否则使用两个字节.在latin1字符集下,VARCHAR(10)占用11个字节的存储空间,VARCHAR(1000)占用1002个字节的存储空间.
VARCHAR能节约存储空间,所以对性能有帮助.然而,由于行的长度是可变的,他们在更新的时候可能会发生变化,这会引起额外的工作.如果行的长度增加并不再适合于原始的位置时,具体的行为则会与存储引擎相关.
当最大长度远远大于平均长度,并且很少发生更新的时候,通常适合使用VARCHAR.这时候碎片就不会成为问题.
通过上面的CHAR和VARCHAR的简单说明,大家可能得到下面的结论:固定长度用CHAR,变长用VARCHAR.这样的选择在latin1等单字符集下,可能是比较好的.但是,在流行的InnoDB引擎以及通用的UTF-8字符集下,是否还要这样选择呢?
下面通过占用的存储空间进行分析.
在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 |
如何验证这一结论,单个值不好测试,而且暂时也没有找到合适的MySQL函数,想到的办法是分别向CHAR和VARCHAR的表,插入相同的数据,观察.ibd文件的大小.
注:下面这两个函数,返回的值,并非所需的存储空间.
CHAR_LENGTH(str),返回字符串str的长度,以字符为单位.
LENGTH(str),返回字符串str的长度,以字节为单位.
创建两个表tc,tv.仅有一个字段CHAR,VARCHAR
DROPTABLEIFEXISTS tc ;
CREATETABLE `tc` (
`a1` char(200)DEFAULTNULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
DROPTABLEIFEXISTS tv ;
CREATETABLE `tv` (
`a1` varchar(200)DEFAULTNULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
drop procedure ifexists 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
insertinto tc select c;
insertinto 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有优势呢?
涉及知识:InnoDB行记录格式: Compact
DROPTABLEIFEXISTS tc ;
CREATE TABLE `tc` (
`a1` char(200)DEFAULTNULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROPTABLEIFEXISTS tv ;
CREATETABLE `tv` (
`a1` varchar(200)DEFAULTNULL
) 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占用更少的存储空间,如果设置得当的话,会占用更少的内存处理资源.
在latin1字符集下,分别对MyISAM,InnoDB引擎做测试.
MyISAM,CHAR比VARCHAR占用的存储空间少.
InnoDB,CHAR与VARCHAR占用的存储空间相同.
<高性能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
附:
根据<MySQL技术内部-InnoDB存储引擎>关于innodb行记录格式的说明,得到的结论是:在多字节字符集的情况下,CHAR和VARCHAR的行存储基本是没有区别的.也就是说,在UTF-8字符集下,CHAR不再代表固定长度的字符串.
查看二进制文件
hexdump -C -v tc.ibd |less
二进制文件解读:
c8表示变长字段的长度,建表的时候设置的为200,把它换成16进制,则是c8
61代表字符串a
20代表未占满的长度.(仍然用0x20填充)
本文出自 “Linux运维” 博客，请务必保留此出处http://2853725.blog.51cto.com/2843725/1394422
CHARvsVARCHAR-何去何从,布布扣,bubuko.com
原文：http://2853725.blog.51cto.com/2843725/1394422