扫码关注
「
后端架构师
」,选择
「
星标
」
公众号
重磅干货,第一时间送达!
责编:架构君 | 来源:Java知音
链接:juejin.cn/post/7350228838151847976
上一篇好文:
大家好,我是后端架构师。
一. 问题描述
我们在设计表结构的时候,设计规范里面有一条如下规则:
对于可变长度的字段,在满足条件的前提下,尽可能使用较短的变长字段长度。
为什么这么规定?我在网上查了一下,主要基于两个方面
基于存储空间的考虑
基于性能的考虑
网上说
Varchar(50)
和
varchar(500)
存储空间上是一样的,真的是这样吗?
基于性能考虑,是因为过长的字段会影响到查询性能?
本文我将带着这两个问题探讨验证一下
二.验证存储空间区别
1.准备两张表
CREATETABLE`category_info_varchar_50` (
`id`bigint(20) NOTNULL AUTO_INCREMENT COMMENT'主键',
`name`varchar(50) NOTNULLCOMMENT'分类名称',
`is_show`tinyint(4) NOTNULLDEFAULT'0'COMMENT'是否展示:0 禁用,1启用',
`sort`int(11) NOTNULLDEFAULT'0'COMMENT'序号',
`deleted`tinyint(1) DEFAULT'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(20) NOTNULL AUTO_INCREMENT COMMENT'主键',
`name`varchar(500) NOTNULLCOMMENT'分类名称',
`is_show`tinyint(4) NOTNULLDEFAULT'0'COMMENT'是否展示:0 禁用,1启用',
`sort`int(11) NOTNULLDEFAULT'0'COMMENT'序号',
`deleted`tinyint(1) DEFAULT'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 / 1024, 2 ) AS"数据容量(MB)",
TRUNCATE ( index_length / 1024 / 1024, 2 ) 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 / 1024, 2 ) AS"数据容量(MB)",
TRUNCATE ( index_length / 1024 / 1024, 2 ) 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大厂面试题学习资料
扫下方二维码回复「面试」就好了
历史好文:
扫码关注「后端架构师」,选择「星标」公众号
重磅干货,第一时间送达
嘿,你在看吗?