MySQLデータベースインデックスの欠点と適切な使用

1. 通常のインデックスのデメリット
2. 主キーインデックスの落とし穴
3. ジョイントインデックスの槍と盾
4. プレフィックスインデックスは短くて強力
5. ユニークインデックスの速さと遅さ
6. 盲目的にインデックスを追加しない
7. インデックスの失敗

インデックスの最適化

1.バッファの変更
2. インデックスプッシュダウン
3. 隣接するページを更新する
4.MRR

やっと

適切なインデックスは、データベースシステムにとって特に重要です。インデックスはデータベースの心臓部とも言えます。データベースにインデックスがなければ、データベース自体はほとんど意味をなさず、通常のファイルと何ら変わりません。今日はMySQLのインデックスについてお話します。MySQLのB+ツリーインデックスのメリットを、ビジネスの観点から詳しく見ていくとともに、インデックスを使用する際に注意すべき知識ポイントについても見ていきましょう。

インデックスの適切な使用

職場では、データテーブル内のフィールドにインデックスを付ける必要があるかどうかを判断する最も直接的な方法は、このフィールドがwhere条件に頻繁に出現するかどうかを確認することです。マクロ的な視点で考えると、このように考えることは何も問題ないのですが、長期的な視点で考えると、この分野で単なるインデックス以上のものを作る必要があるのか、など、より詳細な検討が必要になる場合もあります。複数のフィールドの結合インデックスを使用する方が良いでしょうか?ユーザーテーブルを例にとると、ユーザーテーブルのフィールドには、ユーザーの名前、ユーザーの ID 番号、ユーザーの自宅住所などが含まれる場合があります。

1. 通常のインデックスのデメリット

ここで、ユーザーの ID 番号に基づいてユーザー名を見つける必要があります。この時点で、最初に思いつく解決策は、 id_cardにインデックスを作成することです。厳密に言えば、ID 番号は一意である必要があるため、これは一意のインデックスです。そのため、次のクエリを実行します。

SELECT name FROM user WHERE id_card=xxx

そのプロセスは次のようになります。

まず、id_cardインデックスツリーを検索して、id_cardに対応する主キーidを見つけます。
主キーインデックスをIDで検索し、対応する名前を見つける

パフォーマンスの観点からは結果は問題ありませんが、効率の観点から見ると、このクエリは 2 つの B+ ツリーを取得するため、少しコストが高いようです。 1 つのツリーの高さが 3 であると仮定すると、2 つのツリーの高さは 6 です。ルートノードがメモリ内にあるため (ここでは 2 つのルートノード)、ディスク上で実行される IO の数は 4 です。ランダムディスク IO の平均時間が 10 ミリ秒であると仮定すると、最終的には 40 ミリ秒かかります。この数値は平均であり、速いものではありません。

2. 主キーインデックスの落とし穴

問題はテーブルを返すことであり、両方のツリーで検索することになるので、核となる疑問は 1 つのツリーだけで検索できるかどうかです。ビジネスの観点から見ると、ここで突破口が見つかったかもしれません。ID 番号は一意なので、デフォルトの自動増分 ID とは別の主キーを使用できるでしょうか。主キーを ID 番号に設定して、テーブル全体で 1 つのインデックスのみが必要になり、名前を含む必要なデータはすべて ID 番号を通じて見つけることができます。データを挿入するたびに ID が ID 番号であることを指定する限り、一見理にかなっているように思えます。しかし、よく考えてみると問題があるようです。

ここで、B+ ツリーの特性についてお話しします。B+ ツリーのデータはリーフノードに格納され、ページで管理されます。1 ページは 16K です。これは何を意味するのでしょうか。たとえ現在 1 行のデータがあったとしても、それは 16K のデータページを占有します。データページがいっぱいになったときにのみ、新しいデータページに書き込まれます。新しいデータページと古いデータページは、必ずしも物理的に連続しているわけではありません。そして、非常に重要な点が 1 つあります。データページは物理的に不連続ですが、データは論理的に連続しています。

