【MySQL】約１５万件のテーブルになってからクエリが遅くなりました。テーブル設計とクエリを公開しますのでアドバイスお願いしたいです。

Question

データ量が１０万件を超えてきてからだんだん処理が遅くなり、１５万件になったころには３秒ほど処理速度が遅くなってしまいました。
トップページで行っている処理なので、この遅さは大きな問題になっています。
しかし、自分で各項目にインデックスをつけたり、クエリを変更したりしても改善されることがなかったので、皆さんのお力をお借りしたいです。
各テーブルの設計と、クエリを記載しますので、どうかアドバイスをお願いします。
※サイトの特定回避のため、ここでは内容を変えて説明します。クエリ自体はほぼ一緒です。

MySQLバージョン:5.6.22

t_syumi[InnoDB] 趣味テーブル
syumi_id(autoincrement),syumi_name データ数約2000件 ※syumi_idがプライマリキー
1,スポーツ
2,読書
3,映画鑑賞
4,音楽
…………等

t_user[InnoDB] ユーザーテーブル データ数約6000件
user_id(autoincrement),user_name ※user_idがプライマリキー
1,田中一郎
2,山口明子
3,佐藤俊哉
4,鈴木太郎
…………等

**t_setting[InnoDB] 趣味設定テーブル ** データ数約6000件
※一人で複数の趣味を持っている場合もあります
set_id(autoincrement),user_id,syumi_id ※set_idがプライマリキー,user_id,syumi_idに複合ユニークキー制約があります
1,1,4
2,1,2
3,2,1
4,3,2
…………等

t_petlist[InnoDB] ユーザーの飼っているペットの名前テーブル データ数約150000件
※一人で複数のペットを飼っている場合もあるため、一人で複数登録されている場合もあります
pet_id(autoincrement),user_id,pet_name ※pet_idがプライマリキー,user_id,pet_nameに複合ユニークキー制約があります
1,1,ポチ
2,1,ハチ
3,2,まろ
4,3,ジロー
…………等

使用するテーブルは以上です。
クエリは、**「趣味別で飼っているペットの名前の種類数を表示」**です。

SELECT syumi_id,syumi_name,COUNT(DISTINCT(pet_name)) AS cnt 
FROM t_petlist
INNER JOIN t_setting ON t_petlist.user_id = t_setting.user_id
INNER JOIN t_syumi ON t_syumi.syumi_id = t_setting.syumi_id
GROUP BY syumi_id
ORDER BY cnt DESC

各カラムは、左から「趣味ID」「趣味の名前」「その趣味の人が飼っているペットに名付けた名前の種類数」になります。

結果は
1,スポーツ,5431
2,読書,4480
4,音楽,4398
...といった結果が出力されます。
このクエリが約3秒ほどかかってしまいます。
目標は1秒を切りたいと考えています。

EXPLAIN実行結果

重くしている部分は、COUNT(DISTINCT(pet_id))で、DISTINCTを外すと0.5秒もかからないほど早くなります。
ただ、今回カウントしたいのは、「その趣味の人が飼っているペットに名付けた名前の種類数」なので、
スポーツが趣味の人で、その中の5人がペットに「ポチ」と名付けた場合は、その重複は除かなくてはいけません。

いろいろインデックスをつけたり外したり試してみましたが、どれも大体3秒ほどかかってしまいます。
既に大量のデータが入っている上、なるべく早めの対応が必要となりますので、テーブル設計から1から作り直すのは厳しいです。
とりあえずは、「インデックス操作」「クエリの変更」など、大きな変更を加えなくてもよい範囲でどなたかご教示お願いしたいです。もちろん、テーブル設計のアドバイス等も頂けたらうれしいです。

何かご不明点があればお気軽に聞いてください。どなたかアドバイスお願い致します。

Comments

[KiyoshiMotoki]

現状を詳しく知りたいので、各テーブルに、どのようなインデックスが張られているかも教えてください。また、差し支えなければ、問題のクエリの実行計画も追記してください。クエリの頭に"EXPLAIN"句をつけて実行すると、実行計画を取得することができます。 http://dev.mysql.com/doc/refman/5.6/ja/using-explain.html

[sanset]

ありがとうございます。各テーブルのインデックスをすべて明記し、EXPLAIN結果を画像で表示させてみました。内容を置き換えているので、もし明らかにおかしな点があればご指摘お願いします。。。

[ItoTomonori]

