MySQL インデックスの学習

最近、米中関係の緊張のため、会社は Oracle のサーバーを購入できないという噂があります。また、新プロジェクトは SEF2.0 マイクロサービスを基に開発されており、阿里云のサーバーを借りています。阿里云のサーバーは MySQL が主です。MySQL に関する知識を学びましょう。

2019-10-01

MySQL は現在、ほとんどのインターネット企業が使用しているリレーショナルデータベースです。私たちの会社の社会保険医療保険業務において、クエリは非常に重要です。クエリのシナリオは、更新や書き込みよりもはるかに多いです。したがって、クエリを最適化することが非常に重要です。
以下は一般的なクエリ文です：
SELECT COUNT(*)
FROM SI3C.PER_REG a
WHERE a.ryid=’33580202’
AND a.zcsj BETWEEN ‘2018-01-01’ and ‘2019-01-01’
一般的な最適化方法は、クエリの WHERE 句のフィールドにインデックスを追加することです（または主キーですが、インデックスをどのように追加するか、インデックスの順序はどうするか？一般の人はあまり理解していません（私の上司も含めて）。
MySQL インデックスの原理
次に、シナリオを想像してみましょう。私たちが「LOVE」という単語の中国語の意味を調べたいとき、どのように操作するでしょうか？（もちろん、実体の英語辞典の中で、アプリ上でどうやって実現するかも興味深い問題です。もし最初から調べると、a、abandon、…「LOVE」に到達するまでに多くの時間がかかります。そこで、ディレクトリが作成され、最初に L を調べ、次に O、次に V、最後に E を調べて「LOVE」に到達します。このようにしてデータを特定します。データベースも同様で、=、>、<、BETWEEN、IN、LIKE、AND、OR などは、現実のシナリオを模倣するために使用されます。では、どのように模倣するかが決定的な問題になります。辞書を調べる例を使いましょう。100 個の単語があり、love が 66 番目にある場合、データを 1-10、11-20 のセグメントに分けることができます。そうすれば、6 番目のセグメントを直接探すことができ、90％の無効データを省くことができます。100m のデータはどうでしょうか？データ構造の二分木には BST（Binary Search Tree）があり、非常に高速なクエリ性能を持っていますが、データ構造の複雑さを計算する際は、毎回同じ操作コストに基づいています。実際のコンピュータの状況は、データがディスクに保存されており、毎回の操作はディスク上の一部のデータをメモリに移して処理計算します。ディスクのアクセスコストは、メモリアクセスコストの約 10 万倍ですので、単純な BST では不十分です。
ディスク I/O とプリフェッチ
ディスクからデータを読み取る際、機械的な動きが関与します。データを読み取るのにかかる時間は、シーク時間、回転遅延、転送時間の 3 つの部分に分けられます。シーク時間は、磁臂が指定されたディスクに移動するのに必要な時間で、主流のディスクは一般的に 5ms 以下です。回転遅延は、私たちがよく言うディスクの回転速度です。例えば、7200 回転のディスクは、1 分間に 7200 回転できることを示し、秒に換算すると 1 秒間に 120 回転、回転遅延は 1/120/2=4.17ms です。転送時間は、ディスクからデータを読み取るか、データをディスクに書き込むのにかかる時間で、一般的には数ミリ秒で、前の 2 つに比べて無視できるほどです。したがって、1 回のディスクアクセスの時間、つまり 1 回のディスク I/O の時間は約 5+4.17=9.17ms、9ms 程度です。聞こえは悪くないですが、500-MIPS のマシンは 1 秒間に 5 億命令を実行できることを知っておくべきです。命令は電気の性質に依存しているため、1 回の I/O の時間で 40 万命令を実行できます。データベースは数百万、さらには数千万のデータを持ち、毎回 9ms の時間がかかるのは明らかに災害です。

