MySQLで複合indexのexplainを見る時、key_lenも確認する必要があった話

はじめに

こんにちは。新卒1年目の岡崎です。

MySQLで複合indexを貼るか、単体のindexを貼るのか業務で検討しました。この時は普段確認していること以外に、key_lenも確認する必要があったので、その備忘録として紹介します。また、今回の記事は、初学者向けの内容になっております。

はじめに
環境
事前準備
explain
key_len
最後に

環境

MySQLの5.7です。

事前準備

今回は例として、小説の管理システムを考えます。

テーブル定義は以下のようにしました。

explain

ここで、お知らせの一覧を取得することを考えます。ただし、公開開始日時の降順で取得することとします。

この時のSQLの例は以下です。

SELECT 
    novel_id,
    title,
    author,
    publish_start_at
    
FROM
    novel
    
WHERE 
    publish_start_at <= NOW()
    AND publish_end_at >= NOW()

ORDER BY
    publish_start_at DESC

LIMIT 0, 20
;

この時のexplainを見てみます。

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra                       |
|----|-------------|-------|------------|------|---------------|-----|---------|-----|------|----------|-----------------------------|
|  1 | SIMPLE      | novel |            | ALL  |               |     |         |     |   30 |    11.11 | Using where; Using filesort |

このexplainの結果を見ると、typeにALL、Extraにfilesortが出ているので、改善の必要があります。

なぜこのような結果になっているのか考えてみます。今回の場合、novel_idがPKとなっています。しかしこのPKを使わずに公開開始日時で並び替えをしているので、このような結果になっている可能性が高いです。したがって、公開開始日時にindexを貼るか、公開開始日時を含む複合indexを貼る必要があります。

where句、order by句に関わるカラムを使ったindexで実験をしてみようと思います。今回の候補は以下の3つとします。

公開開始日時のindex
公開開始日時・公開終了日時の複合index
公開終了日時・公開開始日時の複合index

それでは、それぞれのexplainを見ていきます。

1. 公開開始日時のindexを貼った場合のexplain

| id | select_type | table | partitions | type  | possible_keys          | key                    | key_len | ref | rows | filtered | Extra                              |
|----|-------------|-------|------------|-------|------------------------|------------------------|---------|-----|------|----------|------------------------------------|
|  1 | SIMPLE      | novel |            | range | PUBLISH_START_AT_INDEX | PUBLISH_START_AT_INDEX | 6       |     |   11 |    33.33 | Using index condition; Using where |

2. 公開開始日時・公開終了日時の複合indexを貼った場合のexplain

| id | select_type | table | partitions | type  | possible_keys                         | key                                   | key_len | ref | rows | filtered | Extra                 |
|----|-------------|-------|------------|-------|---------------------------------------|---------------------------------------|---------|-----|------|----------|-----------------------|
|  1 | SIMPLE      | novel |            | range | PUBLISH_START_AT_PUBLISH_END_AT_INDEX | PUBLISH_START_AT_PUBLISH_END_AT_INDEX | 6       |     |   11 |    33.33 | Using index condition |

3. 公開終了日時・公開開始日時の複合indexを貼った場合のexplain

| id | select_type | table | partitions | type  | possible_keys                         | key                                   | key_len | ref | rows | filtered | Extra                                 |
|----|-------------|-------|------------|-------|---------------------------------------|---------------------------------------|---------|-----|------|----------|---------------------------------------|
|  1 | SIMPLE      | novel |            | range | PUBLISH_END_AT_PUBLISH_START_AT_INDEX | PUBLISH_END_AT_PUBLISH_START_AT_INDEX | 12      |     |   20 |    33.33 | Using index condition; Using filesort |

typeを見ると、1、2、3の全てがALLではなく、rangeになりました。しかしExtraを見ると、3だけはfile sortが残ってしまっています。よって3は、1と2と比べてパフォーマンスが劣りそうなことが分かります。

それでは、1と2のどちらがいいでしょうか。

key_len

この時見なくてはいけないのは、key_lenです。

key_lenは何であるのか公式ドキュメントを見てみます。

key_len カラムは、MySQL が使用することを決定したキーの長さを示します。 key_len の値を使用すると、MySQL が実際に使用するマルチパーティキーの部分の数を決定できます。 key カラムに NULL と表示されている場合、key_len カラムにも NULL と表示されます。

つまり、key_lenとは選択されたindexのキーの長さになります。例えばdatetimeなら5〜８バイトとなります。もっと詳しく知りたい人はここを見てください。

以上のことを踏まえて、2の公開開始日時・公開終了日時の複合indexを見てみます。公開開始日時と公開終了日時の複合indexが効いていた場合、10〜16になるはずです。

しかし、結果は6です。これは公開終了日時の方のindexは使われていないことを示しています。したがって、今回は1の公開開始日時のindexのみで十分なことが分かりました。

最後に

複合indexを貼った時はtypeにALLがないか、Extraにfile sortがないか等、普段気をつけていること以外にもkey_lenを確認してみてください。

ここまで読んでいただきありがとうございました。