SQL Server - 字典集群索引设计 [英] SQL Server - Clustered index design for dictionary

问题描述

想要一些这方面的建议。我有一个表，我想跟踪一个对象和与对象相关的键列表。示例：

  OBJECTID ITEMTYPE ITEMKEY 
 -------- --------  - ------ 
 1 1 THE 
 1 1 BROWN 
 1 2 APPLE 
 1 3 ORANGE 
 2 2 WINDOW 
  

OBJECTID和ITEMKEY都具有很高的选择性（即OBJECTID和ITEMKEY非常多样化）。我的访问有两种方式：

• 通过OBJECTID：每次对象发生变化时，关键更改列表都需要一个键基于OBJECTID。




• 由ITEMKEY：这是关键字搜索，也经常发生。

所以我可能需要两个键，并选择一个用于聚集索引（更频繁访问的索引，或者我想要的速度），现在让我假设我将优先考虑OBJECTID用于集群）。我很困惑的是我应该如何设计它。

我的问题是更好：

a）（OBJECTID，ITEMTYPE，ITEMKEY）的聚簇索引），然后是（ITEMKEY）的索引。我的关注是，由于聚集索引很大（2个int，1个字符串），索引将很大，因为所有索引项都必须指向聚集密钥。

b）创建一个运行身份为DIRECTORYID（integer）作为主键和聚簇索引的新列，并为（OBJECTID，ITEMTYPE，ITEMKEY）和（ITEMKEY）声明两个索引。这将使索引空间最小化，但查找成本更高。

c）（OBJECTID，ITEMTYPE，ITEMKEY）的集群索引以及（ITEMKEY，ITEMTYPE， OBJECTID）上。我的逻辑是，这避免了一个关键查找，并且仍然会像索引一样大，在a）中，以较高的开销为代价。

d）可能有更好的方式给出了要求？

提前感谢
Andrew

解决方案

如果有可能，尽量保持您的群集密钥尽可能小，因为它也将添加到表上的所有非群集索引。

因此，如果有可能，我将使用INT，或者可能是两个INT的组合，但肯定不会有一个 VARCHAR 列 - 特别是该列可能是广泛的（> 10个字符），并且必然会改变。

所以你提出的选项，我个人会选择b） - 为什么？ p>

添加代理 DirectoryID 将满足聚类键的所有关键标准：

• 小


• 稳定


• 唯一

<增加

，您的其他非聚集索引将受到最小的影响。

请参阅Kimberly Tripp的优秀的博客文章在您的SQL Server表上选择一个良好的集群密钥的主要标准 - 非常有用和启发！

为了满足您的查询要求，我将添加两个非聚集索引，一个在 ObjectID （可能包括其他列经常需要），另一个在 ItemKey 按键名搜索。

Would like some advice from this. I got a table where I want to keep track of an object and a list of keys related to the object. Example:

OBJECTID   ITEMTYPE   ITEMKEY
--------   --------   -------
1          1          THE
1          1          BROWN
1          2          APPLE
1          3          ORANGE
2          2          WINDOW

Both OBJECTID and ITEMKEY have high selectivity (i.e. the OBJECTID and ITEMKEY are very varied). My access are two ways:

• By OBJECTID: Each time an object changes, the list of key changes so a key is needed based on OBJECTID. Changes happen frequently.

• By ITEMKEY: This is for keyword searching and also happens frequently.

So I probably need two keys, and choose one for clustered index (the one that is more frequently accessed, or where I want the speed to be, for now lets assume i will prioritize OBJECTID for clustered). What I am confused about is how I should design it.

My questions is, which is better:

a) A Clustered index of (OBJECTID,ITEMTYPE,ITEMKEY), and then an index of (ITEMKEY). My concern is that since a clustered index is so big (2 ints, 1 string) the index will be big, because all index items got to point back to the clustered key.

b) Create a new column with a running identity DIRECTORYID (integer) as primary key and clustered index, and declare two index for (OBJECTID,ITEMTYPE,ITEMKEY) and just (ITEMKEY). This will minimize index space but have higher lookup costs.

c) A Clustered index of (OBJECTID,ITEMTYPE,ITEMKEY), and a materialized view of (ITEMKEY,ITEMTYPE,OBJECTID) on it. My logic is that this is avoids a key lookup and will still be just as big as the index with a lookup in a), at cost of higher overhead.

d) Err...maybe there is a better way given the requirements?

Thanks in advance, Andrew

解决方案

If ever possible, try to keep your clustered key as small as possible, since it will be also added to all non-clustered indices on your table.

Therefore, I would use an INT if ever possible, or possibly a combination of two INT - but certainly never a VARCHAR column - especially if that column is potentially wide (> 10 chars) and is bound to change.

So of the options you present, I personally would choose b) - why??

Adding a surrogate DirectoryID will satisfy all crucial criteria for a clustering key:

• small
• stable
• unique
• ever-increasing

and your other non-clustered indices will be minimally impacted.

See Kimberly Tripp's outstanding blog post on the main criteria for choosing a good clustering key on your SQL Server tables - very useful and enlightening!

To satisfy your query requirements, I would add two non-clustered indices, one on ObjectID (possibly including other columns frequently needed), and another on ItemKey to search by keyname.

这篇关于SQL Server - 字典集群索引设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！