COUNT(DISTINCT(pet_id))、これコストかかりますよね、、、結局ほとんどのデータを舐めてしまうのでは・・・。
といっても、改善案はご呈示できず、申し訳ない。

[sanset]

ありがとうございます。今回は、大量データの重複を取り除く処理が、重たくさせている原因ですね。。。チューニングって難しいです。

Answer 1

問題がDISTINCT句であることが特定できているようですので、以下のページが参考になるかもしれません。
https://dev.mysql.com/doc/refman/5.6/ja/distinct-optimization.html

また、"情報の追加・修正の依頼をする"欄に記載させていただいた情報を追記いただけると、より詳しい状況がわかるので、具体的な回答を得やすくなると思います。

---

情報の追記、ありがとうございます。

一見したところ、インデックスのつけ方は問題ないように見受けます。

こちらで15万件ものデータを投入した環境を用意することができないので、
以下に、同様の結果を得られ（ると思う）、かつパフォーマンスの改善が見込めそうなクエリを
いくつか挙げさせていただきます。

ケース1 : JOINする順番を変更（レコードの少ないテーブルからJOIN）

SELECT t_syumi.syumi_id, syumi_name, COUNT(DISTINCT pet_id) AS cnt
FROM t_syumi INNER JOIN t_setting ON t_syumi.syumi_id = t_setting.syumi_id 
INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id 
GROUP BY t_syumi.syumi_id 
ORDER BY cnt DESC
;

ケース2 : ケース1に加えて、DISTINCT句を排除

SELECT t_syumi.syumi_id, syumi_name, COUNT(pet_id) AS cnt 
FROM t_syumi 
INNER JOIN t_setting ON t_syumi.syumi_id = t_setting.syumi_id 
INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id 
GROUP BY t_syumi.syumi_id 
ORDER BY cnt DESC
;

ケース3 : ケース2に加えて、レコード数を絞り込んでからJOIN（hirohiro様の回答と同様）

SELECT t_syumi.syumi_id, syumi_name, cnt 
FROM t_syumi INNER JOIN (
    SELECT syumi_id, COUNT(*) AS cnt FROM t_setting 
    INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id 
    GROUP BY syumi_id
) AS tmp ON t_syumi.syumi_id = tmp.syumi_id 
ORDER BY cnt DESC
;

ケース4 : ケース3に加えて、FORCE INDEX句を使用

https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html

SELECT t_syumi.syumi_id, syumi_name, cnt 
FROM t_syumi INNER JOIN (
    SELECT syumi_id, COUNT(*) AS cnt FROM t_setting 
        FORCE INDEX FOR GROUP BY (syumi_id) 
    INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id 
    GROUP BY syumi_id
) AS tmp ON t_syumi.syumi_id = tmp.syumi_id 
ORDER BY cnt DESC
;

どうしても改善できない場合、苦肉の策として、Javascriptなどを使って問題のデータを非同期で取得する、とかでしょうか。。

ちなみに、ご提示のクエリだとペットを飼っている人が１人もいない趣味は結果に含まれないことになりますが、
それは問題ないでしょうか？

---

FORCE INDEXが聞いたということでしたら、以下２通りのクエリを試していただけますでしょうか？

SELECT t_syumi.syumi_id, t_syumi.syumi_name, tmp.cnt 
FROM t_syumi 
INNER JOIN (
    SELECT syumi_id, COUNT(DISTINCT pet_name) AS cnt 
    FROM t_petlist 
        FORCE INDEX FOR GROUP BY (pet_name) 
    INNER JOIN t_setting ON t_setting.user_id = t_petlist.user_id 
    GROUP BY syumi_id 
) AS tmp ON t_syumi.syumi_id = tmp.syumi_id 
ORDER BY cnt DESC;

SELECT t_syumi.syumi_id, t_syumi.syumi_name, tmp.cnt 
FROM t_syumi 
INNER JOIN (
    SELECT syumi_id, COUNT(DISTINCT pet_name) AS cnt 
    FROM t_setting 
        FORCE INDEX FOR GROUP BY (syumi_id) 
    INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id 
    GROUP BY syumi_id 
) AS tmp ON t_syumi.syumi_id = tmp.syumi_id 
ORDER BY cnt DESC;

Comments

[sanset]

