如何从几个具有数百万行的表中选择几个表

2018-06-17 06:41:14

有以下表格（Oracle 10g）：

catalog (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(255),
  owner NUMBER,
  root NUMBER REFERENCES catalog(id)
  ...
)
university (
  id NUMBER PRIMARY KEY,
  ...
)
securitygroup (
  id NUMBER PRIMARY KEY
  ...
)
catalog_securitygroup (
  catalog REFERENCES catalog(id),
  securitygroup REFERENCES securitygroup(id)
)
catalog_university (
  catalog REFERENCES catalog(id),
  university REFERENCES university(id)
)

目录：500 000行，catalog_university：500 000，catalog_securitygroup：1 500 000。

我需要从当前大学和当前安全组中按名称排序的目录中选择50行。有一个查询：

SELECT ccc.* FROM (
  SELECT cc.*, ROWNUM AS n FROM (
      SELECT c.id, c.name, c.owner
        FROM catalog c, catalog_securitygroup cs, catalog_university cu
        WHERE c.root = 100
          AND cs.catalog = c.id
          AND cs.securitygroup = 200
          AND cu.catalog = c.id
          AND cu.university = 300
        ORDER BY name
    ) cc 
) ccc WHERE ccc.n > 0 AND ccc.n <= 50;

其中100 - 一些目录，200 - 一些安全组，300 - 一些大学。这个查询在3分钟内从〜170 000返回50行。

但是下一个查询在2秒内返回这些行：

SELECT ccc.* FROM (
  SELECT cc.*, ROWNUM AS n FROM (
      SELECT c.id, c.name, c.owner
        FROM catalog c
        WHERE c.root = 100
        ORDER BY name
    ) cc 
) ccc WHERE ccc.n > 0 AND ccc.n <= 50;

我构建下一个索引：（catalog.id，catalog.name，catalog.owner），（catalog_securitygroup.catalog，catalog_securitygroup.index），（catalog_university.catalog，catalog_university.university）。

第一次查询计划（使用PLSQL Developer）：

http://habreffect.ru/66c/f25faa5f8/plan2.jpg

计划第二个查询：

http://habreffect.ru/f91/86e780cc7/plan1.jpg

有什么方法来优化我的查询？

首先，我假设你的大学和SecurityGroup表格很小。您发布了大型表格的大小，但实际上其他大小是问题的一部分

你的问题来自于你无法首先加入最小的表格。您的连接顺序应该从小到大。但是因为你的映射表不包括安全组到大学表，所以你不能首先加入最小的表。所以你从一个或另一个开始，到一个大桌子，到另一个大桌子，然后有了这个大的中间结果，你必须去一个小桌子。

如果您始终将current_univ和current_secgrp和root用作输入，则希望尽快使用它们进行过滤。唯一的方法就是改变你的模式。事实上，如果你需要的话，你可以保留现有的表格，但是你会用这个建议添加到这个空间。

你已经很好地规范了数据。对于更新速度来说这太棒了...对查询来说不是很好。我们反规范化以加快查询速度（这就是数据仓库的全部原因（确定那是历史））。使用以下列构建单个映射表。

Univ_id，SecGrp_ID，Root，catalog_id。将它作为前三列的索引组织表作为pk。

现在，当您使用全部三个PK值查询该索引时，您将完成该索引扫描，并显示允许的目录标识的完整列表，现在只需单一连接到猫表以获取猫物品详细信息，然后关闭运行。

这些指标可能有用，应该考虑处理

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））

编辑2虽然接受另一个问题，我会提供更多的思考。我创建了一些测试数据并运行一些基准测试。

测试用例在记录宽度方面是最小的（在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)

数据库是postgres 8.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子句）

如果按照上面的方式运行，没有任何额外的索引（除PK和外键），它在冷数据库中运行556毫秒 （这实际上表明我以某种方式简化了示例数据 - 如果我在这里有2-4s，我会更高兴而不是诉诸于少于运营商）

这使我想到了 - 任何直接查询只能连接和过滤（某些表）并返回一定数量的记录，应该在任何像样的数据库上运行，不需要使用游标或数据非规范化（1这些天我必须写一篇文章）。

此外，如果一个查询只返回50行，并且执行简单的等式连接和限制性的等式条件，它应该运行得更快。

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

CREATE INDEX test1 ON catalog (name, id);

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

这就是要点 - 如果你只想获得一页数据，那么你应该只能得到一页数据和查询的执行时间，比如这样的标准化数据，在索引正确的情况下，适当的索引应该在体面硬件上少于100ms。

我希望我没有将案例过分简化为没有比较的地方（正如我之前所说的，由于我不知道目录与多对多表之间关系的基数），因此出现了一些简化。

所以，结论是

如果我是你，我不会停止调整索引（和SQL），直到我得到的查询性能低于200毫秒作为拇指规则。

只有当我找到一个客观的解释，为什么它不能低于这个值时，我会诉诸反规范化和/或游标等。

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

你列出的前三个表都有主键。其他表（catalog_university和catalog_securitygroup）是否也有主键？主键定义了一个非空且唯一的列或一组列，在关系数据库中非常重要。

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

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

您提供的附加索引没有比现有索引更具选择性。例如，（catalog.id，catalog.name，catalog.owner）上的索引是唯一的，但不如（catalog.id）上的现有主键索引有用。如果在catalog.name列中编写查询来选择，则可以执行并索引跳过扫描，但是这开始是代价高昂的（并且在这种情况下甚至是不可能的）。

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

有关Oracle性能调优的一切都是为优化器提供足够的信息和正确的工具（索引，约束等）来正确完成工作。出于这个原因，使用类似DBMS_STATS.GATHER_TABLE_STATS()优化器统计信息很重要。索引应该在Oracle 10g或更高版本中自动收集统计信息。

不知怎的，这对于Oracle优化器来说已经成长得相当长。希望它能解决你的问题。以下是上述内容的总结：

为优化器提供尽可能多的信息，例如，如果索引是唯一的，则声明它。

在您的访问路径中添加索引

找到查询的正确时间，而不受rowwnum的限制。在一个罐子里找到第一个50个M＆Ms比找到前50个红色M＆Ms总是更快

收集优化器统计信息

在所有存在的表上添加唯一/主键。

链接地址: http://www.djcxy.com/p/48847.html

上一篇: How to otimize select from several tables with millions of rows

下一篇: How to transform exceptions into return codes using Spring