古いログを消すつもりで DELETE FROM access_logs WHERE created_at < '2026-01-01'; を1回で流すと、数百万行の削除が1つのトランザクションになります。終わるまで行ロックを持ち続け、undoログ（取り消し用の記録）もふくらみます。レプリカ（複製先のサーバー）への反映も大きく遅れます。この記事では、LIMIT 付きのDELETE・UPDATEを何回かに分けて流す方法を、ストアドプロシージャのループと主キーの範囲で区切る方法の2通りで説明します。UPDATEで処理済みの行を除き忘れると途中で止まる落とし穴や、JOINと組み合わせたときのエラーも、実際の出力で確かめます。
以下の結果は MySQL 8.0 で実際に実行したものです。
このシナリオで考える
アクセスログのテーブル access_logs を使います。2025年7月1日0時から1時間に1行ずつ、合わせて10,000行あるとします。created_at にはインデックスを付けておきます。
CREATE TABLE access_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
path VARCHAR(50) NOT NULL,
archived TINYINT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL,
INDEX idx_created_at (created_at)
);
SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO access_logs (user_id, path, created_at)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n % 100 + 1,
ELT(n % 3 + 1, '/', '/items', '/cart'),
TIMESTAMP '2025-07-01 00:00:00' + INTERVAL (n - 1) HOUR
FROM seq;2026年より前のログを消すことにしました。消す前に、対象の件数とidの範囲を確かめておきます。
SELECT COUNT(*) AS target_rows, MIN(id) AS min_id, MAX(id) AS max_id
FROM access_logs
WHERE created_at < '2026-01-01';+-------------+--------+--------+
| target_rows | min_id | max_id |
+-------------+--------+--------+
| 4416 | 1 | 4416 |
+-------------+--------+--------+本番なら数百万行になる場面です。ここでは4,416行を1,000行ずつ消していきます。
LIMIT付きのDELETEで少しずつ消す
MySQLの単一テーブルのDELETEには、ORDER BY と LIMIT を付けられます。LIMITで1回に消す行数の上限を決め、ORDER BYで消す順番を決めます。
DELETE FROM access_logs
WHERE created_at < '2026-01-01'
ORDER BY id
LIMIT 1000;
SELECT ROW_COUNT() AS deleted;+---------+
| deleted |
+---------+
| 1000 |
+---------+ROW_COUNT() は、直前のDELETE・UPDATE・INSERTで変わった行数を返す関数です。1,000が返ったなら、まだ残りがあるかもしれません。LIMITより少ない数が返ったら、対象をすべて消し終えたと判断できます。
このDELETEを、ROW_COUNT()がLIMITを下回るまでくり返します。autocommit（1文ごとに自動で確定する設定）がオンなら、1回ごとにコミットされ、ロックもそこで外れます。1回で消す場合との違いは次のとおりです。
| 項目 | 1回で消す | 1,000行ずつ消す |
|---|---|---|
| トランザクションの大きさ | 全件で1つ | 1,000行ずつ |
| 行ロックを持つ時間 | 削除が終わるまで | 1回分の間だけ |
| 途中で止めたとき | 全件をロールバック（これも時間がかかる） | 消した分は消えたまま |
| レプリカへの反映 | 大きなまとまりが一度に届く | 小さく分かれて届く |
| かかる時間の合計 | 短め | 待ち時間の分だけ長め |
ストアドプロシージャでループさせる
くり返しはアプリ側のループで書いても、ストアドプロシージャで書いてもかまいません。ここではSQLだけで動くプロシージャにします。
DELIMITER //
CREATE PROCEDURE purge_old_logs(IN p_before DATETIME, IN p_batch INT)
BEGIN
DECLARE v_rows INT DEFAULT 0;
DECLARE v_total INT DEFAULT 0;
DECLARE v_loops INT DEFAULT 0;
REPEAT
DELETE FROM access_logs
WHERE created_at < p_before
ORDER BY id
LIMIT p_batch;
SET v_rows = ROW_COUNT();
SET v_total = v_total + v_rows;
SET v_loops = v_loops + 1;
DO SLEEP(0.1); -- 次の回の前に少し待つ
UNTIL v_rows < p_batch END REPEAT;
SELECT v_loops AS batches, v_total AS deleted_rows;
END//
DELIMITER ;REPEAT ... UNTIL は、条件が真になるまで中身をくり返す構文です。1回消すたびに DO SLEEP(0.1) で0.1秒待ち、ほかのクエリやレプリカが追いつく時間を作ります。mysqlクライアントで作るときは、DELIMITER で文の区切りを一時的に変えてください。
CALL purge_old_logs('2026-01-01', 1000);+---------+--------------+
| batches | deleted_rows |
+---------+--------------+
| 4 | 3416 |
+---------+--------------+先ほどの1,000行と合わせて、4,416行がすべて消えました。4回目は416行で、LIMITの1,000を下回ったのでループが終わっています。もう一度数えると0件です。
SELECT COUNT(*) AS remaining_old FROM access_logs WHERE created_at < '2026-01-01';+---------------+
| remaining_old |
+---------------+
| 0 |
+---------------+UPDATEは処理済みの行を条件から外す
UPDATEも同じようにLIMITで分割できます。ただし、WHEREに「まだ処理していない」という条件を入れないと、ループが途中で止まります。2026年4月より前のログに archived = 1 の印を付ける例で見てみます。同じUPDATEを2回流します。
UPDATE access_logs
SET archived = 1
WHERE created_at < '2026-04-01'
ORDER BY id
LIMIT 1000;
SELECT ROW_COUNT() AS updated; -- 2回くり返す1回目 2回目
+---------+ +---------+
| updated | | updated |
+---------+ +---------+
| 1000 | | 0 |
+---------+ +---------+2回目は0行でした。同じ1,000行がまた選ばれ、すでに1なので値が変わらなかったためです。MySQLのROW_COUNT()は、条件に合った行ではなく、実際に値が変わった行の数を返します。ループの終わりを「LIMITより少ないとき」にしていると、ここで止まってしまいます。
SELECT archived, COUNT(*) AS cnt
FROM access_logs
WHERE created_at < '2026-04-01'
GROUP BY archived;+----------+------+
| archived | cnt |
+----------+------+
| 1 | 1000 |
| 0 | 1160 |
+----------+------+1,160行が手つかずのまま残りました。エラーも出ないので、気づきにくい失敗です。条件に archived = 0 を足すと、処理済みの行は選ばれなくなります。
UPDATE access_logs
SET archived = 1
WHERE created_at < '2026-04-01' AND archived = 0
ORDER BY id
LIMIT 1000;
SELECT ROW_COUNT() AS updated; -- 2回くり返す1回目 2回目
+---------+ +---------+
| updated | | updated |
+---------+ +---------+
| 1000 | | 160 |
+---------+ +---------+2回目で残りの160行が更新され、LIMITを下回ったので終わりです。分割するUPDATEは、更新した行が次の回の条件から外れるように書くと覚えてください。
主キーの範囲で区切る
LIMITでくり返す方法は、回を重ねるごとに少しずつ遅くなることがあります。InnoDBは消した行をすぐには片づけず、印を付けておいて後から掃除（パージ）します。毎回インデックスの先頭から探すため、まだ掃除されていない行を読み飛ばす手間が増えていきます。
行数が多いときは、主キー（id）の範囲で区切ると読む場所が毎回決まります。最初にidの最小値と最大値を調べ、500ずつ範囲をずらしながら消します。2026年1月のログで試します。
DELIMITER //
CREATE PROCEDURE purge_by_id_range(IN p_before DATETIME, IN p_step INT)
BEGIN
DECLARE v_start INT;
DECLARE v_max INT;
DECLARE v_total INT DEFAULT 0;
DECLARE v_loops INT DEFAULT 0;
SELECT MIN(id), MAX(id) INTO v_start, v_max
FROM access_logs
WHERE created_at < p_before;
WHILE v_start <= v_max DO
DELETE FROM access_logs
WHERE id >= v_start AND id < v_start + p_step
AND created_at < p_before;
SET v_total = v_total + ROW_COUNT();
SET v_loops = v_loops + 1;
SET v_start = v_start + p_step;
DO SLEEP(0.1);
END WHILE;
SELECT v_loops AS batches, v_total AS deleted_rows;
END//
DELIMITER ;
CALL purge_by_id_range('2026-02-01', 500);+---------+--------------+
| batches | deleted_rows |
+---------+--------------+
| 2 | 744 |
+---------+--------------+2026年1月の744行（31日×24時間）が2回で消えました。範囲の中に対象外の行が混ざっていても、created_at の条件があるので消えません。idに抜けが多いテーブルでは、1回あたりの件数がばらつく点に気をつけてください。
| 方法 | 書きやすさ | 回を重ねたときの速さ | 向いている場面 |
|---|---|---|---|
| LIMITでくり返す | 簡単 | 少しずつ遅くなることがある | 数十万行くらいまで |
| 主キーの範囲で区切る | 少し手間 | ほぼ一定 | 数百万行以上で、idが連番に近い |
よくある落とし穴と注意点
JOINで対象を選んで消したいとき、2つのエラーによく出会います。退会したユーザーの一覧 withdrawn_users（user_idが7と42の2行）を作り、そのユーザーのログを消す例で見てみます。
DELETE l
FROM access_logs AS l
JOIN withdrawn_users AS w ON w.user_id = l.user_id
LIMIT 100;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 100' at line 4複数テーブルのDELETEには、ORDER BYもLIMITも付けられません。そこでINのサブクエリでidを100件選ぼうとすると、今度は別のエラーになります。
DELETE FROM access_logs
WHERE id IN (
SELECT l.id
FROM access_logs AS l
JOIN withdrawn_users AS w ON w.user_id = l.user_id
ORDER BY l.id
LIMIT 100
);ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'INの中のサブクエリにはLIMITを書けません。サブクエリをもう1段、派生テーブル（FROMの中に置いたサブクエリ）で包むと動きます。
DELETE FROM access_logs
WHERE id IN (
SELECT id FROM (
SELECT l.id
FROM access_logs AS l
JOIN withdrawn_users AS w ON w.user_id = l.user_id
ORDER BY l.id
LIMIT 100
) AS t
);
SELECT ROW_COUNT() AS deleted;+---------+
| deleted |
+---------+
| 96 |
+---------+残っていた96行が1回で消えました。100を下回ったので、これで全部です。ほかにも、次の点で失敗しがちです。
- WHEREの列にインデックスがないと、小さなDELETEのたびに全件を読むことになります。分割する前に
EXPLAINで確かめてください START TRANSACTIONを開いたままループすると、全体が1つのトランザクションに戻ってしまいます- テーブルを丸ごと空にするなら、DELETEを分割するより
TRUNCATE TABLEのほうが速く終わります - 消す行が残す行よりずっと多いときは、残す行を新しいテーブルにコピーし、
RENAME TABLEで入れ替える方法もあります。MySQLの公式マニュアルでも紹介されています
なお、PostgreSQLのDELETEとUPDATEにはLIMITがありません。WHERE id IN (SELECT id FROM ... LIMIT 1000) のようにサブクエリで対象を選びます。PostgreSQLではINの中にLIMITを書けるので、派生テーブルで包む必要はありません。
まとめ
大量のDELETE・UPDATEは、LIMITを付けて1,000行ほどずつ流し、ROW_COUNT()がLIMITを下回ったら止めます。1回ごとにコミットして少し待つと、ロックの時間やレプリカの遅れを小さくできます。UPDATEでは処理済みの行を条件から外してください。外し忘れると、エラーも出ずに途中で止まります。件数が多いときは主キーの範囲で区切り、JOINで対象を選ぶときは派生テーブルで包んだサブクエリを使います。
もっと学ぶなら【PR】
- MySQL徹底入門 第5版：MySQL の機能を広く解説した本。ストアドプロシージャやトランザクションの細かい動きを調べたいときに
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 リファレンスマニュアル: DELETE Statement
- MySQL 8.0 リファレンスマニュアル: UPDATE Statement
- MySQL 8.0 リファレンスマニュアル: Information Functions（ROW_COUNT）
- DELETE・TRUNCATE・DROPの違い（当サイト）
- デッドロックの原因と回避方法（当サイト）
アイキャッチ画像: Photo by Kelli McClintock on Unsplash
