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

news/2024/5/10 10:13:08/文章来源:https://blog.csdn.net/danpu0978/article/details/107275401

邪恶花网站邪恶花

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
DECLAREv_ts TIMESTAMP;v_repeat CONSTANT NUMBER := 1000;
BEGINv_ts := SYSTIMESTAMP;FOR i IN 1..v_repeat LOOPFOR rec IN (SELECT first_name, last_name,(SELECT count(*) FROM film_actor fa WHERE fa.actor_id = a.actor_id)FROM actor a) LOOPNULL;END LOOP;END LOOP;dbms_output.put_line('Nested select: ' || (SYSTIMESTAMP - v_ts));v_ts := SYSTIMESTAMP;FOR i IN 1..v_repeat LOOPFOR rec IN (SELECT first_name, last_name, count(*)FROM actor aJOIN film_actor fa USING (actor_id)GROUP BY actor_id, first_name, last_name) LOOPNULL;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.191000000Nested select: +000000000 00:00:01.116000000
Group by     : +000000000 00:00:03.104000000Nested 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操作嵌套在派生表中：

SELECTfirst_name, last_name, c
FROM actor a
JOIN (SELECT actor_id, count(*) cFROM film_actorGROUP 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
DECLAREv_ts TIMESTAMP;v_repeat CONSTANT NUMBER := 1000;
BEGINv_ts := SYSTIMESTAMP;FOR i IN 1..v_repeat LOOPFOR rec IN (SELECTfirst_name, last_name,(SELECT count(*) FROM film_actor fa WHERE fa.actor_id = a.actor_id)FROM actor a) LOOPNULL;END LOOP;END LOOP;dbms_output.put_line('Nested select            : ' || (SYSTIMESTAMP - v_ts));v_ts := SYSTIMESTAMP;FOR i IN 1..v_repeat LOOPFOR rec IN (SELECTfirst_name, last_name, count(*)FROM actor aJOIN film_actor fa USING (actor_id)GROUP BY actor_id, first_name, last_name) LOOPNULL;END LOOP;END LOOP;dbms_output.put_line('Group by                 : ' || (SYSTIMESTAMP - v_ts));v_ts := SYSTIMESTAMP;FOR i IN 1..v_repeat LOOPFOR rec IN (SELECT first_name, last_name, cFROM actor aJOIN (SELECT actor_id, count(*) cFROM film_actorGROUP BY actor_id) USING (actor_id)) LOOPNULL;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.592000000Nested select            : +000000000 00:00:01.236000000
Group by                 : +000000000 00:00:03.329000000
Group by in derived table: +000000000 00:00:01.595000000