【MySQL】膨大なレコードを持つテーブルにJOIN前に範囲を指定する

はじめに

業務にて、MySQLのストアドプロシージャを触ることになりました。
その際、長時間かかるストアドプロシージャを分割と高速化で指定時間内に納めなければならなかったのですが、ストアドプロシージャはおろか、MySQLどころかSQLすら実務で触ったことがなかった為、苦労しました。
せっかくなので、その時に苦労したことや気づいたことを備忘録として残したいと思います。

前提条件

データベース：MySQL5.7
データを削除したいtableAのレコード数：約1億件
tableAにJOINするjoin_tableAのレコード数：約20件
tableAにJOINするjoin_tableBのレコード数：約10件
1000件処理するごとに1秒SLEEPしなければならない
分割せずにストアドプロシージャを実行した場合の総実行時間：約24時間
ストアドプロシージャを複数回に分割しても良いので1回の実行時間が４～5時間に収まるようにしなければならない

join_tableA、join_tableBはtableAに対する属性テーブルのようなものと認識していただいて問題ありません。

事象

以下のようなストアドプロシージャを作成し実行しました。
※実行したプロシージャをすべて書くとかなり長くなる、また、お見せ出来ない部分もあった為、一部省略して記載しています。
本プロシージャをそのまま実行しても動作しません。ご了承ください。

CREATE PROCEDURE delete_tableA_01()
BEGIN
    -- カーソルの取得部
    DECLARE cur_tgt CURSOR FOR
        SELECT
            DISTINCT tableA.id
            FROM
                tableA
                JOIN join_tableA ON tableA.join_keyA = join_tableA.join_keyA
                JOIN join_tableB ON join_tableA.join_keyB = join_tableB.join_keyB
            WHERE
                tableA.id between 1 and 20000000

                -- その他条件(記事の内容に関係ないので省略)
            ORDER BY
                tableA.id ASC;

    -- エラー処理の記述(省略)

    --読みだしたカーソルに対して処理を実行する部分(ストアドプロシージャ実行部)
    SET autocommit=0;
    OPEN cur_tgt;
    read_loop: LOOP
    FETCH cur_tgt INTO currentid;

        -- 取得したカーソルと同じidをDELETE
        DELETE FROM
        tableA
        WHERE
            id = currentid;

        --1000件DELETEするごとに1秒sleepし、コミット(省略)

    END LOOP;
    CLOSE cur_tgt;
    SET autocommit=1;
END

この時、WHERE句で1億件のidを5等分した範囲を指定したので、もともとの実行時間24時間を大体5で割って5時間ぐらいになるかな・・・と、簡単に考えていました。

しかし、
ストアドプロシージャの総実行時間：19時間

期待していた結果は得られませんでした。

調査：実行時間の内訳

下記2パターンのどちらか、またはその両方なのか不明だった為、その二つを区別して実行時間を計測しました。

カーソルの取得部で時間がかかっているのか
カーソルに対して処理を実行する部分(ストアドプロシージャ実行部)で時間がかかっているのか

WHERE句での範囲指定前
- カーソルの取得部：17時間
- ストアドプロシージャ実行部：7時間

WHERE句での範囲指定後
- カーソルの取得部：17時間
- ストアドプロシージャ実行部：2時間

どうやら、ストアドプロシージャ実行部にしか高速化の効果が出ていないようです。

原因としては、SELECT以外にもJOINに時間がかかっているのが理由でした。
WHERE句で範囲を指定しただけではJOIN時に1億件全件のレコードを確認していました。

結論：膨大なレコードを持つテーブルにJOIN前に範囲を指定する

以下内容でカーソルの取得部を修正しました。

DECLARE cur_tgt CURSOR FOR
    SELECT
        DISTINCT tableA.id
    FROM
        tableA
    JOIN join_tableA ON tableA.join_keyA = join_tableA.join_keyA AND tableA.id between 1 and 20000000
    
    JOIN join_tableB ON join_tableA.join_keyB = join_tableB-.join_keyB
 -- WHERE
        -- その他条件(記事の内容に関係ないので省略)
    ORDER BY
        tableA.id ASC;

上記6行目のように記載することで、指定した範囲に対してJOINを実行出来るみたいです。

その後、実行時間を計測しました。

WHERE句での範囲指定後
- カーソルの取得部：17時間
- ストアドプロシージャ実行部：2時間

JOIN前に範囲指定後
- カーソルの取得部：2時間
- ストアドプロシージャ実行部：2時間

総実行時間約4時間と期待通りにストアドプロシージャを5時間以内に収めることが出来ました。

また改修後、ストアドプロシージャ実行後に、以下SELECT文でWHERE句を用いて範囲指定し確認しましたが、対象データは問題なく削除されていました。

SELECT
    DISTINCT tableA.id
FROM
    tableA
    JOIN join_tableA ON tableA.join_keyA = join_tableA.join_keyA
    JOIN join_tableB ON join_tableA.join_keyB = join_tableB.join_keyB
WHERE
    tableA.id between 1 and 20000000

    -- その他条件(性能には関係ないので省略)
;

おわりに

WHERE句で条件に範囲指定すればJOIN時に参照される範囲も絞られるものだと考えていた為、意外と対応に時間がかかりました。

本記事が誰かの助けになれば幸いです。

Check

株式会社カサレアル BSブログ