【MySQL—SQLプログラミング】サブクエリ

サブクエリとは、あるSELECT文に別のSELECT文をネストすることです.
通常、サブクエリを使用するメリットは次のとおりです.

サブクエリは、構造化されたクエリを許可し、1つのクエリ文の各部分を区切ることができます.

サブクエリは、複雑なJOINおよびUNIONによって実装される必要があるいくつかの動作を実行するための別の方法を提供する.

は、多くの人から見れば、サブクエリの読み取り可能性が高い.

サブクエリは、スカラー、列、行、および表サブクエリと呼ばれるスカラー(単一値)、テーブル(1行または複数行、および1列または複数列)を返します.特定の種類の結果を返すサブクエリは、特定の文脈でのみ使用されることが多い.サブクエリは、DISTINCT、GROUP BY、ORDERBY、LIMIT、JOIN、UNIONなど、通常のSELECTに含まれることができる任意のキーワードまたは句を含むことができる.
サブクエリの制限は、その外部文がSELECT、INSERT、UPDATE、DELETE、SETまたはDOのいずれかである必要があります.もう1つの制限は、現在、ユーザが1つのサブクエリで1つのテーブルを変更したり、同じテーブルで選択したりすることはできないことである.このような操作は、通常のDELETE、INSERT、REPLACE、UPDATE文で使用できるが、サブクエリに対して同時にこのような操作を行うことはできない.
共通キーワード(ANY/IN/SOME/AL)
ANY、IN、SOME、ALLを使用したサブクエリの構文は次のとおりです.

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)

ANYキーワードは、比較オペレータとともに使用する必要があります.ANYキーワードは、「サブクエリが返す列のいずれかの数値に対して、比較結果がTRUEであればTRUEを返す」という意味である.例:SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
キーワードINは「=ANY」の別名であり、キーワードSOMEはANYの別名である.
ALLキーワードも比較オペレータとともに使用する必要があります.ALLは、「サブクエリが返すカラムのすべての値について、比較結果がTRUEであればTRUEを返す」という意味です.
独立したサブクエリ
独立したサブクエリは、外部クエリに依存せずに実行されるサブクエリです.
例を挙げると、毎月の最終受注日に発生する受注を問い合わせる受注表があります.ここでは、毎月の最終発注日が毎月の最終日ではない可能性があります.したがって、毎月の最終オーダー日はサブクエリで判断する必要があります.クエリ文は次のとおりです.

SELECT orderid,customerid,employeeid,orderdate
    FROM orders
    WHERE orderdate IN
        ( SELECT MAX(orderdate)
                FROM orders
                GROUP BY (DATE_FORMAT(orderdate,'%Y%m'))
        )

サブクエリは、受注を月ごとにグループ化し、各グループの最大の日付結果を返します.これが、毎月の最終受注が生成された日付です.その後、外部クエリーは、サブクエリーで取得した日付によってすべての受注情報を取得します.
この問題は解決されたように見えるが、実際にはそんなに簡単ではない.このデータ量の少ないデータベースで上記のSQL文を実行するのに6.08秒かかるため、EXPLAINで文を分析すると、独立したサブクエリが関連するサブクエリに変換される問題が見つかります.
これはMySQLオプティマイザがINサブクエリを最適化する際に存在する問題であり、MySQLオプティマイザはIN文の最適化に対して「LAZY」である.IN句の場合、IN('a','b','c')のような明示的なリスト定義でなければ、IN句はEXISTSの関連サブクエリに変換されます.
サブクエリと外部クエリがそれぞれM行とN行を返す場合、サブクエリはO(M+N)ではなくO(N+M*N)としてスキャンされる.
したがって、上記の質問のSQL文に対して、MySQLデータベースのオプティマイザは、次の関連サブクエリに対応付けて変換します.

SELECT orderid,customerid,employeeid,orderdate
    FROM orders AS A
    WHERE EXISTS
        ( SELECT ＊
                FROM orders
                GROUP BY(DATE_FORMAT(orderdate,'%Y%M'))
                HAVING MAX(orderdate)= A.OrderDate
        );

ほとんどの場合、MySQLデータベースは独立したサブクエリを関連するサブクエリに変換します.
しかし、上記の文については、最適化する方法があります.遅い理由は、独立したサブクエリが相関サブクエリに変換されるためであり、この相関サブクエリは複数回のグループ化操作を必要とするためである.別の方法で、サブクエリをネストし、複数回のグループ化操作を回避できます.文は次のとおりです.

