MySQL インデックスの設計

データベース インデックスは、データベース テーブル内のデータの迅速なクエリと更新を支援する、データベース管理システム内の並べ替えられたデータ構造です。インデックスの実装には通常、B ツリーとそのバリアント B+ ツリーが使用されます。

データベース システムは、データに加えて、特定の検索アルゴリズムを満たすデータ構造も維持します。これらのデータ構造は、これらのデータ構造に高度な検索アルゴリズムを実装できるように、何らかの方法でデータを参照 (ポイント) します。このデータ構造がインデックスです。

テーブルのインデックスの設定にはコストがかかります。まず、データベースの記憶領域が増加します。次に、データの挿入と変更に時間がかかります (それに応じてインデックスも変更されるため)。

写真は、可能なインデックス作成方法を示しています。左側はデータ テーブルで、合計 2 つの列と 7 つのレコードがあります。一番左はデータ レコードの物理アドレスです (論理的に隣接するレコードがディスク上で物理的に隣接している必要はないことに注意してください)。 Col2 の検索を高速化するために、右に示すように、各ノードにインデックス キー値と、対応するデータ レコードの物理アドレスへのポインターを含めることができます。 O(log2n) の二分探索 計算量内で対応するデータが得られます。

インデックスを作成すると、システムのパフォーマンスが大幅に向上します。

まず、一意のインデックスを作成することで、データベーステーブル内のデータの各行の一意性を保証できます。

2 番目に、データの取得を大幅に高速化できます。これがインデックスを作成する主な理由でもあります。

3 番目に、テーブル間の接続を高速化できます。これは、データの参照整合性を達成する上で特に意味があります。

4 番目に、データの取得にグループ化句と並べ替え句を使用すると、クエリでのグループ化と並べ替えにかかる時間も大幅に短縮できます。

5 番目に、インデックスを使用すると、クエリ プロセス中に最適化非表示機能を使用してシステムのパフォーマンスを向上させることができます。

インデックスを追加すると多くの利点があります。テーブル内のすべての列にインデックスを作成してみてはいかがでしょうか?なぜなら、インデックスの追加には多くのデメリットもあるためです。

まず、インデックスの作成と維持には時間がかかり、データ量が増えるとこの時間も長くなります。

第 2 に、インデックスはデータ テーブルが占有するデータ スペースに加えて、一定量の物理スペースも占有する必要があります。クラスター化インデックスを確立する場合、必要なスペースはさらに大きくなります。

3 番目に、テーブル内のデータを追加、削除、変更する場合、インデックスを動的に維持する必要があるため、データのメンテナンス速度が低下します。

インデックスはデータベーステーブルの特定の列に基づいて構築されます。インデックスを作成するときは、どの列にインデックスを作成できるか、どの列にインデックスを作成できないかを考慮する必要があります。

一般的に、インデックスは次の列に作成する必要があります:

1. 頻繁に検索される列では、一意性を強化し、配置構造を整理します。テーブル内のデータの数

3. 接続でよく使用される列を作成します。これらの列は主に外部キーであり、接続を高速化できます。

4. 範囲に基づいて検索する必要がある列を作成します。インデックス。インデックスはソートされているため、指定された範囲は連続しています。

5. インデックスがソートされているため、クエリでインデックスのソートを使用できるように、頻繁にソートが必要な列にインデックスを作成します。ソートクエリ時間を高速化するため

6 . WHERE 句で頻繁に使用される列にインデックスを作成して、条件の判断を高速化します。


同様に、インデックスを作成すべきでない列もいくつかあります。一般に、インデックスを作成すべきではない列には次のような特徴があります:

まず、クエリでほとんど使用または参照されない列にはインデックスを作成しないでください。これは、これらの列がほとんど使用されないため、インデックスを作成してもしなくてもクエリ速度は向上しないためです。逆に、インデックスの追加により、システムのメンテナンス速度が低下し、必要なスペースが増加します。

第 2 に、データ値が少ない列のインデックスは増加させるべきではありません。これは、クエリ結果ではこれらの列 (人事テーブルの性別列など) の値が非常に少ないため、結果セット内のデータ行がテーブル内のデータ行の大部分を占めるためです。テーブル内で検索する必要があるデータ 行の割合が膨大です。インデックスを増やしても、検索が大幅に高速化されるわけではありません。

3 番目に、テキスト、イメージ、ビットのデータ型として定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に大きいか、値が非常に少ないためです。

4番目に、変更パフォーマンスが検索パフォーマンスよりもはるかに大きい場合、インデックスは作成されるべきではありません。修正性能と検索性能は相反するものだからである。インデックスを追加すると、検索パフォーマンスは向上しますが、変更パフォーマンスは低下します。インデックスを減らすと、変更パフォーマンスは向上しますが、検索パフォーマンスは低下します。したがって、変更パフォーマンスが検索パフォーマンスよりもはるかに優れている場合は、インデックスを作成しないでください。


データベースの機能に応じて、データベースデザイナーでは一意インデックス、主キーインデックス、クラスタードインデックスの3種類のインデックスを作成できます。

ユニークなインデックス

一意のインデックスとは、2 つの行が同じインデックス値を持つことができないインデックスです。ほとんどのデータベースでは、既存のデータに重複するキー値がある場合、新しく作成した一意のインデックスをテーブルに保存することはできません。データベースは、テーブル内に重複するキー値を作成する新しいデータの追加を妨げる場合もあります。たとえば、従業員テーブル内の従業員の姓 (lname) に一意のインデックスが作成されている場合、2 人の従業員が同じ姓を持つことはできません。

主キー インデックス

データベース テーブルには、多くの場合、テーブル内の各行を一意に識別する値を持つ 1 つの列または列の組み合わせがあります。この列はテーブルの主キーと呼ばれます。 データベース ダイアグラム内のテーブルの主キーを定義すると、特定のタイプの一意のインデックスである主キー インデックスが自動的に作成されます。インデックスでは、主キーの各値が一意である必要があります。また、クエリで主キー インデックスが使用される場合、データへの高速アクセスも可能になります。

クラスター化インデックス

クラスター化インデックスでは、テーブル内の行の物理的な順序は、キー値の論理 (インデックス) 順序と同じです。テーブルにはクラスター化インデックスを 1 つだけ含めることができます。インデックスがクラスター化インデックスではない場合、テーブル内の行の物理的な順序はキー値の論理的な順序と一致しません。一般に、クラスター化インデックスは非クラスター化インデックスよりも高速なデータ アクセスを提供します。

上記は MySql インデックス設計の内容です。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) をご覧ください。