さまざまな種類のMySQLインデックス

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

インデックスは、データベース ストレージ エンジンが指定されたデータをすばやく検索するために使用するデータ構造です。

新華辞典を例えに挙げると、新華辞典の各文字の詳細な説明がデータベース内のテーブルのレコードだとすると、部首やピンインでソートされたディレクトリはインデックスであり、特定の文字の詳細な説明の場所をすばやく見つけることができます。

MySQL では、ストレージ エンジンも同様の方法を使用し、最初にインデックス内の対応する値を見つけ、次に一致するインデックス値に基づいて対応するテーブル内のレコードの場所を見つけます。

面接で指標が尋ねられるのはなぜですか?

面接でインデックスについてよく質問されるのは、インデックスがデータベースのパフォーマンス向上の鍵であり、クエリの最適化の最も効果的な手段だからです。インデックスを使用すると、クエリのパフォーマンスを簡単に数桁向上できます。

ただし、不適切なインデックスはクエリのパフォーマンスにも影響します。テーブル内のデータ量が増えると、インデックスがパフォーマンスに与える影響も大きくなります。データ量が少なく複雑度が低い場合、不良インデックスがパフォーマンスに与える影響は明らかではないかもしれませんが、データ量が徐々に増加すると、パフォーマンスが急激に低下します。

インデックスの種類

前回の紹介の後、本題に入り、MySQL でサポートされているインデックスの種類と、その原則と使用方法について学習しましょう。

異なるタイプのインデックスを使用すると、さまざまなシナリオでより優れたパフォーマンスを実現できます。 MySQL では、インデックスはサーバー レベルではなく、ストレージ エンジン レベルで実装されます。ご存知のとおり、MySQL は複数のタイプのストレージ エンジンをサポートしています。したがって、異なるストレージ エンジンでのインデックスの実装は同じではなく、すべての種類のインデックスがすべてのストレージ エンジンでサポートされているわけではありません。複数のストレージ エンジンが同じ種類のインデックスをサポートしている場合でも、その基礎となる実装は異なる場合があります。

Bツリーインデックス

B-Tree インデックスは、ほとんどの MySQL ストレージ エンジンでサポートされています。インデックスについて説明するときに、タイプが具体的に示されていない場合は、おそらく B-Tree インデックスを指していると考えられます。 MySQL は create table やその他のステートメントでこのキーワードを使用するため、B-Tree という用語を使用します。

ただし、ストレージ エンジンが異なれば、最下層で使用されるデータ構造とアルゴリズムも異なります。たとえば、InnoDB ストレージ エンジンは内部で B+Tree 構造を使用しますが、NDB クラスター ストレージ エンジンは内部で T-Tree 構造を使用します。ストレージ エンジンによって B-Tree インデックスの使用方法が異なり、パフォーマンスも異なる場合があります。たとえば、InnoDB インデックスは元のデータ形式を格納しますが、MyISAM ストレージ エンジンはプレフィックス圧縮テクノロジを使用してインデックスを小さくします。InnoDB インデックスの行にはデータ行の主キー参照が格納されますが、MyISAM ストレージ エンジン インデックスの行にはデータ行の物理的な場所が格納されます。

Bツリーインデックスの原理

B ツリー インデックスを使用すると、テーブル全体をスキャンしなくても必要なデータをすばやく取得できるため、データへのアクセスを高速化できます。では、B ツリー インデックスはこれをどのように実行するのでしょうか?簡単な例を通して、InnoDB の B-Tree インデックスがどのように機能するかを見てみましょう。

テーブル `om_address` を作成します (
 `province_name` varchar(255) NOT NULL COMMENT '省',
 `city_name` varchar(255) NOT NULL COMMENT '市',
 `district_name` varchar(255) NOT NULL COMMENT 'District',
 `detailed_address` varchar(255) NULL デフォルト NULL コメント '詳細な住所',
 インデックス `index_province_city_district`(`province_name`, `city_name`, `district_name`) BTREE を使用
)エンジン = InnoDB;

このテーブルには、州、市、地区、詳細な住所を表す 4 つのフィールドと、州、市、地区の 3 つのフィールドを含む B ツリー インデックスがあります。インデックスのすべての値は順番に格納されるため、つまり、ノードの左のサブツリーは現在のノードよりも小さく、ノードの右のサブツリーは現在のノードよりも大きくなります。次に、データをクエリするときに、インデックスのルート ノードから検索を開始し、対応するインデックス値が見つかるまで、またはまったく見つからなくなるまで、現在のノードのインデックス値に従ってサブツリーを検索します。

