MySQLデータベースのトランザクションとインデックスの詳細な説明

1. 事務：

トランザクションとは、すべてが成功するか、すべてが失敗する論理的な操作のセットです。
——————————————————————————————————
1. SQL実行 A: 1000元 ——> 200元を送金 B: 200元
2. SQL実行 A: 800元 ——> B: 400元
——————————————————————————————————

バッチでSQL文のグループを実行する

取引の 4 つの主な特徴:

ACID原則

1. 原子性: トランザクションは最小の実行単位であり、分割できません。トランザクションのアトミック性により、アクションは完全に完了するか、まったく影響がないかのいずれかになります。

2. 一貫性:トランザクションの実行前と実行後にデータの一貫性が維持され、同じデータを読み取る複数のトランザクションの結果は同じになります。

3. 独立性: データベースに同時にアクセスする場合、ユーザーのトランザクションは他のトランザクションによって干渉されず、同時トランザクション間のデータベースは独立しています。

4. 耐久性: トランザクションがコミットされた後。データベース内のデータに加えられた変更は永続的であり、データベースに障害が発生しても影響を受けません。 --------トランザクションコミット

同時トランザクションはどのような問題を引き起こしますか? （孤立によって生じるいくつかの問題）

一般的なアプリケーションでは、複数のトランザクションが同時に実行され、多くの場合、それぞれのタスクを完了するために同じデータに対して操作が行われます (複数のユーザーが同じデータに対して操作を行います)。並行性は必要ですが、次のような問題が発生する可能性があります。

ダーティリード: トランザクションがデータにアクセスしてデータを変更し、この変更がデータベースにコミットされていない場合、別のトランザクションもデータにアクセスしてデータを使用します。このデータはコミットされていないため、別のトランザクションによって読み取られたデータは「ダーティデータ」となり、「ダーティデータ」に基づいて実行される操作は不正確になる可能性があります。

変更の損失: 1 つのトランザクションがデータの一部を読み取ると、別のトランザクションもそのデータにアクセスします。その後、最初のトランザクションでデータが変更されると、2 番目のトランザクションもそのデータを変更します。このように、最初のトランザクションでの変更結果は失われるため、変更の消失と呼ばれます。たとえば、トランザクション 1 はテーブル内のデータ A=20 を読み取り、トランザクション 2 も A=20 を読み取り、トランザクション 1 は A=A-1 を変更し、トランザクション 2 も A=A-1 を変更し、最終結果は A=19 となり、トランザクション 1 の変更は失われます。

反復不可能な読み取り:トランザクション内で同じデータを複数回読み取ることを意味します。このトランザクションが終了する前に、別のトランザクションもデータにアクセスします。その後、最初のトランザクションでの 2 回の読み取りの間で、最初のトランザクションによって 2 回読み取られたデータは、2 番目のトランザクションの変更により異なる可能性があります。これは、トランザクションで 2 回読み取られるデータが異なることを意味するため、非反復読み取りと呼ばれます。

ファントムリード: ファントムリードは、反復不可能なリードに似ています。これは、1 つのトランザクション (T1) が数行のデータを読み取り、次に別の同時トランザクション (T2) がデータを挿入するときに発生します。後続のクエリでは、最初のトランザクション (T1) によって、元々存在しなかった追加のレコードが、まるで錯覚のように見つかるため、ファントムリードと呼ばれます。

非反復読み取りとファントム読み取りの違い。

非反復読み取りの焦点は、レコードを複数回読み取り、一部の列の値が変更されたことが見つかるなどの変更です。ファントム読み取りの焦点は、レコードを複数回読み取り、レコードの数が増えたか減ったかが見つかるなどの追加または削除です。

トランザクション分離レベルとは何ですか?

READ-UNCOMMITTED -UNCOMMITTED: まだコミットされていないデータ変更の読み取りを許可する最も低い分離レベル。これにより、ダーティリード、ファントムリード、または反復不可能な読み取りが発生する可能性があります。

READ-COMMITTED : 同時トランザクションによってコミットされたデータの読み取りを許可します。これにより、ダーティリードは防止できますが、ファントムリードまたは反復不可能なリードは発生する可能性があります。

REPEATABLE REPEATABLE-READ( ): トランザクション自体によってデータが変更されない限り、同じフィールドの複数の読み取りの結果は一貫しています。ダーティ読み取りと繰り返し不可能な読み取りは防止できますが、ファントム読み取りは発生する可能性があります。

