「インデックスを使うとなんか早くなるよ」ぐらいの知識しかない自分が
高速化のためにexplainして確認する大切さを身を持って味わう経験ができました。
後の役に立つと思ってメモを残しておきます。
こんなデータがあるとする。
chuumonsテーブル
| id | date | kyaku_id |
|---|---|---|
| 1 | 2018-01-01 00:00:00 | 1 |
| 2 | 2018-01-02 00:00:00 | 1 |
| 3 | 2018-01-01 10:00:00 | 2 |
- dateは注文発生日、kyaku_idは注文した客のidを表す
- dateは秒単位で指定されるので、重複がかなり少ない。
meisaisテーブル
| id | chuumon_id | shouhin_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
- レコード１つがある注文の商品単位での明細を表す
- 一度の注文で２種類の商品を買った場合、おなじcuumon_idのレコードが２つできる
- shouhin_idは買った商品を表す
やりたいこと
これらのテーブルから以下の条件で注文を抽出したい
・発生時点で商品id=1を３回購入している
これを実現するためにこんなSQLを書いてみた。
SELECT
chuumons_narrowed_by_item.id
FROM
chuumons AS chuumons_narrowed_by_item
INNER JOIN
chuumons AS onaji_kyaku_chuumons
ON
chuumons_narrowed_by_item.kyaku_id = same_user_chuumons.kyaku_id
AND
onaji_kyaku_chuumons.date <= chuumons_narrowed_by_item.date
INNER JOIN
meisais
ON
same_user_chuumons.id = meisais.chuumon_id
AND
meisais.item_id = 1
GROUP BY
chuumons_narrowed_by_item.id
HAVING
COUNT(chuumon_narrowed_by_item.id) = 3
注文ごとに同じ客の過去注文をjoinして、item_idごとに集計する。
これで仕様は満たせるのですが
実際にこのクエリを動かすとかなり時間がかかってしまいました。
原因としては、
chuumonsやmeisaisのレコード数がものすごく多い場合、
これらがJOINされまくることによってとてつもないレコード数になってしまう。
そのレコード一つ一つをCOUNTで数え上げることになるので、
計算量も相当なものになってしまう、ということのようです。
というわけで対策としては
- shouhin_idよりも絞り込みの効果が大きいカラムで、ベースになるchuumonテーブルを絞り込む ということが考えられます。 そうすればJOINしてもレコード数はさほど増えないはず。
テーブルの説明でちらっと述べましたが、
chuumonsテーブルには重複が少ないdateというカラムがあります。これを条件に追加してみます。
SELECT
chuumons_narrowed_by_item.id
FROM
chuumons AS chuumons_narrowed_by_item
INNER JOIN
chuumons AS onaji_kyaku_chuumons
ON
chuumons_narrowed_by_item.kyaku_id = same_user_chuumons.kyaku_id
AND
onaji_kyaku_chuumons.date <= chuumons_narrowed_by_item.date
AND
chuumons_narrowed_by_item.date BETWEEN '2018-01-01' AND '2018-01-31'
INNER JOIN
meisais
ON
same_user_chuumons.id = meisais.chuumon_id
AND
meisais.item_id = 1
GROUP BY
chuumons_narrowed_by_item.id
HAVING
COUNT(chuumon_narrowed_by_item.id) = 3
この改修によって本当に高速化は出来たのですが、すこし気になる現象が起きました。
データ量によって高速化され具合が違う
どの程度効果があったか調べるために、chuumonsのレコード数を変えて試してみました。
（meisaisはchuumonsの約10倍の数があるとする）
| chuumonsレコード数 | 改修前にかかった時間(s) | 改修後にかかった時間(s) |
|---|---|---|
| 20万 | 10 | 1 |
| 100万 | 30 | 27 |
改修によって速くなってはいるのですが、レコード数が多いとあまり改修の効果が高くないのです
これはなぜなのか、explainして調べてみました
-
explain結果 レコード数20万の場合（一部省略）
id select_type table type possible_keys key key_len 1 SIMPLE chuumons_narrowed_by_item range PRIMARY, id, date, kyaku_id date 5 1 SIMPLE onaji_kyaku_chuumons ref PRIMARY, id, date, kyaku_id kyaku_id 4 1 SIMPLE meisais ref chuumon_id, shouhin_id chuumon_id 4 -
explain結果 レコード数100万の場合（一部省略）
id select_type table type possible_keys key key_len 1 SIMPLE meisais ref chuumon_id, shouhin_id shouhin_id 4 1 SIMPLE onaji_kyaku_chuumons eq_ref PRIMARY, id, kyaku_id PRIMARY 4 1 SIMPLE chuumons_narrowed_by_item ref PRIMARY, id, date, kyaku_id kyaku_id 4
結果を出力するにあたって、参照された順番が早いものほど上に表示されます。
なので今回の場合、レコード数20万のときはchuumonsテーブルから、
100万のときはmeisaiテーブルから参照したという違いが見られます。
また、possible_keysとkeysにも違いがあります。
これらはそれぞれ「テーブルの探索に使用可能なキー」「実際に使用したキー」を表しています。
レコード数20万のときは最初にchuumonsテーブルをdateで絞り込んだことがわかります。
つまり、JOINする段階でchuumonsテーブルのレコード数をぐっと小さくすることに成功しているため、
dateの条件を付加した恩恵が大きかったということになります。
なぜこのような違いが現れたのか
MySQLはテーブルを探索する際、どのキーで絞り込むかを
統計情報に基づいて判断しているそうです。
テーブルのレコード数も、どのキーが有効か判断する手がかりとして使われている、ということみたいですね。
今回はMySQLが「テーブル数が100万件あるなら、はじめにこのテーブルをこのキーで絞り込みしよう」と判定した結果、あまり有効でないキーが選択されてしまったようです。
確実に高速化するために
今回の場合、chuumons.dateにインデックスを付加することで
レコード数に関わらず「はじめにchuumonsテーブルをdateで絞り込む」よう動作させることができるようになりました。
インデックスの付いていない日付型のカラムは絞り込みに使用する優先度が低い、ということなのかもしれません。