多数のクエリをUNION ALLした場合のコストについて

Question

そこそこ重いクエリを数百ほどUNION ALLした場合、メモリの使用量などはどのようになるのでしょうか。
1つのSQLを順に実行していった場合と近い処理になるのか、全てを同時に実行したような処理になるのか。
ひとまず結論がわかると助かりますが、これを確認する方法についても教えていただきたいです。

Comments

[退会済みユーザー]

RDBMS名を指定してもらえれば、より的確なアドバイスが得られることでしょう。

Answer 1

MySQLに限って回答させていただくと、、

UNION または UNION ALL を使用した場合、SQLを1つずつ順に実行した場合と比べて
一時的に使用するメモリの量(※)は増えるはずです。
※「のべ」の使用量については、分かりません。

なぜなら、MySQL は UNION 句が含まれるクエリを実行する際に内部一時テーブルを作成するからです。
https://dev.mysql.com/doc/refman/5.6/ja/internal-temporary-tables.html

一時テーブルは、次のような条件で作成される可能性があります。

・ UNION クエリーが一時テーブルを使用します。

上の引用句では「可能性があります」という記載ですが、UNION 句に関しては必ず内部一時テーブルが作成されるようです。
手元の環境（MySQL 5.7）で確認した限りでは、2つの空のテーブルを UNION しただけでもCreated_tmp_tablesステータス変数が増加したからです。

mysql> CREATE TABLE table_1 (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE table_2 (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 16    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table_1;
Empty set (0.00 sec)

mysql> SELECT * FROM table_2;
Empty set (0.00 sec)

mysql> SHOW STATUS LIKE 'Created_tmp_tables'; # 個々に SELECT した場合は一時テーブルは作成されない
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 16    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table_1 UNION ALL SELECT * FROM table_2;
Empty set (0.00 sec)

mysql> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 17    |
+--------------------+-------+
1 row in set (0.00 sec)

おそらく、MySQL の内部では

UNION 句によって結合される全てのデータを内部一時テーブルに格納してから、
その一時テーブルのデータをクライアントに返却する

という動作が行なわれていると思われます。

---

さらに言うと、この内部一時テーブルは最初はインメモリとして作成されますが、テーブルのサイズが一定以上になるとディスク上に書き出されるようです。
上と同じリンク

サーバーは最初にインメモリーテーブルとして内部で一時テーブルを作成し、それが大きくなりすぎた場合に、それをディスク上テーブルに変換することがあります。

一般的に、ディスクの読み書き速度はメモリ上のそれと比べて遅いので、UNION 句はクエリの実行速度も悪化する可能性があると言えます。

---

単にリソースの節約だけが目的であれば、クエリを１つずつ実行する方が良いでしょう。

もし、その場合に
「一貫したデータが取得できなくなる」
ことも懸念されているのでしたら、START TRANSACTION WITH CONSISTENT SNAPSHOTクエリが使用できないか、検討してみてはいかがでしょうか？
https://dev.mysql.com/doc/refman/5.6/ja/commit.html

WITH CONSISTENT SNAPSHOT 修飾子は、この機能に対応しているストレージエンジンでの一貫性読み取りを開始します。

もしくは、Panzer_for様のコメントのようにサマリテーブルを作成する、などの対応のほうが、多数のクエリを UNION するより良いと思います。

Comments

[milktea]

MySQLの場合は、1つのSQL文+一時テーブル分のメモリ消費、という形になるのですね。特殊な事情があり、1つのSQL文で全てのデータを取得したいと思っているので、UNION ALLを使って結合することにします。勉強になるご回答ありがとうございました。

Answer 2

メモリは増やせば良いけど、それよりSELECT文を多数発行することの負荷が大きいです。
WHEREを工夫してSELECT文の発行をなるべく少なく済ませることを考えます。
その前に UNION ALL と書くべきところを UNION で書いて無駄な負荷をかけて平気なプログラマが多過ぎです。

Comments

[milktea]

費用や権限の問題によりスペックを変更することができないのです。
重いクエリをUNION ALLで多数結合した場合、メモリを大量に消費してしまうのでしょうか(DBエンジンによって異なるかもしれませんが)。

[Orlofsky]

どれだけ多くのメモリを消費するかは個々のSQLを調べないとわかりません。
http://www.oracle.com/technetwork/jp/ondemand/database/mysql/mysql-perftun-1484759-ja.pdf
に載っているツールなどを参考にしてください。
UNION ALLは使わないで済むなら使わない方が良いです。
UNION ALLばかり気にされていますが、パフォーマンス・チューニングを効率的に行うにはパフォーマンスが悪い部分から改善するべきです。

Answer 3

UNION ALLする前提ならなぜテーブルをわけているのでしょう？
場合によってはパーティショニングしておけばよいのではないでしょうか？

Comments

[milktea]

サイト上の様々な更新を日毎のレポートのように出力したいと考えています。

[Orlofsky]

グループ関数を使えば済む案件では？
http://www.shift-the-oracle.com/sql/group-by-having.html
日毎に GROUP BY します。
CREATE TABLE, INSERT, と欲しい結果を提示されては？

[milktea]

同じテーブルであればGROUP BYで済みますが、例えば登録アクションや購入アクションなど全く異なるデータについてのサマリーを1つのクエリで取得したいのです。

[yambejp]

テンポラリテーブルをつくって流し込めばよいのでは？

[Orlofsky]

UNION ALL をいくつ使うつもりでしょうか？
日毎に登録アクションや購入アクションを並べたいとか、上記に書いたようにテーブルやそのデータと出力結果を提示していただけないと適切なコメントは難しいです。

[Panzer_vor]

横やり失礼します。
とりあえず下記に思いつくことを並べてみた。

１.CASE式で事足りるならCASE式
２.データ量によるパフォーマンスに懸念があるならパーティションの導入
３.集計パターンが煩雑ならば中間結果をテンポラリテーブル化
４.UNION ALLでいくとして、共通部分が多い、かつ事前にある程度データを絞れるなら共通テーブル式(※MySQLは未サポート)

どの程度複雑なことをやりたいのかわからないので何とも言えないですが、
複雑ならばテンポラリテーブルやらマテリアライズドビューを導入した方がパフォーマンスは望めると思います。

[Panzer_vor]

おっとメモリ節約って所を見落としてた。

共通テーブル式はオンメモリとなるのでメモリ節約から見るとダメですね。

要件次第ですが、
リアルタイム性が低くてよいあれば、
サマリテーブルを追加して夜間バッチなり何なりで負荷の少ない時間帯で集計データ作成するのが一番楽ですね。

ちなみにメモリについては正確には分かりませんが、
UNION ALLでSELECT句を繋げる量があまりにも多いと、
単発で1つずつ実行するより時間を食う可能性は高いですよ。

DBMSにもよるのでしょうが、途端に全く結果が返ってこなくなるってこともありますし。