nikkie-ftnextの日記

イベントレポートや読書メモを発信

MySQLで、サブクエリで列挙したIDでDELETEしようとしたら、あまりにも遅かったことからの学び

はじめに

穴掘って埋まってますぅ1 nikkieです。

公開日の学びではないのですが、Today I Learnedです。
私がMySQLのサブクエリというものを全然分かっていませんでした

目次

やりたいこと：IDを列挙してDELETEしたい

以下の記事を例にします。

Productsという製品のテーブルがあります。
全レコード数は40件と少ないですが、説明のための例として使います。
今回学びを得たテーブルとの違いですが、ProductsはPKが1カラムですが、実際に扱ったテーブルではPKが複数列でした（ぱっと用意できなかったのでこのまま行きます）。

このテーブルから特定の条件を満たすレコードを削除したいです。
特定の条件とは例えば「Order_Detailsにない（＝注文されていない）」。

実現方法としては、特定の条件を満たすレコードを別のテーブルに複製し、そこを見てProductsから消すことを考えました。

CREATE TABLE ProductsToDelete AS
SELECT
    p.ProductNumber
FROM
    Products AS p
    LEFT JOIN Order_Details od on p.ProductNumber = od.ProductNumber
WHERE
    od.ProductNumber IS NULL;

経験したこと：サブクエリではあまりに遅い

※Productsから削除するために制約を外す必要があります。
ALTER TABLE Product_Vendors DROP CONSTRAINT Product_Vendors_FK00;

当初書いたクエリが以下

DELETE FROM Products
WHERE ProductNumber IN (
    SELECT ProductNumber
    FROM ProductsToDelete
);

これって以下と同じだと思って書いたのですが、

DELETE FROM Products
WHERE ProductNumber IN (4, 23);

それぞれ実行してみると所要時間が全然違ったんです。
実際に扱ったProductsテーブルは100万オーダー、ProductsToDeleteは1000オーダーなんですが、ProductsToDeleteを数件にしてもめっちゃ時間がかかるという（待っていても終わらないくらい。※複数組PKが効いてます）

サブクエリでハマった落とし穴

私が思っているとおりにSQLが解釈されていませんでした。
EXPLAINを見たところ、スキャンするrowsはProducts全行（インデックスなし）となっていて、思い知りました。
実際のテーブルに私が書いたサブクエリでは、複数組PKのうち1つしか指定しなかったので、フルスキャンとして動いていました

先人の記事がありました。

DELETEではなくSELECTですが、今回直面した事象と同様と思われます。

MySQLの場合、サブクエリを含むSQLは外側から内側に向かって順に実行されるらしいです。（特性① 参照）

特性② IN句とサブクエリの組み合わせは内部的にEXISTSに変換しちゃう

回避策としては

・IN句の中をサブクエリではなく事前にプログラムで取得しておいてカンマ区切りで渡す
・JOINしちゃう

今回はJOINを使いました

列挙したIDで削除（JOIN篇）

DELETE
    p
FROM
    Products AS p
    JOIN ProductsToDelete pd on p.ProductNumber = pd.ProductNumber;

「DELETE p FROM」としている部分は、こちらの記事に学びをまとめています。

終わりに

MySQLで「特定の条件を満たすレコードを別のテーブルに複製し、そこを見て削除する」ときに、サブクエリで書いたためにあまりにも遅いという事象を経験しました。
「直値を並べているのと同じだろう」と想定して書いたのですが、MySQLの実行順序は全く異なることを知りました。

  • nikkieの想定（の言い換え）：内側のサブクエリが評価されてから、外側のクエリ全体が評価される
  • MySQL：外側から内側へ

EXPLAINってMySQLの気持ちが分かるので、すごいですね。

（自分にとっての）わかりやすさからサブクエリが最初の選択肢になっていたのですが、これは優先順位を見直したほうがいいかもしれません。
パフォーマンスをすこぶる失ってしまう選択になっているのかも。
ただ『Effective SQL』にはサブクエリの章があるので、有用なシーンはつかんでおきたいなと思っています。

  1. 先日お迎え ↩