Re：MDC与集群索引：使用哪个？ [英] Re: MDC vs. cluster index: which to use?
问题描述
Henry J.写道：
Henry J. wrote:
>
关于type_id的MDC？所以集群索引不是一个好主意？
>
MDC on type_id? So cluster index is not a good idea?
MDC *保证*聚类，而具有聚类索引的表
最终需要维护（也称为reorg）来维护
集群比率。
这并不是说集群索引仍然不值贵
（特别是对于高价）基数列不合理
候选人作为MDC维度。）
MDC *guarantees* clustering, whereas a table with a clustering index
will eventually require maintenance (a.k.a. reorg) to maintain the
cluster ratio.
That''s not to say that a clustering index isn''t still valuable
(especially for high cardinality columns that aren''t a reasonable
candidate as an MDC dimension).
推荐答案
10月21,1：24 * am，Ian< ianb ... @ mobileaudio.comwrote：
On Oct 21, 1:24*am, Ian <ianb...@mobileaudio.comwrote:
Henry J.写道：
Henry J. wrote:
关于type_id的MDC？ *所以集群索引不是一个好主意？
MDC on type_id? *So cluster index is not a good idea?
MDC *保证*聚类，而具有聚类索引的表
最终需要维护（也称为reorg）来维护
集群比率。
这并不是说集群索引仍然不值贵
（特别是对于高价）基数列不合理
候选人作为MDC维度）。
MDC *guarantees* clustering, whereas a table with a clustering index
will eventually require maintenance (a.k.a. reorg) to maintain the
cluster ratio.
That''s not to say that a clustering index isn''t still valuable
(especially for high cardinality columns that aren''t a reasonable
candidate as an MDC dimension).
就我而言，每天都会清除并重新填充表格。而另一列的产品__
具有很高的基数。
也许集群索引更有意义？如果我必须为MDC选择一列，我想我会选择type_id
。然后它不会像
a集群索引那样有用（type_id，product_id）？
For my case, the table is purged and re-populated every day. And the
other column, product_id, has high cardinality.
Perhaps a cluster index makes more sense? I guess I''d choose type_id
if I have to pick one column for MDC. Then it won''t be as helpful as
a cluster index on (type_id, product_id)?
10月21日，7：18 * am，Henry J. < tank209 ... @ yahoo.comwrote：
On Oct 21, 7:18*am, "Henry J." <tank209...@yahoo.comwrote:
对于我的情况，每天都会清除并重新填充表格。 *
其他专栏product_id具有很高的基数。
也许集群索引更有意义？ *如果我必须为MDC挑选一列，我想我会选择type_id
。 *那么它不会像
一样有用（（__，product_id）？
For my case, the table is purged and re-populated every day. *And the
other column, product_id, has high cardinality.
Perhaps a cluster index makes more sense? *I guess I''d choose type_id
if I have to pick one column for MDC. *Then it won''t be as helpful as
a cluster index on (type_id, product_id)?
MDC仍然可能更快 - 加载速度更快，等同于基于type_id的搜索
。对于type_id和
product_id的搜索，它可以胜过群集索引，如果你还在product_id上创建了一个索引 - 和组合（MDC +索引在
product_id）肯定会胜过type_id上的集群索引，
product_id for product_id搜索。
你想验证这些声明 - 但是既然你每天都要重建桌子
，那么你就可以尝试各种各样的选择
了。每天挑选一个新的，直到找到合适的
组合。
-Chris
MDC still might be faster - probably faster at loading and equivalent
for searches based on type_id. For searches on type_id and
product_id, it could outperform the cluster index, if you also create
an index on product_id - and the combination (MDC + index on
product_id) will definitely outperform a cluster index on type_id,
product_id for product_id searches.
You''ll want to verify these claims - but since you rebuild the table
every day, you are in a perfect position to try these various options
out. Pick a new one each day until you''ve found the right
combination.
-Chris
10月21日，11：19 * am，ChrisC< cunningham ... @ gmail.comwrote：
On Oct 21, 11:19*am, ChrisC <cunningham...@gmail.comwrote:
10月21日，7：18 * am ，亨利J. < tank209 ... @ yahoo.comwrote：
On Oct 21, 7:18*am, "Henry J." <tank209...@yahoo.comwrote:
就我而言，每天都会清除并重新填充表格。 *
其他专栏product_id具有很高的基数。
也许集群索引更有意义？ *如果我必须为MDC挑选一列，我想我会选择type_id
。 *那么它不会像
一样有用（（__，product_id）？
For my case, the table is purged and re-populated every day. *And the
other column, product_id, has high cardinality.
Perhaps a cluster index makes more sense? *I guess I''d choose type_id
if I have to pick one column for MDC. *Then it won''t be as helpful as
a cluster index on (type_id, product_id)?
MDC仍然可能更快 - 加载速度更快，等同于基于type_id的搜索
。 *对于type_id和
product_id的搜索，它可以胜过群集索引，如果你还在product_id上创建了一个索引 - 和组合（MDC +指数在
product_id）肯定会胜过type_id上的集群索引，
product_id for product_id搜索。
你想验证这些声称 - 但自从你每天重建桌子
后，你就可以尝试各种各样的选择
了。 *每天选择一个新的，直到找到合适的
组合。
-Chris
MDC still might be faster - probably faster at loading and equivalent
for searches based on type_id. *For searches on type_id and
product_id, it could outperform the cluster index, if you also create
an index on product_id - and the combination (MDC + index on
product_id) will definitely outperform a cluster index on type_id,
product_id for product_id searches.
You''ll want to verify these claims - but since you rebuild the table
every day, you are in a perfect position to try these various options
out. *Pick a new one each day until you''ve found the right
combination.
-Chris
谢谢Chris。
表的主键是（type_id，product_id）。现在，如果我们在type_id上添加一个MDC，那么如果我们查询下面的
，或者只使用主键，那么MDC是否会被实际使用？我想即使只使用了主键，但是I / O仍然会受益。
选择client.client_id，sum（prod.quantity） * prod.price *
client.ordersize）
来自prod，客户的
其中prod.type_id = 4和prod.product_id =
client.product_id
group by client.client_id
此外，以下查询是否会受益于MDC和
主键？
从产品中选择*
其中prod.type_id = 4和prod.product_id = 101
或者，而不是主键，只需使用（product_id）上的索引
将涵盖上述两个查询？
我肯定会测试一下。但是我希望获得更好的理解索引的价值。此外，制作此类
更改的转变并不像我想的那么快，我希望能够更快地获得最佳的
解决方案。谢谢。
Thanks Chris.
The primary key of the table is (type_id, product_id). Now if we add
a MDC on type_id, will the MDC be actually used if we query like the
following, or the primary key will be used only? I guess even if only
the primary key is used, the I/O will still be benefited.
select client.client_id, sum( prod.quantity * prod.price *
client.ordersize )
from prod, client
where prod.type_id = 4 and prod.product_id =
client.product_id
group by client.client_id
Also, would the following query benefit from both the MDC and the
primary key?
select *
from prod
where prod.type_id = 4 and prod.product_id = 101
Or, instead of the primary key, just use an index on (product_id)
would cover both of the above queries?
I''ll certainly test to find out. But I''d like to gain better
understanding about indexing. Also, the turnaround of making such
changes are not as fast as I want and I hope to get to the best
solution a little faster. Thanks.
这篇关于Re：MDC与集群索引：使用哪个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
