问如何从数百万行的多个表中优化select

EN

首先，我假设您的University和SecurityGroup表相当小。您发布了大表的大小，但实际上其他大小才是问题的一部分

您的问题在于您不能首先连接最小的表。您的加入顺序应从小到大。但是因为您的映射表不包括securitygroup-to-university表，所以您不能首先连接最小的映射表。所以你最终从一个或另一个开始，到一个大的表，到另一个大的表，然后得到一个大的中间结果，你必须到一个小的表。

如果您总是使用current_univ、current_secgrp和root作为输入，那么您希望尽快使用它们进行过滤。要做到这一点，唯一的方法是对您的模式进行一些更改。事实上，如果有必要，您可以保留现有的表，但此建议将增加空间。

您已经很好地对数据进行了标准化。这对于更新的速度来说是很棒的。对于查询来说并不是很好。我们为了加快查询速度而去规格化(这就是数据仓库的全部原因(好吧，还有历史))。构建包含以下列的单个映射表。

Univ_id、SecGrp_ID、根、catalog_id。使其成为索引组织的表，前3列作为主键。

现在，当您使用所有三个pk值查询该索引时，您将使用允许的目录Id的完整列表来完成索引扫描，现在只需连接到cat表以获取cat项的详细信息，您就可以开始运行了。

可以是有用的，应该考虑处理的索引

WHERE c.root = 100
      AND cs.catalog = c.id
      AND cs.securitygroup = 200
      AND cu.catalog = c.id
      AND cu.university = 300

因此，以下字段可能对索引很有意义

c: id, root   
cs: catalog, securitygroup   
cu: catalog, university

因此，尝试创建

(catalog_securitygroup.catalog, catalog_securitygroup.securitygroup)

和

(catalog_university.catalog, catalog_university.university)

编辑：我错过了ORDER BY -这些字段也应该被考虑，所以

(catalog.name, catalog.id)

可能是有益的(或者其他一些可用于排序和条件的复合索引-可能是(catalog.root，catalog.name，catalog.id))

EDIT2虽然接受了另一个问题，但我将提供一些更多的思考。我已经创建了一些测试数据并运行了一些基准测试。

测试用例在记录宽度方面是最小的(在catalog_securitygroup和catalog_university中，主键是(catalog，securitygroup)和(catalog，university))。下面是每个表的记录数：

test=# SELECT (SELECT COUNT(*) FROM catalog), (SELECT COUNT(*) FROM catalog_securitygroup), (SELECT COUNT(*) FROM catalog_university);
 ?column? | ?column? | ?column? 
----------+----------+----------
   500000 |  1497501 |   500000
(1 row)

数据库为Postgres8.4，默认ubuntu安装，硬件i5，4GRAM

首先，我将查询重写为

SELECT c.id, c.name, c.owner
FROM catalog c, catalog_securitygroup cs, catalog_university cu
WHERE c.root < 50 
  AND cs.catalog = c.id 
  AND cu.catalog = c.id
  AND cs.securitygroup < 200
  AND cu.university < 200
ORDER BY c.name
LIMIT 50 OFFSET 100

注意:将条件转换为小于，以保持可比较的中间行数量(上面的查询将返回198,801行，而不使用LIMIT子句)

如果像上面那样运行，没有任何额外的索引(除了PKs和外键)，它在冷数据库上以556ms运行(这实际上表明我以某种方式过度简化了样本数据-如果我在这里有2-4个，而不求助于更少的then运算符，我会更高兴)

这就引出了我的观点--任何只连接和过滤(一定数量的表)并只返回一定数量的记录的直接查询，在任何像样的数据库上都应该以1以下的速度运行，而不需要使用游标或反规范化数据(总有一天我会写一篇文章来说明这一点)。

此外，如果查询只返回50行，并且执行简单的相等连接和限制性相等条件，那么它的运行速度应该更快。

现在让我们看看是否添加了一些索引，在这样的查询中最大的潜力通常是排序顺序，所以让我尝试一下：

CREATE INDEX test1 ON catalog (name, id);

这使得查询-在冷数据库上的执行时间为22ms。

这就是重点--如果你只想得到一页数据，你应该只得到一页数据和查询的执行时间，比如在规范化的数据上，使用适当的索引，在合适的硬件上，应该不会超过100ms。

我希望我没有过度简化到没有比较的程度(正如我之前所说的那样，由于我不知道catalog和多对多表之间的关系的基数，所以存在一些简化)。

所以，结论是

• 如果我是你，我不会停止调整索引(和SQL)，直到我得到查询性能低于200ms的经验法则。
• 只有当我能找到一个客观的解释为什么它不能低于这样的值时，我才会求助于反规范化和/或游标，等等...

Oracle基于成本的优化器利用它所拥有的所有信息来决定数据的最佳访问路径以及获取该数据的成本最低的方法。因此，以下是与您的问题相关的一些随机点。

您列出的前三个表都有主键。其他表(catalog_university和catalog_securitygroup)也有主键吗？？主键定义了一列或一组列，这些列在关系数据库中是非常重要的，并且是非空的且是唯一的。

Oracle通常通过在给定列上生成唯一索引来强制使用主键。如果唯一索引可用，Oracle优化器更有可能使用它，因为它更有可能具有更高的选择性。

如果可能，应该将包含唯一值的索引定义为唯一(CREATE UNIQUE INDEX...)，这将为优化器提供更多信息。

您提供的其他索引并不比现有索引更具选择性。例如，(catalog.id，catalog.name，catalog.owner)上的索引是唯一的，但不如(catalog.id)上现有的主键索引有用。如果在catalog.name列上编写select查询，则可以执行和索引跳过扫描，但这样做的代价很高(在这种情况下，大多数情况下甚至都不可能实现)。

由于您正尝试在catalog.root列中选择based，因此可能值得在该列上添加索引。这意味着它可以快速地从目录表中找到相关的行。第二个查询的时间可能有点误导。从catalog中查找50个匹配的行可能需要2秒钟，但这些行很容易就是catalog表中的前50行……找到符合所有条件的50个可能需要更长的时间，这不仅仅是因为您需要连接到其他表才能获得它们。当我尝试调优性能时，我总是使用create table as select而不限制rownum。对于复杂的查询，我通常关心返回所有行需要多长时间……使用rownum进行简单的select操作可能会产生误导

关于Oracle性能调优的所有内容都是关于为优化器提供足够的信息和正确的工具(索引、约束等)来正确地执行其工作。因此，使用DBMS_STATS.GATHER_TABLE_STATS()之类的工具获取优化器统计信息非常重要。在Oracle 10g或更高版本中，索引应该自动收集统计信息。

不知何故，这变成了一个关于Oracle优化器的相当长的答案。希望它能部分回答您的问题。以下是上述内容的总结：

• 为优化器提供尽可能多的信息，例如，如果索引是唯一的，则将其声明为唯一。
• 在您的访问路径上添加索引
• 为查询找到正确的时间，而不受行数的限制。在jar中查找前50个M&M&M总是比查找前50个红色M&M&M快。
• Gather optimizer stats
• 在所有存在的表上添加唯一键/主键。