MySQL インデックスの効率的な使用ガイド

序文

ほとんどの方がMySQLとインデックスを使用したことがあると思いますが、適切なインデックスを作成する方法をご存知ですか?データ量が少ない場合、不適切なインデックスはパフォーマンスに大きな影響を及ぼしませんが、データが徐々に増加すると、パフォーマンスが急激に低下します。

この記事はMySQLのインデックスの概要です。誤りがあればコメントして指摘してください。

インデックスの基本

辞書で単語を調べる手順は誰でも知っています。まず、索引ページで単語のページ番号を見つけ、対応するページ番号に移動して単語の情報を確認します。 MySQL のインデックス作成方法もこれに似ています。まず、インデックス内の対応する値を見つけ、次に一致するインデックス レコードに基づいて対応するデータ行を見つけます。次の SQL ステートメントがある場合:

コード='2333' の学生から * を選択

コード列にインデックスが作成されると、MySQL はインデックスを使用して値 '2333' を持つ行を検索し、その行のすべてのデータを読み取って返します。

インデックスタイプ

Bツリーインデックス

(B ツリーまたは B ツリーのいずれか)、インデックス タイプの大部分は B ツリー (または B ツリーのバリアント) であり、通常はこのタイプのインデックスを使用します。 MySQL の MyISAM ストレージ エンジンは B-tree を使用し、InnoDB は B+Tree を使用します。B-tree と B+tree の違いについては、Baidu で検索してください。

ツリー構造のインデックスは、データへのアクセスを高速化できます。ストレージ エンジンは、必要なデータを取得するためにテーブル全体をスキャンする必要がなくなりました。代わりに、ツリーのルート ノードからバイナリ検索を実行します。ご存知のように、バイナリ検索の速度は非常に速いため、インデックスを使用するとクエリ速度を大幅に向上できます。 B-Tree は次のタイプのクエリをサポートします。

学生テーブルには、名前、年齢、体重の複数列インデックスのみがあると仮定します。次のクエリはすべてこのインデックスを使用できます。

• 完全な価値一致

そして、インデックス列内のすべての列が一致します。たとえば、name='abc' および age=12 をクエリするには、ここでは最初の列と 2 番目の列が使用されます。

• 左端の列を一致させる

インデックスの最初の部分のみが使用されます。たとえば、name='ggg' のクエリではインデックスの最初の列のみが使用され、name='ggg' および age=12 のクエリではインデックスの 1 列目と 2 列目が使用されます。

• 一致する列プレフィックス

列の先頭部分のみを一致させることもできます。たとえば、名前が g で始まるレコードをクエリするには、「g%」のようなクエリ名を使用します。ここでは最初の列が使用されます

• 一致する範囲の値

name > 'abc' および name < 'bcd' を照会するなど、範囲値を一致させるために使用できます。

• 列と範囲が完全に一致し、別の列と一致する

name='abc' かつ age > 12 を照会するなど、複数の列を一致させるために使用されます。

一般的に、B ツリー インデックスは、左端のプレフィックスに基づく検索に適していることがわかります。つまり、クエリ フィールドの順序はインデックス フィールドの順序と同じで、最初のインデックス フィールドから始まる必要があります。たとえば、インデックスを使用して、名前、名前と年齢、名前と年齢と体重を照会できますが、年齢、年齢と名前を照会することはできません。

ハッシュインデックス

ハッシュ インデックスはハッシュ テーブルに基づいて実装され、インデックスのすべての列が完全に一致する場合にのみ有効になります。 MySQL では、メモリ エンジンのみがハッシュ インデックスを明示的にサポートしており、これがデフォルトのインデックスでもあります。

InnoDB はハッシュ インデックスを作成できませんが、アダプティブ ハッシュ インデックスと呼ばれる機能があります。特定のインデックス値が頻繁に使用される場合、エンジンは B-Tree インデックスに基づいてメモリ内に別のハッシュ インデックスを作成するため、B-Tree インデックスにもハッシュ インデックスのいくつかの利点があります。この機能は完全に自動的な内部動作であるため、手動で制御または構成することはできません。

高性能インデックス戦略

以下に、一般的なインデックス作成戦略をいくつか示します。

独立した列

これは非常に簡単です。クエリ内の列が独立していない場合、インデックスは使用できません。例:

年齢+1=12の学生から*を選択

age 列にインデックスがあっても、上記のクエリ ステートメントではそのインデックスを使用できません。

プレフィックスインデックスとインデックスの選択性

非常に長い文字列列にインデックスを付ける必要がある場合、インデックスを直接作成すると、インデックスがより多くのスペースを占め、速度が低下します。最適化戦略の 1 つは、ハッシュ インデックスをシミュレートすることです。つまり、列のハッシュ値を計算し、ハッシュ値列にインデックスを作成します。

別の方法は、プレフィックス インデックスを作成することです。このフィールドの先頭の文字のみがインデックス化されます。これにより、占有スペースが大幅に削減され、インデックス作成速度が大幅に向上します。しかし、これには次のような欠点もあります。

• インデックスの選択性が低下します。複数の文字列に同じプレフィックスがある場合、それらを区別することができず、文字列の比較が必要になります。
• Order by と group by はサポートされていません。理由は明らかです。一部の文字のみがインデックス化されており、完全に区別できないためです。

ここで重要なのは、適切にインデックスを作成する文字数を決定することです。過度の長さを避け、十分なインデックス選択性を確保する必要があります。インデックス文字の数を決定するには、次の 2 つの方法があります。

インデックス フィールド プレフィックス データは均等に分散されます。つまり、インデックス文字で始まる文字列の数は均等に分散されます。たとえば、名前フィールドの最初の 3 文字にインデックスを付けると、次の結果が妥当になります (上位 8 文字のみが取得されます)。