おそらく、これは ID 番号を主キー ID として使用することとどのような関係があるのか、興味があるのではないでしょうか。このとき、「連続」というキーワードに注意してください。ID番号が連続していません。これはどういう意味ですか?不連続なデータを挿入する場合、連続性を保つためにデータを移動する必要があります。たとえば、あるページの元のデータが 1->5 で、データ 3 を挿入する場合、5 を 3 の後ろに移動する必要があります。これはあまりコストがかからないと思われるかもしれません。しかし、新しいデータ 3 によってページ A がいっぱいになった場合は、その後ろのページ B にスペースがあるかどうかを確認する必要があります。スペースがある場合、ページ B の開始データはページ A からあふれた部分であるはずなので、対応するデータも移動する必要があります。

この時点でページ B に十分なスペースがない場合は、新しいページ C を申請し、データの一部をこの新しいページ C に移動する必要があります。ページ A とページ B の関係は切断され、その間にページ C が挿入されます。コードの観点からは、これはリンクリストのポインタを切り替えることです。

要約すると、不連続な ID 番号を主キーとして使用すると、ページデータの移動、ランダム IO、新しいページの頻繁な要求に関連するオーバーヘッドが発生する可能性があります。自動増分主キーを使用する場合、ID は連続している必要があり、ランダム IO によるデータ移動の問題は発生せず、挿入オーバーヘッドは比較的小さくなります。

実は、ID番号を主キーとして使用することを推奨しない別の理由があります。ID番号は数値としては大きすぎるため、bigintで保存する必要があります。通常、学校の生徒の場合はintで十分です。1ページには16Kを保存できることがわかっています。インデックス自体が占めるスペースが大きいほど、1ページに保存できるデータは少なくなります。したがって、ある程度のデータ量の場合、bigintを使用するとintよりも多くのページが必要になり、保存スペースが増えます。

3. ジョイントインデックスの槍と盾

上記の 2 つの結論から、次のことが言えます。

テーブルに戻らないようにする
ID番号は主キーインデックスとして適していません

そこで、当然のことながら、共同インデックスを考えて、[ID番号+名前]の共同インデックスを作成しました。共同インデックスの順序には注意してください。左端の原則に従う必要があります。したがって、次の SQL を実行すると、

id_card=xxx のユーザーから名前を選択

テーブルに戻らなくても、必要な名前フィールドを取得できます。ただし、ID カード番号自体が多くのスペースを占有するという問題は解決されていません。これはビジネスデータ自体の問題です。解決したい場合は、いくつかの変換アルゴリズムを使用して、元の大きなデータを crc32 などの小さなデータに変換できます。

crc32.チェックサムIEEE([]byte("341124199408203232"))

もともと 8 バイトの記憶領域を必要としていた ID 番号は、4 バイトの CRC コードに置き換えることができます。そのため、データベースに別のフィールドcrc_id_cardを追加する必要があり、結合インデックスは [ID 番号 + 名前] から [crc32 (ID 番号) + 名前] に変更され、結合インデックスが占める領域は小さくなります。しかし、この変換にはコストもかかります。

CRCを追加するごとにCPUリソースが必要になります
追加のフィールドによりインデックスに必要なスペースは削減されますが、フィールド自体もスペースを占有します。
crc では競合が発生する可能性があり、その場合はデータをクエリし、id_card に従ってフィルタリングする必要があります。フィルタリングのコストは重複データの数によって異なります。重複が多いほど、フィルタリングは遅くなります。

結合インデックスのストレージ最適化に関して、ここで少し詳しく説明します。フィールド A と B が 2 つあり、それぞれ 8 バイトと 20 バイトを占めているとします。結合インデックスがすでに [A、B] である場合、B の個別のクエリもサポートする必要があります。したがって、当然、B にインデックスを作成します。すると、2 つのインデックスが占めるスペースは 8+20+20=48 になります。これで、A を介してクエリを実行する場合でも、B を介してクエリを実行する場合でも、インデックスを使用できます。業務上許可されている場合、[B、A] と A のインデックスを作成できますか? この方法では、インデックスを使用して A または B のみを介してデータをクエリできるだけでなく、占有スペースも少なくなります: 20+8+8=36。

