MySQL インデックスの最適化

プレフィックスインデックスの最適化#

文字列フィールドの最初の数文字にインデックスを作成します。

制約事項：

order by はプレフィックスインデックスを使用できません
[プレフィックスインデックス] を [カバリングインデックス] として使用することはできません

カバリングインデックスの最適化#

意味：SQL の select 文で指定されたすべてのフィールドが、セカンダリインデックスの B + ツリーのリーフノードから見つかり、クラスタインデックスの検索を経由せずにレコードを取得できることを指します。これにより、テーブルへのアクセスが削減されます。

例えば、商品の名前と価格のみが必要な場合、[商品 ID、名前、価格] のような複合インデックスを作成します。このインデックスにデータが存在する場合、クラスタインデックスを再度検索する必要はありません。これにより、回表（I/O 操作の削減）が回避されます。

プライマリキーインデックスは自動増分が最適#

InnoDB では、プライマリキーインデックスはデフォルトでクラスタインデックスとして作成され、データは B + ツリーのリーフノードに格納されます（リーフノードは自動的にソートされます）。

自動増分プライマリキーを使用すると、新しいレコードを挿入するたびにデータを再配置する必要がないため、追加操作のみが必要です。

非自動増分プライマリキーを使用すると、各新しいデータの挿入時にプライマリキーのインデックス値がランダムになるため、新しいデータを挿入するたびに既存のデータページの中間に挿入される可能性があります。これにより、新しいデータの挿入には他のデータの移動が必要になる場合があり、データを 1 つのページから別のページにコピーする必要がある場合もあります。これをページの分割と呼びます。ページの分割により、パフォーマンスが低下し、大量のフラグメント化が発生し、インデックス構造が緊密でなくなり、クエリの効率が低下する可能性があります。

例えば、あるデータページには 1、3、5、9 のデータが含まれており、データページがいっぱいの状態でデータ 7 を挿入する場合、データページを 2 つに分割する必要があります：

ページの分割が発生すると、1 つのページのレコードを別のページに移動する必要があり、パフォーマンスに影響が出ます。また、ページのスペースの利用率が低下し、ストレージスペースの浪費が発生します。

一方、データが順番に挿入される場合、例えばデータ 11 を挿入する場合、新しいデータページを割り当てるだけで、ページの分割は発生しません：

プライマリキーのフィールドの長さは大きすぎないようにする必要があります。なぜなら、プライマリキーのフィールドが短いほど、セカンダリインデックスのリーフノードが小さくなるため（セカンダリインデックスのリーフノードにはプライマリキーの値が格納されます）、その結果、セカンダリインデックスが使用するスペースも小さくなるからです。

インデックス列に NOT NULL 制約を設定#

インデックスをより効果的に利用するために、インデックス列には NOT NULL 制約を設定する必要があります。その理由は 2 つあります：

インデックス列に NULL が存在すると、最適化プランナーがインデックスの選択をより複雑にし、最適化がより困難になる可能性があるためです。NULL 可能な列は、インデックス、インデックス統計、および値の比較をより複雑にします。例えば、インデックス統計を行う際には、NULL 値の行は count の対象外になります。
NULL 値は意味のない値ですが、物理的なスペースを占有します。したがって、InnoDB は、NULL を許容するフィールドが存在する場合、行の形式でNULL 値リストを格納するために少なくとも 1 バイトのスペースを使用します。以下の図の紫色の部分を参照してください：

インデックスの無効化を防ぐ#

インデックスを使用しているからといって、クエリの実行時にインデックスが使用されるわけではありません。インデックスが無効になる一般的なケースは次のとおりです：

左または左右の部分一致を使用する場合、つまり like '%xx' または like '%xx%'
where 句でインデックス列に計算、関数、型変換の操作を行う場合
複合インデックスの最左一致原則に従わない場合
where 句で where インデックス列 or 非インデックス列 のように使用する場合

実際のプロセスでは、実行計画（explain）を確認してインデックスが使用されているかどうかを判断する必要があります。以下の図は、インデックスが使用されていないフルスキャンのクエリです：

実行計画の結果：

possible_keys フィールドは使用可能なインデックスを示します
key フィールドは実際に使用されたインデックスを示します。この項目が NULL の場合、インデックスは使用されていません
key_len はインデックスの長さを示します
rows はスキャンされたデータ行数を示します
重要！！type はデータスキャンのタイプを示します。効率の低い順に並べると：
- All（フルスキャン）！！避けるべきです
- index（インデックス全体のスキャン）！！避けるべきです
- range（インデックス範囲スキャン）：少なくともこのレベルまで到達する必要があります
- ref（非一意インデックススキャン）：重複値が存在する場合
- eq_ref（一意インデックススキャン）：eq_ref は通常、複数のテーブルの結合に使用されます
- const（結果が 1 つだけの主キーまたは一意インデックススキャン）：const は定数と比較する場合に使用され、クエリの効率が向上します
Extra：
- ！！Using filesort を避ける：クエリに group by が含まれ、インデックスのソートが利用できない場合
- ！！Using temporary を避ける：一時テーブルを使用して中間結果を保存し、MySQL がクエリ結果をソートする場合。一般的には order by や group by のようなソートに使用されます
- ！！Using index を避ける：必要なデータがセカンダリインデックスの B + ツリーにすべて含まれており、データを一つずつ取得するための回表が不要な場合、つまりカバリングインデックスが使用されている場合

myEsn2E9