各列のデータが比較的大きい場合は、識別力が十分に高くないことを意味し、プレフィックスの選択性が列全体のインデックス可能性に近づくまで、つまり、先行するデータをできるだけ小さくするまで、インデックス文字の数を増やす必要があります。

完全な列の選択性を計算し、プレフィックスの選択性を完全な列の選択性に近づけます。次のステートメントは、完全な列選択性を計算します。

-- 異なる文字列の数/合計数は、完全な列選択 select count(distinct name)/count(*) from person;

次のステートメントは、インデックスの最初の 3 つのフィールドの選択性を計算します。

-- 最初の 3 文字が異なる文字列データ/合計データ select count(distincy left(city,3))/count(*) from person

選択性が完全な列選択性に近づくまでインデックス文字の数を増やし続けます。インデックス文字の数をさらに増やしても、データの選択性は大幅に向上しません。

作成方法

-- 最適な長さは4であると仮定する
テーブル person を変更し、キー (name(4)) を追加します。

複数列インデックス

多くの人が誤解していますが、クエリで複数のフィールドと「and」クエリを使用する場合、各フィールドにインデックスを付けることで効率を最大化できるのではないでしょうか。そうではありません。MySQL はインデックス検索にフィールドの 1 つだけを選択します。この場合、複数のインデックス フィールドを使用できるように、複数列インデックス (結合インデックスとも呼ばれます) を作成する必要があります。インデックス列の順序はクエリの順序と一致している必要があることに注意してください。

「インデックス マージ」戦略はバージョン 5.0 以降で導入されました。ある程度、次のクエリのように、複数の単一列インデックスを使用することもできます。

-- MySQL は、名前と年齢のインデックスを使用してデータを検索し、それらをマージします -- and を使用すると、データを検索し、比較して共通部分を取得します select * from person where name = "bob" or age=12

ただし、これはお勧めできません。and または or 条件が多すぎると、アルゴリズムのキャッシュ、ソート、およびマージ操作で CPU とメモリが大量に消費されます。

適切なインデックス列の順序を選択する

複数列の B ツリー インデックスでは、インデックス列の順序は、インデックスが最初に一番左の列でソートされ、次に 2 番目の列でソートされることを意味します。優れた複数列インデックスのインデックス作成では、最も選択性の高いインデックスを最初に配置し、順番に下位に配置する必要があります。これにより、インデックス作成が容易になります。選択的計算方法の検出: プレフィックス インデックス セクション。

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

クラスター化インデックスは、独立したインデックス タイプではなく、データ ストレージ方法です。具体的な詳細は、その実装によって異なります。

InnoDB のクラスター化インデックスは、実際にはインデックス値とデータ行を同じ構造で格納します。データの行を同時に 2 つの異なる場所に配置することはできないため、テーブルにはクラスター化インデックスを 1 つだけ含めることができます。 InnoDB のクラスター化インデックス列は「主キー列」と呼ばれます。

主キーが定義されていない場合、InnoDB は代わりに一意の空でないインデックスを選択します。そのようなインデックスがない場合、InnoDB はクラスター化インデックスとして機能する主キーを暗黙的に定義します。

クラスター化インデックスの主な利点は、関連するデータを一緒に保存し、ディスク IO を削減し、クエリの効率を向上できることです。しかし、欠点もあります:

• 挿入順序は挿入順序に大きく依存します。主キーの順序で挿入するのが最も速い方法です。そうしないと、ページ分割の問題が発生したり、ディスク領域を多く占有したり、スキャン速度が低下したりする可能性があります。 OPTIMIZE TABLE によってテーブルを再編成できます。
• クラスター化インデックス列の更新は、インデックス値が変更されると行データがインデックスとともに新しい場所に移動するため、コストがかかります。
• セカンダリ インデックス (非クラスター化インデックス) を使用して行データにアクセスするには、2 回のインデックス検索が必要です。これは、セカンダリ インデックスのリーフ ノードには行データの物理的な場所ではなく、行の主キー値が格納され、その後、主キー値を使用してクラスター化インデックスから行データが取得されるためです。

カバーインデックス

簡単に言えば、インデックスはクエリを実行する必要がある列フィールドをカバーするため、クラスター化インデックスでのセカンダリ検索に主キーを使用する必要はなく、必要なデータはセカンダリ インデックスで取得できます。

InnoDB インデックスはリーフ ノードにインデックス値を格納するため、クエリ対象のすべてのフィールドをインデックスに含め、このインデックスを使用すると、クエリ速度が大幅に向上します。たとえば、次のクエリ:

-- 名前がインデックス化されている場合、二次検索なしでインデックスのリーフノードから名前の値を直接取得します。 select name from person where name = 'abc'
-- `name,age` 集計インデックスがある場合、二次検索なしでデータが直接返されます。select name,age from person where name='abc' and age=12

インデックスを使用したソート

MySQL のソート操作では、インデックスも使用できます。インデックスの列の順序が ORDER BY の順序とまったく同じであり、すべての列のソート方法 (昇順または降順) も同じである場合にのみ、インデックスをソートに使用できます。注: ソートされたフィールドの数は、対応するインデックス フィールドの数より少なくてもかまいませんが、順序は一貫している必要があります。次のように：

-- (名前、年齢、性別) の結合インデックスがあると仮定します -- インデックスを使用して、名前の降順、年齢の降順で並べ替えることができます
選択...名前降順、年齢降順、性別降順で並び替え
-- 並べ替えは許可されていません。select ... order by name desc,sex desc
選択...名前の降順、年齢の昇順で並べ替え

仕上げる

この記事は MySQL 5.5 に基づいています。新しいバージョンでは戦略が異なる場合があります。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。