提高视图的性能

Question

我有一个管理记录级权限的视图。为此，我们将其称为"AuthorityView“。它的工作方式是查看底层表、实际的“数据”、“记录权限”表和“用户组”表。

在本例中，我们选择"Employee“记录。有一个核心表"EmployeeData"，它包含所有数据；一个"EmployeeRecordAuthority"，它指定哪些用户组对数据有什么访问权限(读、读/写、更新、删除等)；"Usergroups“只是存储用户所属的组。

该视图使用了一个相当简单的连接，但它处理了大量记录(大约100k个员工记录和大约3M个记录权限记录)。最终结果是用户可以查看的记录的子集。

我遇到的问题是，在没有条件的情况下查询视图非常慢。执行"select * from EmployeeAuthorityView“大约需要6-7分钟，然而，对其应用"top”会使其按预期执行。“从EmployeeAuthorityView中选择前10000000名*”只需要几秒钟。

所有相关的索引都存在于表之间，并且已经被重建。

查询速度变慢的原因可能是什么？为什么在数量远大于表中记录数量的情况下，使用指定的"top“限制进行查询会更快？

提前谢谢。

Answer 1

性能上的差异可能是因为数据库查询优化器用来检索数据的策略不同。

确切的原因取决于您使用的是哪种DBMS，以及它的查询优化器是如何编写的，但是这个问题是由“没有标准的视图”的概念引起的，如下所示。

您的EmployeeAuthorityView看起来像是Data、RecordAuthority和UserGroups表的连接。因此，视图本身在没有任何过滤条件的情况下，定义了一个理论集，该集是这些表的乘积(外连接)。该理论集包含100,000 x 3,000,000 x U记录(U是UserGroups表的大小)。一旦应用了一些选择条件，理论集的大小就会急剧减小，但在没有条件的情况下，它会有数万亿条记录(假设您的UserGroups表有3行以上)。

DBMS需要实例化这个理论集的多少条记录，才能使缓冲区满？查询优化器考虑了多个策略，包括(策略A)创建所有300U的记录，然后将第一个缓冲区已满返回给您的应用程序，(策略B)只在需要时创建记录，一旦有那么多记录，就将缓冲区满返回到您的应用程序。影响这一选择的因素包括，“它需要以什么顺序将记录返回到您的应用程序”。使用“top”限制会导致不同的顺序定义，在这种情况下，它选择类似于策略B的内容。