MySQL 最適化インデックスのマージによって発生するデッドロックを解決する方法

背景

本番環境でデッドロックが発生しました。デッドロックのログを確認すると、デッドロックの原因は 2 つの同一の更新ステートメント (where 条件の値のみが異なる) であることがわかりました。

は次のとおりです:

UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;

最初は理解するのが難しかったですが、多くの調査と研究を経て、デッドロック形成の具体的な原理を分析しました。同じ問題を抱えている友人たちに役立つことを願って、この記事をみんなと共有したいと思います。

MySQL には多くの知識ポイントがあるため、多くの名詞はここではあまり紹介しません。興味のある友人は、特別に詳しく学習してフォローしてください。

デッドロック ログ

*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

上記のデッドロック ログを簡単に分析します:

• 1. 最初の内容 (行 1 ～ 9) )、行 6 はトランザクション (1) によって実行される SQL ステートメントで、行 7 と 8 はトランザクション (1) が idx_status インデックスの X ロックを待機していることを意味します;

• 2。コンテンツの 2 番目のブロック (11 行目から 19 行目) では、16 行目はトランザクション (2) によって実行される SQL ステートメントであり、17 行目と 18 行目は、トランザクション (2) が idx_status インデックス X ロック オンを保持していることを意味します。

意味: トランザクション (2) は、PRIMARY インデックスの X ロックの取得を待機しています。 (ただし、ギャップではないということは、ギャップ ロックではないことを意味します)
4. 最後の文は、MySQL がトランザクション (1) をロールバックしたことを意味します。
テーブル構造

CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trans_id` varchar(21) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

テーブル構造からわかるように、

trans_id

columnuniq_trans_id # に一意のインデックスがあります。 ##,status 列 idx_status には通常のインデックスがあり、id 列は主キー インデックス PRIMARY です。 InnoDB エンジンには 2 種類のインデックスがあります:

クラスター化インデックス:
• データ ストレージとインデックスを配置します。インデックス構造のリーフ ノードは一緒に行データを格納します。

補助インデックス:
• 補助インデックスのリーフ ノードには、クラスター化インデックスのキー値である主キー値が格納されます。
  主キー インデックス
PRIMARY

はクラスター化インデックスであり、行データはリーフ ノードに格納されます。

uniq_trans_id インデックスと idx_status インデックスは補助インデックスであり、リーフ ノードには主キー値 (id 列の値) が格納されます。 補助インデックスを通じて行データを検索する場合、最初に補助インデックスを通じて主キー ID を見つけ、次に主キー インデックスを通じて二次検索 (これもテーブルに呼び出されます) を実行し、最後に行データを見つけます。

実行計画

実行計画を見ると、update ステートメントでインデックスのマージが使用されていることがわかります。つまり、このステートメントでは ## の両方が使用されています。 #uniq_trans_id インデックスが再度使用されます

idx_status

インデックス、 intersect(uniq_trans_id,idx_status) を使用することは、2 つのインデックスを通じて交差を取得することを意味します。 index_merge が使用される理由 MySQL5.0 より前は、テーブルは一度に 1 つのインデックスしか使用できず、条件付きスキャンで複数のインデックスを同時に使用することはできませんでした。ただし、5.1 からは、

index merge

最適化テクノロジが導入され、複数のインデックスを使用して同じテーブルで条件付きスキャンを実行できるようになりました。

たとえば、実行プラン内のステートメント:

UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;

MySQL は、条件 trans_id = ‘38’ に基づいて uniq_trans_id# を使用します。

## インデックスはリーフ ノードに保存されている ID 値を検索します。同時に、条件

status = 0 に従って、idx_status インデックスを使用して、リーフ ノードに保存された ID 値; その後、2 つのグループ ID 値が交差され、最終的に交差 ID がテーブルに返されます、つまり、リーフ ノードに保存された行データが PRIMARY インデックスを通じて検出されます。 多くの人がここで疑問を抱くかもしれません。uniq_trans_id はすでに一意のインデックスです。このインデックスを通じて見つかるのは最大でも 1 つのデータだけです。では、なぜ MySQL オプティマイザーは 2 つのインデックスを使用するのでしょうか。 2 つのインデックスの共通部分を検索し、クエリのためにテーブルに戻ります。これにより、別の idx_status

インデックス検索プロセスが追加されませんか?これら 2 つの状況の実行プロセスを分析してみましょう。

最初の例では、uniq_trans_id インデックスのみを使用します。

##クエリ条件 trans_id = ‘38’ によると、 use

uniq_trans_id

インデックスはリーフ ノードに保存されている ID 値を検索します;

• PRIMARY インデックスを使用して、見つかった ID 値を通じてリーフ ノードに保存されている行データを検索します;

次に、見つかった行データを
• status = 0

  条件でフィルター処理します。

• 2 番目のタイプはインデックスのマージを使用します。 Using intersect(uniq_trans_id,idx_status) ：

trans_id によると= ‘38’ クエリ条件、

uniq_trans_id

インデックスを使用して、リーフ ノードに保存されている ID 値を検索します;

• status によると = 0 クエリ条件。

  idx_status
インデックスを使用して、リーフ ノードに保存されている ID 値を検索します。

• 1/2 で見つかった ID 値の共通部分を取得し、PRIMARY インデックスを使用してリーフ ノードに保存されている行データを見つけます

上記の 2 つのケースの主な違いは、1 つ目の方法では最初にインデックスを通じてデータを検索し、次に他のクエリ条件を使用してフィルタリングすること、2 つ目の方法では最初に ID 値の共通部分を取得することです。 2 つのインデックスを通じて見つかりました。交差部分の後に ID 値がまだある場合は、テーブルに戻ってデータを取得します。

オプティマイザが 2 番目のケースの実行コストが最初のケースの実行コストより小さいと判断した場合、インデックスのマージが発生します。 (運用環境のフロー テーブルには status = 0 のデータがほとんどありません。これが、オプティマイザが 2 番目のケースを考慮する理由の 1 つです)。

index_merge

index_merge を使用した後にデッドロックが発生する理由 上記は、2 つの更新トランザクションをロックするプロセスを簡単に示しています。図では、idx_status

インデックスと PRIMARY (クラスター化インデックス) の両方に重複部分と交差部分があり、デッドロックの状態が発生します。

たとえば、次のようなタイミングでデッドロックが発生します。

トランザクション 1 がトランザクション 2 の解放を待つ。ロックが解除されている場合、トランザクション 2 はトランザクション 1 がロックを解放するのを待つため、デッドロックが発生します。

MySQL はデッドロックを検出すると、より低いコストでトランザクションを自動的にロールバックします。上のタイミング図のように、トランザクション 1 がトランザクション 2 よりも少ないロックを保持している場合、MySQL はトランザクション 1 を続行します。 。

解決策

1. コード レベル

• # クエリ条件から、データをクエリするために trans_id

  のみを渡します。 、コード レベルでステータスが 0 であるかどうかを判断します。

• Use force Index(uniq_trans_id) クエリ ステートメントで uniq_trans_id

  を使用するように強制します。 index;

• ここで、クエリ条件の直後に id フィールドを使用し、主キーを介して更新します。

2.

idx_status
• インデックスを MySQL レベルから削除するか、これら 2 つの列を含む結合インデックスを作成します。

  ##MySQL オプティマイザーの

  index merge
最適化をオフにします。

以上がMySQL 最適化インデックスのマージによって発生するデッドロックを解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。