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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本篇文章来讨论MySQL字段的字符类型选择并深入实践char与varchar类型的区别以及在千万数据下的性能测试

前言

上篇文章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  ');

image.png

char类型的长度是固定的,char(N)中N设置的是字符长度,总共占用的空间还与字符集有关

比如使用utf8 字符占用空间为1-3B,那么字段设置char(10) ,占用空间范围在10-30B中

MySQL中的记录是存在于页中的,当字符串使用固定长度的char时,字段类型占用的空间会设置为最大值,方便修改操作可以在当前记录中进行修改(原地修改)(超出长度报错)

image.png

与磁盘IO的单位是页,记录越小页中存储的记录数量就可能越多,查询相同记录数量需要的IO次数就可能越少

由于记录中该类型的空间会先分配成最大值,长度会收到限制(最大不能超过255),使用时要设置成满足需求且尽量小的长度

varchar

varchar是可变长的字符串,一般用于不确定字符串长度的时候

存储varchar类型的值时,记录不仅需要记录真实存储的数据,还要记录可变长字段的长度

image.png

当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类型一样在原地修改

image.png

当记录所在的页已满,而修改的varchar字符串又变长时,会产生新的页并重建记录放到新的页中

image.png

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 {mathJaxContainer[1]} 
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-StudyJavagithub-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流,如果觉得菜菜写的不错,可以点赞、关注、收藏支持一下~

关注菜菜,分享更多干货,公众号:菜菜的后端私房菜

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
分布式计算 DataWorks MaxCompute
DataWorks产品使用合集之需要将mysql 表(有longtext类型字段) 迁移到odps,但odps好像没有对应的类型支持,该怎么办
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1天前
|
JSON 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在使用CDAS语法同步MySQL数据到Hologres时,如果开启了字段类型宽容模式,MySQL中的JSON类型会被转换为什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
分布式计算 DataWorks MaxCompute
DataWorks产品使用合集之新创建的工作空间,任务提交了,但是周期实例里面没任何数据,是什么导致的
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
11天前
|
关系型数据库 MySQL 数据库
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
17 0
|
9月前
|
存储 关系型数据库 MySQL
面试时被这样一个问:”存储MD5值应该用VARCHAR还是用CHAR?
一个5年工作经验的小伙伴,在面试的时候被这样一个问题。说”存储MD5值应该用VARCHAR还是用CHAR“,他一时间不只如何选择,感觉用VARCHAR也可以,用CHAR也行。希望我来帮忙分析一下。
79 0
|
1月前
|
存储 数据库
5. CHAR和VARCHAR的区别?
`CHAR`和`VARCHAR`在数据库中有所不同:`CHAR`长度固定,用空格填充,存储效率高,英文占1字节,汉字占2字节;而`VARCHAR`长度可变,节省空间,英文和汉字都占2字节。
21 0
|
1月前
|
存储 SQL NoSQL
面试题:char和varchar的区别?
字节面试题:char和varchar的区别?
39 0
|
1月前
|
存储 机器学习/深度学习 关系型数据库
mysql中char和varchar的区别
mysql中char和varchar的区别
49 1
|
9月前
|
存储 SQL 数据库
char、varchar、nvarchar、text的区别
char、varchar、nvarchar、text的区别
40 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL之varchar和char的区别
MySQL之varchar和char的区别
21 0

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多