技術共有|派生テーブルの外部参照

作者:Guilhem Bichot
原文:管長龍
 
LATERALを使用すると、JOINは2番目のテーブル-サブクエリベースの派生テーブル-1番目のテーブルのカラムの値に基づいて定義できるので、1番目のテーブルの各行を再計算できます.標準:

SELECT ... FROM t1, LATERAL (SELECT ... FROM t2
^ WHERE t2.col=t1.col ... ) AS derived;
| |
| |
+---------------------------+

第2のテーブル(派生)において、t 1.colは、最初のテーブルt 1の「横外部参照」である.参照されるテーブルは、派生テーブルの「横」に配置されます(つまり、両方が同じFROM句の一部です).
このLATERAL機能を実装するとき、派生テーブルの非横外部参照をサポートする別の関連機能を追加しました.
階層データの例:

CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);

INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

直接および間接的なレポートを受信した人の数は?このプロシージャにはMySQL再帰構文が含まれています

SELECT emp.*,
(
WITH RECURSIVE reports AS
(
SELECT emp.id
UNION ALL
SELECT e.id
FROM reports AS rep JOIN employees AS e
ON rep.id = e.manager_id
)
SELECT COUNT(*)-1 FROM reports #            
) AS count_of_all_reports
FROM employees AS emp;

説明:従業員1人あたり:

標識量子クエリ(2-12行目)count_を評価of_all_Reports、そのうち:

従業員のすべての直接および間接レポートを再帰的に検索することによってCTE(3-10行目)

を構築する.

CTEの行数(11行目)を計算し、1行を減算して従業員

を計算しない

はカウントを返します.

CTEは共通式(Common Table Expression)を意味し、通常は複雑なクエリーの構築に使用されます.
結果:

+------+---------+------------+----------------------+
| id | name | manager_id | count_of_all_reports |
+------+---------+------------+----------------------+
| 29 | Pedro | 198 | 2 |
| 72 | Pierre | 29 | 0 |
| 123 | Adil | 692 | 0 |
| 198 | John | 333 | 3 |
| 333 | Yasmina | NULL | 6 |
| 692 | Tarek | 333 | 1 |
| 4610 | Sarah | 29 | 0 |
+------+---------+------------+----------------------+
7 rows in set (0.02 sec)

CTEの解釈:SELECT emp.idは再帰を開始し、これは私たちが計算したい現在の従業員への参照です.これはidは、その中の1行のemp(CTEの外)から来ている.
[参照](Reference)から[参照カラム](Reference Columns)まで矢印を描くと、CTEから境界に移動し、周囲のスケール量子クエリの境界に移動し、最終的に上部クエリに到達します.これが「横外部参照」ではない理由です.

SELECT emp.*,
(
WITH RECURSIVE reports AS
( +----------------------------------+
| |
SELECT emp.id |
UNION ALL |
SELECT e.id |
FROM reports AS rep JOIN employees AS e |
ON rep.id = e.manager_id |
) | crosses CTE's bounds
SELECT COUNT(*)-1 FROM reports |
) AS count_of_all_reports | crosses scalar subquery's bounds
FROM employees AS emp; |
^ |
| |
+-----------------------------+ reaches to farthest outside

MySQL 8.0.14までは不可能でした(MySQLはCTEの定義でemp.idが何なのか分かりません).
新しいバージョンMySQLはこの参照を検出しました.それはempでなければならないと結論した.idの各行は、スケール量子クエリとその含むCTEを再計算する.
EXPLAINクエリーの表示:

+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 3 | DEPENDENT DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 4 | UNCACHEABLE UNION | rep | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where |
| 4 | UNCACHEABLE UNION | e | NULL | ref | manager_id | manager_id | 5 | rep.id | 1 | 100.00 | Using index |
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+

MySQLは、派生テーブルに対しても「依存」(外部データに依存)であることを認識していることを示しています.CTEのUNIONの内容が「キャッシュ不可」であることも見られ、毎回再計算しなければならない.
振り返るとMySQL 8.0.14から:

デフォルトでは、上記の例のクエリに示すように、派生テーブルの定義を解析するときに、MySQLは非横外部参照を受け入れます.

LATERALキーワードを追加すると、MySQLも横方向の外部参照を受け入れます.すなわち,派生テーブルを含むFROM句でも検索を行う.

注:レポートカウントの問題には、他の解決策があります.1つの解決策は、再帰CTEを使用して、すべての従業員と各間接マネージャとの間のすべての接続をリストし、この大きな結果を使用して各マネージャを集約することです.効果的ですが、読みにくいです.逆に,階層から個々に小さな集合を生成することを行った.したがって、「階層全体/集約」ではなく、「階層全体/集約/重複の一部」です.