SELECT orderid,customerid,employeeid,orderdate
    FROM orders A
    WHERE EXISTS
        ( SELECT ＊ FROM (SELECT MAX(orderdate) AS orderdate
                FROM orders
                GROUP BY (DATE_FORMAT(orderdate,'%Y%M')) ) B
                WHERE A.orderdate = B.orderdate
        );

相関サブクエリ
関連サブクエリとは、外部クエリの列を参照したサブクエリのことです.つまり、サブクエリは外部クエリの各行に対して1回計算されます.
たとえば、1人の従業員が同じ受注日を持つ複数の受注を持つ可能性があるため、各従業員に対して複数行のデータが返される可能性がある各従業員の最大受注日の受注を問い合せます.
この例では、サブクエリは外部クエリに関連付けられ、内部クエリのemployeeidを外部のemployeeidと一致させる必要があります.

SELECT orderid,customerid,employeeid,orderdate,requireddate
    FROM orders AS A
    WHERE orderdate =
        ( SELECT MAX(orderdate) FROM orders AS B
                WHERE A.employeeid=B.employeeid);

しかし、論理IOが大量に実行されているため、この文の実行速度が遅すぎて、どのような方法で最適化することができますか?
まず、一意のインデックスを追加して処理を高速化することを考慮します.

CREATE UNIQUE INDEX idx_empid_od_rd_oid
    ON orders(employeeid,orderdate,requireddate,orderid)

速度は一定に向上しましたが、問題は、関連するサブクエリが外部サブクエリのカラムと複数回比較する必要があることです.
サブクエリと外部サブクエリの複数回の比較操作を避けるために、関連サブクエリの場合、派生テーブルで書き換えることができます.上記のSQLの場合、次のように書き換えることができます.

SELECT
        A.orderid,A.customerid,A.employeeid,
        B.orderdate,requireddate
    FROM orders AS A,
    (SELECT employeeid,MAX(orderdate) AS orderdate FROM orders
    GROUP BY employeeid) AS B
    WHERE A.employeeid=B.EmployeeID AND A.OrderDate=B.orderdate;

このとき,関連サブクエリの実行過程がなくなり,論理IOが大幅に減少し,インデックスも利用でき,実行速度が大幅に向上した.
EXISTS述語
EXISTSは非常に強力な述語であり、指定されたクエリがローを生成するかどうかをデータベースが効率的にチェックできます.通常、EXISTSの入力はサブクエリであり、外部クエリに関連付けられますが、これは必須ではありません.サブクエリがローを返すかどうかに応じて、述語はTRUEまたはFALSEを返します.他の述語や論理式とは異なり、入力サブクエリがローを返すかどうかにかかわらず、EXISTSはUNKNOWNを返さない.サブクエリのフィルタがローに対してUNKNOWNを返す場合、ローは返さないことを示すため、このUNKNOWNはFALSEとみなされます.
現在、ほとんどの場合、INとEXISTSは同じ実行計画を持っている.しかし、NOT INとNOT EXISTSは非常に異なる実行計画を持っていることに注意してください.
EXISTSとINの1つの違いは3値論理の判断に現れる.EXISTSは常にTRUEまたはFALSEを返しますが、INの場合はTRUE、FALSEの値に加えてNULLの値に対してUNKNOWNを返すこともあります.ただし、フィルタではUNKNOWNの処理方法はFALSEと同じであるため、INを使用する場合はEXISTSを使用する場合と同様にSQLオプティマイザは同じ実行計画を選択する.
しかし,入力リストにNULL値が含まれている場合,NOT EXISTSとNOT INの差が顕著に現れる.入力リストにNULL値が含まれている場合、INは常にTRUEとUNKNOWNを返すので、NOT INは常にNOT TRUEとNOT UNKNOWN、すなわちFALSEとUNKNOWNを返す.一方、NOT EXISTSについては、常にTRUEおよびFALSEを返す.
個人的な心得:「非存在型」の問題については,NOT EXISTS述語を用い,残りの関連サブクエリを用いたシーンは可能な限り関連派生テーブルに書き換え,適切なインデックスを追加すべきである.