4. プレフィックスインデックスは短くて強力

インデックスを作成する必要があるフィールドが文字列型で、この文字列が非常に長い場合があります。このフィールドにインデックスを追加したいのですが、このインデックスが多くのスペースを占めないようにする必要があります。この場合、プレフィックスインデックスを作成し、このフィールドの最初の部分の文字でインデックスを作成することを検討できます。この方法では、インデックスを活用でき、スペースを節約できます。ここで注意すべき点は、プレフィックスの繰り返し率が高い場合、プレフィックスインデックスと通常のインデックスの速度に違いがあるはずだということです。

alter table xx add index(name(7));#nameの最初の7文字に基づいてインデックスを作成します。select xx from xx where name="JamesBond"

5. ユニークインデックスの速さと遅さ

ユニークインデックスについて説明する前に、まず通常のインデックスの特性を理解しましょう。B+ ツリーの場合、リーフノードのデータは順序付けられていることがわかっています。

ここで、データ 2 をクエリするとします。インデックスツリーで 2 が見つかると、ストレージエンジンは検索を停止しません。2 が複数ある可能性があるためです。つまり、ストレージエンジンはリーフノードを逆方向に検索し続けます。2 番目の 2 を見つけたら、停止するのでしょうか。答えは「いいえ」です。ストレージエンジンは、その後ろにさらに 2 があるかどうかわからないため、2 ではない最初のデータ (つまり 3) が見つかるまで逆方向に検索を続ける必要があります。3 が見つかったら、検索を停止します。これが通常のインデックスの取得プロセスです。

ユニークインデックスは異なります。ユニークであるため、重複したデータは存在しません。したがって、対象データを取得した後、通常のインデックスのようにもう一度検索し直す必要はなく、直接返されます。この観点から見ると、ユニークインデックスは通常のインデックスよりも高速です。ただし、通常インデックスのデータがすべて 1 ページにある場合は、それほど高速ではありません。データ挿入の点では、ユニークインデックスはユニークであるがゆえに若干劣るかもしれません。挿入を行うたびに、挿入するデータがすでに存在するかどうかを判断する必要がありますが、通常のインデックスではこのロジックは必要ありません。また、ユニークインデックスは変更バッファを使用しないという点も非常に重要な点です（下記参照）。

6. 盲目的にインデックスを追加しない

職場では、次のような状況に遭遇することがあります。「このフィールドにインデックスを追加する必要がありますか?」。この問題については、通常、クエリがこのフィールドを使用するかどうかで判断します。このフィールドがクエリ条件に頻繁に含まれる場合は、インデックスの追加を検討できます。しかし、この条件だけで判断すると間違った指標を加えてしまう可能性があります。例を見てみましょう。約 100 万件のデータを持つユーザーテーブルがあるとします。ユーザーテーブルには男性と女性を示す性別フィールドがあり、男性と女性が全体の約半分を占めています。ここで、すべての男性の情報をカウントし、性別フィールドにインデックスを追加して、次のような SQL を記述します。

sex="男" のユーザーから * を選択します

予期しない事態が発生しない場合、InnoDB は性別インデックスを選択しません。性別インデックスを使用する場合は、テーブルを返す必要があります。データ量が多い場合、返される結果にはどのような影響がありますか?上の写真に似た写真を投稿します。皆さんご存知だと思いますが、

肝心なのは大量のIOです。データ1個につき4回必要なので、50万個だとどうなるでしょうか？結果は予想通りです。したがって、この場合、MySQL オプティマイザは、パフォーマンスが向上する可能性があるため、テーブル全体のスキャンを実行し、主キーインデックスを直接スキャンする可能性があります。

7. インデックスの失敗

場合によっては、私たち自身の不適切な使用により、MySQL がインデックスを使用できないことがあります。これは通常、型変換で簡単に発生します。MySQL はすでに暗黙的な変換をサポートしていないのかと疑問に思うかもしれません。たとえば、整数の user_id インデックスフィールドがあります。クエリを実行するときに注意を払わず、次のように記述しました。

