对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型

开始之前:
设计某数据库表结构的过程中,收到了一个另外令人感到意外的建议:对于字符型数据类型,数据库里统一使用varchar(max)来存储,也就是所有字符数据类型都用varchar(max)字段类型,理由是ORM写代码方便?是的,你没有听错,为了ORM中写代码方便,所以建议数据库中字符型字段全部使用varchar(max)数据类型。这是中了ORM多深的毒!!!
对于这个问题,真的非常非常非常意外,有人竟然提出这种“建议”,我第一反应是想反问:为啥你上下班通勤,开个小轿车,而不是开个载重80吨的重型卡车?重型卡车想对小轿车又结实,又能走烂路,又能更多地载重,牵引力又大,空间也大,双肩包,电脑、钢筋、水泥都能装,所谓的兼容性更好,为什么不用重型卡车来上下班通勤?
 
刚好碰到一个对比VARCHAR(max) 与VARCHAR(n) 数据类型文章,于是就翻译了一下,以下为原文链接:
 
以下是译文:
 
我见过SQL开发人员在设计表或临时表时使用varchar(max)数据。我们可能不确定数据长度,或者我们想消除字符串或二进制截断错误。每次使用都使用varchar(max)是否是一个好习惯?
我们可以为varchar(n)数据类型定义一个特定的范围,这是推荐的作法。为了了解这个数据类型,请阅读SQLvarchar(n)文章。
我们将在本文中讨论varchar max的使用及其影响,并与varchar(n)数据类型进行比较。
 

VARCHAR(max) SQL Server数据类型概述

SQL Server 2005引入了varchar(max)数据类型。它取代了大型blob对象Text、NText和Image数据类型。所有这些数据类型最多可存储2 GB的数据。
您可能知道SQL Server中的基本存储单位是页。SQL Server中的页大小为8 KB(8192字节),而且是固定的。在页上,SQL Server使用96个字节作为页头。我们可以在SQL Server中存储8096个字节(8192-96个字节)的数据。除此之外,页还包含行开销和行偏移,并留下8000个字节用于数据存储。因此,我们最多可以使用varchar(8000)数据类型存储8000个字节的数据(译者注:varchar(n)中n额最大值为8000,如果是nvarchar(n),n的最大值是4000)。
您可能会考虑使用varchar(max)数据类型来存储2 GB的数据,以解决字符串截断问题。
让我们创建一些具有不同varchar数据类型大小的示例表。我们还将创建一个具有varchar(max)数据类型的表。
CREATE TABLE dbo.Employee_varchar_2000
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(2000)
);
 
CREATE TABLE dbo.Employee_Varchar_4500
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(4500)
);
 
CREATE TABLE dbo.Employee_Varchar_8000
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(8000)
);
 
CREATE TABLE dbo.Employee_Varchar_Max
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(MAX)
);
让我们使用以下查询将记录插入到这些示例表中。
INSERT INTO Employee_varchar_2000 (Col1)
SELECT REPLICATE('A', 2000);
 
INSERT INTO Employee_varchar_4500 (Col1)
SELECT REPLICATE('A', 4500);
 
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE('A', 8000);
 
INSERT INTO Employee_varchar_max (Col1)
SELECT REPLICATE('A', 8000);
我们可以使用以下查询验证这些表中的数据长度。
Use SQLShackDemo
go

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_2000;

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_4500;

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_8000;

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_max;
现在,我们可以使用DMV sys.dm_db_index_physical_stats检查页面计数、行计数和分配单元等对象统计信息。
SELECT OBJECT_NAME([object_id]) AS TableName,
       alloc_unit_type_desc,
       record_count,
       page_count,
       round(avg_page_space_used_in_percent,0) as avg_page_space_used_in_percent ,
       min_record_size_in_bytes,
       max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME([object_id]) LIKE 'Employee_varchar%';
我们可以看到,所有表都包含分配单元IN_ROW_Data。SQL Server将所有数据存储在IN_ROW_Data分配单元中。

