在多租户数据库中索引TenantID [英] Indexing TenantID in Multi Tenant DB

问题描述

我正在为一个应用程序创建一个多租户数据库.我在每种表方法中都使用了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.

我的问题是，应该对每个表中的每个TenantID进行索引以优化搜索，因为数据库上的每个查询都会在此列上进行过滤吗?

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?

期待任何建议.

谢谢

推荐答案

尽管建立索引时需要考虑很多因素，但根据我的经验，(唯一的)聚簇索引可以很好地与 tenantId + PK 所有PK查询都可以在组合键上进行搜索.

Although there are many considerations you need to make when indexing, In my experience, the (unique) clustered index works well as tenantId + PK All PK queries can seek on the composite key.

这具有将tenantID放入非聚集索引的附加优点，因为SQL Server使用聚集键作为非聚集索引对表的引用.

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.

请注意分页，因为插入几乎总是在页面的中间，因此此方法无疑可以优化读取操作.考虑填充因子为70并观察您的碎片情况，请确保定期维护索引(无论如何都需要这样做)

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)

好运.

这篇关于在多租户数据库中索引TenantID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！