邪恶花网站邪恶花_相关的子查询是“邪恶的”和“缓慢的”。 还是他们？

邪恶花网站邪恶花

SQL中一个常见的神话是，相关子查询是邪恶且缓慢的。 例如，此查询在这里：

SELECT 
  first_name, last_name,
  (SELECT count(*) 
   FROM film_actor fa 
   WHERE fa.actor_id = a.actor_id)
FROM actor a

它“强制”数据库引擎运行以下形式的嵌套循环（以伪代码）：

for (Actor a : actor) {
  output(
    a.first_name,
    a.last_name,
    film_actor.where(fa -> fa.actor_id = a.actor_id)
              .size()
}

因此，对于每个演员，请收集所有相应的film_actor并对其进行计数。 这将产生每个演员上演的电影数量。

似乎最好在“批量”中运行此查询，即运行：

SELECT 
  first_name, last_name, count(*)
FROM actor a
JOIN film_actor fa USING (actor_id)
GROUP BY actor_id, first_name, last_name

但是真的更快吗？ 如果是这样，您为什么会期望呢？

批量聚合与嵌套循环

在这种情况下，批量聚集实际上仅意味着我们正在收集所有actor和所有film_actor，然后将它们按操作分组存储在内存中。 执行计划（在Oracle中）如下所示：

-------------------------------------------------------------------
| Id  | Operation              | Name                    | A-Rows |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                         |    200 |
|   1 |  HASH GROUP BY         |                         |    200 |
|*  2 |   HASH JOIN            |                         |   5462 |
|   3 |    TABLE ACCESS FULL   | ACTOR                   |    200 |
|   4 |    INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
-------------------------------------------------------------------

film_actor表中有5462行，对于每个演员，我们将它们合并并分组和汇总，以得到结果。 让我们将其与嵌套循环的计划进行比较：

-----------------------------------------------------------------------
| Id  | Operation         | Name                    | Starts | A-Rows |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                         |      1 |    200 |
|   1 |  SORT AGGREGATE   |                         |    200 |    200 |
|*  2 |   INDEX RANGE SCAN| IDX_FK_FILM_ACTOR_ACTOR |    200 |   5462 |
|   3 |  TABLE ACCESS FULL| ACTOR                   |      1 |    200 |
-----------------------------------------------------------------------

现在，我包括了“开始”行，以说明在收集了所有200个参与者之后，我们将子查询启动200次以获取每个参与者的计数。 但是这次，进行范围扫描。

仅从计划中，我们能说出哪个更快吗？ 批量聚合将需要更多的内存（以加载所有数据），但是算法复杂度较低（线性）。 嵌套循环将需要更少的内存（所有必需的信息都可以直接从索引中获得），但是似乎具有更高的算法复杂度（二次）。

事实是，事实并非如此。

整体聚集确实是线性的，但是根据O（M + N），其中M =演员人数，N =电影演员人数，而嵌套循环不是二次方的，则为O（M log N）。 我们不需要遍历整个索引来获取计数。

在某些时候，较高的复杂性会更糟，但是由于只有少量的数据，所以不是：

在x轴上是N的大小，在y轴上是“复杂度值”，例如，算法使用了多少时间（或内存）。

算法复杂度对大N的影响

算法复杂度对“小” N的影响

以下是关于上述内容的免责声明：

“小N” =“在我的机器上工作”的算法复杂度

没有什么比用测量证明事情更好的了。 让我们运行以下PL / SQL程序：

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 1000;
BEGIN
  v_ts := SYSTIMESTAMP;
  
  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT 
        first_name, last_name,
        (SELECT count(*) 
         FROM film_actor fa 
         WHERE fa.actor_id = a.actor_id)
      FROM actor a
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;
  
  dbms_output.put_line('Nested select: ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;
  
  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT 
        first_name, last_name, count(*)
      FROM actor a
      JOIN film_actor fa USING (actor_id)
      GROUP BY actor_id, first_name, last_name
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;
  
  dbms_output.put_line('Group by     : ' || (SYSTIMESTAMP - v_ts));
END;
/

经过三轮运行，并针对我们的标准Sakila数据库（在此处获取： https : //github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/Sakila ），该数据库具有200个演员和5462个film_actors，我们可以看到嵌套的通过以下方式选择始终胜过批量销售组：

Nested select: +000000000 00:00:01.122000000
Group by     : +000000000 00:00:03.191000000

Nested select: +000000000 00:00:01.116000000
Group by     : +000000000 00:00:03.104000000

Nested select: +000000000 00:00:01.122000000
Group by     : +000000000 00:00:03.228000000

帮助优化者

Twitter上给出了Markus Winand（ http://sql-performance-explained.com的作者）的一些有趣的反馈：

@lukaseder我的初步判断：首先制定计划，然后再加入。 Oracle有时可以自己完成此“下推”操作。

— Markus Winand（@MarkusWinand） 2016年5月28日

第三种选择：将GROUP BY操作嵌套在派生表中：

SELECT
  first_name, last_name, c
FROM actor a
JOIN (
  SELECT actor_id, count(*) c
  FROM film_actor
  GROUP BY actor_id
) USING (actor_id)

通过查询，与“普通”组相比，它产生的计划略好：

--------------------------------------------------------------------
| Id  | Operation               | Name                    | A-Rows |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                         |    200 |
|*  1 |  HASH JOIN              |                         |    200 |
|   2 |   TABLE ACCESS FULL     | ACTOR                   |    200 |
|   3 |   VIEW                  |                         |    200 |
|   4 |    HASH GROUP BY        |                         |    200 |
|   5 |     INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
--------------------------------------------------------------------

像这样将其添加到基准中：

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 1000;
BEGIN
  v_ts := SYSTIMESTAMP;
   
  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT
        first_name, last_name,
        (SELECT count(*) 
         FROM film_actor fa 
         WHERE fa.actor_id = a.actor_id)
      FROM actor a
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;
   
  dbms_output.put_line('Nested select            : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;
   
  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT
        first_name, last_name, count(*)
      FROM actor a
      JOIN film_actor fa USING (actor_id)
      GROUP BY actor_id, first_name, last_name
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;
   
  dbms_output.put_line('Group by                 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;
  
  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      SELECT 
        first_name, last_name, c
      FROM actor a
      JOIN (
        SELECT actor_id, count(*) c
        FROM film_actor
        GROUP BY actor_id
      ) USING (actor_id)
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Group by in derived table: ' || (SYSTIMESTAMP - v_ts));
END;
/

…表明它已经非常接近嵌套的select选项：

Nested select            : +000000000 00:00:01.371000000
Group by                 : +000000000 00:00:03.417000000
Group by in derived table: +000000000 00:00:01.592000000

Nested select            : +000000000 00:00:01.236000000
Group by                 : +000000000 00:00:03.329000000
Group by in derived table: +000000000 00:00:01.595000000

结论

我们已经表明，在某些情况下，相关子查询可能比批量聚合更好。 在Oracle中。 具有中小型数据集。 在其他情况下，这不是正确的，因为M和N的大小会增加我们的两个算法复杂度变量，因此O（M log N）会比O（M + N）差得多。

这里的结论是：不要相信任何最初的判断。 测量。 当您多次运行这样的查询时，慢3倍可以带来很大的不同。 但是也不要替换所有的批量聚合。

喜欢这篇文章吗？ 内容是我们的Data Geekery SQL性能培训的一部分

翻译自: https://www.javacodegeeks.com/2016/05/correlated-subqueries-evil-slow.html

邪恶花网站邪恶花