今日は新人ブートキャンプ研修で id:beniyama 先生によるデータの研修だったのだけれど、Prestoなどの分散SQLクエリエンジンの話があり、その中でどうやったら効率良いクエリ書けるかという話が面白かったのでもう少し調べていた。
TDからPresto Performance Tuningという記事が出ていて、面白かったしためになったので簡単に訳してみた。
tl;dr
- 必要なものだけ
SELECT
する
time
は整数との比較になるようにする
TD_TIME_RANGE
のUDFを活用する
GROUP BY
するときは濃度が大きい方から(種類が多い方から)
ORDER BY
するときはなるべくLIMIT
- 精密さが求められないときはapproximate aggregate functionの活用を検討する
- 複数の
LIKE
句はregexp_like
でまとめる
JOIN
するときは軽い方を手前にする
- 上位N件を示したいときは
RANK
関数ではなくROW_NUMBER
関数が使えないか検討する
前提条件
必要なカラムだけ指定する
Treasure Dataでは、特定のカラムのみを扱うことに絞ったクエリに最適化された、列指向フォーマットでデータを保存している。必要なカラムのみSELECT
することでパフォーマンスを劇的に改善することができる。ワイルドカードの代わりに、必要なカラムのみ選択するようにすべき。
[GOOD]: SELECT time,user,host FROM tbl
[BAD]: SELECT * FROM tbl
時間指定の影響
インポートされた全てのデータはtime
フィールドを参考に1時間ごとに分けてパーティションに格納されている。時間指定を行えば、無駄なデータ読み込みを避けることが出来るので、スピードアップにつながる。
WHERE time <=> Integerになるようにする
WHERE
句でtime
フィールドを使うとき、クエリーパーザはどのパーティションを使うべきか自動判別している。この時、比較元がint
ではなくfloat
だと自動判別が効かない。
[GOOD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020
[GOOD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020 + 3600
[GOOD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020 - 3600
[BAD]: SELECT field1, field2, field3 FROM tbl WHERE time > 13493930200 / 10
[BAD]: SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020.00
[BAD]: SELECT field1, field2, field3 FROM tbl WHERE time BETWEEN 1349392000 AND 1349394000
TD_TIME_RANGEを使う
データをスライスする時にはTD_TIME_RANGE UDFを使うと便利
[GOOD]: SELECT ... WHERE TD_TIME_RANGE(time, '2013-01-01 PDT')
[GOOD]: SELECT ... WHERE TD_TIME_RANGE(time, '2013-01-01', NULL, 'PDT')
[GOOD]: SELECT ... WHERE TD_TIME_RANGE(time, '2013-01-01',
TD_TIME_ADD('2013-01-01', '1day', 'PDT'))
ただ、TD_TIME_RANGE
を割ってしまうと、パーティションを自動判別する最適化が効かないので注意。
[BAD]: SELECT ... WHERE TD_TIME_RANGE(time, TD_SCHEDULED_TIME() / 86400 * 86400))
[BAD]: SELECT ... WHERE TD_TIME_RANGE(time, 1356998401 / 86400 * 86400))
GROUP BYの順番に気をつける
GROUP BY
するとき、濃度(列の中での種類の多さ)が高い方を手前に持ってきたほうが少しだけ早くなる。
[GOOD]: SELECT GROUP BY uid, gender
[BAD]: SELECT GROUP BY gender, uid
また、文字列でGROUP BY
するよりも、数字で行ったほうが使用メモリが少なく、素早く比較できる。
ORDER BYするときにはLIMITを使う
ORDER BY
するときには一つのワーカーにすべてのデータを送る必要があるので、たくさんのメモリを使ってしまう場合がある。LIMIT
句を使うことで、ソートするコストとメモリ使用量を抑えることができる。
[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100
[BAD]: SELECT * FROM tbl ORDER BY time
近似した集計値を使う
少しの誤差を許す代わりに、性能改善が行えるapproximate aggregation functionsがPrestoには備わっている。例えばapprox_distinct()
関数は、COUNT(DISTINCT x)
の近似値が標準誤差2.3%で取得できる。
SELECT
approx_distinct(user_id)
FROM
access
WHERE
TD_TIME_RANGE(time,
TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d', 'PDT'),
TD_SCHEDULED_TIME())
LIKE句の集合は一つのregexp_like
にまとめる
Prestoのクエリオプティマイザは複数のLIKE句が使われているとクエリを改善できない。Prestoネイティブのregexp_like
にまとめると速度改善が期待できる。
SELECT
...
FROM
access
WHERE
method LIKE '%GET%' OR
method LIKE '%POST%' OR
method LIKE '%PUT%' OR
method LIKE '%DELETE%'
このクエリ内で、4つのLIKE句は一つのregexp_like
句に置き換えられる。
SELECT
...
FROM
access
WHERE
regexp_like(method, 'GET|POST|PUT|DELETE')
JOIN句では大きいテーブルを先に書く
Prestoでのデフォルトのjoinアルゴリズムは broadcast joinになっていて、内部表(joinする方, right-hand side table)が各ワーカーに送信される仕組みになっている(後ろに書いたテーブルが送信される)。内部表が十分に小さく、一つのノードに収まるとうまくjoinができ、目安としてはたいてい2GB以下。もしExceeded max memory xxGB
が出たときは、内部表が大きすぎることを意味している事が多い。
Prestoは自動でjoinの順序を調整してくれないので、大きなテーブルは先に書き、小さくて軽いテーブルをあとに書くようにすべき。
Hashに基づくDistributed JOINをチューニングする
もしまだメモリの問題が解決しなかったら、distributed hash joinを試してみるのがよい。2つのテーブルのハッシュ値をjoinのキーとして使うアルゴリズムで、内部表のサイズが大きくとも動作する。ただし、データ転送量が増大するので、速度は遅くなってしまう。distributed hash joinに切り替えるには、次のようにコメントすればよい。
SELECT ... FROM large_table l, small_table s WHERE l.id = s.id
上位Nレコードを取得するためにはROW_NUMBER()
の代わりにRANK()
を使う
最高/最低Nレコードを取ってきたいことがある。しかし、row_number()
はrank()
より遅い。
SELECT checksum(rnk)
FROM (
SELECT row_number() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
FROM lineitem
) t
WHERE rnk = 1
RANK
関数を使ったほうが、ROW_NUMBER
関数よりも性能が良い。
SELECT checksum(rnk)
FROM (
SELECT rank() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
FROM lineitem
) t
WHERE rnk = 1
まとめ
- 必要なものだけ
SELECT
する
time
は整数との比較になるようにする
TD_TIME_RANGE
のUDFを活用する
GROUP BY
するときは濃度が大きい方から(種類が多い方から)
ORDER BY
するときはなるべくLIMIT
- 精密さが求められないときはapproximate aggregate functionの活用を検討する
- 複数の
LIKE
句はregexp_like
でまとめる
JOIN
するときは軽い方を手前にする
- 上位N件を示したいときは
RANK
関数ではなくROW_NUMBER
関数が使えないか検討する
参考文献