user_id="1234" のユーザーから xx を選択します。

これは文字 1234 であることに注意してください。このような場合、MySQL は文字 1234 を数値 1234 に変換し、user_id インデックスを適切に使用するほどスマートです。しかし、文字型の user_id インデックスフィールドがある場合、またはクエリ時に注意を払わなかった場合は、次のように記述します。

user_id=1234 のユーザーから xx を選択します。

この時点では問題があり、インデックスは使用されません。なぜ MySQL はこの時点で変換しないのかと疑問に思うかもしれません。数値 1234 を文字タイプ 1234 に変換するだけで十分ではないでしょうか。ここで、変換ルールを説明する必要があります。文字列と数値を比較する場合、MySQL は文字列を数値に変換することに注意してください。文字型の user_id フィールドを数値に変換した後、インデックスが不要になるのはなぜですか? これは、B+ ツリーインデックスの構造に関係しています。B+ ツリーのインデックスは、インデックス値に従って分岐およびソートされることがわかっています。インデックスフィールドタイプを変換すると、値が変わります。たとえば、元の値が A の場合、整数変換を実行すると、B 値 (int(A)=B) に対応する可能性があります。この場合、インデックスツリーは B ではなく A に従って構築されるため、インデックスツリーは使用できません。したがって、インデックスは使用されません。

インデックスの最適化

1. バッファを変更する

データを更新するときは、まずそのデータのページがメモリ内にあるかどうかを判断する必要があることはご存じのとおりです。メモリ内にある場合は、対応するメモリページを直接更新します。メモリ内にない場合は、ディスクにアクセスして対応するデータページをメモリに読み込んでから更新するしかありません。これにより、どのような問題が発生しますか?

ディスクへの読み取り操作は少し遅いです。
大量のデータが同時に更新されると、多くの個別の IO が発生する可能性があります。

このような状況での速度問題を解決するために、変更バッファが生まれました。まず、バッファという言葉に惑わされないでください。変更バッファは、パブリックバッファプールにあるだけでなく、ディスクにも保存されます。変更バッファを取得した後、更新プロセス中に対応するデータページがメモリ内にないことがわかった場合、ディスクから対応するデータページを読み取るのではなく、更新するデータを変更バッファに格納します。変更バッファ内のデータはいつディスクに同期されますか?この時点で読み取りアクションが発生するとどうなるでしょうか?まず、バックグラウンドで、変更バッファデータをディスクに定期的に同期するスレッドがあります。スレッドに同期する時間がなかったが、読み取り操作が発生した場合は、変更バッファデータをディスクにマージするイベントもトリガーされます。

すべてのインデックスがチェンジャーバッファを使用できるわけではないことに注意してください。主キーインデックスとユニークインデックスは使用できません。ユニークであるため、更新時にデータが存在するかどうかを判断する必要があります。データページがメモリ内にない場合は、対応するデータページをディスクからメモリに読み込む必要があります。これは通常のインデックスでは問題ではなく、ユニーク性を検証する必要はありません。変更バッファが大きいほど、理論上の利点は大きくなります。これは、第一に、個別の読み取り IO が削減され、第二に、データページで複数の変更が発生した場合、それらをディスクに 1 回だけマージする必要があるためです。もちろん、すべてのシナリオがチェンジャーバッファに適しているわけではありません。業務で更新直後に読み取りを行う必要がある場合、マージアクションを継続的にトリガーする必要があるため、チェンジャーバッファは逆効果となり、ランダム IO の数は減少せず、チェンジャーバッファを維持するためのオーバーヘッドが増加します。

2. インデックスプッシュダウン

以前、結合インデックスについて説明しました。結合インデックスは、左端の原則を満たす必要があります。つまり、結合インデックスが [A, B] の場合、次の SQL を通じてインデックスを使用できます。

A="xx" のテーブルから * を選択
A="xx" AND B="xx" のテーブルから * を選択

実際、結合インデックスでは、次の最左接頭辞の原則も使用できます。

A が "赵%" であり、B が "沪" であるテーブルから * を選択します

