MySQLインデックスを最適化する方法

1. MySQL のインデックスの使用方法

インデックスは、特定の列の値を持つ行をすばやく見つけるために使用されます。インデックスがない場合、MySQL は最初の行から開始し、テーブル全体を走査して関連する行を見つける必要があります。テーブルが大きくなればなるほど、費用も高くなります。テーブルの関連列にインデックスがある場合、MySQL はすべてのデータを調べなくても、データファイルの途中のどこを調べるべきかをすぐに判断できます。これは、各行を順番に読み取るよりもはるかに高速です。

ほとんどの MySQL インデックス (PRIMARY KEY、UNIQUE、INDEX、FULLTEXT) は B ツリーに格納されます。例外: 空間データ型のインデックスは R ツリーを使用します。MEMORY テーブルはハッシュインデックスもサポートします。 InnoDB は FULLTEXT インデックスに逆リストを使用します。

MySQL はインデックスを使用して次のことを行います。

WHERE句に一致する行を素早く見つける
複数のインデックスから選択できる場合、MySQL は通常、行数が最も少ないインデックス (最も選択性の高いインデックス) を使用します。
複数列インデックス (「複合インデックス」または「結合インデックス」とも呼ばれます) を使用すると、オプティマイザーはインデックスの左端のプレフィックスを使用して行を見つけることができます。たとえば、(col1、col2、col3) に 3 列のインデックスがある場合、(col1)、(col1、col2)、(col1、col2、col3) に対してインデックス検索機能が使用できます。
結合クエリを使用して他のテーブルから行を取得する場合、列が同じタイプとサイズで宣言されていれば、MySQL は列のインデックスをより効率的に使用できます。この場合、VARCHAR と CHAR は同じサイズとして宣言されていれば同じものとみなされます。たとえば、VARCHAR(10)とCHAR(10)は同じサイズですが、VARCHAR(10)とCHAR(15)はサイズが異なります。
非バイナリ文字列列間の比較では、両方の列で同じ文字セットを使用する必要があります。
使用可能なインデックスの左端のプレフィックスでソートまたはグループ化が行われる場合 (たとえば、ORDER BY key_part1, key_part2)、テーブルはソートまたはグループ化されます。すべてのキー部分の後に DESC が続く場合、キーは逆の順序で読み取られます。
場合によっては、MySQL はインデックスを使用して ORDER BY 句を満たし、ファイルソート操作の実行に伴う余分なソートを回避できます。
場合によっては、データ行をクエリせずに値を取得するようにクエリを最適化できます。（クエリに必要なすべての結果を提供するインデックスはカバーインデックスと呼ばれます）クエリがいくつかのインデックスに含まれるテーブルの列のみを使用する場合、選択された値はインデックスツリーから取得され、速度が向上します。

最後に、小さなテーブルに対するクエリではインデックスはそれほど重要ではありません。クエリがほとんどの行にアクセスする必要がある場合、インデックスを処理するよりも順次読み取りの方が高速です。

2. テーブル全体のスキャンを避ける

MySQL がクエリを解決するためにフルテーブルスキャンを使用する場合、EXPLAIN からの出力では、type 列に ALL が表示されます。これは通常、次の場合に発生します:

テーブルが非常に小さいため、完全なテーブルスキャンはインデックスシークよりもはるかに高速です。これは、行数が 10 未満で行の長さが短いテーブルでよく見られます。
インデックス列は ON 句または WHERE 句では使用されません。
インデックス列を、MySQL が (インデックスツリーに基づいて) すでに計算した定数値と比較すると、テーブルの大部分がカバーされ、テーブルスキャンが高速化されます。
別の列を介して、カーディナリティが低いキー (多くの行がキー値と一致する) を使用しています。この場合、MySQL は、そのキーを使用することで、潜在的に多くのキー検索を実行でき、テーブルスキャンが高速化されると想定します。

小さなテーブルの場合、テーブルスキャンは通常適切であり、パフォーマンスへの影響はごくわずかです。

大きなテーブルの場合、オプティマイザーが誤ってテーブルスキャンを選択するのを回避するために、次の手法を試すことができます。

ANALYZE TABLE tbl_nameを使用してキー分布を更新します
FORCE INDEX を使用して、特定のインデックスを使用する場合と比較して、テーブルスキャンのコストが非常に高いことを MySQL に伝えます。

3. 列インデックス

