MySQL 最適化における B ツリーインデックスの知識ポイントのまとめ

SQL を最適化する必要があるのはなぜですか?当然ですが、SQL ステートメントを記述する場合、次のようになります。

1 パフォーマンスが低いことがわかります
2. 実行時間が長すぎる
3. 待ち時間が長すぎる
4. SQL文が正しくなく、インデックスが無効です
5. サーバーのパラメータ設定が不合理である

SQL文実行プロセス分析

1. 執筆プロセス:

記述プロセスは、日常生活における SQL 文の記述プロセスであり、記述順序とも言えます。記述順序は次のとおりです。

選択、結合、条件、グループ化、グループ化、フィルタグループ、順序、並べ替え制限、クエリ数の制限

このように記述しましたが、MySQL エンジンは解析時に上記の順序に従いません。

最初に選択して解析するのではなく、最初から解析します。つまり、解析プロセスは書き込みプロセスと一致していません。そこで、発行された解析順序を見てみましょう。

2. 解析プロセス:

from on join where group by having select order by limit

上記は MySQL の解析プロセスです。私たちが書いたプロセスとはまったく矛盾していることがわかりました。

索引

インデックスとは何ですか?簡単に言えば、それは本のカタログです。

例えば、辞書で「王」という単語を調べたい場合、目次なしで「王」という単語を見つけたい場合は、辞書を最初から最後までめくる必要があります。1000ページある場合は、見つけるまで1ページずつめくる必要があります。

索引はディレクトリに相当します。「王」という単語を検索する前に、まずディレクトリを調べて、「W」が 300 ページにあることを確認します。「王」の最初の文字は「W」なので、300 ページから直接検索できます。この方法により、非常に迅速に見つけることができます。

データベース内のキーワード「index」は「insex」です。公式の定義によると、インデックスは、MySQL がデータを迅速かつ効率的に取得するのに役立つデータ構造です。

インデックスは、データを効率的にクエリするために使用されるデータ構造です。

それで、それはどのようなデータ構造ですか?

実際、それはツリーです。B ツリーとハッシュツリーの方がよく使用されます。MySQL では、B ツリーインデックスが使用されます。

Bツリーインデックス

まず、これをデータテーブルであると仮定して図を描き、年齢列にインデックスを追加します。

このインデックスをディレクトリとして考えてください。つまり、年齢 50 は最初の行を指し、年齢 33 は 5 番目の行を指します。

次に、B ツリーインデックスを描画して、インデックスがどのように作成されるかを確認します。

ageにインデックス列を追加すると、ツリーのようになり、小さい値は左側に、大きい値は右側に配置されます。最初の列は50で、50より小さい値は左側に配置され、23と23より小さい値は引き続き左側に配置されます。

33 は 23 より大きいので、左に配置されます。20 は 22 より小さいので、22 の後も左に配置され続けます。

たとえば、今度は 33 を確認する必要があります。

テーブル名から、年齢 = 33 を選択します。

インデックスがない場合、検索は 50 から始まります。50 は 23 でも 22 でも、... でもありません。インデックスがない場合、検索は 1 つずつ開始されます。

インデックスを追加すると、33 を検索し、33 が 50 より小さいことがわかります。最初は 23 を検索します。2 回目は、33 が 23 より大きいことがわかります。3 回目は、わずか 3 回で検索します。

インデックスのデメリット

1. インデックス自体は多くのスペースを占有し、メモリ/ハードディスクに保存できます（通常）

2. インデックスは、データ量が少ない場合や頻繁に更新されるフィールドなど、すべての状況に適用できるわけではありません (データテーブル内の列が頻繁に変更される場合、この列はインデックス作成に適していません)

3. インデックスは確かにクエリの効率を向上させますが、同時に追加、削除、変更の効率を低下させます。次に例を示します。

インデックスはありません。44 を 45 に変更する場合は、簡単に変更できます。インデックスがある場合は、テーブル内の 44 だけでなく、B ツリー内の 44 も変更する必要があります。

1 つ上げて 3 つ下げるのは費用対効果が悪いと考える人もいますが、実際には非常に費用対効果が高いのです。

クエリ、追加、削除、変更はほとんど行われず、クエリはパフォーマンスに大きな影響を与えるため、使用することが非常に重要です。

インデックスの利点

1. クエリ効率の向上

クライアントは IO、つまり入力ストリームと出力ストリームを介してサーバーに接続するため、クエリの効率を向上させることは IO 使用量を削減することを意味します。

2. CPU使用率を下げる

たとえば、SQL で order by desc ステートメントを使用して年齢を降順または昇順に並べ替える場合、インデックスがなければすべての年齢を並べ替える必要があります。ただし、インデックスがあれば、年齢を並べ替える必要はありません。B ツリー自体は並べ替えられた構造です。左端が最小で、右端が最大である必要があります。