MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%?
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%?
前言
上篇文章MySQL字段的时间类型该如何选择？千万数据下性能提升10%~30%🚀我们讨论过时间类型的选择
本篇文章来讨论MySQL中字符类型的选择并来深入实践char与varchar类型的最佳使用场景
字符类型
我们最经常使用的字符串类型应该是char与varchar，它们作为本篇文章的主角，对于它们的描述我们放在后文详细介绍
文本字符串
当需要存储长文本时，可以使用文本类型
先来看看存储文本字符串的类型，从小到大依次为TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
它们分别用于存储不同大小的文本，读取文本时（由于文本可能较大），因此是从磁盘中读取的
文本类型的查询会慢，但是可以存放的内容多
| 类型 | 范围（单位字符） |
|---|---|
| TINYTEXT | 0到2^8-1（255B） |
| TEXT | 0到2^16-1（64KB） |
| MEDIUMTEXT | 0到2^24-1（16MB） |
| LONGTEXT | 0到2^32-1 （4GB） |
字节串
当存储二进制数据流时，可以选择二进制类型
它们从小到大依次是：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
占用空间与范围 和 文本字符串类似
| 类型 | 范围（单位字节） |
|---|---|
| TINYBLOB | 0到2^8-1（255B） |
| BLOB | 0到2^16-1（64KB） |
| MEDIUMBLOB | 0到2^24-1（16MB） |
| LONGBLOB | 0到2^32-1 （4GB） |
char
char类型是固定长度的字符串
比如char(10) 就会占用10个字符的长度，当字段上存储的值不超过10个字符时，剩下的会用空格进行填充
因此存储的值最后有空字符串时，不能使用char，char会使用空格填充满，再读取时就不知道有多长的空格
insert into string_test (test_full_char,test_varchar) values ('caicai ','caicai ');
char类型的长度是固定的，char（N）中N设置的是字符长度，总共占用的空间还与字符集有关
比如使用utf8 字符占用空间为1-3B，那么字段设置char(10) ，占用空间范围在10-30B中
MySQL中的记录是存在于页中的，当字符串使用固定长度的char时，字段类型占用的空间会设置为最大值，方便修改操作可以在当前记录中进行修改（原地修改）（超出长度报错）
与磁盘IO的单位是页，记录越小页中存储的记录数量就可能越多，查询相同记录数量需要的IO次数就可能越少
由于记录中该类型的空间会先分配成最大值，长度会收到限制（最大不能超过255），使用时要设置成满足需求且尽量小的长度
varchar
varchar是可变长的字符串，一般用于不确定字符串长度的时候
存储varchar类型的值时，记录不仅需要记录真实存储的数据，还要记录可变长字段的长度
当varchar使用长度≤255时使用一个字节记录，长度超出255时使用二个字节记录
既然≤255只使用一个字节，那么是不是长度不超过255的情况都用varchar(255)呢？
最好不要这么使用，在innodb中可能没什么影响，但如果使用临时表（使用memory存储引擎），则会将varchar设置成最大值的char来使用
In-memory temporary tables are managed by the MEMORY storage engine, which uses fixed-length row format. VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.
当临时表中的varchar被分配成最大值的char，如果存储的字符串占用空间很少，则会出现浪费
同时可能导致查询相同数量的记录IO次数变多，因此尽量将varchar的长度控制在一个预估使用长度的范围中
varchar可变长长度最多使用2个字节，那是不是代表着长度最大为 2^16-1（65535）呢？
MySQL允许最大占用空间为65535，当字符集使用UTF8时，每个字符可能占用1-3 Byte，那么varchar最大长度也就只能是 65535/3 = 21845
当修改varchar类型的字符串时，并不一定能和char类型一样在原地修改
当记录所在的页已满，而修改的varchar字符串又变长时，会产生新的页并重建记录放到新的页中
varchar可以理解成char的空间换时间版本
对于写操作来说，char能够原地修改，而varchar有重建记录、页分裂的开销
对于读操作，char与varchar类型的性能要看具体场景，如果char冗余部分空间，那么查询相同记录数量可能会增加IO次数；如果使用空间紧凑，那么性能会优于varchar
搭建千万数据环境
为了更好的论证我们的观点，我们先搭建千万数据的环境进行实践
表结构
CREATE TABLE `string_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test_full_char` char(40) DEFAULT NULL,
`test_not_full_char` char(10) DEFAULT NULL,
`test_short_varchar` varchar(10) DEFAULT NULL,
`test_long_varchar` varchar(1000) DEFAULT NULL,
`test_varchar` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
查看是否开启函数创建
#开启函数创建
set global log_bin_trust_function_creators=1;
#ON表示已开启
show variables like 'log_bin_trust%';
生成字符串函数
#分割符从;改为$$
delimiter $$
#函数名ran_string 需要一个参数int类型 返回类型varchar(255)
create function ran_string(n int) returns varchar(255)
begin
#声明变量chars_str默认'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
#声明变量return_str默认''
declare return_str varchar(255) default '';
#声明变量i默认0
declare i int default 0;
#循环条件 i<n
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$
编写插入函数
#插入 从参数start开始 插入max_num条数据（未使用startc）
delimiter $$
create procedure insert_string_test(in start int(10),in max_num int(10))
begin
declare i int default start;
declare str varchar(255);
set autocommit = 0;
repeat
set i = i+1;
set str = ran_string(10);
#SQL 语句
insert into string_test(test_full_char,test_not_full_char,test_short_varchar,test_long_varchar,test_varchar)
values (str,str,str,str,str);
until i=max_num
end repeat;
commit;
end $$
执行
#执行插入函数
delimiter ;
call insert_string_test(1,10000000);
生成索引
alter table string_test add index idx_test_full_char (test_full_char)
alter table string_test add index idx_test_not_full_char (test_not_full_char)
alter table string_test add index idx_test_varchar (test_varchar)
alter table string_test add index idx_test_short_varchar (test_short_varchar)
alter table string_test add index idx_test_long_varchar (test_long_varchar)
测试
char不同长度的测试
test_not_full_char char(10)
test_full_char char(40)
由于char是定长，设置的长度越小，每条记录占用空间就越少，查询相同数量就可能减少一定的IO开销
select SQL_NO_CACHE test_not_full_char from string_test where test_not_full_char like 'aa%'
> OK
> 时间: 0.651s
select SQL_NO_CACHE test_full_char from string_test where test_full_char like 'aa%'
> OK
> 时间: 0.959s
因此char类型在满足需求时越小越好
varchar不同长度的测试
test_short_varchar varchar(10)
test_long_varchar varchar(1000)
test_varchar varchar(255)
varchar(1000)在记录可变长长度时会多使用一个字节
varchar类型的不同长度在测试中差不多，只有varchar(1000)会慢一点
select SQL_NO_CACHE test_short_varchar from string_test where test_short_varchar like 'aa%'
> OK
> 时间: 0.698s
select SQL_NO_CACHE test_long_varchar from string_test where test_long_varchar like 'aa%'
> OK
> 时间: 0.747s
select SQL_NO_CACHE test_varchar from string_test where test_varchar like 'aa%'
> OK
> 时间: 0.684s
char与varchar 的测试
test_not_full_char char(10)
test_short_varchar varchar(10)
相同长度的char与varchar性能差不多，char略好
select SQL_NO_CACHE test_not_full_char from string_test where test_not_full_char like 'aa%'
> OK
> 时间: 0.628s
select SQL_NO_CACHE test_short_varchar from string_test where test_short_varchar like 'aa%'
> OK
> 时间: 0.699s
当存储10长度，但char(40)长度设置太大时，性能会比varchar较慢，相差近30%
select SQL_NO_CACHE test_full_char from string_test where test_full_char like 'zz%'
> OK
> 时间: 0.932s
select SQL_NO_CACHE test_short_varchar from string_test where test_short_varchar like 'zz%'
> OK
> 时间: 0.667s
char与varchar频繁写测试
我们测试会修改聚簇（主键）索引和它们的二级索引，先将数据改为长度5的，再测试改成长度为10
@Resource
private JdbcTemplate jdbcTemplate;
@Test
void updateCharAndVarcharTest(){
//先将数据修改成5字符长度
String charSqlPre = "update string_test set test_not_full_char = 'aaaaa' where id < 10000;";
String varcharSqlPre = "update string_test set test_short_varchar = 'aaaaa' where id < 10000;";
jdbcTemplate.update(charSqlPre);
jdbcTemplate.update(varcharSqlPre);
String charSql = "update string_test set test_not_full_char = 'aaaaabbbbb' where id < 10000;";
String varcharSql = "update string_test set test_short_varchar = 'aaaaabbbbb' where id < 10000;";
long start = System.currentTimeMillis();
int updateVarchar = jdbcTemplate.update(varcharSql);
//varchar:203ms,update:9,999
System.out.println(MessageFormat.format("varchar:{0}ms,update:{1}", System.currentTimeMillis() - start,updateVarchar));
start = System.currentTimeMillis();
int updateChar = jdbcTemplate.update(charSql);
//char:141ms,update:9,999
System.out.println(MessageFormat.format("char:{0}ms,update:{1}", System.currentTimeMillis() - start,updateChar));
}
在频繁写的场景下，char可以原地修改，而varchar需要重建记录或产生新的页，性能相差近30%
总结
需要存储文本字符时，可以选择TEXT相关的类型，读取时需要从磁盘中获取，但可以存储的空间多适合存储大文本
需要存储二进制流可以选择BLOB相关的类型
char是固定的字符串，varchar是可变长的字符串，它们占用的空间与选择使用的字符集和分配的长度有关
varchar长度255及以下会使用一个字节记录可变长长度，以上会使用两个字节记录可变长长度
char中未存满的值会用空格填充，因此字符吗末尾无法存储空格，而varchar不会填充末尾可以存储空格
当char与varchar长度相同时，char查询性能较好；但如果char未使用的空间太多，可能会导致查询不如varchar
char可以在原始记录进行修改，由于varchar是可变长的，当修改的值变长时，可能会重建记录和产生新的页存储，频繁写的场景下char性能大大优于varchar
char适合存储固定、频繁修改字符串，而varchar适合存储不确定长度、末尾需要存储空格的字符串
在设置长度时要尽量的小，如果是varchar尽量不超过255，可以少用一个字节记录可变长长度
最后（不要白嫖，一键三连求求拉~）
本篇文章被收入专栏 由点到线，由线到面，构建MySQL知识体系，感兴趣的同学可以持续关注喔
本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~
有什么问题可以在评论区交流，如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~
关注菜菜，分享更多干货，公众号：菜菜的后端私房菜
本文由博客一文多发平台 OpenWrite 发布！