ありがとうございます。DISTINCTを使わないでクエリも発行しようとも考えましたが、
「グループ化（趣味）された件数内の重複を取り除く」になると、どうしても上記のクエリに行き付いてしまい、使わないやり方では逆に処理が遅くなったりとなかなか上手くいきませんでした。
情報修正致しましたので、一度KiyoshiMotoki様に修正内容にも目を通して頂ければ幸いです。。。

[KiyoshiMotoki]

ご報告ありがとうございます。

今、気づいたのですが、やりたいことは
「趣味別で飼っているペットの【名前の】種類数を表示」

なので、正しくは
　COUNT(DISTINCT(pet_id))
ではなく
　COUNT(DISTINCT(pet_name))
でしょうか？

であれば、t_petlistテーブルのpet_name カラムに単一のインデックスをつけると、改善するかもしれません。

[sanset]

詳しい解説まで頂きありがとうございます。
早速確認してみたいのですが、こちらの返信を最初に…

＞正しくはCOUNT(DISTINCT(pet_name))でしょうか？

仰る通りです。こちらの間違いです、申し訳ありません。早速修正致しました。
複合ユニークインデックスに加え、pet_nameにも単一のインデックスを付与したりもしていましたが、見て分かる変化はありませんでした。

[sanset]

すいません、もう一点質問見逃していました。
＞ちなみに、ご提示のクエリだとペットを飼っている人が１人もいない趣味は結果に含まれないことになりますが、
それは問題ないでしょうか？

これは問題ありません。

[sanset]

私のSQL文が間違っていたので、KiyoshiMotoki様のクエリも少し修正させて動かしてみました。

SELECT t_syumi.syumi_id, syumi_name, COUNT(cnt) as cnt
FROM t_syumi INNER JOIN (
SELECT syumi_id, syumi_name, COUNT(*) AS cnt FROM t_setting
FORCE INDEX FOR GROUP BY (syumi_id)
INNER JOIN t_petlist ON t_setting.user_id = t_petlist.user_id
GROUP BY syumi_id, syumi_name
) AS tmp ON t_syumi.syumi_id = tmp.syumi_id
GROUP BY t_syumi.syumi_id, syumi_name
ORDER BY cnt DESC

こちらで約1.9秒～2秒でした。hirohiro様と同じくかなり改善されました、ありがとうございます。
ただ、今回の肝であろうインデックスを明示？する命令のFORCE INDEX FORの理解が追い付かないまま実行させてしまったので、またいろいろ確認及び調整して報告させていただきます。
もしお時間あれば添削などして頂けると幸いです。。。

[KiyoshiMotoki]

情報の追記、ありがとうございます。
回答を追記させていただきましたので、ご確認ください。

ちなみに、1つ上のコメントで報告いただいたクエリは、実行不可能でした。
サブクエリ内で、サブクエリからは見えないカラム(syumi_name)を参照しているためです。

[sanset]

クエリを実行してみました。
前者も後者も平均で0.2秒(!)で処理が完遂しました。目標以上の素晴らしい結果です。
「FORCE INDEX FOR GROUP BY」
命令自体の存在を教えて頂いて初めて知った口ですが…ここまで処理が早くなるとは思いませんでした。
解説を見ていたらＳＱＬ実行時だけインデックス扱いにするという意味だと思ってましたが、違ってたみたいですね…（そもそも予めインデックスをつけても変化がなかったので）
しっかり頂いた記事やその他の記事も調べて、「FORCE INDEX FOR GROUP BY」を頭に落とし込みたいと思います。

自身の環境でデータを用意し辛いので難しい質問だったと思いますが、ここまで親身に考えて頂き本当にありがとうございます。
teratailのようなサービスがあって本当に助かりました。

[KiyoshiMotoki]

sanset様、お役に立てたなら、幸いです。

念のため、`FORCE INDEX FOR GROUP BY (...)`句を外して、パフォーマンスを計測していただけますか？

FORCE INDEX 句は実行計画に制限を加える可能性があるものなので、今後、データの件数や分布具合が変わった時、逆にこの句がパフォーマンスの足を引っ張ってしまうかもしれないからです。

FORCE INDEX 句をつけずに同様のパフォーマンスが得られるなら、この句をつけないに越したことはありません。

[sanset]

申し訳ありません、作業場から離れていたため日を跨いでの返信となりました。
先ほど確認致しましたところ、どうやら`FORCE INDEX FOR GROUP BY (...)`を外して実行しても同じパフォーマンスを得られました。
hirohiroさんも仰ってましたが、JOIN句の結合数を減らすことが重要だったみたいですね。
ＢＡ後もアドバイスを頂きありがとうございます。

