MySQLでJOINの高速化
2018/04/24
JOINで無茶苦茶悩んだのでメモ。やりたかった事は、MySQLで
- テーブルAには必要最低限のログが格納
- テーブルBにはカテゴライズ情報が格納
- AとBを組み合わせてカテゴリー毎に集計したい
というもの。
あと難点として、テーブルAは200万件ほどデータが入っていて、尚且つサーバースペックが5年以上前のものらしくあまりよくないらしい。
で、最初に考えた案としては普通にJOINして、結果をWHEREで絞って集計。
SELECT SUM(aaa) FROM table_a AS a LEFT JOIN table_b AS b ON a.aaa = b.bbb WHERE ~~~
みたいな感じ。結合のキーは文字列。
1日毎の集計だと問題ないけど月間、年間で集計したら
トラフィックがえげつないことになった。
かといってPHPで処理したらメモリ不足でタイムアウトが発生した。
上司に相談したところ、DBの構成を考え直した方が良いとの回答。
どうしようもないなら下記みたいにしてみたら、とのこと。
SELECT SUM(aaa) FROM ( SELECT aaa FROM table_a WHERE ~~~ //条件絞って件数減らす ) AS a LEFT JOIN ( SELECT bbb FROM table_b WHERE ) AS b ON a.aaa = b.bbb
結合する際は、各々の件数を減らした上で結合するといいらしい。試してみると確かに高速化になった。
ただ、これでもトラフィックがあれだったのでDBの構成を考え直すことに。
1番はAテーブルにカテゴライズ情報を一緒に格納することだけど、色々事情があってそれはしたくなかった。
妥協案としてJOIN用のテーブルを作って解決できそう。要点はプライマリのカラムを結合させる。
idとかseqとかautoincrementに設定するようなものを結合キーにすると速度が出た。
今後も同じことがあると思うので覚えておく。
関連記事
-
MySQLで日時や日付で範囲検索する際に動作速度を向上させる方法
MySQLでログ系等の大量にデータが格納されているテーブルから日時や日付で範囲検 ...
-
MySQLでdatetime型にもインデックスを使用して高速化を図る方法
ログ関連のデータを格納するテーブルがあり、集計を行う際に非常に時間がかかった。レ ...
-
MySQLで特定のテーブルにパーティショニング設定し高速化を図る方法
以前に他社が設計されたログ系のテーブルを拝見した際にパーティショニング設定が行わ ...