MySQLインデックスを正しく作成する方法

インデックス作成は大学図書館の書誌インデックスの構築に似ており、データ検索の効率を向上させ、データベースの IO コストを削減できます。 MySQL のパフォーマンスは、公式ドキュメントでは 500 万～800 万レコードと記載されていますが、約 300 万レコードを超えると低下し始めるため、大量のデータに対してインデックスを作成することが非常に重要です。 MySQL は、SQL 実行に関する詳細情報を表示し、インデックスを最適化するために使用される Explain を提供します。

インデックスとは何ですか?

MySQL のインデックスの公式定義は次のとおりです: インデックスは、MySQL がデータを効率的に取得するのに役立つデータ構造です。簡単に言えば、ソートされた順序を素早く見つけるデータ構造です。 MySQL インデックスには、B+ ツリー インデックスとハッシュ インデックスという 2 つの主な構造があります。インデックスについて話すときは、特に指定がない限り、通常は B ツリー構造 (B+ ツリー インデックス) で編成されたインデックスを指します。インデックスは図に示されています:

最も外側の水色のディスク ブロック 1 には、データ 17、35 (濃い青) とポインター P1、P2、P3 (黄色) が含まれています。 P1 ポインタは 17 より小さいディスク ブロックを示し、P2 は 17 から 35 の間であり、P3 は 35 より大きいディスク ブロックを指します。実際のデータは、最下層の 3、5、9、10、13 などのサブリーフ ノードに存在します。非リーフ ノードには実際のデータは保存されず、17、35 など、検索方向を導くデータ項目のみが格納されます。

検索プロセス: たとえば、データ項目 28 を検索するには、まずディスク ブロック 1 をメモリにロードし、I/O を発生させ、バイナリ検索を使用して P2 ポインターを決定します。次に、28 が 26 と 30 の間にあることがわかり、ディスク ブロック 3 が P2 ポインタのアドレスを通じてメモリにロードされ、2 番目の I/O が発生します。ディスク ブロック 8 も同様に見つかり、3 番目の I/O が発生します。

実際には、B+ ツリーの上位 3 層は数百万のデータを表すことができます。数百万のデータには、数百万の I/O 操作ではなく 3 つの I/O 操作のみが必要であり、時間の改善は大幅に改善されます。

データ テーブル インデックスを使用すると、データ取得の効率が向上し、データベース IO コストが削減されます。また、インデックスを使用すると、データベースのソート コストも削減されます。ソートおよびグループ化操作は主に CPU リソースとメモリを消費するため、ソートおよびグループ化操作でインデックスを有効活用できれば、CPU リソースの消費を大幅に削減できます。以下では、MySQL データ インデックスを正しく作成する方法を簡単に分析します。

インデックスを作成する必要があるかどうかを判断するにはどうすればよいでしょうか?

1. クエリ条件として頻繁に使用する必要があるフィールドがある場合は、それらのフィールドのインデックスを作成する必要があります。

誰もがこれを知っているはずですが、何が頻繁であると考えられるのでしょうか?実行するすべての SQL ステートメントを包括的に分析します。すべてを一つずつリストアップするのが最善でしょう。その後、分析の結果、一部のフィールドがほとんどの SQL クエリ ステートメントで使用されていることがわかったので、それらのフィールドに対してインデックスを作成することに決めました。

2. 一意性が低いフィールドはインデックス作成に適さない

十分に一意ではないフィールドは何ですか?ステータス フィールドやタイプ フィールドなど。ユーザーのログイン ステータス、メッセージのステータスなど、いくつかの固定値のみを保存するフィールド。これには、インデックススキャンの特性が関係します。たとえば、インデックスを通じてキー値 A と B を持つデータを検索し、ページ X にある A を通じて一致するデータを見つけ、スキャンを続行してページ Y で A に一致するデータを見つけた場合、ストレージ エンジンはページ X のデータを破棄し、A に一致するすべてのデータが見つかるまでページ Y にデータを格納します。次に、フィールド B を検索し、ページ X でフィールド B に一致するデータを見つけた場合、ストレージ エンジンはページ X を再度スキャンします。つまり、ページ X は 2 回以上スキャンされることになります。同様に、同じデータ ページが繰り返し読み取られ、破棄され、再度読み取られる可能性があり、これによりストレージ エンジンの IO 負荷が大幅に増加するのは間違いありません。

3. 頻繁に更新されるフィールドはインデックス作成には適していません

フィールドにインデックスを作成すると、フィールドのデータを再度更新すると、データベースは自動的にインデックスを更新します。そのため、フィールドが頻繁に更新されると、インデックスが常に更新され、パフォーマンスへの影響が予想されます。何十回も検索された後に一度だけ更新されるフィールドは、インデックス作成に適しています。フィールドが同じ期間内に複数回更新された場合、インデックスを作成することはできません。

4. where条件に含まれないフィールドはインデックス化しない

これについては、実際には特に言うことはありません。クエリ条件として使用されないフィールドにインデックスを作成しても意味がありません。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。