#MySQLシリーズ-高性能インデックスの作成

索引の分類
インデックスはB-Treeインデックス、ハッシュインデックス、空間インデックス、全文インデックス、その他のインデックスに分けられます.
MySQLでは、B-Treeインデックスが最もよく使われています(つまり、よく言われるB+ツリーインデックスですが、ここではB-Treeインデックスは分類にすぎません)
インデックスの利点

インデックスは、サーバがスキャンする必要があるデータ量を大幅に削減します.

インデックスは、サーバがソートおよびテンポラリ・テーブル

を回避するのに役立ちます.

インデックスは、ランダムI/Oを順次I/O

にすることができる.
高性能インデックスの作成
次に、適切で高性能なインデックスを作成してインデックス効率を向上させる方法について、状況に応じて説明します.

独立列

クエリのカラムが独立していない場合、MySQLはインデックスを使用しません.
独立したカラムとは、クエリ内のカラムが式の一部ではないことを意味します.独立している場合は、次のようになります.

select * from t_user where id + 1 = 9;

上記のクエリ文では、クエリ内のカラムidは独立ではなく、式id + 1の一部であるため、インデックスは有効になりません.
インデックス失効の諸事情については、MySQLインデックス失効総括を参照

プレフィックスインデックス

長い文字列が必要な場合があります.これにより、インデックスが大きくなり、遅くなります.
最初の文字の一部をインデックスすることで、インデックスのスペースを大幅に節約し、インデックスの効率を向上させることができます.
もちろん、選択した部分の文字が適切でないとフィルタリングの効果が悪くなるので、分割度の高い接頭辞を選択してインデックスを作成する必要があります.
接頭辞インデックスの作成スクリプトは次のとおりです.

alter table t_user add key (address(10));

このスクリプトは、アドレスの最初の10文字でインデックスを作成することを示します.

マルチカラムインデックス

多くの人は、複数のカラムのインデックスを理解していないか、複数のカラムに独立したインデックスを作成したり、誤った順序で複数のカラムのインデックスを作成したりします.
複数のカラムに独立したインデックスを作成するのは、一般的にMySQLのパフォーマンスを向上させることはできません.where条件の場合、複数のカラムに組み合わせインデックスを作成するのが正しい方法です.
複数のカラムインデックスを使用すると、クエリ条件は左前の接頭辞の原則を満たす必要があります.すなわち、where条件の順序は、結合インデックスフィールドの順序と一致します.
たとえば、フィールド1とフィールド2に結合インデックスを作成しますが、クエリー条件はフィールド2のみで、インデックスを使用してクエリーを行うことはありません.

インデックス

を上書き
インデックスは確かにデータを検索する効率的な方法ですが、MySQLはインデックスを使用してカラムのデータを直接取得することができ、データ行を読み込む必要がなくなります.
インデックスにクエリーが必要なすべてのフィールドの値が含まれている場合は、 と呼ばれます.
たとえば、ユーザーのロールをクエリーすると、1人のユーザーには複数のロールがあります.通常、ユーザーIDとロールIDにインデックスを作成します.これにより、ユーザーのロールIDリストをクエリーするときに、インデックスで直接返すことができ、テーブルに戻る必要はありません.

インデックスによるソート

インデックスの順序がORDER BY句の順序と完全に一致し、すべての列のソート方向が同じである場合、MySQLはインデックスを使用して結果をソートできます.
たとえば、作成時間に基づいてソートする必要がある場合は、作成時間フィールドにインデックスを作成すると、ソートクエリーを行うときに効率的になります.
ORDER BY句がインデックスの最左接頭辞の要件を満たしていない場合、インデックスを使用してソートすることはできません.
もちろん、特別な場合、ORDER BY句がインデックスの2番目のフィールドから始まる場合、インデックスの1番目のフィールドは定数として指定され、クエリー・ソートにも使用できます.

分割度の高いインデックス

を作成
たとえば、ユーザーの性別は、性別の値が一般的に であることを知っています.つまり、2つの値で重複率が高く、性別フィールドにインデックスを作成すると、インデックスの効率が低下します.
たとえば、ユーザーID番号は、個人のID番号が異なり、ID番号フィールドにインデックスを作成すると、区別度が非常に高く、ID番号によるインデックスの効率が高くなります.
まとめ
これらのインデックスを利用したクエリーを選択し、作成するには、特定のビジネス状況に応じて決める必要があります.万能な方法はありません.しかし、一般的には次の原則を覚えておく必要があります.

単行アクセスは遅いです.サーバがストレージからデータブロックを読み出すのは、データのローを取得するためだけである場合、多くの作業が無駄になります.読み出したデータブロックには、できるだけ多くのデータ行が含まれていることが望ましい.

は、範囲データに順次アクセスするのが速い.第一に、シーケンスI/Oは複数回のディスクトラッキングを必要とせず、ランダムI/Oよりずっと速い.第二に、サーバがデータを順番に読み取ることができれば、ソート操作を追加する必要はありません.

インデックスがクエリーを上書きするのは速いです.インデックスにクエリーに必要なすべてのカラムが含まれている場合、ストレージエンジンはテーブルに戻ってデータ行を検索する必要はありません.