Yige

Yige

Build
ホームアーカイブ作品集

MySQLのまとめ

#MySQL48
AI 翻訳
この記事はAIを通じて中国語から日本語に翻訳されました。原文を表示
AI が生成した要約
MySQLのまとめ 1. **トランザクションのACID特性** - **原子性**: トランザクションは不可分な作業単位で、全ての操作が成功するか、全てが失敗する。 - **一貫性**: トランザクション前後でデータベースの整合性が保たれる。 - **隔離性**: 複数のユーザーが同時にデータベースにアクセスしても、トランザクションは互いに干渉しない。 - **持続性**: トランザクションがコミットされると、その変更は永続的で、データベースの障害に影響されない。 2. **データベースの隔離レベル** - **Read Uncommitted**: 他の未コミットのトランザクションの結果が見える（脏读）。 - **Read Committed**: コミットされた変更のみが見える（不可重复读の可能性）。 - **Repeatable Read**: 同一トランザクション内で同じデータが見える（幻读の可能性）。 - **Serializable**: 最も高い隔離レベルで、トランザクションが互いに干渉しないように強制的に順序付けされる。 3. **MySQLのロック機構** - **共有ロック**: 他のトランザクションは読み取り可能だが、書き込みは不可。 - **排他ロック**: 他のトランザクションは読み書き不可。 - **ロックの粒度**: - **表レベルロック**: オーバーヘッドが

MySQL のまとめ#

一、トランザクションの四大特性 (ACID)#

原子性(Atomicity)#

原子性とは、トランザクションが分割不可能な作業単位であり、トランザクション内の操作はすべて実行されるか、まったく実行されないことを指します。

一貫性(Consistency)#

トランザクション実行前にデータベースが完全な状態であれば、トランザクション終了後、トランザクションが成功したかどうかにかかわらず、データベースは依然として完全な状態であることを意味します（データベースの完全性状態：データベース内のすべてのデータが、データベースで定義されたすべての制約に従っている場合、そのデータベースは完全な状態と呼ばれます）。

隔離性(Isolation)#

トランザクションの隔離性とは、複数のユーザーが同時にデータベースにアクセスする際に、一つのユーザーのトランザクションが他のユーザーのトランザクションに干渉されないことを指します。複数の同時トランザクション間でデータは相互に隔離される必要があります。

持続性(Durability)#

持続性とは、トランザクションが一度コミットされると、そのデータベース内のデータの変更は永続的であり、その後データベースに障害が発生しても影響を受けるべきではないことを指します。

拡張#

二、データベースの隔離レベル#

Read Uncommitted#

未コミットの内容を読み取ります。この隔離レベルでは、すべてのトランザクションが他の未コミットトランザクションの実行結果を見ることができます。この隔離レベルは実際のアプリケーションではほとんど使用されません。なぜなら、パフォーマンスも他のレベルとそれほど変わらないからです。未コミットのデータを読み取ることは、ダーティリード（Dirty Read）とも呼ばれます。

Read Committed#

コミットされた内容を読み取ります。これはほとんどのデータベースシステムのデフォルトの隔離レベルです（ただし、MySQL のデフォルトではありません）。これは隔離の単純な定義を満たします：トランザクションは、すでにコミットされたトランザクションによって行われた変更のみを見ることができます。この隔離レベルは、同じトランザクションの他のインスタンスが処理中に新しいコミットがある可能性があるため、いわゆる非再現読（Nonrepeatable Read）を引き起こす可能性があります。

Repeatable Read#

これは MySQL のデフォルトのトランザクション隔離レベルであり、同じトランザクションの複数のインスタンスがデータを同時に読み取る際に、同じデータ行を見ることができることを保証します。しかし、理論的には、これにより別の厄介な問題であるファントムリード（Phantom Read）が発生する可能性があります。

Serializable#

直列化です。これは最高の隔離レベルであり、トランザクションの順序を強制することによって、互いに衝突することが不可能になり、ファントムリードの問題を解決します。簡単に言えば、各読み取りデータ行に共有ロックを追加します。このレベルでは、大量のタイムアウト現象やロック競合が発生する可能性があります。