Bツリーインデックスの使用

B-Tree インデックスの特性に応じて、完全な値の一致、値の範囲の一致、および左端のプレフィックスの一致に使用できます。

• 完全な値の一致とは、インデックス内のすべてのフィールドを一致させることを指します。たとえば、黒龍江省ハルビン市南港区のデータを照会します。
• 値範囲の一致とは、インデックス内の特定の範囲のフィールドを一致させることを指しますが、前のフィールドは完全に一致する必要があります。たとえば、最初のフィールドである Province_name は州名に完全に一致し、2 番目のフィールドである city_name は都市名の範囲と一致します。
• 左端のプレフィックス一致は、インデックス内のフィールドの特定の開始部分の一致を指しますが、前のフィールドの完全な一致を満たす必要があります。たとえば、最初のフィールドのprovince_nameは省の名前であるInner Mongoliaであり、2番目のフィールドのcity_nameは「呼」で始まる都市の名前です。
  

ハッシュインデックス

ハッシュ インデックスはハッシュ テーブルに基づいて実装され、インデックスによって指し示されるデータを正確に一致させるために使用されます。ストレージ エンジンは、各データ行のすべてのインデックス フィールドのハッシュ コードを計算します。ハッシュ コードは比較的小さな値であり、異なるデータに対して計算されるハッシュ コードは通常異なります。ハッシュ インデックスには、ハッシュ コードとデータ行へのポインターが格納されます。

MySQL では、メモリ ストレージ エンジンのみがハッシュ インデックスをサポートしています。これは、メモリ ストレージ エンジンのデフォルトのインデックス タイプでもあります。さらに、ハッシュ インデックスは、InnoDB ストレージ エンジンでも使用され、アダプティブ ハッシュ インデックスと呼ばれます。特定のインデックスが非常に頻繁に使用される場合、InnoDB ストレージ エンジンはメモリ内に B ツリー インデックスに基づいてハッシュ インデックスを作成するため、B ツリー インデックスにはハッシュ検索が高速であるという利点もあります。

ハッシュ インデックスは対応するデータのハッシュ値のみを保存すればよいため、インデックス構造は非常にコンパクトで、占有スペースが少なく、クエリ速度も非常に高速です。ただし、ハッシュ インデックスは完全な値の等価クエリのみをサポートし、インデックス フィールド範囲の一致や部分的なインデックス フィールドの一致はサポートできません。

空間データインデックス

空間データ インデックス (R ツリー) は、主に地理データを格納するために使用されます。すべての次元のデータをインデックス化し、クエリ中に複合クエリに任意の次元を効果的に使用できます。 現在、MyISAM ストレージ エンジンは空間データのインデックス作成をサポートしていますが、データを維持するには MySQL の GIS 関連関数を使用する必要があります。

MySQL では、空間インデックスは GEOMETRY、POINT、LINESTRING などの空間データ型に対してのみ作成できます。

全文索引

全文インデックスは、これまで紹介したインデックスのようにインデックス内の値を直接比較するのではなく、検索対象テキスト内のキーワードを直接比較します。単純な where 条件一致ではなく、検索エンジンが行うことと似ています。

同じフィールドで、競合することなくフルテキスト インデックスと B ツリー インデックスを同時に作成できます。フルテキスト インデックスは、通常の where 条件操作ではなく、一致操作と反対操作に適用できます。 MySQL では、フルテキスト インデックスは CHAR、VARCHAR、または TEXT 型のフィールドにのみ作成できます。

要約する

インデックスは、データベース ストレージ エンジンが特定のデータをすばやく検索するために使用するデータ構造です。インデックスには、B ツリー インデックス、ハッシュ インデックス、空間データ インデックス、およびフルテキスト インデックスが含まれます。最もよく使用されるのは B ツリー インデックスです。InnoDB ストレージ エンジンは、内部的に B+ ツリー構造を使用します。ハッシュ インデックスはハッシュ テーブルに基づいており、インデックスが指すデータを正確に一致させるために使用されます。空間データ インデックスは、すべての次元のデータをインデックス化し、クエリ中に任意の次元を組み合わせて効果的に使用できます。フルテキスト インデックスは、検索エンジンと同様に、検索対象テキスト内のキーワードを直接比較します。

上記は、MySQL インデックスのさまざまなタイプについての詳細な紹介です。MySQL インデックス タイプの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。