MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%?

insert into string_test (test_full_char,test_varchar) values ('caicai  ','caicai  ');

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.

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;
1
2
3
4
5
6
7
8

#开启函数创建
set global log_bin_trust_function_creators=1;

#ON表示已开启
show variables like 'log_bin_trust%';
1
2
3
4

#分割符从;改为$$
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

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17

编写插入函数

#插入 从参数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 $$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

执行

#执行插入函数
delimiter ;
call insert_string_test(1,10000000);
1
2

生成索引

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

测试

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

因此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
1
2
3
4
5
6
7
8

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

当存储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
1
2
3
4
5

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));
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

在频繁写的场景下，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 发布！