B ツリーデータ構造により、インデックスは WHERE 句内の =、>、≤、BETWEEN、IN などの演算子に対応する特定の値、値のセット、または値の範囲をすばやく見つけることができます。

各ストレージエンジンは、テーブルあたりのインデックスの最大数と最大インデックス長を定義します。すべてのストレージエンジンはテーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。

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

列の最初の N 文字のみを使用してインデックスを作成するには、col_name(N) を使用します。 InnoDB テーブルでは、プレフィックスの最大長は 767 バイトです。

全文索引

FULLTEXT インデックスは全文検索に使用されます。 FULLTEXT インデックスは、InnoDB および MyISAM ストレージエンジンでのみサポートされ、CHAR、VARCHAR、および TEXT 列に対してのみサポートされます。インデックス作成は常に列全体に対して行われ、列プレフィックスインデックスはサポートされません。

空間インデックス

空間オブジェクトの位置や形状、または空間オブジェクト間の特定の空間関係に基づいて、特定の順序で配置されたデータ構造を指します。

MEMORYストレージエンジンのインデックス

デフォルトでは、MEMORY ストレージエンジンは HASH インデックスを使用しますが、BTREE インデックスもサポートします。

4. 複数列インデックス

MySQL は複合インデックス (つまり、複数の列のインデックス) を作成できます。インデックスには最大 16 列を含めることができます。

次のように定義された表現があるとします。

CREATE TABLEテスト（
  id INT NOT NULL、
  last_name CHAR(30) NOT NULL、
  first_name CHAR(30) NOT NULL、
  主キー (id)、
  INDEX idx_name (姓、名)
);

idx_name インデックスは、last_name 列と first_name 列に基づいて構築されるインデックスです。このインデックスは左端のプレフィックスと一致するため、last_name と first_name 値の組み合わせを指定するクエリ、または last_name 値のみを指定するクエリに使用できます。

したがって、idx_name インデックスは次のクエリに使用できます。

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');

SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';

ただし、idx_name インデックスは次のクエリには使用できません。

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test WHERE last_name='Jones' OR first_name='John';

次の SQL を考えてみましょう。

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

col1 と col2 に複数列のインデックスが存在する場合は、適切な行を直接フェッチできます。 col1 と col2 に別々の単一列インデックスが存在する場合、オプティマイザーはインデックスマージ最適化を使用するか、どのインデックスがより多くの行を除外する必要があるかを判断して最も制限の厳しいインデックスを見つけ、そのインデックスを使用して行をフェッチします。

テーブルに複数列のインデックスがある場合、オプティマイザーはインデックスの左端のプレフィックスを使用して行を検索できます。たとえば、3 列のインデックス (col1、col2、col3) がある場合、(col1)、(col1、col2)、(col1、col2、col3) に対してインデックス検索機能を使用できます。

列がインデックスの左端のプレフィックスを形成しない場合、MySQL はインデックスを使用して検索を実行できません。

次の SQL ステートメントを見てください。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

(col1、col2、col3) に複合インデックスがある場合、最初の 2 つのクエリのみがそれを使用します。すると、最後の 2 つのクエリでは、(col2) と (col2, col3) が (col1, col2, col3) の左端のプレフィックスではないため、インデックスを使用して検索を実行しません。

5. Bツリーインデックスとハッシュインデックスの比較

Bツリーインデックスの特性

B ツリーインデックスは、=、>、>=、<、<=、および BETWEEN 演算子を使用する式での列比較に使用できます。 LIKE の引数がワイルドカード文字で始まらない定数文字列である場合、インデックスは LIKE 比較にも使用できます。

次の句ではインデックスは使用されません。

/* LIKE 値はワイルドカード文字で始まります */
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
/* LIKE 値は定数ではありません */
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

WHERE 句内のすべての AND レベルをカバーしないインデックスは、クエリの最適化には使用されません。つまり、インデックスを使用できるようにするには、各 AND グループでインデックスのプレフィックスを使用する必要があります。

次の WHERE 句はインデックスを使用します。

... WHERE index_part1=1 かつ index_part2=2 かつ other_column=3

  /* インデックス = 1 または インデックス = 2 */
... WHERE インデックス=1 または A=10 かつ インデックス=2

  /* "index_part1='hello'" のように最適化されています */
... index_part1='hello' かつ index_part3=5 の場合

  /* index1 ではインデックスを使用できますが、index2 または index3 では使用できません */
... WHERE index1=1 AND index2=2 または index1=3 AND index3=3;

次の WHERE 句ではインデックスは使用されません。