我们不能在varchar(n)数据类型中插入超过8000字节的数据。如果尝试这样做,将得到以下错误消息。
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE('A', 8001);
Go
 
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE('A', 10000);
它成功插入数据,但将值截断为8000个字符。对于包含varchar(max)数据类型的Employee_varchar_max表也会发生类似的截断。

我们需要将值转换为varchar(max)并插入超过8000个字符的长度。在尝试向Employee_varchar_8000表中插入记录时,我们得到了错误消息。
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE(CONVERT(VARCHAR(max), 'x'), 8001);

它成功地在Employee_varchar_max表中插入了记录。

重新运行查询以检查分配单元。我们获取LOB_Data分配单元,以便在Employee_Varchar_Max表中存储超过8000字节的数据。我们在IN_Row_DATA分配单元中有一个指向此数据的指针。

我们可以得出以下结论:

如果数据小于或等于8000字节,SQL Server使用IN_ROW_DATA页来存储varchar(max)数据类型的数据。
如果数据超过8000字节,SQL Server使用LOB_DATA页来存储varchar(max)数据类型的数据。

varchar(max)和varchar(n)数据类型之间的性能比较

让我们将10,000条记录插入到我们之前创建的每个表中。我们想检查数据插入时间。您可以使用ApexSQL Generate工具插入数据,而无需编写T-SQL代码。

在下面的屏幕截图中,您可以注意到以下几点。 译者注:我们无需关注原文作者使用的工具本身,只需要看他的测试方法和得到的结论

Employee_varchar_2000插入时间0.08秒
Employee_varchar_4500插入时间0.19秒
Employee_varchar_8000插入时间0.31秒
Employee_varchar_Max插入时间2.72秒

 

在 VARCHAR(N) and VARCHAR(MAX) 列上创建索引

作为DBA,您可能不会设计表。但是,需要在表上创建一个索引来提高查询的性能。
我们可以在持有varchar(n)数据类型的表的键列上创建索引。
CREATE INDEX IX_Employee_varchar_2000_1
ON dbo.Employee_varchar_2000(col1)
GO
当我们尝试对varchar(max)数据类型执行相同的操作时,会收到以下错误消息(译者注:SQLserver中varchar(max)类型字段不允许创建索引):
CREATE INDEX IX_Employee_varchar_max
ON dbo.Employee_varchar_max(col1)
GO

Msg 1919, Level 16, State 1, Line 23 Column ‘col1’ in table ‘dbo.Employee_varchar_max’ is of a type that is invalid for use as a key column in an index.
我们可以使用varchar(max)列作为索引中的包含列,但不能对这一列执行索引查找。它还需要额外的存储空间。因此,应避免使用varchar(max)数据类型创建索引。
 

执行计划(Execution plan)对比

让我们比较两个select语句的执行计划。

在第一个查询中,我们想从Employee_Varchar_2000表中检索数据并获取实际的执行计划。

在实际的执行计划中,我们可以看到一个非聚集索引查找操作符。

如果我们使用varchar(max)数据类型运行相同的查询,它会使用聚集索引扫描操作符,并且根据表中的行数,它可能是一个资源密集型操作符。

select col1 from Employee_varchar_max where col1 like ‘xxxx%’

让我们使用SSMS的Compare Showplan选项比较执行计划。

要比较两个执行计划,右键单击其中一个执行计划并选择Save Execution Plan As,然后提供要保存该计划的路径。在另一个查询执行计划中,右键单击并选择Compare Showplan。它打开一个窗口,您可以在其中指定先前保存的执行计划的路径。在下面的屏幕截图中,您可以看到两个执行计划之间的比较。

1,对于相似的查询,varchar(max)数据类型的估计CPU成本更高
2,对于varchar(max),它使用聚集索引扫描操作符并扫描所有记录。您可以看到,估计的行数是10000行,而在varchar(2000)数据类型中,它使用索引查找操作符,估计的行数是1.96078行
3,估计的行大小4035 B大于varchar(max)中的1011 B,与varchar(2000)数据类型相比

