MySQL中varchar与char的性能解析

养生修身
577 阅读4分钟

提要

最近看书时看到Mysql在update时对定长字段和变长字段的处理过程有所不同（目前仅讨论聚集索引）:

  • 如果更新字段长度与原来长度不同，那么会采取先delete再insert的操作执行（这里的delete和insert与实际sql的delete和insert不同，是指对于数据页中链表的操作）
  • 如果更新字段与原来长度相同，那么会直接更新数据页上的内容

varchar字段在更新字段长度变更时由于其本身在数据页存储的是真实长度所以会采用第一种方式，而char字段在更新字段长度变更时由于其本身在数据页存储的是一开始指定的固定长度，所以会采用第二种方式。

下面先看一组测试数据，再分析两种类型在select和update时的优劣

测试

建表

CREATE TABLE `varchar_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT comment 'ID',
 `random` varchar(64) NOT NULL comment '随机数',
 `create_time` datetime NOT NULL comment '创建时间',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `char_size` (
 `id` int(11) NOT NULL AUTO_INCREMENT comment 'ID',
 `random` char(64) NOT NULL comment '随机数',
 `create_time` datetime NOT NULL comment '创建时间',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用存储过程先给俩表整相同的10W随机数据

create procedure my_test_data()
begin
 declare i int;
 declare j int;
 declare str varchar(64) DEFAULT '' ;
    set i=1;
    while(i<=100000) do
				set j = FLOOR(1 + RAND() * 32);
				set str = substring(md5(rand()), j, j * 2);
        insert into varchar_test(random,create_time) values(str,now());				
				insert into char_size(random,create_time) values(str,now());
        set i=i+1;
    end while;
end;

call my_test_data();

两表统计情况，因为random字段本身就占了一条数据的大部分空间，所以看到定长字段表的容量为变长字段的两倍左右，这个行数统计只是一个估算的参考值。

image.png

更新用到的SQL

@Select("select id from varchar_test")
List<Long> selectVarchar();

@Update("update varchar_test set random = #{random} where id = #{id}")
void updateVarcharRandom(String random, Long id);

@Select("select id from char_size")
List<Long> selectChar();

@Update("update char_size set random = #{random} where id = #{id}")
void updateCharRandom(String random, Long id);

更新的逻辑

@Test
@Rollback(value = false)
public void test() {
    String str = "abcdefghijklmnopqrstuvwxyzAUVWXYZ012345678978ffj8668asda";
    StopWatch stopWatch = new StopWatch();
    List<Long> varcharIds = mapper.selectVarchar();
    stopWatch.start();
    for (Long id : varcharIds) {
        mapper.updateVarcharRandom(str, id);
    }
    stopWatch.stop();
    log.info("更新变长字段用时：" + stopWatch.getTime(TimeUnit.SECONDS) + "s");

    List<Long> charIds = mapper.selectChar();
    stopWatch.reset();
    stopWatch.start();
    for (Long id : charIds) {
        mapper.updateCharRandom(str, id);
    }
    stopWatch.stop();
    log.info("更新定长字段用时：" + stopWatch.getTime(TimeUnit.SECONDS) + "s");
}

测试了多次，在目前这种简单场景下，增量更新时定长字段比更新变长字段快了10s左右

image.png

分析

下图中可以把数据页中的数据理解为一个单向链表，链表的节点上存储着value的值，原地更新就相当于直接修改这个value，不会去改变链表结构，而非原地更新则会先删除这个节点，然后再插入一个带着新value的节点 image.png

对于update

如果由于字段大小的扩到导致原来的数据页空间不足，则需要进行扩容，这就是页分裂，页分裂会产生大量的页面修改信息需要去记录比如redolog，假设原地更新产生一条redolog，非原地更新导致了页分裂，那么就还需要去更改维护数据页物理信息的系统表由此产生的级联改动会产生出几倍的redolog数据。
所以在聚集索引上更新非主键字段时定长字段的效率是要优于变长字段的

对于select

如果变长字段更新次数少，空隙率低的情况下可以看出，在聚集索引的范围查询时，指针遍历的数据页肯定是要小于定长字段的，那么查询相同数量的数据速度也会更快，但是要注意如果变长字段的在修改频率高和字段大小变更多的情况下大量的空隙会导致聚集索引的范围查询效率不稳定
所以在聚集索引上查询时需要根据对于字段的更新场景来选择使用哪种类型

总结

上面展示了对于定长字段和变长字段在更新时数据库对于不同更新场景下的处理流程，其实只是简单介绍了一部分，定长字段和变长字段在order by排序时由于数据库的版本和排序规则的不同也可能出现极端情况，感兴趣的读者可以自己去了解一下，在实际应用中还是需要根据实际需求以及空间和时间上的权衡来决定使用什么类型的字段而不是无脑varchar。对于数据库这方面作者也还在持续学习，有错误的地方，希望大家多多包涵，即时指正。

avatar
文章
阅读
粉丝