多租户聚集索引设计

Question

我有一个多租户应用程序，我希望数据有一个聚集索引来支持快速范围查询。

如果我这样设计我的聚集索引：

(SystemID, EntityID, IsHidden)

SystemID是多租户实例的唯一标识符，EntityID是实体的标识，IsHidden是该行是否显示在结果中的标志。SQL Server是否能够有效地丢弃所有不属于系统的数据以及隐藏数据？这些列的指定顺序重要吗？

如果我有这样的查询：

SELECT * FROM MyTable WHERE SystemID = @pSystemID AND IsHidden = 0

我想我要做的是对表进行有效的分区，以便将属于特定系统的所有行以及隐藏的数据在物理上紧密地分组在一起。这样，根据对该数据的查询，可以很容易地将其丢弃。

这是好事还是坏事？(我倾向于好的，我不期望发生很多插入)

Answer 1

改为这样：(SystemID, IsHidden, EntityID)。将IsHidden列放在EntityID之后将使其基本无用，因为EntityID已经是唯一的。搜索您作为示例给出的条件(WHERE SystemID=@SystemID AND IsHidden=0)仍然必须搜索该租户的整个范围，因为带有IsHidden=0的行分布在整个范围内。将该列移到EntityID之前，可以实现更高效的范围扫描。

您将面临的一个问题是，默认情况下，搜索特定的EntityID将是低效的(WHERE EntityID=@EntityID)。您可以通过在EntityID上添加非聚集索引来改进问题，但这只能解决部分问题。大部分问题将来自与其他表的联接，例如将在条件下联接的details表：

FROM Master JOIN Detail ON Master.EntityID = Detail.ParentEntityID

随着这些查询变得越来越复杂，候选行的范围越来越大，EntityID/ParentEntityID键上的非聚集索引的效率开始降低，直到命中the tipping point并基本上被忽略。如果可能，请确保all这些连接指定聚集索引键：

FROM Master JOIN Detail 
   ON Master.SystemID = Detail.SystemID 
   AND Master.IsHidden = Detail.IsHidden 
   AND Master.EntityID = Detail.ParentEntityID

问题是，大多数建模工具(如EF或Linq)将倾向于通过逻辑主键( EntityID)连接，而不是通过物理聚集键。