关闭。这个问题是基于意见的。它目前不接受答案。想改进这个问题?更新问题,以便可以通过编辑这篇文章用事实和引用来回答它。 2年前关闭。改进这个问题
每次设置新的 SQL 表或向现有表添加新的 varchar
列时,我都想知道一件事:length
的最佳值是多少。
因此,假设您有一个名为 name
的列,类型为 varchar
。所以,你必须选择长度。我想不出名字> 20个字符,但你永远不会知道。但我总是四舍五入到下一个 2^n 数字,而不是使用 20。在这种情况下,我会选择 32 作为长度。我这样做是因为从计算机科学家的角度来看,数字 2^n 在我看来比其他数字更 even
,我只是假设下面的架构可以比其他数字更好地处理这些数字。
另一方面,例如,当您选择创建 varchar 列时,MSSQL 服务器将默认长度值设置为 50。这让我思考。为什么是50?它只是一个随机数,还是基于平均列长度,还是什么?
也可能——或者可能是——不同的 SQL 服务器实现(如 MySQL、MSSQL、Postgres ......)具有不同的最佳列长度值。
我所知道的任何 DBMS 都没有任何“优化”,可以使具有 2^n
长度的 VARCHAR
比具有不是 2 次幂的 max
长度的 max
执行得更好。
我认为早期的 SQL Server 版本实际上对长度为 255 的 VARCHAR
的处理方式与处理最大长度的 VARCHAR
不同。我不知道现在是否仍然如此。
对于几乎所有 DBMS,所需的实际存储空间仅取决于您放入其中的字符数,而不是您定义的 max
长度。因此,从存储的角度来看(很可能也是性能方面),将列声明为 VARCHAR(100)
还是 VARCHAR(500)
没有任何区别。
您应该将为 VARCHAR
列提供的 max
长度视为一种约束(或业务规则),而不是技术/物理事物。
对于 PostgreSQL,最好的设置是使用没有长度限制的 text
和将字符数限制为您的业务需要的 CHECK CONSTRAINT
。
如果该要求发生变化,则更改检查约束比更改表要快得多(因为不需要重写表)
这同样适用于 Oracle 和其他 - 但在 Oracle 中它将是 VARCHAR(4000)
而不是 text
。
我不知道 SQL Server 中的 VARCHAR(max)
和例如 VARCHAR(500)
之间是否存在物理存储差异。但显然,与 varchar(8000)
相比,使用 varchar(max)
会对性能产生影响。
请参阅 this link(由 Erwin Brandstetter 作为评论发布)
编辑 2013-09-22
关于bigown的评论:
在 9.2 之前的 Postgres 版本中(在我编写初始答案时不可用),对列定义的更改确实重写了整个表,例如 here。从 9.2 开始,情况不再如此,一项快速测试证实,增加具有 120 万行的表的列大小确实只需要 0.5 秒。
对于 Oracle 来说,这似乎也是正确的,从更改大表的 varchar
列所需的时间来看。但我找不到任何参考。
对于 MySQL the manual says“在大多数情况下,ALTER TABLE
会制作原始表的临时副本”。我自己的测试证实:在有 120 万行的表上运行 ALTER TABLE
(与我使用 Postgres 的测试相同)来增加列的大小需要 1.5 分钟。然而,在 MySQL 中,您可以不使用“解决方法”来使用检查约束来限制列中的字符数。
对于 SQL Server,我找不到明确的说明,但增加 varchar
列大小的执行时间(同样是上面的 120 万行表)表明 no 重写发生。
编辑 2017-01-24
似乎我对 SQL Server (至少部分)错了。请参阅 this answer from Aaron Bertrand,它表明 nvarchar
或 varchar
列的声明长度会对性能产生巨大影响。
VARCHAR(255)
和 VARCHAR(2)
占用完全相同相同数量的磁盘空间!所以限制它的唯一原因是如果你有一个特定的需要它更小。否则,将它们全部设为 255。
具体来说,在进行排序时,较大的列确实会占用更多空间,因此如果这会损害性能,那么您需要担心它并使其更小。但是,如果您只从该表中选择 1 行,那么您可以将它们全部设为 255,这无关紧要。
请参阅:What are the optimum varchar sizes for MySQL?
VARCHAR(MAX)
?对数据库进行建模时,空间并不是唯一的考虑因素。您正在建模的域应该驱动数据类型和大小。
VARCHAR(MAX)
与 varchar(255)
或 varchar(65535)
不同 - varchar max 是一种 text
数据类型。就您而言-如果他知道“他正在建模的领域”是什么,他就不会问这个问题。显然他不知道他的数据会有多大,我向他保证,将其全尺寸化并没有什么坏处。
VARCHAR(255)
时,您不能有 (a,b,c,d)
索引。
每当我设置一个新的 SQL 表时,我都觉得 2^n 更“均匀”......但总结一下这里的答案,仅仅通过定义 varchar(2^n) 对存储空间没有显着影响甚至是 varchar(MAX)。
也就是说,在设置高 varchar() 限制时,您仍然应该预测对存储和性能的潜在影响。例如,假设您创建了一个 varchar(MAX) 列来保存具有全文索引的产品描述。如果 99% 的描述只有 500 个字符长,然后突然有人用 wikipedia 文章替换所述描述,您可能会注意到未预料到的显着存储和性能损失。
Another thing to consider from Bill Karwin:
有一个可能的性能影响:在 MySQL 中,临时表和 MEMORY 表将 VARCHAR 列存储为固定长度列,填充到其最大长度。如果您设计的 VARCHAR 列比您需要的最大大小大得多,那么您将消耗更多的内存。这会影响缓存效率、排序速度等。
基本上,只需在稍大的尺寸上提出合理的业务限制和错误。正如@onedaywhen 指出的那样,英国的姓氏通常在 1-35 个字符之间。如果您决定将其设为 varchar(64),那么您不会真正伤害任何东西……除非您存储的 this guy's family name 据说最长为 666 个字符。在这种情况下,也许 varchar(1028) 更有意义。
如果它有帮助,这就是 varchar 2^5 到 2^10 填充后的样子:
varchar(32) Lorem ipsum dolor sit amet amet.
varchar(64) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
varchar(128) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
varchar(256) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
varchar(512) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
varchar(1024) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
dapibus leo lobortis eleifend. Vivamus vitae diam turpis. Vivamu
nec tristique magna, vel tincidunt diam. Maecenas elementum semi
quam. In ut est porttitor, sagittis nulla id, fermentum turpist.
Curabitur pretium nibh a imperdiet cursus. Sed at vulputate este
proin fermentum pretium justo, ac malesuada eros et Pellentesque
vulputate hendrerit molestie. Aenean imperdiet a enim at finibus
fusce ut ullamcorper risus, a cursus massa. Nunc non dapibus vel
Lorem ipsum dolor sit amet, consectetur Praesent ut ultrices sit
最佳值是适合基础域中定义的数据的值。
对于某些域,VARCHAR(10)
适合 Name
属性,对于其他域 VARCHAR(255)
可能是最佳选择。
添加到 a_horse_with_no_name 的答案中,您可能会发现以下感兴趣的内容......
将列声明为 VARCHAR(100) 还是 VACHAR(500) 没有任何区别。
-- try to create a table with max varchar length
drop table if exists foo;
create table foo(name varchar(65535) not null)engine=innodb;
MySQL Database Error: Row size too large.
-- try to create a table with max varchar length - 2 bytes for the length
drop table if exists foo;
create table foo(name varchar(65533) not null)engine=innodb;
Executed Successfully
-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65533))engine=innodb;
MySQL Database Error: Row size too large.
-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65532))engine=innodb;
Executed Successfully
不要忘记长度字节和可空字节,所以:
name varchar(100) not null
将是 1 个字节(长度)+ 最多 100 个字符(latin1)
name varchar(500) not null
将是 2 个字节(长度)+ 最多 500 个字符(latin1)
name varchar(65533) not null
将是 2 个字节(长度)+ 最多 65533 个字符(latin1)
name varchar(65532)
将是 2 个字节(长度)+ 最多 65532 个字符(latin1)+ 1 个空字节
希望这可以帮助 :)
请务必咨询您的业务领域专家。如果是您,请寻找行业标准。例如,如果相关域是自然人的姓氏(姓氏),那么对于英国企业,我会转到 UK Govtalk data standards catalogue for person information 并发现姓氏长度介于 1 到 35 个字符之间。
我最近没有检查过这个,但我过去知道 Oracle JDBC 驱动程序会在查询执行期间保留一大块内存来保存返回的结果集。内存块的大小取决于列定义和提取大小。因此 varchar2 列的长度会影响保留多少内存。几年前,这给我带来了严重的性能问题,因为我们总是使用 varchar2(4000)(当时的最大值)并且垃圾收集的效率比现在低得多。
从某种意义上说,您是对的,尽管任何低于 2^8 个字符的字符仍将注册为一个数据字节。
如果您将留下任何 VARCHAR < 255 的任何内容的基本字符考虑为消耗相同数量的空间。
255 是一个很好的基线定义,除非您特别希望减少过多的输入。
varchar(max)
可能更像 Oracle 的CLOB