数据类型大小;空间和速度问题？ [英] Datatype sizes; a space and speed issue?
问题描述
嗨再次，
我一直想知道的东西，还没有找到答案
还有一个数据类型的大小（我希望这是正确的术语）
效果表现。如果我指定TEXT，那会有什么影响？而不是
说VARCHAR（255）？通过使用VARCHAR（128）来说，有多少好处是更具侵略性，并且说再次将它减少一半？
我希望这个问题不是太基本了！谢谢！
麦迪逊
----------------------- ----（广播结束）---------------------------
提示3：如果发布/通过Usenet阅读，请发送适当的
subscribe-nomail命令到 ma ******* @ postgresql.org 这样你的
消息可以干净利落地进入邮件列表
2004年6月22日星期二晚上11点26分，麦迪逊凯利写道：我一直想知道的东西，还没有找到答案
但是大小如何一种数据类型（我希望这是正确的术语）
效果表现。如果我指定TEXT，那会有什么影响？而不是
说VARCHAR（255）？通过使用VARCHAR（128）再次将它减少一半会有多大的好处？
没有varchar，char和text之间的性能差异。事实上，
文本等于没有指定长度的varchar（Postgresql扩展名）。
当我想要数量的最大限制时我使用varchar字符。
时的字符我想要一定数量的字符（填充自动空间）。并且使用其他所有文本
，因为它比varchar更具可读性。
所有这些以及更多内容都可以在Docs - 7.4.2中找到：第8章：数据类型，第8.3节：
字符类型。
祝你好运，
Vams
---------------------------（广播结束）-------- -------------------
提示7：别忘了增加你的免费空间地图设置
>如果我指定TEXT，那会有什么影响？而不是说VARCHAR（255）？
嗯，从理论上讲，在后一种情况下，数据库将为记录本身中的该字段分配256个b $ b（b = 257？259？）个字节。也就是说，对于每个存储的记录，
字段将消耗256个字节。
在前一种情况下，数据库将（理论上再次）分配<
字段的
字符串池（想象一个单独的文件，作为一种方法），并将字符串池中的指针（4或8个字节）分配给字符串池
现场
。这意味着对于每个存储的记录，该字段将仅消耗四或八个
字节，另外（不要忘记这一点，这很重要）
然而很多字符串池中实际需要字节为文本
值。例如，如果你有一个十五个字符的名字存储在
Unicode UTF32中，那么池中将有九十个字节（对于大多数
CPU来说都是字节）对于该记录的那个字段，加上一些指针和
这样系统就可以知道在哪里收集垃圾。
有多少好处是更具侵略性的并且说使用VARCHAR（128）将它再次切成两半？
那么，这会将该字段的存储需求减少一半。
它也可能阻止您存储必要的信息。如果你只使用UTF-8中的US-ASCII，那么很容易就是128个字符，但是它只需要32个字符的UTF字符串-32，它可以是Unicode UTF-8中128到32之间的任何地方。对
权衡做出很好的预测是数据库工程师付出的一件事。
我希望这不是一个基本问题！
既然你问，是的，但是除非最近事情发生了变化，否则这里的人们不会提高烤箱
他们烤我回答它时温度太高。
-
Joel Matthew< re ** @ddcom .co.jp>
---------------------------（播出结束）----- ----------------------
提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令 ma*******@postgresql.org 以便您的
消息可以干净地通过邮件列表
2004年6月23日星期三，Joel Matthew写道：
效果如何如果我指定TEXT，那就在那里而不是
说VARCHAR（255）？
嗯，理论上，在后一种情况下，数据库将分配256
（257？259 ？）记录本身中该字段的字节数。也就是说，
字段将为每个存储的记录消耗256个字节。
text和varchar都作为length + string存储在PostgreSQL中，所以
varchar（256）不需要存储未使用的字节。 char space
pad这是奇怪的（要求长度+填充字符串）。
多少好处是有更多的攻击性，并说使用VARCHAR（128）将它再次削减一半？
那会降低该领域的存储需求减半。
它也可能阻止您存储必要的信息。如果你只使用UTF-8中的US-ASCII，那么很容易就是128个字符，但它只有UTF-32中只有32个字符的Unicode，它可能在任何地方
在Unicode UTF-8中介于128和32之间。对数据库工程师进行权衡取舍是一个很好的预测。
varchar长度应该是字符而不是字节，所以在任何编码中都应该是128
个字符，但这些128个字符占用的实际字节数可能会有所不同。
---------------------------（播出结束）-------------- -------------
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org
Hi again,
Something I have been wondering about and haven''t found an answer to
yet is how the size of a datatype (I hope that is the right term)
effects performance. What effect is there if I specify "TEXT" instead of
say "VARCHAR(255)"? How much benefit is there do being more aggresive
and say cutting it in half again by using "VARCHAR(128)"?
I hope this isn''t too basic a question! Thanks!
Madison
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
On Tuesday 22 June 2004 11:26 pm, Madison Kelly wrote:Something I have been wondering about and haven''t found an answer to
yet is how the size of a datatype (I hope that is the right term)
effects performance. What effect is there if I specify "TEXT" instead of
say "VARCHAR(255)"? How much benefit is there do being more aggresive
and say cutting it in half again by using "VARCHAR(128)"?
There is no performance difference between varchar, char and text. Infact,
text is equal to varchar with no length specified (a Postgresql extension).
I use varchar when I want a max limit on the number of characters. Char when
I want a set number of characters (auto space padded). And use text all
other times, because it is more readable than varchar.
All this and more can be found in the Docs - 7.4.2: Ch 8: Data Types, Sec 8.3:
Character Types.
good luck,
Vams
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
> What effect is there if I specify "TEXT" instead ofsay "VARCHAR(255)"?
Well, theoretically, in the latter case, the database will allocate 256
(257? 259?) bytes for that field in the record itself. That is, that
field will consume 256 bytes for each record stored.
In the former case, the database will (again, theoretically) allocate a
string pool (think of a separate file, as one way to do it) for the
field, and will allocate a pointer (4 or 8 bytes) into the string pool
for the field. This means that the field will consume only four or eight
bytes for each record stored, plus (don''t forget this, it''s important)
however many bytes are actually needed in the string pool for the text
value. If you have, for instance, a fifteen character name stored in
Unicode UTF32, there will be ninety, erm, octets (think bytes for most
CPUs) in the pool for that field of that record, plus a few pointers and
such so the system can tell where to collect garbage.
How much benefit is there do being more aggresive
and say cutting it in half again by using "VARCHAR(128)"?
Well, that would reduce the storage requirements for that field by half.
It might also prevent you from storing necessary information. That''s
easily 128 characters if you''re only using US-ASCII in UTF-8, but it''s
only 32 characters of Unicode in UTF-32, and it could be anywhere
between 128 and 32 in Unicode UTF-8. Making a good prediction about the
tradeoff is one of the things a database engineer is paid for.
I hope this isn''t too basic a question!
Since you ask, yeah, it is, but unless things have changed here recently,
the people here aren''t going to raise the oven temperature too high when
they roast me for answering it.
--
Joel Matthew <re**@ddcom.co.jp>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
On Wed, 23 Jun 2004, Joel Matthew wrote:
What effect is there if I specify "TEXT" instead of
say "VARCHAR(255)"?
Well, theoretically, in the latter case, the database will allocate 256
(257? 259?) bytes for that field in the record itself. That is, that
field will consume 256 bytes for each record stored.
Both text and varchar are stored in PostgreSQL as length + string so
varchar(256) doesn''t not require storage of the unused bytes. char space
pads so it''s the oddball (requiring length + padded string).
How much benefit is there do being more aggresive
and say cutting it in half again by using "VARCHAR(128)"?
Well, that would reduce the storage requirements for that field by half.
It might also prevent you from storing necessary information. That''s
easily 128 characters if you''re only using US-ASCII in UTF-8, but it''s
only 32 characters of Unicode in UTF-32, and it could be anywhere
between 128 and 32 in Unicode UTF-8. Making a good prediction about the
tradeoff is one of the things a database engineer is paid for.
varchar lengths should be in characters not bytes, so that should be 128
characters in any of the encodings, but the actual number of bytes that
those 128 characters can take up may vary.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
这篇关于数据类型大小;空间和速度问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