SERIALIZABLE( : 最高の分離レベル。ACID 分離レベルに完全に準拠しています。すべてのトランザクションは 1 つずつ順番に実行されるため、トランザクション間の干渉が発生することはありません。つまり、このレベルでは、ダーティリード、非反復リード、ファントムリードを防ぐことができます。

MySQL のデフォルトの分離レベル:

MySQL InnoDB ストレージエンジンでサポートされるデフォルトの分離レベルは、REPEATABLE-READ (再読み取り可能) です。 SELECT@@tx_isolation;コマンドで確認できる。

2. 索引:

MySQL のインデックスの公式定義は次のとおりです: インデックスは、MySQL がデータを効率的に取得するのに役立つデータ構造です。文の主幹を抽出することで、インデックスの本質、つまりインデックスがデータ構造であることがわかります。

MySQL インデックスで使用されるデータ構造には、主に BTree インデックスとハッシュインデックスが含まれます。ハッシュインデックスの場合、基礎となるデータ構造はハッシュテーブルです。したがって、ほとんどの要件が単一レコードクエリである場合は、クエリパフォーマンスが最も高速なハッシュインデックスを選択できます。その他のほとんどのシナリオでは、BTree インデックスが推奨されます。

MySQL の BTree インデックスは B ツリー内の B+Tree を使用しますが、2 つの主要なストレージエンジンの実装方法は異なります。

MyISAM: B+Tree リーフノードのデータフィールドには、データレコードのアドレスが格納されます。インデックスの取得中、まず B+Tree 検索アルゴリズムに従ってインデックスが検索されます。指定されたキーが存在する場合は、そのデータフィールドの値が取得され、次にデータフィールドの値をアドレスとして使用して対応するデータレコードが読み取られます。これは「非クラスター化インデックス」と呼ばれます。

InnoDB: データファイル自体がインデックスファイルです。 MyISAM と比較すると、インデックスファイルとデータファイルが分離されており、テーブルデータファイル自体は B+Tree によって編成されたインデックス構造であり、ツリーのリーフノードデータフィールドに完全なデータレコードが格納されます。このインデックスのキーはデータテーブルの主キーであるため、InnoDB テーブルデータファイル自体が主インデックスになります。これはクラスター化インデックスと呼ばれます。残りのインデックスは補助インデックスとして使用されます。補助インデックスのデータフィールドには、MyISAM とは異なり、アドレスではなく、対応するレコードの主キーの値が格納されます。主インデックスに基づいて検索する場合は、キーが配置されているノードを直接見つけてデータを取得できます。補助インデックスに基づいて検索する場合は、まず主キーの値を取得してから、主インデックスを調べる必要があります。したがって、テーブルを設計するときには、長すぎるフィールドを主キーとして使用したり、非単調なフィールドを主キーとして使用したりすることはお勧めしません。主インデックスが頻繁に分割される原因となるためです。

インデックスの役割:

クエリ速度の向上
データの一意性を確保する
テーブル間の接続を高速化し、テーブル間の参照整合性を実現できます。
データ取得にグループ化句と並べ替え句を使用すると、グループ化と並べ替えにかかる時間が大幅に短縮されます。
検索最適化のための全文検索フィールド。

インデックス分類:

主キー

一意の識別子、主キーは繰り返すことができず、主キーとして使用できる列は 1 つだけです。

ユニークインデックス

重複する列は避けてください。一意のインデックスは繰り返すことができます。複数の列を一意のインデックスとして識別できます。

従来型インデックス

デフォルトでは、インデックスまたはキーキーワードを使用して設定します。

全文索引

特定のデータベースエンジン、MylSAMでのみ利用可能

データを素早く見つける

インデックス基準:

インデックスの数が増えれば増えるほど良いわけではない
頻繁に変更されるデータをインデックスしない
データ量が少ないテーブルにはインデックスを追加しないことをお勧めします。
通常、検索条件のフィールドにインデックスを追加する必要があります。

インデックスのデータ構造:

-- 上記のインデックスを作成するときに、インデックスの種類を指定できます。ハッシュ タイプのインデックスには、単一クエリ用の高速インデックスと範囲クエリ用の低速インデックスの 2 種類があります。Btree タイプのインデックス: B+ ツリー。レイヤーが増えるほど、データ量が指数関数的に増加します (InnoDB がデフォルトでサポートしているため、これを使用します)
-- 異なるストレージ エンジンは異なるインデックス タイプをサポートします。InnoDB はトランザクション、行レベルのロック、B ツリー、フルテキストなどのインデックスをサポートしますが、ハッシュ インデックスはサポートしません。
MyISAM はトランザクションをサポートしていませんが、テーブル レベルのロック、B ツリー、フルテキスト、およびその他のインデックスをサポートしていますが、ハッシュ インデックスはサポートしていません。
メモリはトランザクションをサポートしていませんが、テーブル レベルのロック、B ツリー、ハッシュ、およびその他のインデックスをサポートしていますが、フルテキスト インデックスはサポートしていません。
NDB はトランザクション、行レベルのロック、ハッシュ インデックスをサポートしますが、B ツリー、フルテキスト、その他のインデックスはサポートしません。
アーカイブはトランザクションをサポートしていませんが、テーブル レベルのロックはサポートしています。B ツリー、ハッシュ、フルテキスト、およびその他のインデックスはサポートしていません。