译者注:这部分原作者想表达的是:对于varchar(max)类型字段,数据库优化器在编译SQL生成执行计划的时候会需要更多的CPU资源,同时对于数据行的预估也没有varchar(n)准确

varchar(max) and varchar(n) 数据类型的不同之处

varchar(max) varchar(n)
这种数据类型最多可以存储2gb的数据 这种数据类型最多可以存储8000字节的数据
它使用分配单元IN_ROW_Data最多8000字节的数据。如果数据大于8000字节,则使用LOB_Data页,并将其指针存储在IN_ROW_Data页中 它将数据存储在标准数据页中
不能在varchar(max)数据类型的键列上创建索引 可创建索引
不能压缩LOB数据 可压缩
LOB数据的数据检索和更新相对较慢 在varchar(n)数据类型中不会遇到这样的问题
 

总结

在本文中,我们演示了varchar(max)数据类型,并探讨了varchar(max)和varchar(n)数据类型之间的几个差异。您应该使用适当的数据类型。我们应该考虑数据库设计、性能、压缩和索引。您应该审查数据库中的数据类型,并在需要时进行适当的测试和更改。
 

 

笔者补充

1,执行计划差异

鉴于varchar(max)字段不支持创建索引,其实已经完全没有进一步做性能测试的必要了,为了把问题说清楚,那么就在没有索引的情况下继续对比测试下去,单纯地比较两种字段类型在生产执行计划时的差异。
进步一对比对比varchar(max)和varchar(n)对执行计划的影响,先创建一个测试环境。
create table sb_test1
(
    c1 int identity(1,1),
    c2 varchar(50),
    c3 varchar(50),
    c4 varchar(50),
    c5 varchar(50),
    c6 varchar(50),
    c7 varchar(50),
    c8 varchar(50),
    c9 varchar(50),
    c10 datetime2
);


create table sb_test2
(
    c1 int identity(1,1),
    c2 varchar(max),
    c3 varchar(max),
    c4 varchar(max),
    c5 varchar(max),
    c6 varchar(max),
    c7 varchar(max),
    c8 varchar(max),
    c9 varchar(max),
    c10 datetime2
);


declare @i int = 0;
begin tran
    while @i<1000000
    begin
        insert into sb_test1 values (newid(),newid(),newid(),newid(),newid(),newid(),newid(),newid(),sysdatetime())
        set @i = @i + 1;
    end
commit
go 10

insert into sb_test2 
select c2,c3,c4,c5,c6,c7,c8,c9,c10 from sb_test1 

以上新建两张表结构和数据一样的表,sb_test1表字段用varchar(50),sb_test2表字段用varchar(max),都是1000W行数据。

 

1,对于同一个查询,执行计划的Memory Grant明显不一样,varchar(max)类型字段的表的执行计划内存(memory grant)明显要高很多。

2,对于同一个查询,谓词predicate过滤的时机也不一样,varchar(n)可以再扫描的过程中实现谓词过滤(边扫描边过用where条件滤),而varchar(max)只能在将表扫描完之后,在内存中单独执行谓词过滤(完全扫描之后再用where条件过滤)

3,对于同一个查询,执行结果中的的CPU资源消耗明显不一样(基于上述中的2,执行计划无法再扫描的时候进行谓词过滤),varchar(max)类型字段的表的查询要varchar(n)高2倍多。

再总结

不单单是字符型数据,包括整型(tinyint,smallint,int,bigint),时间类型(date,time,smalldatetime,datetime,datetime2)等等,具体的类型选择是基于业务的,不是基于ORM好不好处理的问题。
错误的做法没有造成特别明显的问题,这并不是说明这就是可行的,而是是因为数据量没到,不要把错误的经验当成经验使用。

 

热门相关:我的黑月光女友   诛天至极   惊世第一妃:魔帝,宠上身!   万道龙皇   万道龙皇