/* index_part1 は使用されません */
... index_part2=1 かつ index_part3=2 の場合

  /* インデックスは WHERE 句の両方の部分では使用されません */
... インデックス=1 または A=10

  /* すべての行にまたがるインデックスはありません */
... index_part1=1 または index_part2=10 の場合

MySQL では、インデックスが使用可能であっても、インデックスを使用しない場合があります。これが発生する理由の 1 つは、インデックスを使用するとテーブル内の行の大部分にアクセスする必要があるとオプティマイザーが見積もっていることです。 (この場合、テーブルスキャンの方が検索回数が少ないため、高速になる可能性があります。) ただし、このようなクエリで LIMIT を使用して特定の行のみを取得する場合、MySQL では、返す行をより速く見つけることができるため、引き続きインデックスが使用されます。

ハッシュインデックスの特性

ハッシュインデックスには、先ほど説明したインデックスとは異なる特性がいくつかあります。

ハッシュインデックスは、= または <=> 演算子を使用した等価比較にのみ使用されます (ただし、非常に高速です)。値の範囲を見つけるための比較演算子には使用されません。このような単一値の検索に依存するシステムは「キー値ストア」と呼ばれます。このようなアプリケーションに MySQL を使用するには、可能な限りハッシュインデックスを使用します。
オプティマイザーはハッシュインデックスを使用して ORDER BY 操作を高速化することはできません。（ハッシュ型インデックスは、次のエントリを順番に検索するのには使用できません）
MySQLは、2つの値の間に何行あるかを概算で判断できません（範囲オプティマイザはこれを使用して、どのインデックスを使用するかを決定します）。
行の検索にはキー全体のみを使用できます。 (B ツリーインデックスの場合、キーの左端のプレフィックスを使用して行を見つけることができます。)

Bツリー

データベースのインデックス作成で広く使用されているツリーデータ構造。構造は常に整然と保たれ、完全一致 (等号演算子) と範囲 (より大きい、より小さい、BETWEEN 演算子など) の高速検索が可能になります。このようなインデックスは、InnoDB や MyISAM などのほとんどのストレージエンジンで使用できます。

B ツリーノードは多数の子を持つことができるため、各ノードが最大 2 つの子を持つことができるバイナリツリーとは異なります。

B ツリーという用語は、インデックス設計の一般的なクラスを指すために使用されます。 MySQL ストレージエンジンで使用される B ツリー構造は、従来の B ツリー設計には存在しない複雑さのため、バリアントと見なすことができます。

ハッシュインデックス

範囲演算子の代わりに等価演算子を使用するクエリ用に設計されたインデックスの種類。 MEMORY テーブルに使用できます。歴史的な理由により、ハッシュインデックスは MEMORY テーブルのデフォルトのインデックスですが、ストレージエンジンは B ツリーインデックスもサポートしており、これは一般に汎用クエリに適した選択肢です。

6. データサイズを最適化する

ディスク上のスペースを最小限にとどめるようにテーブルを設計します。これにより、ディスクに書き込まれるデータとディスクから読み取られるデータの量が削減され、大幅な改善が実現します。通常、テーブルが小さいほど、クエリ実行中にその内容を処理する際に必要なメインメモリが少なくなります。テーブルデータのスペースが削減されると、インデックスが小さくなり、処理が高速化されます。

MySQL は、さまざまなストレージエンジン (テーブルタイプ) と行形式をサポートしています。各テーブルごとに、使用するストレージとインデックス作成方法を決定できます。アプリケーションに適切なテーブル形式を選択すると、パフォーマンスが大幅に向上します。

表の列

可能な限り最も効率的な（最小の）データ型を使用します。 MySQL には、ディスク領域とメモリを節約できる特殊なタイプが多数あります。たとえば、可能であれば、より小さな整数型を使用して、より小さなテーブルを取得します。 MEDIUMINT 列は 25% 少ないスペースを使用するため、通常は INT よりも MEDIUMINT の方が適しています。
可能であれば、列を NOT NULL として宣言します。インデックスをより有効に活用し、すべての値を NULL かどうかテストするオーバーヘッドを排除することで、SQL 操作を高速化できます。また、列ごとに 1 ビットずつ、ストレージスペースも節約されます。テーブルに NULL 値が本当に必要な場合は、それを使用してください。すべての列で NULL 値を許可するデフォルト設定の使用は避けてください。

行の形式

