はじめに

こんにちは、バックエンドエンジニアのSakiです！バックエンドでPHPを書いたり、PHPという言語そのもののメンテナーもしています。

この度、注文データダウンロードAppのパフォーマンスをアップさせるため、とても入念にデータベースまわりの処理を見直しました。その中でも特に速度に関わってくる「index」についての考え方をまとめたいと思います。

この記事はMySQL（InnoDB）についての記事であり、他のRDBについては当てはまらない場合もあるということにご注意ください。

indexとは何か、おさらい

ご存知の方ももちろん多いと思いますが、indexについておさらいさせてください。

indexとは辞書でいうところの目次に相当するもので、目的のデータをいち早く検索するために重要なものです。もし辞書に目次が存在しなかった場合、目的の情報を探すのにとても苦労するだろうというのは想像しやすいと思います。

特別なindex、プライマリキーindex

DBテーブルには、大抵プライマリキーindexというものが存在します。これはMySQLでは各テーブルに1つずつしか持つことができない特別なindexです。

プライマリキーのカラムで構成され、これはテーブルで必ずユニーク（一意）になります。

必要に応じて追加するセカンダリindex

検索効率を上げる、カラムに対する制約を追加するなど、さまざまな目的で必要に応じて追加するのがセカンダリindexです。言い換えると、プライマリキーindexではないindexは、全てセカンダリindexです。これは1カラムのみで構成されることもあれば、複数のカラムの複合で構成されることもよくあります。また、ユニークなindexにすることも、重複を許すこともできます。

複合indexが使えるシーン、使えないシーン

次のようなindexがあるとします。

単一カラムのindexであればプライマリキーindexと使用感は変わらないので、複合indexについて少し詳しくおさらいします。

// test table
UNIQUE KEY `test_prefecture_city` (`prefecture`, `city`) // 都道府県と市の組み合わせ

この時、次の2つのクエリを考えてみます。

SELECT * FROM test WHERE prefecture = 'Tokyo' // 例1
SELECT * FROM test WHERE city = 'Chiyoda' // 例2

ご存知の方はもちろん多いと思いますが、この場合、例1ではindexが効きますが、例2ではindexが効きません（使えません）。

なぜそうなるのかは、本の目次を例にするとわかりやすいです。大カテゴリとして都道府県の並びがあり、それぞれの都道府県の項目の中で市が並んでいるとしましょう。

東京
- 足立区
- 荒川区
...（略）

千葉
- 千葉市
...（略）

さて、このような構成の目次から、都道府県を無視して（純粋に市だけで並べた場合の）例えば「あいうえお」順で市を探すことは理にかなっているでしょうか？無理があることがわかるかと思います。

したがって、複合indexを使用する場合、必ず左側のカラムから順に絞り込んでいけるクエリである必要があります。

必ずしもメリットばかりではない

セカンダリindexはあればあるほど検索効率の向上に繋がりますが、反面、データのinsertやupdate時に全てのindexを更新する必要があるため、indexが多ければ多いほど書き込み処理の負荷が上がるというトレードオフの関係でもあります。

クエリの妥当性はexplainで評価する

実際に業務で使用するクエリは、例でよく見るクエリほど単純ではないことが多いです。実際にクエリが「きちんとindexを使える」かどうかは、explainという機能を使って評価します。

explainの実行方法と結果の見方は記事がたくさんあるので、ここでは割愛します。

本題: explainだけじゃわからないことがある

ここからがこの記事の本題です。

実は、explainの結果がとても良いのに「なぜか遅い」クエリというものが存在します。これは、主に次のような状況が関係します（Profileを見ても、「データ転送が遅い」というざっくりしたことしかわからなかったりする）。

テーブルが巨大（レコード数が多い）
IN句で大量の条件を指定している（取りたいデータの対象が多い）
SELECTで取りたいカラムのデータ取得コストがかかっている

どういうことなのか、なぜ遅くなるのか、詳しく見てみましょう。

IN句は遅くなりやすい

不等号の範囲指定と違い、IN句（等価範囲）検索は、IN句に渡す値が増えれば増えるほど遅くなりやすい傾向があります。

例として、次のようなクエリを考えてみましょう。

// shipping_number = 配送番号
// shipping_numberのユニークindexがあるとする
SELECT * FROM test WHERE shipping_number IN (
    '0000-0000-0000',
    '0000-0000-0002',
    ...（略）
);

等価範囲検索の性質上、単に「ここからここまで」という不等号的な検索方法ではなく、IN句の値ひとつひとつについて検索する必要があります。例の場合、0000-0000-0000〜0000-0000-0002 という不等号的な範囲の検索では間の0000-0000-0001 が含まれてしまいます。

テーブルが巨大であればあるほど、検索コストが大きくなりやすいことがわかります。

ただし、このようなクエリであったとしても、indexを適切に使用できているため、explain上ではかなりいい結果に見えるはずです。このクエリの問題点は、indexを使用することそのものにコストがかかっている、という点です。

取得したいカラムのデータ取得コストがかかる

次のクエリをご覧ください。

// prefecture = 都道府県
// city = 市
// zip_code = 郵便番号
// prefecture, cityの複合indexを持つとする
SELECT prefecture, city, zip_code FROM test WHERE prefecture = 'Tokyo';

このクエリもまた、indexを適切に使用できるため、explainでは良い結果となります。しかし、テーブル自体が巨大であったり取得件数が多いと「なぜか遅い」ということになりやすいクエリです。

葉ノードという概念を考える

ここで重要になるのは葉ノードという概念です。MySQLの使用しているindexの構造は木に例えられるのですが、そこから生えているデータなので「葉」というわけですね。

葉ノードには、カラムのデータが含まれています。冒頭のあたりで使用した、都道府県と市の目次の例を見てみましょう。

東京
- 足立区
- 荒川区
...（略）

千葉
- 千葉市
...（略）

この例では、目次（index）で指定されている本のページ（テーブルデータ）にアクセスせずとも、都道府県と市の情報は目次の時点で手に入ることがわかります。東京 - 足立区というデータが存在することは目次を見ただけでわかる、ということです。

この情報が、すなわち葉ノードに含まれるデータだと考えてください。

indexは、そのindexに使用するカラムのデータを葉ノードに持っています。ただしプライマリキーindexだけは例外で、全てのカラムのデータを持っています（なので特別なindexなのです）。

ここで追加で「郵便番号」のデータを取得するとします。その場合目次には郵便番号の情報が書かれていないため、実際に目次の示すページを開き、そこから郵便番号を取得する必要があります。

セカンダリindexの使用時は、indexに含まれないカラムのデータを取得する際にテーブルデータへのアクセスが発生し、それがコストになる、と考えることができます（このコストは1件取得程度では誤差ですが、数万件取得、というような規模になってくると大きな差となります）。

解決方法

大量のIN句問題と取得カラム問題、2つの問題を提示しました。これらの解決策を考えてみましょう。

IN句問題: IN句の絞り込みの前に、絞れるだけ絞る

次のクエリをご覧ください。

// shipping_number = 配送番号
// prefecture = 都道府県
// prefecture, shipping_numberの複合ユニークindexがあるとする
SELECT * FROM test WHERE prefecture = 'Tokyo' AND shipping_number IN (
    '0000-0000-0000',
    '0000-0000-0001',
    ...（略）
);

問題の提示に使用したクエリとの差は、indexと検索条件に都道府県を加えていることです。こうすることで、IN句の絞り込みの前に都道府県で絞り込んでくれるので、「テーブルの全てのレコード」に対してIN句の絞り込みを行うコストが、「都道府県分のレコードに対して」の規模まで小さくなります。

可能であれば、「都道府県, 配送番号」から「都道府県, 市, 配送番号」、というindexにして、都道府県まで絞り込んでから配送番号をIN句検索、ではなく、市まで絞り込んでからIN句検索にすると、もっと早くなります。

トリッキーな例

