はじめに
Amazon RDSのMySQL 5.7で開発を行うことになりました。そこで、少しですがMySQL 5.7の機能を調べる機会がありましたので、今回は「UNION ALL」に関してのオプティマイザの改善について記載したいと思います。
「UNION」と「UNION ALL」の違い
先ずは、改めて「UNION」と「UNION ALL」の違いを簡単に説明しておきます。
- UNION
結合対象のSELECT文の結果の全レコードを取得し、重複レコードを除去し返却します。 - UNION ALL
結合対象のSELECT文の結果の全レコードををそのままつなげて返却します。
違いは結合した結果から、「重複レコードを除去するか、しないか」だけとなります。
MySQL 5.7での「UNION ALL」の改善とは
後ほど、「UNION」「UNION ALL」の実行計画の例を記載しますが、実行計画は単純であり、結合されているSELECT文を上から順に実行し、結果をつなげるだけで、「UNION」はそこから重複レコードを除去することになります。
そのため、「UNION」では重複レコードの除去のために、テンポラリーテーブルを作成し、そこで重複レコードを除去する必要がありますが、「UNION ALL」ではその必要がないので、本来はテンポラリーテーブルは不要なはずです。
しかし、MySQL5.6では、「UNION」「UNION ALL」ともにテンポラリーテーブルを作成していました。今回MySQL5.7からは、「UNION ALL」の場合は不必要にテンポラリーテーブルを作成しないようになり、パフォーマンスが改善されたということになります。
MySQL 5.7での実行計画
では、MySQL 5.7での「UNION」と「UNION ALL」の実行計画を見てみましょう。
下記を見ていただくとわかりますが、MySQL 5.7では「UNION ALL」の場合は、テンポラリーテーブルが作成されていませんね。なお、比較のためにMySQL 5.7でも「UNION」を使った実行例も記載します。実行計画上で、「3. row」が追加されており、重複レコードの除去のためにテンポラリーテーブルが作成されていることがわかります。
UNION ALL実行計画
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> explain (SELECT id, title, like_count FROM `user_photos` WHERE `user_photos`.`mst_spot_id` = 1218 ORDER BY created_at DESC LIMIT 5) UNION ALL (SELECT id, title, like_count FROM `user_photos` WHERE `user_photos`.`mst_spot_id` = 1233 ORDER BY created_at DESC LIMIT 5)\G; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: user_photos partitions: NULL type: ref possible_keys: index_user_photos_on_mst_spot_id_and_created_at key: index_user_photos_on_mst_spot_id_and_created_at key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: UNION table: user_photos partitions: NULL type: ref possible_keys: index_user_photos_on_mst_spot_id_and_created_at key: index_user_photos_on_mst_spot_id_and_created_at key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using where |
UNION実行計画
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
mysql> explain (SELECT id, title, like_count FROM `user_photos` WHERE `user_photos`.`mst_spot_id` = 1218 ORDER BY created_at DESC LIMIT 5) UNION (SELECT id, title, like_count FROM `user_photos` WHERE `user_photos`.`mst_spot_id` = 1233 ORDER BY created_at DESC LIMIT 5)\G; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: user_photos partitions: NULL type: ref possible_keys: index_user_photos_on_mst_spot_id_and_created_at key: index_user_photos_on_mst_spot_id_and_created_at key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: UNION table: user_photos partitions: NULL type: ref possible_keys: index_user_photos_on_mst_spot_id_and_created_at key: index_user_photos_on_mst_spot_id_and_created_at key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using where *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Using temporary |
プログラムの見直しについて
SELECT文を結合する際に、暗黙的にUNIONだけを使っていた場合は、プログラムを見直すことでパフォーマンスが改善するかもしれません。
もちろん、UNIONとUNION ALLでは結果セットが異なってきますので、下記のように問題ないことを確認して置き換えると良いと思います。
- レコードが重複することがないので、UNION ALLを使用する
- レコードが重複するが、結果として問題ない、もしくはプログラム側で処理するので、UNION ALLを使用する
さいごに
いかがでしたでしょうか。ちょっとしたオプティマイザの改善ではありますが、DBがパフォーマンスのボトルネックになることが多いので、こういったところでもしっかりとチューニングしていきたいですね。
