MySQLでDelete/Updateクエリにユニークインデックスを使うことは意味がある

経緯: 日時バッチ中にSelectクエリがタイムアウトする問題が発生

ある時から日時バッチ中にSelectクエリがタイムアウトするようになり、調査することになりました。

調査の結果分かったことは、

当該時刻のDBサーバーのCPU使用率が通常は20%程なのに40%ほどになる
タイムアウトするのは特定のSelectクエリのみ

上記から、単にサーバの高負荷により不特定のクエリがタイムアウトしているのではなく、特定のクエリでデッドロックっぽい現象が起こっているということが分かりました。
しかしLATEST DETECTED DEADLOCKには記録されていませんでした。ただこれに記録されないデッドロックもあるとのことなのでデッドロックの可能性はまだあります。

タイムアウトするクエリ

タイムアウトするクエリはサブクエリやJoinをいくつか含みますが、一般的なSelectクエリです。

日時バッチの内容

日時バッチでは主に不要レコードや不要ファイルの削除を行っています。以下のようなクエリです。

-- 不要レコードの削除
DELETE FROM `table` WHERE CreatedAt < '2025-05-30';

-- 不要ファイル削除時のレコード更新
UPDATE `table` SET WHERE FileObjName = NULL;

デッドロックの可能性を探る

日時バッチ中にのみ特定クエリがタイムアウトすることから、バッチ中のクエリに問題があると思い「Deleteクエリ + デッドロック」を調べていくと、DeleteクエリやUpdateクエリではWhere句でスキャン対象となったすべてのレコード(つまり全行)をロックするらしいことが分かりました。
マニュアルの以下の部分です。

DELETE FROM … WHERE … は、検索で見つかったすべてのレコード上に排他ネクストキーロックを設定します。ただし、一意の行を検索するために一意のインデックスを使用して行をロックするステートメントには、インデックスレコードのロックのみが必要です。

UPDATE … WHERE … は、検索で見つかったすべてのレコード上に排他ネクストキーロックを設定します。ただし、一意の行を検索するために一意のインデックスを使用して行をロックするステートメントには、インデックスレコードのロックのみが必要です。

MySQL 8.0 リファレンスマニュアルより

この時のロックを回避する方法はWhere句にユニークインデックス(プライマリインデックスを含む)の値を使うことのようです。

そこでいくつかのクエリパターンで実験をすることにしました。

Deleteのロック実験

全行をロックしないDelete方法を模索。

テストデータ

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `age` int NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `users` VALUES (10,'hanako',30),(20,'tarou',20),(30,'jirou',40),(40,'yui',45);

-- Show current locks
SELECT * from performance_schema.data_locks
WHERE OBJECT_NAME = 'users'

-- Use begin and rollback for multiple trying

通常指定

DELETE FROM users WHERE age < 30;
 => 全行ロック

サブクエリ

DELETE FROM users WHERE id IN (SELECT * FROM (SELECT id FROM users WHERE age < 30) sub);
 => 全行ロック(2重に)

With句

WITH ToDelete AS 
(
   SELECT id FROM users WHERE age < 30
)   
DELETE FROM users USING users JOIN ToDelete ON users.id = ToDelete.id;
 => 全行ロック

PK指定

DELETE FROM users WHERE id IN (10, 20);
 => 対称行のみロック

PK指定 + オプティマイザヒント追加

DELETE /*+INDEX(users PRIMARY) */ FROM users WHERE id IN (10, 20);
 => 対称行のみロック

実験の結果

実験の結果、全行ロックを回避するにはユニークインデックス(PK)を指定するしかないことが分かりました。(もし他の方法があればぜひ教えて下さい!)

オプティマイザヒントによるインデックス強制指定

IN句に大量のIDを指定するとフルスキャンとなり全行ロックになってしまう問題があるらしく、その対策としてMySQL 8.0から使えるオプティマイザヒントを追加します。

特定の量でクエリを分割

IN句に大量のIDを指定するとクエリ最大長を超える可能性がある、またサーバーに負荷を与える恐れがあるため、対策として1000件ずつなど特定の量でクエリを分割します。

対策と効果

上記の実験から以下の3つの対策を行いました。

削除対象レコードのPKをSelectし、Delete .. WhereでPK指定の2ステップにする。Updateも同様
オプティマイザヒントによるインデックス強制使用
特定の量でクエリを分割

対策を行った結果CPU使用率が40%から25%程に軽減しました。またSelectクエリのタイムアウトも発生しなくなりました。

まとめ

今回の件で全行ロックをしないDelete/Updateはデッドロック回避やサーバー負荷軽減の効果があるということがわかりました。今後は作法としてDelete/UpdateはPK指定で行うようにしようと思います。

ただ今回のタイムアウトがデッドロックだったかどうかは特定できませんでした。(やる必要がなくなったため)