MySQLでGROUP BYを高速化したい

MySQL(5.6)でデータの挿入バッチ作業をやってます

仮に以下のようなurlsテーブルがあったとして

|url|count|
|||
|yahoo.co.jp|2|
|google.com|9|
|yahoo.co.jp|3|

これをurlでGROUP BYしてかつcountの合計値とりたいので

sql
1select url, SUM(`count`) AS count_sum from urls group by url

でやってます。

もちろんこのぐらいの量なら問題ないのですが、すでにurlsテーブルに数百万件のデータが入っているので結構遅いです。

urlでINDEXキー？貼っても効いてくれませんでした

バッチなのでクエリの実行回数自体はそんなに多くはないのですが、なにか改善点はございますでしょうか。

よろしくお願いいたします。

KiyoshiMotoki

2017/01/30 02:58

urlsテーブルのテーブル定義と、問題の SELECT 文の実行計画を追記してください。テーブル定義は SHOW CREATE TABLE urls; という SQLで、実行計画は EXPLAIN select ... という SQLで取得できます。

kiq

2017/01/30 03:02

業務に関わることなので規約上詳しくはお答えできないのですが、explainしてurlカラムに対してUsing temporary; Using filesortとなります。PRIMARY　KEYはid(int,auto increment)です

masaya_ohashi

2017/01/30 03:04

ちなみにurlの型はなんですか？TEXTですか？VARCHARですか？

kiq

2017/01/30 03:06

urlはTEXT型です

yambejp

2017/01/30 03:27

たんなる単純ミスならいいのですが、上のyahooと下のyahooが別のurlとして解釈されているのは何か意味がありますか？もし本当に別のモノならそのロジックはどうしていますか？

行動規範の内容に同意します

回答4件

ベストアンサー

このテーブルにカラムを追加してよいのであれば、URLのハッシュ値を生成して、ハッシュ値でGROUP BYしてみるのはどうでしょうか。
別テーブルを作ってよいのであれば、URLをユニークにした一覧テーブルを作成し、そのテーブルのIDをこのテーブルに持たせ、そのIDでGROUP BYすれば衝突もないでしょう。

追記

基本的にTEXT型はINDEXを貼ったり条件に使ったりするのは苦手で低速なので、最大長がわかっていて、VARCHAR型にすることができるのであれば型を変えることもおすすめします。

投稿2017/01/30 03:03

編集2017/01/30 03:15

masaya_ohashi

総合スコア9206

kiq

2017/01/30 07:41

使用確認してTEXT型からVARCHARに変更、urlのハッシュ値でインデックス貼るようにしました。まだベンチマークは取ってませんが改善点を一番早く提示してくださったのでベントアンサーとします。ありがとうございました

行動規範の内容に同意します

おそらく、urlカラムを TEXT型で定義していることが問題です。

MySQLには、以下のような制限があるからです。

TEXT型にはプリフィクスインデックスしか作成できない

https://dev.mysql.com/doc/refman/5.6/ja/column-indexes.html

BLOB または TEXT カラムにインデックス設定する場合、インデックスのプリフィクス長を指定する必要があります。

プリフィックスインデックスは GROUP BY句で使用できない

https://dev.mysql.com/doc/refman/5.6/ja/group-by-optimization.html#loose-index-scan

インデックス内のカラムの場合、プリフィクスだけでなく、完全なカラム値にインデックスが設定されている必要があります。たとえば、c1 VARCHAR(20), INDEX (c1(10)) では、インデックスはルースインデックススキャンに使用できません。

実際、TEXT型のままインデックスを作成しても、それは使用されません。
http://sqlfiddle.com/#!9/001bc0/1

というわけで、以下のようにurlカラムの型を VARCHAR型に変更し、さらにインデックスを作成してやると、高速化が見込めます。

sql
1ALTER TABLE urls
2  CHANGE COLUMN url url VARCHAR(255),
3  ADD INDEX (url);

http://sqlfiddle.com/#!9/6decfc/3

なお、urlカラムに他の属性（例えば NOT NULL など）もある場合、ALTER TABLE 文では以下のようにそれら全ての属性を含めてやる必要があります。

sql
1ALTER TABLE urls
2  CHANGE COLUMN url url VARCHAR(255) NOT NULL ...,
3  ADD INDEX (url);

https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

CHANGE または MODIFY を使用する場合は、column_definition に、データ型および PRIMARY KEY や UNIQUE などのインデックス属性以外の、新しいカラムに適用されるすべての属性を含める必要があります。元の定義には存在するが、新しい定義として指定されていない属性は引き継がれません。

ちなみに、VARCHAR型に変更する際は格納できる文字数を指定してやる必要がありますが、
もし、urlカラムに格納されているデータが質問欄にご提示の通りの「ドメイン」のみなのであれば、255文字で十分です。
https://ja.wikipedia.org/wiki/%E3%83%9B%E3%82%B9%E3%83%88%E5%90%8D#.E6.9C.89.E5.8A.B9.E3.81.AA.E3.83.9B.E3.82.B9.E3.83.88.E5.90.8D.E3.81.AE.E5.88.B6.E9.99.90

すべてのラベルを結合しドメイン名を構成したとき、その最大文字数は255文字以下であると言う制限がある。

投稿2017/01/30 04:01

KiyoshiMotoki

総合スコア4791

書かれている通りであれば高速化するにしてもパーティショニングするくらいしか思いつきませんが、
そもそもインデックスがうまく効いているかどうか、

SQL
1EXPLAIN select url, SUM(`count`) AS count_sum from urls group by url

を使って、クエリー解析を行ってみてからになるかと思います。

追記：
マニュアルによると、

GROUP BY または DISTINCT 句内の、バイナリ文字列の場合に 512 バイトまたは非バイナリ文字列の場合に 512 文字より大きい文字列カラムの存在。(MySQL 5.6.15 より前のこの制限は、文字列の型に関係なく 512 バイトです。)

url が varchar(512) を超えている、もしくは text 型である場合、ソートをインメモリで行わず file を介するため、遅くなります。

投稿2017/01/30 03:03

編集2017/01/30 03:52

tacsheaven

総合スコア13703

kiq

2017/01/30 03:30

explainで見る限りUsing temporary; Using filesortなので効いてないです

tacsheaven

2017/01/30 03:38

group by するのにオンメモリの一時テーブルでは足りてないのですね。あるいは url の型が varchar(512) 以上でしょうか？　この場合 MySQL は filesort 以外を使用できないです。サーバーパラメータチューニングでメモリを増やすのも手ですが、masaya_ohashi さんの書かれたように、url ではなくハッシュ値などでインデックスを圧縮してしまうのも手です。

行動規範の内容に同意します

masaya_ohashiさんの回答がベストかと思いますが、
カバーリングインデックスを試してみて、変化があるのかは気になるところです。
こちらなどを参考に試してみてはいかがでしょうか。

ただ仮に速度が改善されたとしても、劇的には変わらない気はしますが・・・＾＾；

投稿2017/01/30 04:06