ダーティリード非再現読ファントムリード
Read Uncommitted✔️✔️✔️
Read Committed✖️✔️✔️
Repeatable Read✖️✖️✔️
Serializable✖️✖️✖️

ダーティリード#

あるトランザクションがデータを更新し、別のトランザクションがその時点で同じデータを読み取った場合、何らかの理由で前のトランザクションがロールバックされた場合、後のトランザクションが読み取ったデータは不正確になります。

ファントムリード#

あるトランザクションの二回のクエリでデータの行数が一致しない場合、例えば、あるトランザクションが数行の (Row) データをクエリしたとき、別のトランザクションがその時に新しい数行のデータを挿入した場合、前のトランザクションは次のクエリで、以前には存在しなかった数行のデータを発見することになります。InnoDB と Falcon ストレージエンジンは、多バージョン同時実行制御（MVCC、Multiversion Concurrency Control）メカニズムを通じてこの問題を解決しました。

三、MySQL のロックメカニズム#

参考リンク: MySQL ロックのまとめ
連想記憶: Java のHappens Beforeセマンティクス保証（volatileキーワード）

共有ロックと排他ロック#

  • 共有ロック（読み取りロック）: 他のトランザクションは読み取ることができますが、書き込むことはできません。
  • 排他ロック（書き込みロック）: 他のトランザクションは読み取ることも書き込むこともできません。

ロックの粒度#

  • 表レベルロック

    1. オーバーヘッドが少なく、ロックが速い；デッドロックは発生しない；ロック粒度が大きく、ロック競合が発生する確率が最も高く、並行度が最も低い。
    2. 表レベルロックは、クエリが主で、並行ユーザーが少なく、インデックス条件に従ってデータを更新するアプリケーションに適しています。例えば、Web アプリケーションなど。
    3. これらのストレージエンジンは、常に必要なすべてのロックを一度に取得し、常に同じ順序で表ロックを取得することでデッドロックを回避します。

  • 行レベルロック

    1. オーバーヘッドが大きく、ロックが遅い；デッドロックが発生する可能性がある；ロック粒度が最小で、ロック競合が発生する確率が最も低く、並行度が最も高い。
    2. 最大限の並行性をサポートしますが、最大のロックオーバーヘッドも伴います。
    3. 行レベルロックはストレージエンジン層でのみ実装され、MySQL サーバー層では実装されていません。行レベルロックは、インデックス条件に従って大量の異なるデータを並行して更新し、同時に並行クエリがあるアプリケーションに適しています。例えば、オンライントランザクション処理（OLTP）システムなど。

  • ページロック：オーバーヘッドとロック時間は表ロックと行ロックの間に位置し、デッドロックが発生する可能性がある；ロック粒度は表ロックと行ロックの間に位置し、並行度は一般的です。

    デフォルトでは、表ロックと行ロックは自動的に取得され、追加のコマンドは必要ありません。しかし、特定の状況では、ユーザーはロックテーブルやトランザクションの制御を明示的に行う必要があり、トランザクション全体の整合性を確保するために、トランザクション制御とロック文を使用する必要があります。

各ストレージエンジンのロック#

  • MyISAMとMEMORYストレージエンジンは表レベルロック（table-level locking）を採用しています。
  • BDBストレージエンジンはページロック（page-level locking）を採用していますが、表レベルロックもサポートしています。
  • InnoDBストレージエンジンは、行レベルロック（row-level locking）と表レベルロックの両方をサポートしていますが、デフォルトでは行レベルロックを使用します。 
    InnoDBでは、単一のSQLで構成されたトランザクションを除き、ロックは段階的に取得されます。これにより、InnoDBでデッドロックが発生する可能性があります。

MyISAM 表ロック#

MyISAM 表レベルロックモード#

  • 表共有読み取りロック （Table Read Lock）：他のユーザーの同じ表に対する読み取り要求をブロックしませんが、同じ表に対する書き込み要求をブロックします。

  • 表独占書き込みロック （Table Write Lock）：他のユーザーの同じ表に対する読み取りおよび書き込み操作をブロックします。

    MyISAM 表の読み取り操作と書き込み操作の間、および書き込み操作の間は直列化されます。あるスレッドが表に対する書き込みロックを取得すると、そのロックを保持しているスレッドのみが表を更新できます。他のスレッドの読み取りおよび書き込み操作は、ロックが解放されるまで待機します。

    デフォルトでは、書き込みロックは読み取りロックよりも優先度が高く、ロックが解放されると、そのロックは書き込みロックのキューに待機している取得ロック要求に優先的に与えられ、その後に読み取りロックのキューに待機している取得ロック要求に与えられます。

    ** これが MyISAM 表が大量の更新操作とクエリ操作のアプリケーションにあまり適していない理由です。** なぜなら、大量の更新操作がクエリ操作が読み取りロックを取得するのを非常に困難にし、結果として永遠にブロックされる可能性があるからです。

MyISAM の表ロック方法#

自動ロックの状況下で、MyISAM は常に SQL 文が必要とするすべてのロックを一度に取得します。これが MyISAM 表がデッドロック（Deadlock Free）を発生させない理由です。

MyISAM ストレージエンジンは、読み取りと書き込み操作の間の競合を減らすために、並行挿入をサポートしています：

MyISAM 表がデータファイルの中間に空きブロックがない場合、行は常にデータファイルの末尾に挿入されます。この場合、他のスレッドが読み取り操作を行っている間に、MyISAM 表に行を挿入するために INSERT と SELECT 文を自由に混合して使用できます。ファイルの中間に空きブロックがある場合、それは表の中間から削除または更新された行から生じる可能性があります。ファイルの中間に空きブロックがある場合、並行挿入は無効になりますが、すべての空きブロックが新しいデータで埋められると、自動的に再度有効になります。この動作を制御するために、MySQL の concurrent_insert システム変数を使用できます：

  • concurrent_insert が 0 に設定されている場合、並行挿入は許可されません。
  • concurrent_insert が 1 に設定されている場合、MyISAM 表に空洞がない場合（つまり、表の中間に削除された行がない場合）、MyISAM は一つのスレッドが表を読み取っている間に、別のスレッドが表の末尾からレコードを挿入することを許可します。これが MySQL のデフォルト設定です。
  • concurrent_insert が 2 に設定されている場合、MyISAM 表に空洞があるかどうかにかかわらず、表の末尾に並行してレコードを挿入することが許可されます。

表レベルロックの競合状況をクエリする#

table_locks_waited と table_locks_immediate の状態変数をチェックすることで、システム上の表ロックの競合を分析できます。Table_locks_waited の値が高い場合、表レベルロックの競合が深刻であることを示します：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+

InnoDB の行レベルロックと表レベルロック#

InnoDB のロックモード#

InnoDB は以下の二種類の行ロックを実装しています：

  • 共有ロック（S）：トランザクションが行を読み取ることを許可し、他のトランザクションが同じデータセットの排他ロックを取得するのを防ぎます。
  • 排他ロック（X）：排他ロックを取得したトランザクションがデータを更新することを許可し、他のトランザクションが同じデータセットの共有読み取りロックと排他書き込みロックを取得するのを防ぎます。

行ロックと表ロックが共存できるようにするために、InnoDB は二種類の内部使用の意図ロック（Intention Locks）を持っています。これらの意図ロックはすべて表ロックです：

  • 意図共有ロック（IS）：トランザクションがデータ行に行共有ロックを追加するつもりであることを示し、トランザクションがデータ行に共有ロックを追加する前に、その表の IS ロックを取得する必要があります。
  • 意図排他ロック（IX）：トランザクションがデータ行に行排他ロックを追加するつもりであることを示し、トランザクションがデータ行に排他ロックを追加する前に、その表の IX ロックを取得する必要があります。

InnoDB のロック方法#

  • 意図ロックは InnoDB によって自動的に追加され、ユーザーの介入は必要ありません。
  • UPDATE、DELETE、INSERT 文に対して、InnoDB は自動的に関与するデータセットに排他ロック（X）を追加します。
  • 通常の SELECT 文に対して、InnoDB はロックを追加しません。
  • トランザクションは以下の文を使用して、明示的にレコードセットに共有ロックまたは排他ロックを追加できます： 
    (1) 共有ロック（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
(2) 排他ロック（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
隠れたロック#

InnoDB はトランザクションの実行中に、** 二段階ロックプロトコル（2pc プロトコルに関連付けられています）** を使用します：

  • いつでもロックを実行でき、InnoDB は必要に応じて自動的にロックを追加します。
  • ロックは commit または rollback を実行する時にのみ解放され、すべてのロックは同時に解放されます。
明示的ロック#
select ... lock in share mode //共有ロック
select ... for update //排他ロック
  • select for update
    1. この select クエリ文を実行する際に、対応するインデックスアクセスエントリに排他ロック（X ロック）が追加されます。つまり、この文に対応するロックは update による効果と同等です。
    2. select *** for update の使用シナリオ：自分が取得したデータが最新のデータであることを確保し、取得した後のデータは自分だけが変更できるようにするために、for update 句を使用する必要があります。
    3. 他のセッションはそのレコードをクエリできますが、そのレコードに共有ロックまたは排他ロックを追加することはできず、ロックを取得するのを待つ必要があります。
  • select lock in share mode

    1. in share mode 句の役割は、見つかったデータに共有ロックを追加することです。これは、他のトランザクションがこれらのデータに対して単純な select 操作しか行えず、DML 操作を行うことはできないことを示します。

    2. 自分が取得したデータが他のトランザクションによって変更されていないことを確保するため、つまり、取得したデータが最新のデータであり、他の人がデータを変更することを許可しないことを確保します。しかし、自分がデータを変更できるとは限りません。なぜなら、他のトランザクションもこれらのデータに対して in share mode の方法で S ロックを使用している可能性があるからです。

    3. 他のセッションは依然としてレコードをクエリでき、またそのレコードに共有モードの共有ロックを追加することもできます。しかし、現在のトランザクションがそのレコードを更新する必要がある場合、デッドロックが発生する可能性が非常に高いです。

    • 両者の違い：for update は排他ロック（X ロック）であり、一度トランザクションがこのロックを取得すると、他のトランザクションはこれらのデータに対して操作を実行できません；lock in share mode は共有ロックであり、複数のトランザクションが同時に同じデータに対して操作を実行できます。

InnoDB の行ロックの実装方法#

  • InnoDB の行ロックはインデックス上のインデックス項目にロックを追加することによって実現されます。これは MySQL と Oracle の違いであり、後者はデータブロック内の対応するデータ行にロックを追加することによって実現されます。InnoDB のこの行ロックの実装特性は、インデックス条件でデータを検索する場合にのみ行レベルロックを使用し、そうでない場合は表ロックを使用することを意味します！
  • 主キーインデックス、ユニークインデックス、または通常のインデックスを使用しても、InnoDB は行ロックを使用してデータにロックを追加します。
  • 実行計画が実際にインデックスを使用している場合にのみ、行ロックを使用できます。インデックスフィールドを条件に使用しても、データを検索するためにインデックスを使用するかどうかは、MySQL が異なる実行計画のコストを判断することによって決定されます。MySQL が全表スキャンの効率が高いと判断した場合、例えば非常に小さな表に対しては、インデックスを使用しません。この場合、InnoDB は表ロックを使用し、行ロックは使用しません。したがって、ロック競合を分析する際には、
  • MySQL の行ロックはインデックスに対して追加されるロックであり、レコードに対して追加されるロックではないため、複数のセッションが異なる行のレコードにアクセスしていても、同じインデックスキーを使用している場合、ロック競合が発生します（後にインデックスを使用するセッションは、先にインデックスを使用したセッションがロックを解放するのを待つ必要があります）。

MySQL のロックアルゴリズム#

  • Record Lock：単一の行レコードに対するロック。
  • Gap Lock：間隙ロックで、範囲をロックしますが、レコード自体は含まれません。GAP ロックの目的は、同じトランザクションの二回の現在の読み取りでファントムリードが発生するのを防ぐことです。
  • Next-Key Lock：Record + Gap で、範囲をロックし、レコード自体もロックします。行のクエリには常にこの方法が採用され、主な目的はファントムリードの問題を解決することです。
  • 余計なことは言わず、さまざまな SQL が実際にどのロックを追加したか？

四、MySQL のMVCCメカニズム#

MVCCメカニズムとは？#

実際には、各行レコードの後ろに二つの隠し列を追加し、作成バージョン番号と削除バージョン番号を記録します。各トランザクションが開始されると、ユニークな増分のバージョン番号が付与されます。InnoDBでは、各行に二つの隠しフィールドを追加して MVCC を実現し、二つの列はトランザクションのバージョン番号を保存します。新しいトランザクションが開始されるたびに、トランザクションのバージョン番号は増加します。

一貫性のある非ロック読み取り#

一貫性のある読み取り（consistent read）では、InnoDB は多バージョンを使用して、データベースのある時点でのスナップショットを提供します。隔離レベルが REPEATABLE READ の場合、同じトランザクション内のすべての一貫性のある読み取りは、トランザクション内の最初のそのような読み取りで得られたスナップショットを読み取ります。READ COMMITTED の場合、トランザクション内の各一貫性のある読み取りは、自己更新されたスナップショットバージョンを読み取ります。一貫性のある読み取り（一貫性のある読み取り）は、READ COMMITTED と REPEATABLE READ の隔離レベル下での通常の SELECT 文のデフォルトモードです。一貫性のある読み取りは、アクセスするテーブルに対していかなる形式のロックも追加しないため、他のトランザクションは同時にそれらを変更できます。

非再現読の解決#

MVCC データベースがデータレコードを変更する必要がある場合、新しいデータで古いデータを直接上書きするのではなく、古いデータを時代遅れ（obsolete）としてマークし、別の場所に新しいバージョンのデータを追加します。これにより、複数のバージョンのデータが保存されますが、最新のものだけが存在します。この方法により、読者は読み取る前に既に存在していたデータを読み取ることができ、たとえそのデータが読み取り中に他の誰かによって変更または削除されても、先に読み取っているユーザーには影響を与えません。同じトランザクション内で同じデータを複数回読み取った結果が同じであることが保証され、非再現読の問題が解決されます。

欠点は：
この多バージョン方式は、削除操作によってメモリとディスクストレージ構造に生じる空洞のオーバーヘッドを回避しますが、システムは定期的に整理（sweep through）して、古い、時代遅れのデータを実際に削除する必要があります。

要約すると、MVCCは同じデータの一時的な多バージョンを保持する方法であり、これにより並行制御を実現します。

参考リンク#

  • etcdを通じてMVCCメカニズムを学ぶ: ectd

五、MySQL のストレージエンジン#

InnoDB#

概要#

  1. ACID トランザクションをサポートし、トランザクションの四つの隔離レベルをサポートします。
  2. 行レベルロックと外部キー制約をサポートしているため、書き込みの並行性をサポートできます。
  3. 総行数を保存しません。
  4. 一つの InnoDB エンジンは一つのファイルスペースに保存されます（共有テーブルスペースで、テーブルサイズはオペレーティングシステムによって制御されず、一つのテーブルは複数のファイルに分散する可能性があります）。また、複数のファイル（独立したテーブルスペースとして設定され、テーブルサイズはオペレーティングシステムのファイルサイズ制限を受け、一般的には 2G）を持つこともあります。
  5. 主キーインデックスはクラスタインデックス（インデックスのデータ領域がデータファイル自体を保存）を採用し、補助インデックスのデータ領域は主キーの値を保存します。したがって、補助インデックスからデータを検索するには、まず補助インデックスを通じて主キー値を見つけ、その後補助インデックスにアクセスする必要があります。
  6. 自動増分主キーを使用することをお勧めします。データを挿入する際に B + ツリー構造を維持するために、ファイルの大きな調整を防ぎます。
  7. OLTP（オンライントランザクション処理）に適しており、リアルタイム性の要求が高いです。

主な特性#

挿入バッファ（insert buffer）、二重書き込み（double write）、適応ハッシュ（Adaptive Hash index）、非同期 IO（Async IO）、隣接ページのフラッシュ（Flush Neighbor Page）

参考#

MyISAM#

  1. トランザクションをサポートしていませんが、各クエリは原子的です。
  2. 表レベルロックをサポートしており、各操作はテーブル全体にロックをかけます。
  3. テーブルの総行数を保存します。
  4. 一つの MyISAM テーブルには三つのファイルがあります：インデックスファイル、テーブル構造ファイル、データファイル。
  5. 非クラスタインデックスを採用し、インデックスファイルのデータ領域はデータファイルへのポインタを指します。補助インデックスは主インデックスと基本的に同じですが、補助インデックスは一意性を保証する必要はありません。
  6. OLAP（オンライン分析処理）に適しており、リアルタイム性の要求は高くありませんが、一般的にデータ量が大きいです。

MEMORY#

ARCHIVE#

参考リンク#

六、MySQL のインデックス#

主に B + インデックスとハッシュインデックスがあり、違いは：#

  1. 等値クエリの場合、ハッシュインデックスは明らかに絶対的な優位性を持ちます。なぜなら、アルゴリズムを一度通過するだけで、対応するキー値を見つけることができるからです。もちろん、この前提は、キー値がすべて一意であることです。キー値が一意でない場合、そのキーが存在する位置を見つけてから、リンクリストをスキャンして対応するデータを見つける必要があります。
  2. 範囲クエリ検索の場合、ハッシュインデックスはまったく役に立ちません。なぜなら、元々は順序付けられたキー値がハッシュアルゴリズムを通過することで不連続になる可能性があるため、インデックスを利用して範囲クエリ検索を完了することができなくなるからです。
  3. 同様に、ハッシュインデックスはインデックスを利用してソートを完了したり、like ‘xxx%’のような部分的な曖昧検索を行ったりすることもできません（このような部分的な曖昧検索は、本質的には範囲クエリでもあります）。
  4. ハッシュインデックスは複数列の結合インデックスの最左一致ルールもサポートしていません。
  5. B + ツリーインデックスのキーワード検索効率は比較的均一であり、B ツリーのように大きく変動することはなく、大量の重複キー値がある場合、ハッシュインデックスの効率も極めて低くなります。なぜなら、いわゆるハッシュ衝突の問題が存在するからです。

B + インデックスデータ構造と B ツリーの違い#

  1. B ツリー：順序付き配列 + 平衡多分岐木
    その特徴は：
    (1) もはや二分探索ではなく、m 分岐探索です；
    (2) 葉ノード、非葉ノードの両方がデータを保存します；
    (3) 中間順序走査により、すべてのノードを取得できます；

  2. B + ツリー：順序付き配列リスト + 平衡多分岐木、B ツリーの基礎の上にいくつかの改良が加えられています。
    (1) 非葉ノードはもはやデータを保存せず、データは同じ層の葉ノードにのみ保存されます。
    (2) 葉ノード間にリンクリストが追加され、すべてのノードを取得するために中間順序走査を行う必要がなくなります。

  3. B + ツリーの改良された特性
    (1) 範囲検索では、min と max を特定した後、中間の葉ノードが結果セットとなり、中間順序の回帰が不要です。
    (2) 葉ノードは実際の記録行を保存し、記録行は比較的密に保存され、大量のデータのディスクストレージに適しています；非葉ノードは記録の PK を保存し、クエリを加速するために使用され、メモリストレージに適しています；
    (3) 非葉ノードが実際の記録を保存せず、記録の KEY のみを保存する場合、同じメモリの条件下で、B + ツリーはより多くのインデックスを保存できます。

なぜ B + ツリーがインデックスの構造として適しているのか#

  1. 二分探索木とは異なり、B ツリーは m 分岐であり、木の高さを大幅に低下させることができるため、大量のデータを保存できます。
  2. ディスクストレージに非常に適しており、局所性の原理を十分に活用し、ディスクのプリリードを行います。
    (1) メモリの読み書きブロックは、ディスクの読み書きよりも遅く、非常に遅いです；
    (2) ディスクのプリリード：ディスクの読み書きは必要に応じて読み取るのではなく、ページ単位でプリリードされます。一度にページ（4K のデータ）を読み取り、将来読み取る予定のデータがこのページにある場合、将来のディスク IO を回避し、効率を向上させることができます；
    (3) 局所性の原理：ソフトウェア設計は「データの読み取りを集中させる」ことと「データを使用する際に、その近くのデータを使用する可能性が高い」という原則に従うべきです。これにより、ディスクのプリリードが十分にディスク IO を向上させることができます。
  3. MyISAM と InnoDB は両方とも B + ツリーをインデックスストレージ構造として使用していますが、葉にデータを保存する方法は異なります。前者はインデックスファイルとデータファイルを分離し、インデックスファイルは記録が存在するページのポインタ（物理位置）を保存するだけですが、後者はデータを直接保存するか、主キー値を保存します（主キー値を保存し、補助インデックスを検索する場合、実際には二次クエリが行われ、IO 回数が増加します）。

インデックスの分類#

  • 通常のインデックス：最も基本的なインデックスで、制限はありません。
  • ユニークインデックス：通常のインデックスに似ていますが、異なるのはインデックス列の値が一意でなければならず、空の値は許可されます。
  • 主キーインデックス：これは特別なユニークインデックスであり、空の値は許可されません。
  • フルテキストインデックス：MyISAM テーブルにのみ使用でき、大量のデータに対してフルテキストインデックスを生成するのは非常に時間とスペースを消費します。（MATCH... AGAINST...）
  • 組み合わせインデックス：MySQL の効率を向上させるために、組み合わせインデックスを作成することができます。これは「最左前缶」原則に従います。
  • カバリングインデックス：クエリに必要なすべてのフィールドの値を含むインデックスです。

explain による SQL クエリ計画のシミュレーション#

explain 実行計画に含まれる情報#

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

対応する意味の詳細#

  • id: select クエリのシーケンス番号で、一連の数字が含まれ、クエリ内で select 句または操作テーブルの実行順序を示します。

  • select_type: クエリのタイプで、次のように分類されます：

    1. SIMPLE：単純な select クエリで、サブクエリや union を含まないクエリ。
    2. PRIMARY：クエリに複雑なサブ部分が含まれている場合、最外層のクエリは primary としてマークされます。
    3. SUBQUERY：select または where リストにサブクエリが含まれています。
    4. DERIVED：from リストに含まれるサブクエリは derived（派生）としてマークされ、MySQL はこれらのサブクエリを再帰的に実行し、結果を一時テーブルに格納します。
    5. UNION：二番目の select が union の後に出現する場合、union としてマークされます。union が from 句のサブクエリに含まれている場合、外側の select は derived としてマークされます。
    6. UNION RESULT：union テーブルから結果を取得する select。

  • type: アクセスタイプで、SQL クエリの最適化において非常に重要な指標であり、結果値は良い順から悪い順に次のようになります：
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    一般的に、良い SQL クエリは少なくともrangeレベルに達し、できればrefレベルに達することが望ましいです。

    1. system：テーブルに 1 行のレコードしかない（システムテーブルと等しい）で、これは const タイプの特例であり、通常は発生しません。無視しても構いません。
    2. const：インデックスを通じて一度で見つけたことを示します。const は主キーまたはユニークインデックスの比較に使用されます。1 行のデータをマッチさせるだけで済むため、非常に迅速です。主キーを where リストに置くと、MySQL はそのクエリを const に変換できます。
    3. eq_ref：ユニークインデックススキャンで、各インデックスキーに対して、テーブル内に一致するレコードが 1 つだけあります。主キーまたはユニークインデックススキャンによく見られます。
    4. ref：非ユニークインデックススキャンで、特定の値に一致するすべての行を返します。本質的には、特定の値に一致するすべての行を返すインデックスアクセスの一種であり、複数の条件に一致する行を見つける可能性があるため、検索とスキャンの混合体であるべきです。
    5. range：指定された範囲の行のみを検索し、行を選択するためにインデックスを使用します。key 列は使用されたインデックスを示します。一般的には、where 文に between、<、>、in などのクエリが出現します。
    6. index：フルインデックススキャンで、index と ALL の違いは、index タイプはインデックスツリーのみを走査します。これは通常 ALL ブロックであり、インデックスファイルは通常データファイルよりも小さいためです。（Index と ALL はどちらも全表を読み取りますが、index はインデックスから読み取るのに対し、ALL はハードディスクから読み取ります）。
    7. ALL：フルテーブルスキャンで、マッチする行を見つけるために全表を走査します。

  • possible_keys: クエリに関与するフィールドにインデックスが存在する場合、そのインデックスがリストされますが、クエリで実際に使用されるとは限りません。

  • key: 実際に使用されたインデックスで、NULL の場合はインデックスが使用されていないことを示します。クエリでカバリングインデックスが使用された場合、そのインデックスは key リストにのみ表示されます。

  • key_len: インデックスで使用されるバイト数を示し、クエリで使用されるインデックスの長さ（最大可能長）であり、実際に使用される長さではありません。理論的には、長さが短いほど良いです。key_len はテーブル定義に基づいて計算され、テーブル内から取得されたものではありません。

  • ref: 使用されたインデックスの列を示し、可能であれば定数 const です。

  • rows: テーブルの統計情報とインデックスの選択状況に基づいて、必要なレコードを見つけるために読み取る必要がある行数を大まかに推定します。

  • Extra: 他のフィールドに表示するのには適していませんが、非常に重要な追加情報です。

    1. Using filesort: MySQL がデータに外部インデックスソートを使用しており、テーブル内のインデックスを利用してソート読み取りを行っていないことを示します。つまり、MySQL はインデックスを利用してソート操作を完了できず、「ファイルソート」となります。
    2. Using temporary: 一時テーブルを使用して中間結果を保存しており、MySQL がクエリ結果をソートする際に一時テーブルを使用していることを示します。これは order by や group by で一般的です。
    3. Using index: 対応する select 操作でカバリングインデックス（Covering Index）が使用されていることを示し、テーブルのデータ行にアクセスすることを回避し、効率が高いです。Using where が同時に表示される場合、インデックスがインデックスキーの値の検索を実行するために使用されたことを示します。Using where が同時に表示されない場合、インデックスがデータを読み取るために使用されたことを示します。
    4. Using Where: where フィルタリングが使用されたことを示します。
    5. Using join buffer: ジョインバッファが使用されたことを示します。
    6. Impossible WHERE: where 句の値が常に false であり、いかなるタプルも取得できないことを示します。
    7. select tables optimized away: group by 句がない場合、インデックスを基に MIN/MAX 操作を最適化したり、MyISAM ストレージエンジンで COUNT（*）操作を最適化したりすることを示します。これは、実行段階で計算を行うのではなく、クエリ実行計画生成段階で最適化を完了することができます。
    8. distinct: distinct 操作を最適化し、最初の一致するタプルを見つけた後に同じ値の動作を停止します。

参考リンク#

クラスタインデックスと非クラスタインデックスの違い#

クラスタ（clustered）インデックス、またはクラスタインデックス#

定義：データ行の物理的順序と列値（一般的には主キーの列）の論理的順序が同じであり、一つのテーブルには一つのクラスタインデックスしか存在できません。
主キーが定義されていない場合、一意の非 NULL インデックスが選択され、そうしたインデックスがない場合は、暗黙的に主キーがクラスタインデックスとして定義されます。

非クラスタ（unclustered）インデックス#

定義：このインデックスの論理的順序は、ディスク上の行の物理的ストレージ順序とは異なり、一つのテーブルには複数の非クラスタインデックスを持つことができます。

参考#

七、データベースの主従複製#

  • MySQL が複数に分割されても、主と従を分ける必要があり、すべての書き込み操作は主 MySQL で完了する必要があります。
  • すべての従 MySQL のデータは主 MySQL から（同期されて）来ています。
  • MySQL の主従関係において、ビジネス（サービス内の一つのメソッド）に R 操作と W 操作が両方ある場合、W 操作は必ず主 MySQL で行われるため、すべてのデータが一つのトランザクション内で来る必要があります。

拡張#

MySQL の主従遅延がこんなに長い場合、どう最適化するか

八、正規化設計#

  • 第一正規形（1NF）は関係モデルの基本的な要件であり、第一正規形（1NF）を満たさないデータベースは関係データベースではありません。これは、データベースの各列が分割不可能な基本データ項目であり、同じ列に複数の値を持つことができないことを指します。
  • 第二正規形（2NF）は、データベースの各インスタンスまたは行が一意に識別できる必要があることを要求します。つまり、各フィールドと主キーの間に部分依存が存在しないことを意味します。
  • 第三正規形（3NF）は、データベースのテーブルに他のテーブルに既に含まれている非主キー情報が含まれていないことを要求します。つまり、第二正規形の基礎の上に、伝達依存が存在しないこと（冗長データが許可されないこと）を意味します。

九、拡張#

読み込み中...
文章は、創作者によって署名され、ブロックチェーンに安全に保存されています。