ディスク I/O が非常に高価な操作であることを考慮して、コンピュータシステムは最適化を行いました。1 回の I/O で、現在のディスクアドレスのデータだけでなく、隣接するデータもメモリバッファに読み込まれます。局所的なプリフェッチ原理は、コンピュータがあるアドレスのデータにアクセスする際、隣接するデータもすぐにアクセスされることを示しています。I/O で読み取られるデータは 1 ページ（Page）と呼ばれます。具体的に 1 ページのデータがどれくらいの大きさかはオペレーティングシステムによりますが、一般的には 4K または 8K です。つまり、1 ページのデータを読み取ると、実際には 1 回の I/O が発生します。この理論はインデックスのデータ構造設計に非常に役立ちます。
インデックスのデータ構造
上記でインデックスの基本原理、データベースの複雑さ、オペレーティングシステムのいくつかの内容について説明しました。目的は、どんなデータ構造も無から生じるものではなく、必ずその背景と使用シーンがあることを理解してもらうことです。では、これらのデータ構造が何をする必要があるのでしょうか？実際には非常にシンプルです。データを検索するたびに、ディスク I/O の回数を非常に小さなオーダー、できれば定数のオーダーに制限することです。そこで、高度に制御された多路探索木がその要求を満たすことができるかどうかを考えます。このような背景の中で、B + 木が登場しました。

B + 木の詳細（実際には B 木であり、上の 17、35、8、12 などの非葉ノードは単にインデックスです）
上の図は B + 木です。B + 木の定義は、学生が自分で百度で調べることができますが、いくつかの重要な点を述べます。図中の浅い青色のブロックは、1 つのディスクと呼ばれ、各ディスクブロックにはいくつかのデータ項（濃い青色）とポインタ（黄色）が含まれています。例えば、ディスクブロック 1 にはデータ 17 とデータ 35 が含まれ、ポインタ P1、P2、P3 が含まれています。P1 はデータが 17 未満のディスクブロックを指し、P2 はデータが 17 から 35 の間にあるディスクブロックを指し、P3 はデータが 35 より大きいディスクブロックを指します。実際のデータは葉ノードに存在し、3、5、9、10、13、15、28、29、36、60、75、79、90、99 です。非葉ノードは実際のデータを保存せず、検索方向を指示するデータ項（例えば 17、35 など）を保存します。これらはデータテーブルには実際には存在しません。

B + 木の検索プロセス
上記の B + 木を使用します。仮に、データ項 29 を検索したいとします。まず、ディスクブロック 1 をディスクからメモリにロードします。この時、1 回の I/O が発生します。メモリ内で二分探索を使用して 29 が 17 と 35 の間にあることを確認し、ディスクブロック 1 の P2 ポインタをロックします。メモリ計算時間は非常に短いため（I/O と比較して）無視できます。ディスクブロック 1 の P2 ポインタのディスクアドレスを介してディスクブロック 3 をメモリにロードします。この時、2 回目の I/O が発生します。29 は 26 と 30 の間にあり、ディスクブロック 3 の P2 ポインタをロックします。ポインタを介してディスクブロック 8 をメモリにロードします。この時、3 回目の I/O が発生し、同時にメモリ内で二分探索を使用して 29 を見つけ、検索が終了します。このプロセスでは、合計で 3 回の I/O が発生しました。実際の使用シーンでは、3 層の B + 木が数百万のデータを表すことができ、数百万のデータを検索するのにわずか 3 回の I/O が必要であれば、性能の向上は非常に大きくなります。B + 木はインデックスデータ構造の一種であり、そのようなインデックスがなければ、各データ項が 1 回の I/O を発生させることになり、コストが大幅に増加します。

B + 木の性質
上記の検索例から、いくつかの B + 木の性質を分析できます：

I/O の回数は B + 木の高さ H に依存します。現在のデータテーブルのデータ数を N、各ディスクブロックのデータ項の数を M とすると、H=log (M+1) N となります。データ量 N が一定のとき、M が大きくなるほど H は小さくなります。また、M はディスクブロックサイズ / データ項サイズであり、ディスクブロックサイズはデータページのサイズであり、固定されています。データ項が占めるスペースが小さくなるほど、データ項の数が多くなり、木の高さも低くなります。これが、各データ項、つまりインデックスフィールドをできるだけ小さくする理由です。例えば、int は 4 バイトであり、bigint の 8 バイトの半分です。これが、B + 木が実際のデータを葉ノード内に置くことを要求する理由でもあります。内層ノードに置くと、ディスクブロックのデータ項が大幅に減少し、木の階層が増加します。データ項が 1 の場合、B + 木は線形リストに退化します。
B + 木のデータ項は複合データ構造です。例えば（name、age、gender）の場合、B + 木は左から右の順序で検索ツリーを構築します。例えば（小張、22、女）のようなデータを検索する場合、B + 木は最初に name を比較して次の検索方向を決定し、name が同じ場合は age と gender を順次比較して最終的に検索データを取得します。しかし、（22、女）のように name がないデータが来た場合、B + 木は次にどのノードを検索すべきか分かりません。なぜなら、検索ツリーを構築する際、name が最初の比較因子であり、name に基づいて検索しなければ次にどこを検索するかが分からないからです。例えば、（小張、男）のようなデータを検索する場合、B + 木は name に基づいて検索方向を指定できますが、次のフィールドである age が欠落しているため、「小張」という名前のすべてのデータを見つけてから、性別が「男」であるデータをマッチングする必要があります。これは非常に重要な性質であり、インデックスの最左一致特性です。

