不管是在MSSQL还是MySQL或者Oracle,变长字段的长度衡量都是要经常面对的。
对于一个变长的字段,在满足业务的情况下(其实所谓的满足业务是一个比较模糊的东西),到底是选择varchar(50)还是varchar(200)亦或是varchar(500)?
对于保守型选择,往往是选择一个较大的长度,比如varchar(500)要比varchar(50)更具有兼容性,因为是变长字段的原因,存储空间也一样。
这样的选择并不能说就不好,看站在哪个角度来看问题。
那么,相对于varchar(50),varchar(500)在更具备兼容性的同时,有哪些不好的地方,也是需要思考的,。

今天项目中需要增加一个表字段的长度,提示 Error Code: 1118. Row size too
large. The maximum row size for the used table type, not counting BLOBs,
is 65535. This includes storage overhead, check the manual. You have to
change some columns to TEXT or BLOBs  

SELECT (CASE WHEN a.colorder=1 THEN d.name ELSE ” END)表名
,a.colorder 字段序号
,a.name 字段名
,( CASE WHEN COLUMNPROPERTY(a.id ,a.name ,’IsIdentity’)=1 THEN ‘√’ ELSE
” END) 标识 ,
( CASE WHEN (
SELECT COUNT(*) FROM sysobjects WHERE ( NAME IN (
SELECT NAME FROM sysindexes WHERE (id=a.id) AND ( indid IN (
SELECT indid FROM sysindexkeys WHERE (id = a.id) AND ( colid IN (
SELECT colid FROM syscolumns WHERE (id = a.id) AND (NAME = a.name)
)
)
)
)
))AND (xtype=’PK’))>0 THEN ‘√’ELSE ”END) 主键
,b.name 类型
,a.length 占用字节数
,COLUMNPROPERTY(a.id ,a.name ,’PRECISION’) AS 长度
,ISNULL(COLUMNPROPERTY(a.id ,a.name ,’Scale’) ,0) AS 小数位数
,(CASE WHEN a.isnullable=1 THEN ‘Y’ ELSE ‘N’ END) 允许空
,ISNULL(e.text ,”) 默认值
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = ‘U’ AND
d.name<>’dtproperties’
LEFT JOIN syscomments e ON a.cdefault = e.id

SELECT
(case when a.colorder=1 then d.name else ” end) 表名,
a.colorder 字段序号,
a.name 字段名,
b.name 字段类型,
b.length 字段长度,
g.[value] AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
–WHERE d.[name] <>’table_desc’
–你要查看的表名,注释掉,查看当前数据库所有表的字段信息
–WHERE d.[name]
=’CityHot’–你要查看的表名,注释掉,查看当前数据库所有表的字段信息
order by a.id,a.colorder

SELECT
(case when a.colorder=1 then d.name else ” end) 表名,
a.colorder 字段序号,
a.name 字段名,
b.name 字段类型,
b.length 字段长度,
g.[value] AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
–WHERE d.[name] <>’table_desc’
–你要查看的表名,注释掉,查看当前数据库所有表的字段信息
–WHERE d.[name]
=’CityHot’–你要查看的表名,注释掉,查看当前数据库所有表的字段信息
order by a.id,a.colorder

这里的原则就是:对于可变长度的字段,在满足条件的前提下,尽可能使用较短的变长字段长度。

于是翻看mysql文档,发现字段单表行长度有65535字节数的限制。下边的博友 wenlj2000

where d.name in (‘DT_BLOG_CP’
,’mst_udc’)

 

的这边文章已经写得比较详细,就直接摘录过来了

ORDER BY
d.name,
a.id
,a.colorder

以下是一个相对极端的例子,以SQL Server为例,
TestVarchar1和TestVarchar2的SortColumn
字段长度分别是varchar(50)和varchar(8000),两个表写入10000条测一样的试数据,
SortColumn 的实际长度是36个字符。

 

Create Table TestVarchar1
(
    Id INT IDENTITY(1,1),
    SortColumn varchar(50)
)

Create Table TestVarchar2
(
    Id INT IDENTITY(1,1),
    SortColumn varchar(8000)
)

DECLARE @SortColumn char(36);
set @SortColumn = CAST(NEWID() as char(36))
insert into TestVarchar1(SortColumn) values (@SortColumn)
insert into TestVarchar2(SortColumn) values (@SortColumn)
GO 10000

第一种解决方式 是 将字段从varchar 转化为 text,但项目中表的数据量非常大

 

 

第二种解决方式 是 拆分表,但已有的代码不是很好调整

 

1,基于存储空间的考虑

比较下也只有使用第一种方式

select b.name tablename ,a.name as columnname,
(CASE WHEN b.isnullable=1 THEN ‘√’ ELSE ” END) as isemp,
c.name typename,
a.max_length,
a.precision,
a.scale
from sys.columns a,
sys.objects b,
sys.types c
where a.object_id=b.object_id
and a.user_type_id=c.user_type_id
and b.type=’u’

存储空间上,存储不超过一定长度的变长字段,不同长度的变长字段存储空间是一样的,比如选择使用varchar(50)和varchar(500)是一样的,
也就说,对于不超过50个字符串的数据存储,两者在物理空间占用上并没有区别。

在此记录,希望以后数据库设计初期需要尽量全面考虑,如果单表行长度比较大时而且后期存在增加字段长度的可能,要尽量提前考虑分表或改用text

and b.name in (‘DT_BLOG_CP’,’DT_BOOK’)
–and c.name in(‘varchar’,’nvarchar’,’char’,’nchar’,’text’,’ntext’)
–and object_name(a.object_id)<>’t’

这里会发现,两个表的数据在完全一致的情况下,其存储空间也是完全一样的,的确,并不会因为varchar使用一个较长的长度而多占用存储空间

order by b.name

图片 1

 

2,基于性能的考虑
选择varchar(50)还是varchar(8000),在性能上确实有显著的差异,考虑到某些查询需要内存(Memory
Grant),查询引擎会预估当前查询需要的内存,影响查询内存的因素有以下几个方面
1,查询的类型,有没有聚合运算,有没有排序等等
2,每个操作符涉及到的记录数量
3,数据行的大小(这里是字段类型的长度而不是字段实际长度)
当行记录的数据类型长度较大的时候,执行计划预估的平均大小较大,数据类型定义的长度越大,预估的长度越大,需要分配的内存越大
如果一个查询涉及一些聚合操作并且数据量较大,就可能需要大量的内存来完成这个查询,查询引起会分配多余实际需要的内存。

两者对数据行Size的预估是一样的(尽管是完全一样的数据)

图片 2图片 3

造成的结果就是两个查询的内存授予是一样的,同时第二个执行计划还有一个警告信息(黄色的感叹号)

图片 4图片 5

以上可以看出,尽管两个表的数据是完全一致的,
不过字段的最大长度不一致,造成执行计划预估出现较大的偏差,因此给予较高的内存,浪费无所谓的资源。

再看一个通过聚合函数操作两张表的例子,会增加CPU的使用。

图片 6图片 7

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

 

 

当然,较大的字段(相比较小)还可能存在一些不是太直观的影响,参考:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图