テーブルデータを圧縮形式で保存してスペースをさらに削減するには、InnoDB テーブルを作成するときに ROW_FORMAT=COMPRESSED を指定します。

インデックス

テーブルの主キーインデックスはできる限り短くする必要があります。これにより、各行の識別が簡単かつ効率的になります。 InnoDB テーブルの場合、プライマリキー列はすべてのセカンダリインデックスエントリで繰り返されるため、セカンダリインデックスが多数ある場合は、プライマリキーを短くすると多くのスペースを節約できます。
クエリのパフォーマンスを向上させるために必要なインデックスのみを作成します。インデックスは検索には最適ですが、挿入および更新操作の速度が低下します。主に列の組み合わせを検索してテーブルにアクセスする場合は、列ごとに個別のインデックスを作成するのではなく、テーブルに単一の複合インデックスを作成します。インデックスの最初の部分は、最も頻繁に使用される列である必要があります。テーブルからクエリを実行するときに多くの列が一貫して使用される場合、インデックスをより適切に圧縮するには、インデックスの最初の列を最も頻繁に繰り返される列にする必要があります。
長い文字列の列の場合、最初の文字に一意のプレフィックスが付いている可能性が非常に高くなります。この場合、インデックス作成には MySQL プレフィックスを使用するのが最適です (PS: 最初の数文字のみがインデックス作成されます)。インデックスが短いほど、必要なディスク容量が少なくなるだけでなく、インデックスキャッシュのヒット数も増え、ディスクシークの回数も減るため、速度が速くなります。

結合

同じデータ型を持つ異なるテーブルで同一の情報を持つ列を宣言すると、対応する列に基づく結合が高速化されます。
列名はシンプルにしておくと、異なるテーブルで同じ名前を使用でき、結合クエリが簡素化されます。たとえば、customer という名前のテーブルでは、customer_name の代わりに列名 name を使用します。名前を他の SQL サーバーに移植できるようにするには、名前の長さを 18 文字以下に制限することを検討してください。

正規化

一般的に、すべてのデータを非冗長状態に保つようにしてください（データベース理論では第 3 正規形と呼ばれます）。 1 つの長い繰り返し値の代わりに一意の ID を割り当て、必要な数の小さなテーブルでそれらの ID を繰り返し、結合句で ID を参照してクエリでテーブルを結合します。

7. データ型を最適化する

数値型

行を一意に識別するには、文字列ではなく数値を使用する方が適切です。これは、大きな数値は対応する文字列よりもストレージのバイト数が少なくなるため、転送と比較が高速になり、メモリの消費も少なくなるためです。

文字と文字列の型

異なる列の値を比較する場合は、クエリ実行時の文字列変換を回避するために、可能な限り同じ文字セットと照合順序を使用してそれらの列を宣言します。
8KB未満の列値の場合は、BLOBではなくバイナリVARCHARを使用します。 GROUP BY 句と ORDER BY 句は一時テーブルを生成でき、元のテーブルに BLOB 列が含まれていない場合、これらの一時テーブルは MEMORY ストレージエンジンを使用できます。
テーブルに名前や住所などの文字列列が含まれているが、多くのクエリでこれらの列が取得されない場合は、文字列列を別のテーブルに分割し、必要に応じて外部キーを使用した結合クエリを使用することを検討してください。 MySQL は行から値を取得するときに、その行 (および場合によっては他の隣接する行) のすべての列を含むデータブロックを読み取ります。各行を小さく保ち、最もよく使用される列のみを含めると、データブロックごとにより多くの行を収めることができます。このコンパクトなテーブルにより、一般的なクエリのディスク I/O とメモリ使用量が削減されます。
ランダムに生成された値を InnoDB テーブルの主キーとして使用する場合は、現在の日付や時刻などの昇順の値をプレフィックスとして付けるのが最適です (可能な場合)。 InnoDB では、連続する主キー値が物理的に隣接して保存されている場合、それらの値をより高速に挿入および取得できます。

他の

ORDER BY および GROUP BY で使用される列に矛盾がある場合、または結合クエリの ORDER BY または GROUP BY が最初のテーブル以外のテーブルの列を使用する場合に、一時テーブルが使用されます。
MySQL にはテーブルあたり 4096 列のハード制限がありますが、特定のテーブルでは実際の最大値はそれより少なくなる場合があります。 InnoDB ではテーブルあたり 1017 列の制限があります。

上記は、MySQL インデックスを最適化する方法の詳細です。MySQL インデックスの最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: