More than 1 year has passed since last update.

【MySQL】複合インデックスを設定してみる

Posted at 2022-08-16

はじめに

上記の記事で単一インデックスを作成して速度が80倍になったことを確認しました。
次に複合インデックス作成してみます。

単一インデックスは1つのカラムを用いて、検索の速度を向上させることが目的でしたが、複数インデックスは複数のカラムを組み合わせて1つのインデックスを作成します。

兎にも角にもやってみます。データは上記のQiitaの記事で準備したsampleテーブルをそのまま使います。データのイメージは下記のようになります。

また、複数インデックスを作成するときのクエリは下記のようになります。

CREATE INDEX インデックス名 ON データベース名 (カラム名1, カラム名2, カラム名3...);

こちらを用いてsampleテーブルにindex_sampleというインデックス名でpoint, type, flagカラムの順番で作成します↓

CREATE INDEX index_sample ON sample (point, type, flag);

インデックスが作成されていることを確認できました↓

下記のような検索のクエリを投げて、インデックスの効果を確認します。

SELECT * FROM sample WHERE point = 99;

◆速度調査
作成前と作成後の速度はそれぞれ同じクエリで検索したときの5回の平均速度、速度比は作成後/作成前を示します。(半端な値は適度に丸めています)

パターン	indexの効果	作成前(ms)	作成後(ms)	速度比
WHERE point	○	15.4	5.2	3.0
WHERE point AND type	○	53.2	5.5	9.7
WHERE point AND flag	○	45.3	5.6	8.1
WHERE point AND type AND flag	○	51.9	7.4	8.1
WHERE type	×	5.1	5.2	1.0
WHERE type AND flag	×	6.5	5.6	1.2
WHERE type AND point	○	47.4	4.5	10.5
WHERE flag	×	5.1	6.1	0.8

複合インデックスを作成するときは指定する順番が重要である
pointカラムを使用して検索を行なっている時はインデックスが効いていますが、pointカラムを使用しない場合はインデックスが効いていません。

CREATE INDEX index_sample ON sample (point, type, flag);

上記のようなクエリでインデックスを作成しているためpointカラムを使わないと上手くインデックスが使えません。

WHERE type AND pointではインデックスが使えているように、WHERE句内の順番が異なっていてもよしなにインデックスを使ってくれる。ただし、type, pointの順でインデックスを設定した方が実際は早くなるらしい
1つの絞り込み条件で検索するのであれば、複合インデックスより単一インデックスの方が早い(こちらの記事で調査した単一インデックスの速度より明らか)

パターン	インデックス作成前(ms)	単一インデックス作成後(ms)	複合インデックス作成後(ms)
WHERE point	19.2	0.24	5.2

複合インデックスを上手く設定する際には、実際に発行されるSQLを踏まえる必要があります。次回書く記事ではカーディナリティという特定の列に含まれるデータの一意性の指標に関して書いていきます。
下記の記事が大変参考になりますので、よろしければmm