多租户表在主键和外键中是否应包含TenantID？

Question

对于多租户的单个共享数据库，是否应将tenantid字段包括在主键和聚集索引中？或者只添加一个tenantid的额外索引同样高效？

我们正在生产系统上遇到性能问题，其唯一索引是主键上的聚集索引。

所有的SQL select语句都以tenantid开头，在它们的linq to entities语句中。

invoiceitems.tenantid = thecurrenttenantid order by invoicedate

当前架构

租户（tenantid uniqueidentifier primary key，tenantname） 外键（tenantid） 索引（按tenantid集群）

客户（tenantid uniqueidentifier，customerid uniqueidentifier primary key，customername varchar(50)） 外键（tenantid，customerid） 索引（按customerid集群）

发票（tenantid uniqueidentifier，invoiceid uniqueidentifier primary key，billcustomerid uniqueidentifier，shipcustomerid uniqueidentifier，invoicedate datetime） 外键（tenantid，billcustomerid，shipcustomerid） 索引（按invoiceid集群）

发票项目（tenantid uniqueidentifier，invoiceitemid uniqueidentifier primarykey，invoiceid uniqueidentifier，lineitemorder int） 外键（tenantid，invoiceid） 索引（按invoiceitemid集群）

SqlAzure要求每个表都有一个聚集索引，所以现在只有默认的主键索引。目前每个表上只有这一个索引。系统中各表中有各种外键，并且没有对任何外键表字段进行索引。

我们正在解决一些性能问题，想知道什么是最好的聚集索引，如果有其他索引可能会有帮助。我们希望不必改变现有的聚集索引，除非绝对必要，但我们愿意这样做。在SqlAzure中，据我所知，您无法简单地调整现有表中的聚集索引-您必须创建一个带有所需聚集索引的新表，并将旧表中的所有记录插入到新表中（并处理所有外键约束和其他表依赖项）。

所有SQL查询语句都以tenantid作为其Linq to Entities语句的开头。

invoiceitems.tenantid = thecurrenttenantid order by invoicedate

有些 SQL SELECT 语句只有一个排序 - 有些在引入子表时会有其他连接条件值，比如

invoiceitems.tenantid = thecurrenttenantid and invoice.invoiceid = invoiceitems.invoiceid order by invoicedate

以下是一些想法（除此之外，我们也欢迎其他建议）-哪一个最好，为什么？

主键索引选项

为了加快访问租户的记录

选项1-在tenantid上添加非聚集索引

Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) Foreign Keys (tenantid, billcustomerid, shipcustomerid) Indexes (clustered on invoiceid, non-clustered on tenantid)

选项2-将主键从primaryid更改为tenantid + primaryid，并将聚集索引更改为tenantid + primaryid。

Invoices (tenantid uniqueidentifier primary key, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) Foreign Keys (tenantid, billcustomerid, shipcustomerid) Indexes (clustered on tenantid + invoiceid)

外键索引选项

为了加快联接速度

选项3-仅在foreignkeyid上的所有外键字段上添加非聚集索引。

Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) Foreign Keys (tenantid, billcustomerid, shipcustomerid) Indexes (clustered on invoiceid, non-clustered on billcustomerid, non-clustered on shipcustomerid)

选项4-将所有外键从foreignkeyid更改为tenantid + foreignkeyid，并在tenantid + foreignkeyid上添加索引

Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) Foreign Keys (tenantid, tenantid + billcustomerid, tenantid + shipcustomerid) Indexes (clustered on invoiceid, non-clustered on tenantid + billcustomerid, non-clustered on tenantid + shipcustomerid)

SQL SELECT优化索引选项

为了加快常用查询的速度，例如选择字段从发票中where tenantid = value order by invoicedate

选项5-在除tenantid之外的每个表中添加最常用排序字段的索引。

Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) Foreign Keys (tenantid, billcustomerid, shipcustomerid) Indexes (clustered on invoiceid, non-clustered on invoicedate)