// prefecture, ordered, shipping_numberの複合ユニークindexがあるとする
// それぞれ、都道府県、注文日時、配送番号
SELECT *
FROM test
WHERE prefecture = 'Tokyo'
AND ordered < {クエリ実行時よりも十分に未来の日時}
AND shipping_number IN (
    '0000-0000-0000',
    '0000-0000-0001',
    ...（略）
);

普通に考えて「未来に注文された注文」というものは存在するはずがありません（少なくともこのテーブルの運用では存在しないとします）。

そんなデータあるわけがないのでわざわざWHERE句に含める意味などないように思えますが、例示したindexを使用するためには、実はこの条件指定は有効です。

ORDER BY句で日時カラムを使用したい場合など、日時系の入ったindexを使用したい、しかし、検索条件そのものに日時データは不要…というシーンはあると思います。そのような場合に有用な、少しトリッキーな方法です。

取得カラム問題: 取得するカラムが本当に必要かをよく考え、使用するindexをよく考える

まず、特に理由のないSELECT *は避けるべきです。これは、ただデータ取得コストを増やすことに繋がります。

処理に必要なカラムが何であるのかをしっかり考えて、最低限のカラムだけを取得することがパフォーマンス面では重要です。

そして、使用するindexを吟味することもとても重要です。クエリに使用可能なindexが複数存在するのはよくあることなので、使用できるindexの中で、indexへのアクセスのみで必要なデータが全て取得できないかを検討します。

また、その処理がアプリケーションの中でとても重要で、indexを追加してでもパフォーマンスを上げたい場合、indexを新しく追加することは十分に価値のあることです。

余談ですが、私の試した中では、indexを追加したことで30秒オーバーのクエリが1.6秒まで短縮できました。それだけの速度差があっても、explainの結果に差はありません。

あえてクエリを分割するという選択肢

次のクエリをご覧下さい。

// idはプライマリキー
// shipping_number = 配送番号
// prefecture = 都道府県
// zip_code = 郵便番号
// name = 注文した人の名前
// prefecture, shipping_number, zip_codeの複合ユニークindexがあるとする
// 別で、prefecture, shipping_number, nameの複合ユニークindexがあるとする
SELECT id, shipping_number, prefecture, zip_code, name
FROM test USE INDEX (`test_prefecture_shipping_number_zip_code`)
WHERE prefecture = 'Tokyo'
AND shipping_number IN (
    '0000-0000-0000',
    '0000-0000-0001',
    ...（略）
);

USE INDEXを使用して、使用するindexを固定しています。

この例では、nameカラムについて、テーブルアクセスしてデータを取得するコストが発生します（idはコストがかかりません。InnoDBでは、暗黙的にセカンダリindexの最後にプライマリキーが勝手に入るため、葉ノードにidは常に含まれます）。

一方で、使用するindexをtest_prefecture_shipping_number_nameにすると、今度は郵便番号の取得コストがかかります。

実際にやってみて計測して判断する必要がありますが、これは、次のような方法で解決できる場合があります。

// クエリ1
SELECT id, shipping_number, prefecture, zip_code
FROM test USE INDEX (`test_prefecture_shipping_number_zip_code`)
WHERE prefecture = 'Tokyo'
AND shipping_number IN (
    '0000-0000-0000',
    '0000-0000-0001',
    ...（略）
);

// クエリ2
SELECT id, name
FROM test USE INDEX (`test_prefecture_shipping_number_name`)
WHERE prefecture = 'Tokyo'
AND shipping_number IN (
    '0000-0000-0000',
    '0000-0000-0001',
    ...（略）
);

このように、indexのみでデータ取得が完結するようにクエリを分割し、後からアプリケーション側でidを使用してデータをマージする、という方法です。USE INDEXを使用することで、MySQLのオプティマイザによる自動的なindex選択に任せず、使用するindexを常に固定することができます。

繰り返しになりますが、これは実際に速度計測を行なって検討する必要があります。カラムの型やサイズによっても結果が異なってくるためです。

余談になりますが、私が色々と試した中では10秒かかっていたものが1秒未満まで短縮したというような例もあったため、試してみる価値は十分にあります。