对于SqlServer查找表，使用tinyint而不是int是值得的吗？ [英] Is it worth the trouble to use tinyint instead of int for SqlServer lookup tables?

问题描述

在SqlServer 2005中设计查找表（枚举）时，如果您知道条目数将永远不会变得非常高，那么应该使用tinyint而不是int？我最关心的是性能，特别是索引的效率。

When designing a lookup table (enum) in SqlServer 2005, if you know the number of entries will never get very high, should you use tinyint instead of int? I'm most concerned about performance, particularly efficiency of indexes.

假设你有这些代表性表：

Let's say you have these representative tables:

Person
------
PersonId int  (PK)
PersonTypeId tinyint  (FK to PersonTypes)

和

PersonTypes
-----------
PersonTypeId tinyint
PersonTypeName varchar(50)

显而易见的因素是数据大小和编码麻烦。当我们在个人表中达到1亿行时，我们正在使用tinyint存储3亿个字节，而不是int，加上索引占用的空间。数据量不大，但如果设计决定适用于数十个大桌子，则重要。编码麻烦当然来自ASP.NET C＃/ VB代码中的所有这些铸造问题。

The obvious factors are data size and coding hassle. When we get to 100 million rows in the person table, we're storing 300 million less bytes with tinyint as opposed to int, plus the space taken up by our indexes. Not a huge amount of data, but significant if the design decision is applied to dozens of big tables. The coding hassle, of course, comes from all those casting problems back in the ASP.NET C#/VB code.

如果我们抛开这两个问题，还有什么进场吗由于索引页面的尺寸减少，查询会更有效吗？还是有一些填补，发生只会否定好处？任何其他的困扰？

If we set aside those two issues, what else comes into play? Will queries be much more efficient due to the decreased size of the index pages? Or is there some sort of padding that happens that will just negate the benefits? Any other gotchas?

我一直只是使用ints个人，但我正在考虑tinyint即将在一些巨大的桌子上进行重新设计/迁移工作，所以我会喜欢得到一些建议。

I've always just used ints personally, but I'm considering tinyint for an upcoming redesign/migration effort on some huge tables, so I'd love to get some advice.

经过实验，编码麻烦我预料到成为一个非问题。从int更改为tinyint根本没有导致任何投射问题。

After experimenting with this, the coding hassles I anticipated turned out to be a non-issue. Changing from int to tinyint hasn't resulted in any casting problems at all.

推荐答案

表格（或索引节点条目） ）是更多的记录（或索引节点）可以适合单个IO页面，并且对于任何查询需要较少的物理（和逻辑）读IO操作。而且，单个页面上的索引节点越多，索引中可能存在的级别越低，从根到叶级别，如果通过使表格更窄，则通过阈值，索引可以是一个较小的级别，可以对穿透性产生巨大的影响。

The narrower a table (or index node entry) is, the more records (or index nodes) can fit on a single IO page, and the fewer physical (and logical) reads IO operations are required for any query. Also, the more index nodes there are on a single page, the fewer levels there may be in the index, from root to leaf level, and if by making a table narrower you pass the threshold where the index can be one level smaller, this can have a dramatic effect on perforamnce.

如果通过切换到TinyInt，可以将表从200字节宽改为197字节宽，这可能没有任何区别。 ..但是如果你把它从20个字节改为14（说你有2个int），那么这可能是戏剧性的...

If by switching to TinyInt you change your table from 200 bytes wide to 197 bytes wide, it probably won't make any difference... But if you change it from 20 bytes to 14, (say you have 2 ints in there), then it could be dramatic...

这篇关于对于SqlServer查找表，使用tinyint而不是int是值得的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！