在多租户数据库中索引 TenantID
我正在为应用程序创建多租户数据库。我在每个表方法中都使用了 TenantID，效果非常好。我正处于性能调整阶段。
我的问题是，每个表中的每个 TenantID 是否都应该建立索引以进行优化搜索，因为数据库上的每个查询都会在此列上进行过滤？
期待任何建议。
谢谢
I am creating a multi tenant db for an application. I have gone with the TenantID in every table approach and it works very well. I am at the performance tuning stage.
My question is should each TenantID in every table be indexed for optimized searching as every query on the db will filter on this column?
Look forward to any advice.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尽管在建立索引时需要考虑很多因素，但根据我的经验，（唯一）聚集索引可以很好地工作
tenantId + PK所有 PK 查询都可以在组合键上查找。这样做的另一个好处是，将tenantID放入非聚集索引中，因为SQL Server使用聚集键作为从非聚集索引返回到表的引用。
不过，请注意页面拆分，因为插入几乎总是在页面中间，因此这种方法肯定会优化读取。考虑填充因子为 70 并注意碎片，确保定期进行索引维护（无论如何您都需要这样做）
祝您好运。
Although there are many considerations you need to make when indexing, In my experience, the (unique) clustered index works well as
tenantId + PKAll PK queries can seek on the composite key.This has the added advantage of putting the tenantID in your nonclustered indexes as SQL Server uses the clustered key as the reference back to the table from the nonclustered indexes.
Watch out for page splits though, since inserts will almost always be mid page, this approach definitely optimizes for reads. Consider a fill factor of 70 and watch your fragmentation, be sure to have regular index maintenance in place (you want this anyway)
Best of luck.
对于每个利用设计的多租户性质的查询，您都需要一个包含tenantId 的索引。问题是，tenantId 应该出现在索引结构中的什么位置呢？当然，答案是视情况而定。尝试构建索引，使选择性最大的字段排在第一位。
例如，假设一个表在州（50）之间均匀分布并且有 10 个租户，我将构建索引为 State，然后是 TenantId；对于包含 1000 个租户的同一张表，我将构建索引 TenantId，然后构建 State。
在实践中，这两个索引可能都会派上用场（让优化器对其进行排序）。
For every query that makes use of the multi-tenant nature of your design you will want to have an index that includes the tenantId. The question is, where in the index structure should tenantId occur? Of course the answer is it depends. Try to build the index so the field with the greatest selectivity comes first.
For example assuming a table with an even distribution across states (50) and with 10 tenants I would build the index as State, then TenantId; for the same table with 1000 tenants I would build the index TenantId, then State.
In practice, both indexes would probable come in handy (let the optimizer sort it out).