インデックスの種類
MySQL では、インデックスは主に 2 つの大きなカテゴリに分けられます：クラスタインデックスと非クラスタインデックス。クラスタインデックスはデータが物理的に保存されている位置に基づいて順序付けられていますが、非クラスタインデックスは異なります。クラスタインデックスは複数行の検索速度を向上させることができ、非クラスタインデックスは単一行の検索速度が非常に速いです。
この 2 つの大カテゴリのインデックスタイプの下に、さらに 4 つの小カテゴリに分けることができます：

通常のインデックス：最も基本的なインデックスで、制限がなく、私たちがほとんどの場合使用するインデックスです。
ユニークインデックス：通常のインデックスとは異なり、ユニークインデックスの列値は一意でなければならず、NULL 値は許可されます。
フルテキストインデックス：フルテキストインデックス（FULLTEXT）は MyISAM エンジンのデータテーブルにのみ適用され、CHAR、VARCHAR、TEXT データ型の列に作用します。
組み合わせインデックス：いくつかの列を 1 つのインデックスとして検索し、最左一致の原則を使用します。

インデックスを作成する原則
最初に言及した遅いクエリを振り返ると、インデックスの原理を理解した後、遅いクエリの最適化についていくつかの考えがあるはずです。ここでは、インデックスを作成する際のいくつかの原則をまとめてみましょう：

最左接頭辞一致原則。これは非常に重要で、非常に重要で、非常に重要な原則です（重要なことは 3 回言います）。MySQL は、範囲クエリ（>, <, BETWEEN, LIKE）に出会うまで右に一致し続けます。例えば、a = 1 AND b = 2 AND c> 3 AND d = 4 の場合、（a, b, c, d）の順序でインデックスを作成すると、d はインデックスを使用しませんが、（a, b, d, c）のインデックスを作成すれば、すべて使用できます。a, b, d の順序は任意に調整できます。
等しい（=）と in は順不同で構いません。例えば、a = 1 AND b = 2 AND c = 3 の場合、（a, b, c）インデックスは任意の順序で作成できます。MySQL のクエリオプティマイザがインデックスが認識できるパターンに最適化します。
できるだけ識別度の高い列をインデックスとして選択します。識別度の公式は COUNT (DISTINCT col) / COUNT (*) です。これは、フィールドが重複しない割合を示し、割合が大きいほどスキャンするレコード数が少なくなります。ユニークキーの識別度は 1 ですが、いくつかの状態や性別フィールドは大データの前では識別度が 0 になる可能性があります。これに関して、経験則はありますか？使用シーンによって異なり、この値を特定するのは難しいですが、一般的に JOIN が必要なフィールドは 0.1 以上、つまり平均して 1 つのスキャンで 10 件のレコードを要求します。
インデックス列は計算に参加できず、できるだけ「クリーン」に保つ必要があります。例えば、FROM_UNIXTIME (create_time) = ‘2016-06-06’はインデックスを使用できません。理由は非常に簡単で、B + 木に保存されているのはデータテーブル内のフィールド値ですが、検索時にはすべての要素に関数を適用して比較する必要があるため、明らかにコストが大きすぎます。したがって、文は次のように書く必要があります：create_time = UNIX_TIMESTAMP (‘2016-06-06’)。
インデックスを新たに作成するのではなく、できるだけ既存のインデックスを拡張します。例えば、テーブルにすでに a のインデックスがある場合、（a, b）のインデックスを追加する必要がある場合は、元のインデックスを修正するだけで済みます。
この記事は以下から引用されています：
https://blog.csdn.net/mysteryhaohao/article/details/51719871