当前位置: 欣欣网 > 码农

MySQL 中 Varchar(50) 和 varchar(500) 区别是什么?

2024-07-05码农

扫码关注 后端架构师 」,选择 星标 公众号

重磅干货,第一时间送达!

责编:架构君 | 来源:Java知音

链接:juejin.cn/post/7350228838151847976

上一篇好文:

正文

大家好,我是后端架构师。

一. 问题描述

我们在设计表结构的时候,设计规范里面有一条如下规则:

  • 对于可变长度的字段,在满足条件的前提下,尽可能使用较短的变长字段长度。

  • 为什么这么规定?我在网上查了一下,主要基于两个方面

  • 基于存储空间的考虑

  • 基于性能的考虑

  • 网上说 Varchar(50) varchar(500) 存储空间上是一样的,真的是这样吗?

    基于性能考虑,是因为过长的字段会影响到查询性能?

    本文我将带着这两个问题探讨验证一下

    二.验证存储空间区别

    1.准备两张表

    CREATETABLE`category_info_varchar_50` (
    `id`bigint(20NOTNULL AUTO_INCREMENT COMMENT'主键',
    `name`varchar(50NOTNULLCOMMENT'分类名称',
    `is_show`tinyint(4NOTNULLDEFAULT'0'COMMENT'是否展示:0 禁用,1启用',
    `sort`int(11NOTNULLDEFAULT'0'COMMENT'序号',
    `deleted`tinyint(1DEFAULT'0'COMMENT'是否删除',
    `create_time` datetime NOTNULLCOMMENT'创建时间',
    `update_time` datetime NOTNULLCOMMENT'更新时间',
    PRIMARY KEY (`id`USING BTREE,
    KEY`idx_name` (`name`USING BTREE COMMENT'名称索引'
    ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COMMENT='分类';
    CREATETABLE`category_info_varchar_500` (
    `id`bigint(20NOTNULL AUTO_INCREMENT COMMENT'主键',
    `name`varchar(500NOTNULLCOMMENT'分类名称',
    `is_show`tinyint(4NOTNULLDEFAULT'0'COMMENT'是否展示:0 禁用,1启用',
    `sort`int(11NOTNULLDEFAULT'0'COMMENT'序号',
    `deleted`tinyint(1DEFAULT'0'COMMENT'是否删除',
    `create_time` datetime NOTNULLCOMMENT'创建时间',
    `update_time` datetime NOTNULLCOMMENT'更新时间',
    PRIMARY KEY (`id`USING BTREE,
    KEY`idx_name` (`name`USING BTREE COMMENT'名称索引'
    ENGINE=InnoDB AUTO_INCREMENT=288135DEFAULTCHARSET=utf8mb4 COMMENT='分类';

    2.准备数据

    给每张表插入相同的数据,为了凸显不同,插入100万条数据

    DELIMITER $$
    CREATEPROCEDURE batchInsertData(IN total INT)
    BEGIN
    DECLARE start_idx INTDEFAULT1;
    DECLARE end_idx INT;
    DECLARE batch_size INTDEFAULT500;
    DECLARE insert_values TEXT;
    SET end_idx = LEAST(total, start_idx + batch_size - 1);
    WHILE start_idx <= total DO
    SET insert_values = '';
    WHILE start_idx <= end_idx DO
    SET insert_values = CONCAT(insert_values, CONCAT('(\'name', start_idx, '\', 0, 0, 0, NOW(), NOW()),'));
    SET start_idx = start_idx + 1;
    ENDWHILE;
    SET insert_values = LEFT(insert_values, LENGTH(insert_values) - 1); -- Remove the trailing comma
    SET @sql = CONCAT('INSERT INTO category_info_varchar_50 (name, is_show, sort, deleted, create_time, update_time) VALUES ', insert_values, ';');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET @sql = CONCAT('INSERT INTO category_info_varchar_500 (name, is_show, sort, deleted, create_time, update_time) VALUES ', insert_values, ';'); 
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET end_idx = LEAST(total, start_idx + batch_size - 1);
    ENDWHILE;
    END$$
    DELIMITER ;
    CALL batchInsertData(1000000);



    3.验证存储空间

    查询第一张表SQL

    SELECT
    table_schema AS"数据库",
    table_name AS"表名",
    table_rows AS"记录数",
    TRUNCATE ( data_length / 1024 / 10242 ) AS"数据容量(MB)",
    TRUNCATE ( index_length / 1024 / 10242 ) AS"索引容量(MB)"
    FROM
    information_schema.TABLES 
    WHERE
    table_schema = 'test_mysql_field'
    and TABLE_NAME = 'category_info_varchar_50'
    ORDERBY
    data_length DESC,
    index_length DESC;

    查询结果

    查询第二张表SQL

    SELECT
    table_schema AS"数据库",
    table_name AS"表名",
    table_rows AS"记录数",
    TRUNCATE ( data_length / 1024 / 10242 ) AS"数据容量(MB)",
    TRUNCATE ( index_length / 1024 / 10242 ) AS"索引容量(MB)"
    FROM
    information_schema.TABLES 
    WHERE
    table_schema = 'test_mysql_field'
    and TABLE_NAME = 'category_info_varchar_500'
    ORDERBY
    data_length DESC,
    index_length DESC;

    查询结果

    4.结论

    两张表在占用空间上确实是一样的,并无差别

    三.验证性能区别

    1.验证索引覆盖查询

    selectnamefrom category_info_varchar_50 wherename = 'name100000'
    -- 耗时0.012s
    selectnamefrom category_info_varchar_500 wherename = 'name100000'
    -- 耗时0.012s
    selectnamefrom category_info_varchar_50 orderbyname;
    -- 耗时0.370s
    selectnamefrom category_info_varchar_500 orderbyname;
    -- 耗时0.379s

    通过索引覆盖查询性能差别不大

    1.验证索引查询

    select * from category_info_varchar_50 wherename = 'name100000'
    --耗时 0.012s
    select * from category_info_varchar_500 wherename = 'name100000'
    --耗时 0.012s
    select * from category_info_varchar_50 wherenamein('name100','name1000','name100000','name10000','name1100000',
    'name200','name2000','name200000','name20000','name2200000','name300','name3000','name300000','name30000','name3300000',
    'name400','name4000','name400000','name40000','name4400000','name500','name5000','name500000','name50000','name5500000',
    'name600','name6000','name600000','name60000','name6600000','name700','name7000','name700000','name70000','name7700000','name800',
    'name8000','name800000','name80000','name6600000','name900','name9000','name900000','name90000','name9900000'
    -- 耗时 0.011s -0.014s 
    -- 增加 order by name 耗时 0.012s - 0.015s
    select * from category_info_varchar_50 wherenamein('name100','name1000','name100000','name10000','name1100000',
    'name200','name2000','name200000','name20000','name2200000','name300','name3000','name300000','name30000','name3300000',
    'name400','name4000','name400000','name40000','name4400000','name500','name5000','name500000','name50000','name5500000',
    'name600','name6000','name600000','name60000','name6600000','name700','name7000','name700000','name70000','name7700000','name800',
    'name8000','name800000','name80000','name6600000','name900','name9000','name900000','name90000','name9900000'
    -- 耗时 0.012s -0.014s 
    -- 增加 order by name 耗时 0.014s - 0.017s

    索引范围查询性能基本相同, 增加了order By后开始有一定性能差别;

    3.验证全表查询和排序

    全表无排序

    全表有排序

    select * from category_info_varchar_50 orderbyname ;
    --耗时 1.498s
    select * from category_info_varchar_500 orderbyname ;
    --耗时 4.875s

    结论:

    全表扫描无排序情况下,两者性能无差异,在全表有排序的情况下, 两种性能差异巨大;

    分析原因

    varchar50 全表执行sql分析

    我发现86%的时花在数据传输上,接下来我们看状态部分,关注Created_tmp_files和sort_merge_passes

    Created_tmp_files为3

    sort_merge_passes为95

    varchar500 全表执行sql分析

    增加了临时表排序

    Created_tmp_files 为 4

    sort_merge_passes为645

    关于sort_merge_passes, Mysql给出了如下描述:

    Number of merge passes that the sort algorithm has had to do. If this value is large, you may want to increase the value of the sort_buffer_size.

    其实sort_merge_passes对应的就是MySQL做归并排序的次数,也就是说,如果sort_merge_passes值比较大,说明sort_buffer和要排序的数据差距越大,我们可以通过增大sort_buffer_size或者让填入sort_buffer_size的键值对更小来缓解sort_merge_passes归并排序的次数。

    四.最终结论

    至此,我们不难发现,当我们最该字段进行排序操作的时候,Mysql会根据该字段的设计的长度进行内存预估, 如果设计过大的可变长度, 会导致内存预估的值超出sort_buffer_size的大小, 导致mysql采用磁盘临时文件排序,最终影响查询性能。


    你还有什么想要补充的吗?

    最后给大家推荐一个ChatGPT 4.0国内网站,是我们团队一直在使用的,我们对接是OpenAI官网的账号,给大家打造了一个一模一样ChatGPT,很多粉丝朋友现在也都通过我拿这种号,价格不贵,关键还有售后。

    一句话说明:用官方一半价格的钱,一句话说明:用跟官方 ChatGPT4.0 一模一样功能,无需魔法,无视封号,不必担心次数不够。

    最大优势:可实现会话隔离!突破限制:官方限制每个账号三小时可使用40次4.0本网站可实现次数上限之后,手动切换下一个未使用的账号【相当于一个4.0帐号,同享受一百个账号轮换使用权限】

    最后,再次推荐下我们的AI星

    为了跟上AI时代我干了一件事儿,我创建了一个知识星球社群:ChartGPT与副业。想带着大家一起探索 ChatGPT和新的AI时代

    有很多小伙伴搞不定ChatGPT账号,于是我们决定,凡是这三天之内加入ChatPGT的小伙伴,我们直接送一个正常可用的永久ChatGPT独立账户。

    不光是增长速度最快,我们的星球品质也绝对经得起考验,短短一个月时间,我们的课程团队发布了 8个专栏、18个副业项目

    简单说下这个星球能给大家提供什么:

    1、不断分享如何使用ChatGPT来完成各种任务,让你更高效地使用ChatGPT,以及副业思考、变现思路、创业案例、落地案例分享。

    2、分享ChatGPT的使用方法、最新资讯、商业价值。

    3、探讨未来关于ChatGPT的机遇,共同成长。

    4、帮助大家解决ChatGPT遇到的问题。

    5、 提供一整年的售后服务,一起搞副业

    星球福利:

    1、加入星球4天后,就送ChatGPT独立账号。

    2、邀请你加入ChatGPT会员交流群。

    3、赠送一份完整的ChatGPT手册和66个ChatGPT副业赚钱手册。

    其它福利还在筹划中... 不过,我给你大家保证,加入星球后,收获的价值会远远大于今天加入的门票费用 !

    本星球第一期原价 399 ,目前属于试运营,早鸟价 149 ,每超过50人涨价10元,星球马上要来一波大的涨价,如果你还在犹豫,可能最后就要以 更高价格加入了 。。

    早就是优势。 建议大家尽早以便宜的价格加入!

    欢迎有需要的同学试试,如果本文对您有帮助,也请帮忙点个 赞 + 在看 啦!❤️

    在 还有更多优质项目系统学习资源,欢迎分享给其他同学吧!

    PS:如果觉得我的分享不错,欢迎大家随手点赞、转发、在看。

    最后给读者整理了一份BAT大厂面试真题,需要的可扫码加微信备注:「面试」获取。

    版权申明:内容来源网络,版权归原创者所有。除非无法确认,我们都会标明作者及出处,如有侵权烦请告知,我们会立即删除并表示歉意。谢谢!

    END

    最近面试BAT,整理一份面试资料【Java面试BAT通关手册】,覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。在这里,我为大家准备了一份2021年最新最全BAT等大厂Java面试经验总结。

    别找了,想获取史上最全的Java大厂面试题学习资料

    扫下方二维码回复面试就好了

    历史好文:

    扫码关注后端架构师」,选择星标公众号

    重磅干货,第一时间送达

    ,你在看吗?