MySQLのまとめ

MySQL のまとめ#

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

`原子性(Atomicity)`#

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

`一貫性(Consistency)`#

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

`独立性(Isolation)`#

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

`永続性(Durability)`#

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

拡張#

CAP 定理と BASE 理論：参考:CAP と BASE 理論
dubbo+zookeeper は主にCPを実現します。
springcloud eureka [hystrix] は主にAPを実現します。
一つのビジネスシーンの考察
トランザクションがコミットされたが、データが失われた

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

`Read Uncommitted`#

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

`Read Committed`#

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

`Repeatable Read`#

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

`Serializable`#

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

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

ダーティリード#

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

ファントムリード#

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

三、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 は以下の 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）としてマークし、新しいバージョンのデータを別の場所に追加します。これにより、複数のバージョンのデータが保存されますが、最新のものだけが存在します。この方法により、読者は自分が読む前に存在していたデータを読み取ることができ、たとえその読み取りの過程で他の人によって変更または削除されても、先に読んでいるユーザーには影響を与えません。同じトランザクション内で同じデータを複数回読み取った結果が同じであることを保証し、非再現読の問題を解決します。

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

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

参考リンク#

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

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

`InnoDB`#

概要#

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

主な特徴#

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

参考#

`MyISAM`#

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

MEMORY#

ARCHIVE#

参考リンク#

六、MySQL のインデックス#

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

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

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

B ツリー：順序付き配列 + 平衡多分岐木
その特徴は：
(1) 二分探索ではなく、m 分岐探索です；
(2) 葉ノードと非葉ノードの両方がデータを保存します；
(3) 中間順序遍歴を行うことで、すべてのノードを取得できます；
B + ツリー：順序付き配列リスト + 平衡多分岐木、B ツリーの基盤の上にいくつかの改良が加えられています。
(1) 非葉ノードはデータを保存せず、データは同じ層の葉ノードにのみ保存されます。
(2) 葉ノード間にリンクリストが追加され、すべてのノードを取得するために中間順序遍歴を行う必要がなくなります。
B + ツリーの改良された特性
(1) 範囲検索では、min と max を特定した後、中間の葉ノードが結果セットとなり、中間順序の回溯が不要になります。
(2) 葉ノードは実際の記録行を保存し、記録行は比較的密に保存されており、大量のデータのディスクストレージに適しています。非葉ノードは記録の PK を保存し、クエリの加速に使用され、メモリストレージに適しています。
(3) 非葉ノードは実際の記録を保存せず、記録の KEY のみを保存する場合、同じメモリの条件下で B + ツリーはより多くのインデックスを保存できます。

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

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

インデックスの分類#

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

explain で SQL クエリ計画をシミュレート#

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

対応する意味の詳細#

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：インデックスを通じて一度で見つけたことを示します。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）インデックス、またはクラスタインデックス#

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

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

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

参考#

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

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

拡張#

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

八、正規化設計#

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