选项6-在每个表中添加tenantid +“最常用排序字段”的索引，并在tenantid +“最常用排序字段”上添加非聚集索引

Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) Foreign Keys (tenantid, billcustomerid, shipcustomerid) Indexes (clustered on invoiceid, non-clustered on tenantid + invoicedate)

- DkDev

---

很棒的问题！我很想看看你最终实现了什么。 - Paul Rasmussen

SqlAzure要求每个表都有一个聚集索引，所以目前只有主键ID上有一个索引，因为这是默认设置。目前每个表上只有这一个索引。系统中的各个表中都有不同的外键，但没有对外键表字段进行索引。嗯，那真是个不太理想的想法。看一下执行计划。预计需要在每个用于连接的列和每个用于WHERE子句的列上创建索引。您可能需要更少的多列索引或更多的单列索引。 - Mike Sherrill 'Cat Recall'

只有一个选项。使用CombGuid作为TenantId。或者如果TenantId在您的域中，为什么不使用int？ - Denis Kucherov

Answer 1

看起来你已经认真考虑过这个问题了。无论我或其他人说什么，唯一确定的方法就是自己进行测量。在这种情况下，这就不再是一个 SQL Azure 的问题，而更像是一个通用的 SQL Server 查询优化问题。

针对您的情况，有一些提示可以帮助您入门。由于您正在使用LINQ，因此无法直接访问在SQL中运行的实际查询。您可能认为自己知道查询应该是什么样子，但根据您使用的EF版本，它可能会做出一些有趣的决策来构造查询。要确定正在运行哪些查询，您需要使用SQL Profiler或Extended Events。SQL Profiler不适用于SQL Azure，因此您需要使用扩展事件或在本地服务器上获取数据库副本，并运行指向本地的应用程序。导出数据层应用程序和相关的导入在SQL Server Management Studio（SSMS）中非常有用。

通过实际查询，您可以在Azure中针对数据库运行它们以获取执行计划。然后，您可以更改索引，再次运行查询并比较计划。如果您不想干扰主开发数据库，您可以使用多种方法创建副本，包括使用“CREATE DATABASE xxx AS COPY OF yyyy”命令。

不要尝试在本地数据库上进行优化。SQL Azure与大多数本地SQL安装具有不同的性能概述。

话虽如此，如果您所有的查询都将始终包含租户ID，则我期望将其作为聚集索引的第一部分包含将提高查询性能。对于所有其他索引，我不太确定，所以我会进行测量。还要记住，索引不是免费的，您创建的每个索引都会影响写入性能和数据库大小，因此我不会过度索引所有内容。

最后，不要担心使用GUID作为您的主键，如果您的数据库变得足够大，需要通过租户ID进行联邦（您的结构看起来可以很好地处理），自增列将不再是一个选项。

- knightpfhor

Answer 2

我同意@knigtpfhor的答案，但是如果您打算在SQL Azure中使用Federations，则需要将Federation Key（TenantID）作为集群索引的一部分包括在Federation Member的每个表中。 （您上面的选项＃2）。有关更多详细信息，请参见Federation Guidelines and Limitations。

我绝对会在您的表上添加其他非聚集索引；选择要索引的字段是一部分科学和一部分艺术，但我通常尝试首先查看我可能发布的查询，并确保我有一个涵盖所涉及字段的索引。我的直觉是，虽然您的主/外键已被索引，但它们在某些情况下可能与您实际查询数据的方式不相符。

您正在遇到什么样的性能问题？您是否遇到写入数据或查询数据或两者都有问题？涉及的数据库有多大？您的性能问题是间歇性的还是比较持续的？

- Tim Lentine

---

发现在数据检索方面，在添加索引之前速度更快 - 因此暂时保持原样 - 虽然经过了所有额外的工作，但这是疯狂但真实的 - 因此，实际上只有默认聚集索引在GUID上比我认为的最常用的租户ID + 最常使用的排序结果上的聚集索引更快。 - DkDev