ただし、ここで注意すべき点は、A の一部が使用されているため、MySQL5.6 より前では、上記の SQL は、A が「Zhao」で始まるすべてのデータを取得した後、すぐにテーブルに戻り (select * を使用)、次に B と比較して「Shanghai City」であるかどうかを確認します。これは少しわかりにくいのではないでしょうか。 B はなぜ結合インデックスで直接判断しないのですか? そうするとテーブルリターンの数が減りませんか?この問題の原因は、依然として左端のプレフィックスの使用によるものです。その結果、インデックスはAの一部を使用できるものの、Bをまったく使用できません。少し「愚か」に見えます。そのため、MySQL5.6以降では、インデックスプッシュダウン最適化（Index Condition Pushdown）が登場しました。この機能により、左端のプレフィックスが使用されているにもかかわらず、非Bデータをフィルタリングしながら、ジョイントインデックスでA％を満たすデータを検索することも可能になり、テーブルリターンの数が大幅に削減されました。

3. 隣接するページを更新する

隣接ページのリフレッシュについて話す前に、ダーティページについて説明しましょう。データを更新するときは、まずそのデータが配置されているページがメモリ内にあるかどうかを判断する必要があります。メモリ内にない場合は、まずデータページをメモリに読み込み、メモリ内のデータを更新する必要があります。このとき、メモリ内のページには最新のデータがあるのに、ディスク上のページはまだ古いデータであることがわかります。このとき、データが配置されているメモリ内のページはダーティページであり、一貫性を保つためにディスクにフラッシュする必要があります。では、いつ歯を磨くべきかという疑問が生じます。毎回フラッシュするダーティページ数はいくつですか?変更のたびにデータをフラッシュすると、パフォーマンスが非常に低下します。長い時間後にデータをフラッシュすると、ダーティページが蓄積され、メモリプールで使用可能なページが少なくなり、通常の機能に影響します。したがって、フラッシュ速度は速すぎてはならず、タイムリーでなければなりません。MySQL には、速すぎないように定期的に実行されるクリーンアップスレッドがあります。ダーティページが多すぎる場合や、REDO ログがほぼいっぱいになっている場合は、タイムリーさを確保するために、ディスクのフラッシュがすぐにトリガーされます。

InnoDB はダーティページをフラッシュするプロセスにおいて、最適化を行います。フラッシュするダーティページの隣接ページもダーティである場合、それらを一緒にフラッシュします。これの利点は、ランダム IO を削減できることです。機械式ディスクの場合、最適化はかなり大きくなるはずですが、落とし穴がある可能性があります。現在のダーティページの隣接ダーティページが一緒にフラッシュされ、その後、データの変更により隣接ページがすぐに再びダーティになると、冗長な動きのように感じられ、時間と費用が無駄になります。さらに悪いことに、隣接ページの隣接ページもダーティである場合、この連鎖反応によって短期的なパフォーマンスの問題が発生する可能性があります。

4.MRR

実際のビジネスでは、テーブルに戻ると IO が増え、時間がかかるため、できるだけカバーリングインデックスを使用し、テーブルに戻らないようにと言われることがあります。しかし、テーブルに戻らなければならない場合もあります。テーブルに戻ると、IO が多すぎるだけでなく、より深刻なことに、個別の IO が多すぎます。

グレードが60から70のユーザーから*を選択

グレードが60〜70のユーザー情報を照会する必要があるため、もちろんSQLが記載されています。最初にgradeインデックスでグレード= 60のデータを見つけ、次にGrade = 60のデータに応じてIDに応じてIDに応じてIDに対応しています。 Grade = 61に対応するID = 10はpage_no_1で、Grade = 62に対応するID = 2 id=1 page_no_2であるため、実際の状況は最初にpage_no_1に切り替えて、最後にPAGE_NO_1のみを使用できます。 id=2であるランダムIOを回避します。 MRR を使用した後、補助インデックスはすぐにテーブルに戻るのではなく、取得した主キー ID をバッファーに入れてからソートします。ソート後、主キーインデックスは順番に読み取られるため、離散 IO が大幅に削減されます。