深入理解MySQL中varchar(50)与varchar(500)的关键差异与影响

一. 问题描述

我们在设计表结构的时候，设计规范里面有一条如下规则:对于可变长度的字段，在满足条件的前提下，尽可能使用较短的变长字段长度。
为什么这么规定？我在网上查了一下，主要基于两个方面

• 基于存储空间的考虑
• 基于性能的考虑
  网上说Varchar(50)和varchar(500)存储空间上是一样的,真的是这样吗?
  基于性能考虑,是因为过长的字段会影响到查询性能?
  本文我将带着这两个问题探讨验证一下

二、验证存储空间区别

1、准备两张表

2、准备数据

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

3、验证存储空间

查询第一张表SQL

查询结果

查询第二张表SQL

查询结果

4、结论

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

三、验证性能区别

1、验证索引覆盖查询

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

3.1、验证索引查询

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

3.2、验证全表查询和排序
全表无排序


全表有排序



结论：全表扫描无排序情况下,两者性能无差异,在全表有排序的情况下, 两种性能差异巨大
分析原因
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采用磁盘临时文件排序,最终影响查询性能;