Answer 2

t_petlist の pet_id は primary ですよね？
それが重複...ですか？

その趣味の人がつけた名前の種類数　=> t_petlistの pet_id は重複しないため、user_idからsyumi_idをjoinしたt_petlistでsyumi_idをgroup_byした時のcount(pet_id)ではないでしょうか。

Comments

[sanset]

ありがとうございます。
t_petlistのpet_idはautoincrementのプライマリキーです。インデックス情報も各テーブル掲載させています。
重複があるのはpet_nameです。

以前はクエリを２つに分けていて、重かったので1度のＳＱＬで表示方式に変えた過去があります。クエリ2つで上手く組めば高速化できるのでしょうか。。。

[sanset]

すいません、先ほどの投稿中に編集していただいてたみたいで気づきませんでした。

＞その趣味の人がつけた名前の種類数　=> t_petlistの pet_id は重複しないため、user_idからsyumi_idをjoinしたt_petlistでsyumi_idをgroup_byした時のcount(pet_id)ではないでしょうか。

おっしゃってるクエリですと「その趣味の人が飼っているペットの数」を求めることになってしまうと思います。
今回求めたいのは「その趣味の人が飼っているペットに名付けた名前の種類数」ですので、同じ趣味内の重複を取り除く必要があるので、pet_idでカウントは難しいです。
ややこしい条件で申し訳ないです。。。

[sokha]

なるほど。解決済みになってしまいましたが、今日の夜か明日にでも実際にtableを作成して検証しようと思っております。また書き込みますね。

Answer 3

手元に試せる環境がないため言葉での説明だけになってしまいますことご了承ください。
単にJOINするとレコード全体を結合してしまうので、最終的に抽出される１レコードのサイズが大きくなってしまい、レスポンス悪化の原因になるかもしれません。
SELECT文を用いて必要なカラムだけに限定すると良いと思います。

Comments

[sanset]

ありがとうございます。JOINでテーブル名をそのまま使用せず、必要なカラムだけSELECTで結合するという解釈で間違っていないでしょうか。
今回はカラムが少ない例だったので、改善には至らなかったですが、テーブルをそのままJOINしてしまうことが多かったので開発に役立てたいと思います。

[ttyp03]

その解釈であっています。
改善には至りませんでしたか。
カラム数が多いときは有効かと思いますので、今後の役に立てれば幸いです。

Answer 4

SELECT b.syumi_id, s.syumi_name, b.cnt
FROM (
  SELECT symi_id, pet_id, COUNT(pet_id) AS cnt
  FROM t_petlist
  INNER JOIN t_setting ON t_petlist.user_id = t_setting.user_id
  GROUP BY syumi_id, pet_id
) b
INNER JOIN t_syumi s ON s.syumi_id = b.syumi_id
ORDER BY b.cnt DESC

こんなSQLだとどうでしょう？
syumi_idとpet_idにindexがあればそれなりに早いのではないかと思うのですが...

オプティマイザがどのように実行計画を作っているかわからないため確かなところは解りませんが、

1. 最後のjoinは趣味の名前を得るためだけに数十万件のレコードに名前をくっつけているかも知れません。上のSQLならサブクエリの結果は6000レコード以下です。
2. COUNTでdistinctを実行してるので、6000回以上groupとソートを実行しているかも知れません。

Comments

[sanset]

ありがとうございます。
試してみたところ、
1,スポーツ,4
1,スポーツ,4
1,スポーツ,4...となってしまいましたので、こちらで少しクエリを修正しました。

SELECT b.syumi_id, s.syumi_name, COUNT(b.syumi_id) AS cnt
FROM (
SELECT symi_id, pet_id, COUNT(pet_id) AS cnt
FROM t_petlist
INNER JOIN t_setting ON t_petlist.user_id = t_setting.user_id
GROUP BY syumi_id, pet_id
) b
INNER JOIN t_syumi s ON s.syumi_id = b.syumi_id
GROUP BY b.syumi_id
ORDER BY cnt DESC

こちらで試したところ、1.9秒～2秒で結果が出せたため、1秒改善されました。ありがとうございます。
目標は1秒と言われてますので、まだ改善しなくてはならないのですが…。
情報の訂正で、インデックス情報も掲載しました。現在はプライマリキーと複合ユニークキーを使用しています。こちらの使い方が適切ではないでしょうか…

Answer 5

