字符串数据类型,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
附:
根据
查看二进制文件
hexdump -C -v tc.ibd |less
二进制文件解读:
c8表示变长字段的长度,建表的时候设置的为200,把它换成16进制,则是c8
61代表字符串a
20代表未占满的长度.(仍然用0x20填充)
