MySQLのGroup Byが遅すぎる

Question

MySQL5.7を使用しています.

以下のSQLを実行したところ,10分以上時間が掛かってしまいます.

SELECT hinban,tanaban FROM rireki GROUP BY hinban,tanaban

データは約40万行あります.
rirekiテーブルには15列あり,その内の2列をグループ化して表示させたいです.
hinbanはvarchar(100),tanabanはvarchar{20)と定義しています.
GROUP BYしなければ約10秒程度で結果が返ってきます.
indexは設定していません.

単純なsqlなのになぜこんなに時間が掛かるのでしょうか?
また,高速化するためのヒントなどが御座いましたら,何卒ご教授宜しくお願い致します.

Answer 1

質問内容に回答があるので、考え方と補足だけ。

group byの処理内容は、
「基本的に、指定した項目でソートした後、同一レコードがあれば、間引いたりする作業」になります。

今回のテーブル項目hinban,tanabanにはindexがないという事なので、
40万件のソート作業（2項目（120桁分）を永遠と比較し続ける）に時間がかかっています。

（以下、参考までに）
10レコードをソートするのに、最大４５回比較・並べ替えします。
100レコードをソートするのに、最大４９５０回比較・並べ替えします。
1万レコードをソートするのに、最大約５０００万回比較・並べ替えします。
40万レコードをソートするのに、最大約７９９億回比較・並べ替えします。
⇒40万レコードの処理で役10秒とのことなので、
10分どころか、データによっては、1日以上かかるような気もします。
※ソート方法にも色々な手法があったりするので、回数は参考値程度に。

ただし、hinban,tanabanにindexがあれば、上記のようなソート作業時間は発生しません。

論より証拠。

Alter table rireki 
ADD INDEX IX_rireki( hinban, tanaban );

を実行後、再度、遅延しているSQLを実行してみてください。
恐らく約10秒程度以下で結果が出ると思います。

Comments

[masayoshi001]

ご回答ありがとうございます.
group byにはindexをつけても意味がないと勝手に思い込んでおりました.
indexをつけることによって約10秒程度でデータが返ってくるようになりました.

[tomari_perform]

GROUP BYの結果、どのくらいの件数が抽出されるかにもよりますが、
10秒もかかるのであれば、
hinban_tanaban_masterテーブル（Distinctの結果）を作った方がより良いかもしれません。

[masayoshi001]

ご回答ありがとうございます.
さらなる改善策の提案有難うございます.
データがもっと増えた場合に備えて検討させていただきます.

Answer 2

GROUP BYしなければ約10秒程度で結果が返ってきます.
indexは設定していません.

indexも外部キー制約もつけてないとすると、可変長テキストの比較を行数*2回行うことになるので10分掛かるのは妥当だと思います。
環境の方を変えられないなら、indexか外部キー制約をつけるぐらいしか思いつきません。

Comments

[hihijiji]

index(見出し)をつけることによって、可変長テキストから比較用のハッシュ値を作ってくれると言う副次効果があります。
ハッシュ値はスタック扱いされればCPUのレジスタまで入り込んでいける可能性があるのに対して、可変長テキストはディスクキャッシュにヒットすることすら多くはありません。

アクセス速度は、左が一番早く右に一つ毎に桁違いに遅くなります。
レジスタ > キャッシュメモリ(SRAM) > ディスクキャッシュ(DRAM) > ディスク

[hihijiji]

捕捉追加:外部キー制約を付けるとインデックスがおまけで付いてきます。

[masayoshi001]

ご回答ありがとうございます.
indexを適切に追加し,所望の結果を得ることが出来ました.

Answer 3

グループ関数を共わないGROUP BYはSQLのルール違反です。

SELECT DISTINCT hinban,tanaban FROM rireki

はどうなりますか？

索引を追加したら違いはありますか？

Comments

[KiyoshiMotoki]

hinbanカラム、tanabanカラムのいずれも GROUP BY句で指定しているカラムであるため、グループ関数を使用せずとも「ルール違反」ではありません。

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

> SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

Answer 4

＞単純なsqlなのになぜこんなに時間が掛かるのでしょうか?
確かにかかり過ぎな気はしますね。
EXPLAIN取ってはいかがでしょう。
GROUP BYしない全件40万の取得が10秒くらいとして、
GROUP BYした場合の件数って何件くらいで10分以上なりますか？
ちなみに、他の検索条件があったり、
rirekiテーブルをパーティショニングはしていませんよね。

＞また,高速化するためのヒントなどが御座いましたら,何卒ご教授宜しくお願い致します.
複合インデックスを作るのが一般的な性能対策です。
rirekiテーブルの使われ方によりますが、おそらく大丈夫かと思います。

Comments

[masayoshi001]

ご回答ありがとうございます.
GroupByしたデータで約6000件でした.
indexを適切に追加し,所望の結果を得ることが出来ました.