mysqlはあまり使ったことがありませんが、
そのクエリをviewにしておいて、それをselectするようにすれば少しは早くなるんじゃないでしょうか？

---

追記

SELECT syumi_id,syumi_name,COUNT(pet_id) AS cnt
FORM
  (
  SELECT DISTINCT
    syumi_id, syumi_name, pet_id
  FROM 
    t_petlist
    INNER JOIN t_setting ON t_petlist.user_id = t_setting.user_id
    INNER JOIN t_syumi ON t_syumi.syumi_id = t_setting.syumi_id
   ) tbl
GROUP BY syumi_id
ORDER BY cnt DESC

Comments

[sanset]

回答ありがとうございます。
ググった上で理解が追い付かなかったのでお伺いしたいのですが、「そのクエリをviewにしておいて、それをselectする」というのはどうゆう意味でしょうか？

[kaputaros]

Viewを一度つくっておけば、何度もクエリを実行しなくても、
テーブルのようにデータを用意してくれるので、select * ～とかで取ってこれると思います。
↓こちらが参考になるかと。

MySQL ビューを使ってレコードを表示する方法。
http://mysqlweb.net/article/62230614.html

[sanset]

ありがとうございます。上記の記事に加え、
http://dotinstall.com/lessons/basic_postgresql/26115
を見て確認しました。
ビューを利用する際に、参照している表から実際のデータを拾ってこなくてはいけないため、リアルタイムに件数が変動する今回のケースでは、おそらく速度の改善にはならないと考えております。（ビュー自体、検索をしやすくするもので、検索結果が早くなるものではない？）
知らなった仕様なので、開発面の速度は改善できそうです。ありがとうございます。

[kaputaros]

なるほど。リアルタイムデータなんですね。。。
そしたらViewを利用するのは難しいかもしれません。

ちょっと気になったのですが、count()の中でdistinctしていると、
１つずつdistinctが走るような気がするのですが、
いちどdistinctしたデータの塊にしてからカウントするのではどうでしょうか？

※ ここでは見づらいので、SQLは↑に追記しました。

[sanset]

ありがとうございます。上記のＳＱＬを試してみたところ、
おおよそ2.5秒で結果が出せたので、少しスピードアップは出来ました。ただ、できれば1秒は切りたいところです。
とはいえ、一歩前進できました。ありがとうございます。頂いたクエリを眺めて何かできないか考えてみます。

[kaputaros]

そうですねー、あと気になるのは、
① group by に指定されているのが syumi_id だけで syumi_name がない
② order by が cnt のみ
なところですかね。

①はDBによって違うお作法的なものかもしれませんが、今まで自分が扱ってきたDBでは集計関数を使う際には group by にその他のカラム全てを指定しなければならなかったような。。。

②はcntが同じになった際、つぎに順番を決めるのはどれなのか、を指定していないと同じcntで毎回表示順が変わってしまう気がして。。。

いろいろ見ていたら、mysqlでのcount()高速化の方法がいくつか載っているサイトを見つけたので参考にされるとよいかもです。

InnoDBでCOUNT()を扱う際の注意事項あれこれ。
http://nippondanji.blogspot.jp/2010/03/innodbcount.html

[sanset]

ご返信遅れて申し訳ありません。
①は確かに、気持ち悪い纏め方ですね…。現状はidのみで拾えていますが、すべて指定することで処理速度に関わってくるのでしょうか。

ＵＲＬ、ありがとうございます。確認させていただきます。

[sanset]

ちなみに、
① group by に指定されているのが syumi_id だけで syumi_name がない
こちらはsyumi_nameもGROUP BYすると少しパフォーマンスが落ちました。詳しく調べてみようと思います。

[kaputaros]

調べました。

GROUP BY句の後にカラム名を指定すると、そのカラム名に格納されている値が同じデータをグループとしてまとめます。
複数のカラム名を指定した場合、複数のカラムの値の組み合わせが同じデータをグループとしてまとめます。

　[参考] DBOnline > データをグループ化
　http://www.dbonline.jp/mysql/select/index9.html

なので、指定した分組み合わせを考える処理が増え、遅くなるようです。

また、mysqlだけは他と違って、group by句に書いていないものも select句に書けるようです。

　[参考] SELECT句に書ける項目とGROUP BY句に書ける項目
　http://d.hatena.ne.jp/CAMUS/20060626/1151334328

syumi_id と syumi_name が 1 : 1 なのであれば、指定しなくても問題ないのかもしれません。