MySql の知識ポイント: トランザクション、インデックス、ロックの原則、使用状況の分析

この記事では、トランザクション、インデックス、ロックなどの MySQL の知識ポイントの原理と使用法を例を使用して説明します。ご参考までに、詳細は以下の通りです。

取引

取引コンセプト

トランザクションは、一連のアトミック SQL クエリ、または独立した作業単位です。データベースエンジンが一連の操作ステートメントを実行すると、すべての操作が実行されます。いずれかの操作がクラッシュしたり、その他の理由で実行できない場合は、すべてのステートメントは実行されません。つまり、トランザクション内のすべてのステートメントは、正常に実行されるか、すべて失敗します。

ACIDトランザクション特性
- 原子性
トランザクションは作業の最小単位とみなされ、分割できません。トランザクション全体のすべての操作は、正常にコミットされるか、すべて失敗してロールバックされる必要があります。操作の一部のみを実行することはできません。
- 一貫性
データベースは、ある一貫した状態から別の一貫した状態に変換されます。データベースがすべての整合性制約に準拠している状態。
- 分離
一般的に、1 つのトランザクションによって行われた変更は、最終的にコミットされるまで他のトランザクションには表示されません。このとき、各トランザクションが分離され、相互に干渉できないようにする必要があります。
- 耐久性
トランザクションがコミットされると、すべての変更がデータベースに永続的に保存されます。このとき、システムがクラッシュしても、変更されたデータは失われません。

トランザクション分離レベル
- コミットされていない読み取り
トランザクション内の変更は、コミットされていない場合でも他のトランザクションから参照できます。トランザクションはコミットされていないデータを読み取ることができるため、ダーティリードが発生し、再現性が失われます。
- コミットされた読み取り
ほとんどのデータベースのデフォルトレベルは READ COMMITTED です (MySQL のデフォルトは REPEATABLE READ)。このレベルのトランザクションはダーティリード (dirty read) を解決しますが、同じクエリが 2 回実行されるとクエリ結果が異なるため、非反復読み取りが発生する可能性があります。
- 繰り返し読み取り
このレベルでは、ダーティリードが解決され、繰り返し読み取りが保証されます。ただし、理論上は、繰り返し読み取り分離レベルではファントムリードは解決できません。いわゆるファントムリードとは、あるトランザクションが特定の範囲内のレコードを読み取っているときに、別のトランザクションが同じ範囲内に新しいレコードを挿入する状況を指します。 InnoDB および XtraDB ストレージエンジンは、マルチバージョン同時実行制御 (MVVC) を通じてファントムリードの問題を解決します。
- シリアル化可能
直列化可能性は、最高レベルの分離です。トランザクションを直列に実行するように強制し、ファントムリードを完全に回避します。簡単に言うと、SERIALIZABLE は各行の読み取りをロックするため、待機タイムアウトが大量に発生し、ロック競合の問題が発生します。実際の開発ではほとんど使用されません。

索引

インデックスコンセプト

インデックスは、ストレージエンジンのユーザーがレコードを素早く見つけられるようにするデータ構造です。例えば、

userId = 1 の場合、userName を user から選択します。

userId 列にインデックスを追加すると、MySQL はインデックスを使用して userId の行を検索します。つまり、MySQL は最初に値でインデックスを検索し、次にその値を含むすべてのデータ行を返します。

インデックス作成
- Bツリーインデックス
データの保存には B-Tree データ構造を使用します。ほとんどの MySQL エンジンはこのインデックスをサポートしています。 B-Tree インデックスでは、B-Tree インデックス列が順番に格納され、高速な範囲検索が可能になるため、データアクセスを高速化できます。
- ハッシュインデックス
ハッシュインデックスは、基本的なハッシュテーブルとして実装されます。インデックスのすべての列と完全に一致するクエリのみが有効です。データの各行について、ストレージエンジンはすべてのインデックス列のハッシュコードを計算し、ハッシュコードの値は小さくなります。ハッシュインデックスは、すべてのハッシュコードをインデックスに格納し、ハッシュテーブル内の各データ行へのポインターを保存します。 MySQL では、メモリエンジンのみがハッシュインデックスを明示的にサポートします。

インデックスタイプ
- 通常のインデックス
主なタスクデータへのアクセスを高速化する
- ユニークインデックス
通常のインデックスでは、データの繰り返しが許可されます。列内のデータが重複しないことが確実な場合は、一意のインデックスを作成できます。一意のインデックスには 2 つの利点があります。インデックスがより効率的です。新しいデータを挿入するときに、重複していると、MySQL は挿入を拒否します。
- 主キーインデックス
主キー自体はデフォルトでインデックス化されます。
- 全文索引
テキストフィールドの通常のインデックスでは、フィールドの最初の文字列の検索速度のみが向上します。フィールドに複数の単語で構成される大きなテキストが含まれている場合、通常のインデックスは機能しません。この場合は、フルテキストインデックスの方が適しています。
クエリの効率: 一意のインデックス > 自動増分主キー > 主キー
挿入: 主キー > 自動増分主キー > 一意のインデックス

ロック

ここでは主にダウンリンクレベルのロックについて説明します。

テーブルレベル

MyISAM エンジンは、テーブル全体をロックするものと理解できます。同時に読み取ることはできますが、同時に書き込むことはできません。ロック期間中、他のプロセスはテーブルに書き込むことができません。書き込みロックの場合、他のプロセスは読み取りできません。

行レベル

エンジン INNODB では、レコードの 1 行がロックされ、同時に読み取ることはできますが、同時に書き込むことはできません。行レベルのロックはオーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があります。ロックの粒度は最小で、ロック競合の可能性は最も低く、同時実行性は最も高くなります。

InnoDB ロック行

InnoDB はデフォルトで行レベルロックを実行するため、MySQL は主キーが明示的に指定されている場合にのみ行ロックを実行します。それ以外の場合、MySQL はテーブルロックを実行します。

例1: (主キーを明示的に指定し、そのようなレコードがある場合は行ロック)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例 2: (主キーを明示的に指定します。そのようなレコードが見つからない場合は、ロックは実行されません)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例 3: (主キーなし、テーブルロック)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例 4: (主キーが不明、テーブルロック)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例 5: (主キーが不明、テーブルロック)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注 1: FOR UPDATE は InnoDB にのみ適用可能であり、有効にするにはトランザクションブロック (BEGIN/COMMIT) で実行する必要があります。
注 2: ロック状態をテストするには、MySQL のコマンドモードを使用し、2 つのウィンドウを開いてテストを実行します。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキルの概要」、および「MySQL データベースロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: