Yige

Yige

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

MySQLのまとめ

#MySQL99
AI 翻訳
この記事はAIを通じて中国語から日本語に翻訳されました。原文を表示
AI が生成した要約
MySQLのまとめ ### 一、トランザクションの4つの特性（ACID） 1. **原子性（Atomicity）**: トランザクションは不可分の作業単位であり、すべての操作が成功するか、全てが失敗する。 2. **一貫性（Consistency）**: トランザクションの実行前後でデータベースの整合性が保たれる。 3. **隔離性（Isolation）**: 複数のユーザーが同時にデータベースにアクセスしても、トランザクションは互いに干渉しない。 4. **持続性（Durability）**: トランザクションがコミットされると、その変更は永続的であり、データベースの障害があっても影響を受けない。 ### 二、データベースの隔離レベル - **Read Uncommitted**: 他のトランザクションの未コミットのデータが見える（脏读）。 - **Read Committed**: コミットされたデータのみが見えるが、不可重复读が発生する可能性がある。 - **Repeatable Read**: MySQLのデフォルトで、同じトランザクション内で同じデータが見えるが、幻读が発生する可能性がある。 - **Serializable**: 最も高い隔離レベルで、トランザクションを順序付けることで幻读を防ぐ。 ### 三、MySQLのロックメカニズム - **共有ロック（Sロック）**: 他のトランザクションは読み取り可能だが書き込み不可。 - **排他ロ

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 表に行を挿入することができます。ファイルの中間の空きブロックは、表の中間から削除または更新された行から生成される可能性があります。ファイルの中間に空きブロックがある場合、並行挿入は無効になりますが、すべての空きブロックが新しいデータで埋められると、自動的に再有効化されます。この動作を制御するには、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 は以下の 2 種類の行ロックを実装しています：

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

行ロックと表ロックの共存を許可するために、InnoDB は多粒度ロックメカニズムを実装しており、これには 2 種類の内部使用の意図ロック（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 が行レベルロックを使用し、それ以外の場合は InnoDB が表ロックを使用することを意味します！
  • 主キーインデックス、ユニークインデックス、または通常のインデックスを使用しても、InnoDB は行ロックを使用してデータにロックを追加します。
  • 実行計画が実際にインデックスを使用している場合にのみ、行ロックを使用できます。条件にインデックスフィールドを使用しても、データを検索するためにインデックスを使用するかどうかは、MySQL が異なる実行計画のコストを判断することによって決定されます。MySQL が全表スキャンの方が効率的だと判断した場合、例えば非常に小さな表に対しては、インデックスを使用しません。この場合、InnoDB は行ロックではなく表ロックを使用します。したがって、ロック競合を分析する際には、
  • MySQL の行ロックはインデックスに対して追加されるロックであり、レコードに対して追加されるロックではないため、複数のセッションが異なる行のレコードにアクセスしている場合でも、同じインデックスキーを使用している場合、ロック競合が発生する可能性があります（後にそのインデックスを使用するセッションは、先にそのインデックスを使用するセッションがロックを解放するのを待つ必要があります）。

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

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

四、MySQL のMVCCメカニズム#

MVCCメカニズムとは？#

実際には、各行の後ろに 2 つの隠し列を追加し、作成バージョン番号と削除バージョン番号を記録します。各トランザクションは起動時に一意の増分バージョン番号を持ちます。InnoDBでは、各行に 2 つの隠しフィールドを追加して MVCC を実現し、これらの 2 つの列はトランザクションのバージョン番号を格納します。新しいトランザクションが開始されるたびに、トランザクションのバージョン番号が増加します。

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

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

非再現可能リードの解決#

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

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

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

参考リンク#

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

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

InnoDB#

概要#

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

主な特徴#

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

参考#

MyISAM#

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

MEMORY#

ARCHIVE#

参考リンク#

六、MySQL のインデックス#

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

  1. 等価クエリの場合、ハッシュインデックスは明らかに絶対的な利点があります。なぜなら、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：2 番目の 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：インデックスを通じて 1 回で見つけたことを示します。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）インデックス、またはクラスタインデックス#

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

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

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

参考#

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

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

拡張#

MySQL の主従遅延を最適化するにはどうすればよいか

八、正規化設計#

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

九、拡張#

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