インデックス作成の原則 - innodb を例に挙げる

1. 前に書く

開発とテストのタスクが終了すると、全員がプロジェクトのリリース前にいくつかの準備を整理しています。これは、以前に作成されたいくつかの SQL ステートメントのインデックスを作成することです。同時実行性は、トラフィックの多い環境では非常に必要です。適切なインデックスを構築すると、SQL ステートメントのクエリ効率が大幅に向上します。そこで問題となるのは、インデックスとは何か、そして適切なインデックスを構築する方法です。この記事では、mysql Innodb ストレージ エンジンを例として、実際のプロジェクトに基づいて適切なインデックスを構築する方法を説明します。

2. インデックスの定義

MySQL のインデックスの公式定義は次のとおりです: インデックス (インデックス) は、MySQL がデータを効率的に取得するのに役立つデータ構造です。文の語幹を抽出すると、インデックスの本質がわかります。インデックスはデータ構造です。
次の SQL ステートメントのように、データベース クエリがデータベースの最も重要な機能の 1 つであることはわかっています: SELECT * FROM test_table WHERE id = 99; id 99 のデータ レコードはテーブル test_table から取得できます。
私たちは皆、できるだけ早くデータをクエリしたいと考えているため、データベース システムの設計者はクエリ アルゴリズムの観点から最適化します。最も基本的なクエリ アルゴリズムは、もちろん、test_table を走査し、id の値が 99 であるかどうかを行ごとに照合します。このアルゴリズムは複雑度が O(n) であり、データ量が多い場合には明らかに問題になります。コンピューターサイエンスの発展により、二分探索や二分木探索など、より優れた検索アルゴリズムが数多く提供されています。少し分析すると、各検索アルゴリズムは特定のデータ構造にのみ適用できることがわかります。たとえば、二分探索では取得したデータを順序付けする必要がありますが、二分木検索では二分探索木にのみ適用できます。データ自体 組織構造はさまざまなデータ構造を完全に満たすことはできません (たとえば、両方の列を同時に順番に整理することは理論的に不可能です)。そのため、データベース システムはデータに加えて、特定の検索を満たすデータ構造も維持します。アルゴリズム。構造は何らかの方法でデータを参照 (ポイント) し、これらのデータ構造に高度な検索アルゴリズムを実装できます。このデータ構造がインデックスです。
上記の例は、主にインデックスの役割を簡単に説明するためのものです。mysql Innodb を含むほとんどのデータベース システムとファイル システムは、インデックスとして B-Tree またはそのバリアント B+Tree を使用します。このインデックス構造により、検索プロセス中のディスク I/O アクセスの数を最小限に抑えることができます。B-Tree または B+Tree とは何か、およびそれらをデータベース インデックス構造として選択する理由については、自分で学ぶことができます。以下では、まず mysql Innodb エンジンの 2 つの B+Tree インデックスを紹介します。

3. Mysql Innodb B+Tree インデックス

1 つは主キー インデックスであり、主キーだけでなく、主キーが属するすべてのデータも保持します。つまり、Innodb では、主キー インデックスはデータです。

1 つは、列値を Key、主キー位置を値 (列値、主キー位置) とする非主キー インデックス (セカンダリ インデックス) です

Innodb はインデックス構成テーブルに属し、すべてのデータは主キーのリーフ ノードの下にハングされます。したがって、主キーの挿入順序が保証できない場合、多数の主キー ノードの分割が発生し、多数の I/O 操作が発生します。さらに、Innodb では、1 つのインデックス フィールドの長さが 768 バイトを超えてはいけないと規定しています。768 バイトを超えない場合、長さは切り捨てられ、インデックスに配置されません。 Innodb の非主キー インデックスはすべて主キー インデックスを指しています。非主キー インデックスを検索すると、データの行全体を取得できません。行全体を取得するには、リーフ ノード ポインターを介して主キー インデックスの場所を見つける必要があります。したがって、主キー インデックスはできるだけ小さくなるように設計する必要があります。そうしないと、非主キー インデックスが非常に大きくなります。

4. インデックス作成の原則

立 適切なインデックスを確立するために従う必要がある原則を見て、具体的な例を組み合わせて説明します。1. 左側のプレフィックス マッチングの原則は、MySQL が常にスコープに一致するまで一致するという非常に重要な原則です。たとえば、a = 1 と b = 2、c > 3 と d = 4 の場合、クエリ (>、2. = と in は、a = 1、b = 2、c = 3 のように順序が崩れることがあります。(a, b, c) インデックスは、最適化に役立ちます。インデックスが認識できる形式に変換します。
3. 区別性の高い列をインデックスとして選択するようにしてください。区別の式は count(distinctcol)/count(*) であり、重複しないフィールドの割合が大きいほど、スキャンするレコードが少なくなります。 、および一意のキー 区別は 1 ですが、一部のステータスおよび性別フィールドは、ビッグ データに直面すると区別が 0 になる可能性があります。この比率には経験的な値はあるのかと疑問に思う人もいるかもしれません。使用シナリオは異なり、この値を決定することも困難です。一般に、結合する必要があるフィールドは 0.1 より大きい必要があります。つまり、平均 10 レコードがスキャンされます。
4.計算では、from_unixtime(create_time ) = '2015-08-14' のように列を「クリーン」に保つ必要があります。その理由は非常に単純です。ただし、データテーブルを取得する場合、すべての要素に関数を適用して比較する必要があるのは明らかです。したがって、ステートメントは create_time = unix_timestamp(‘2015-08-14’) として記述する必要があります。
5. インデックスをできるだけ拡張し、新しいインデックスを作成しないでください。たとえば、テーブル内に a のインデックスがすでに存在し、(a, b) のインデックスを追加したい場合は、元のインデックスを変更するだけで済みます。
6. order by 句または group by 句で、インデックスによって並べ替える場合、インデックス列の順序は、order by 句または group by 句の順序、およびすべての列の並べ替え方向 (逆) と一致している必要があります。 order または forward order) order) は同じです。クエリが複数のテーブルに関連付けられている場合、order by 句によって参照されるフィールドがすべて最初のテーブルからのものである場合にのみ、インデックスを使用して order by または group を並べ替えることができます。 by ステートメントにはクエリ ステートメントと同じ制限があります。インデックスの左端のプレフィックスの原則を満たす必要があります。そうでない場合、mysql はソート操作を実行し、ソートにインデックスを使用できません (order by または group が使用できない場合があります)。 by 句は左端の接頭辞の原則を満たしていません。つまり、そのリーダーが定数の場合、where または join でこれらの列の定数を指定すると、インデックスの不足を補うことができます)。

5. 例

ステートメント 1:

ステートメント 2:

これら 2 つのステートメントについては、2 つのインデックスを作成できます。 、debtor_agent_member_id); ステートメント 2 に対して (netting_batch_no,債務者_agent_member_id,transaction_currency) を作成します。

すべてを考慮すると、インデックスは 1 つだけで十分です。ステータスを追加する必要はありません。または、transaction_currency フィールドが配置されます。 2 つのフィールドの区別が不十分なため、インデックスに追加します。

インデックス作成の原則 2 に従って、ステートメント 1 はこのインデックスに移動できます。
インデックス作成の原則 1 に従って、ステートメント 2 もこのインデックスに移動できます。インデックスを作成しすぎると、データベースのメモリやディスクの消費が増加し、インデックスの作成の原則に従う必要があります
。
上記は、innodb を例として挙げたインデックス作成の原則です。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。