MySQLとPostgreSQLとINDEX

第 18 回 PostgreSQL アンカンファレンス@オンライン 2020/11/02

Transcript

1. MySQL と PostgreSQL と INDEX （良いタイトルが思い浮かばなかったので考えるのを諦めた） 第 18 回 PostgreSQL アンカンファレンス@オンライン　 2020/11/02 まつひさ（hmatsu47）
2. 自己紹介（前回とだいたい同じ） 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています MySQL 8.0 の薄い本を作って配っています ◦ Qiitaの記事： https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ GitHub リポジトリの他、印刷版を勉強会などで無料配布していました ◦ 新型コロナウイルスの関係でオフライン勉強会ができなくなったので、 現在は BOOTH でも配布しています（100円＋送料）8.0.22対応版準備中 https://booth.pm/ja/items/2062599 2
3. 自己紹介（前回と同じ） 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています MySQL 8.0 の薄い本を作って配っています ◦ Qiitaの記事： https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ GitHub リポジトリの他、印刷版を勉強会などで無料配布していました ◦ 新型コロナウイルスの関係でオフライン勉強会ができなくなったので、 現在は BOOTH でも配布しています（100円＋送料）不良在庫が… https://booth.pm/ja/items/2062599 3 （ちょっとだけ）レビューに参加しました　 https://booth.pm/ja/items/2398704
4. 余談① ??? 4
5. 余談① !!!（原因：なぜかはてブのホッテントリ入りしてた） 5
6. 余談② 今秋の IPA DB スペシャリスト試験で… • 前回のネタでの予想が一部当たって（？）、午後 Ⅱ 問 1 に IoT っぽいデータを扱う出題が !? • クラスタリングも含まれていたが、こちらは試験問題の レベルを上げない配慮か、かなり簡略化された扱いに 6
7. 余談③ Qiita で… • 前回ネタのおまけ記事が デイリートレンドの下の ほうに… !? 7
8. 今日は… • PostgreSQL アンカンファレンスのゆるふわ枠存続の　 ために、登壇枠で参加（3 回目） • だがしかし、ネタがない ◦ 10/31（土）までネタ選定に悩む… 8
9. そーだいさんの Tweet を思い出した →残念ながらカンファレンスには出れないけれど、今回のネタは　 とりあえず MySQL と PostgreSQL の違いをテーマにしてみる 9
10. また、とある Twitter の FF さんが… • 「MySQL で INDEX 作ったら SELECT が遅くなった」 • 「MySQL で 10 秒以上掛かったのに、PostgreSQL では 1 秒で終わった」 • 「最適な INDEX を作ったら MySQL でも 1 秒で終わった けれど、INDEX 職人するのつらい」 （すべて意訳） 10
11. というわけで、 • FF さん事例を少しアレンジして、 • 同じ構造のテーブルを作って同じデータを入れて、 • 同じように INDEX を数パターン作ってみて 比較してみた 11
12. テストの内容（1/3） • 100 台の機器（uuid で識別）から、毎日 1 時間おきに　 約 7 年半の間に届いた2 ヶ月分の計測値を、機器別に　 集計して平均値を求める →6,553,600 行のデータから、100 (台) × 24 (時間) × 61 (日) 　=146,400 行を抽出・グループ化して平均値を取得 12
13. テストの内容（2/3） • 比較する INDEX は以下の 6 種類 ◦ ①なし ◦ ②日時（date_created）のみ ◦ ③日時（date_created）＋uuid ◦ ④日時（date_created）＋uuid＋計測値（value） ◦ ⑤日時（date_created）＋計測値（value） ◦ ⑥uuid のみ 13
14. テストの内容（2/3） • 比較する INDEX は以下の 6 種類 ◦ ①なし ◦ ②日時（date_created）のみ ◦ ③日時（date_created）＋uuid ◦ ④日時（date_created）＋uuid＋計測値（value） ◦ ⑤日時（date_created）＋計測値（value） ◦ ⑥uuid のみ 14 ※③~⑤については、絞り込み範囲 を日時のみからさらに限定すること を意図したものではありません。 （④はカバリングになるかの確認）
15. テストの内容（3/3） • テスト環境は以下のとおり ◦ AWS RDS（MySQL 5.7.31 / PostgreSQL 12.3 R1） ◦ db.m5.xlarge（4vCPU・16GiB Mem） ◦ Single AZ ◦ デフォルトパラメータグループ 15
16. ①INDEX なし CREATE TABLE t1noindex (id int PRIMARY KEY, uuid VARCHAR(40) NOT NULL, date_created timestamp NOT NULL, value int); ②日付（date_created）のみ CREATE TABLE t1date (id int PRIMARY KEY, uuid VARCHAR(40) NOT NULL, date_created timestamp NOT NULL, value int); CREATE INDEX indexdate ON t1date (date_created); ③日付（date_created）＋uuid CREATE TABLE t1dateuuid (id int PRIMARY KEY, uuid VARCHAR(40) NOT NULL, date_created timestamp NOT NULL, value int); CREATE INDEX indexdateuuid ON t1dateuuid (date_created, uuid); テスト用のテーブル（1/2） 16
17. ④日付（date_created）＋uuid＋計測値（value） CREATE TABLE t1dateuuidval (id int PRIMARY KEY, uuid VARCHAR(40) NOT NULL, date_created timestamp NOT NULL, value int); CREATE INDEX indexdateuuidval ON t1dateuuidval (date_created, uuid, value); ⑤日付（date_created）＋計測値（value） CREATE TABLE t1dateval (id int PRIMARY KEY, uuid VARCHAR(40) NOT NULL, date_created timestamp NOT NULL, value int); CREATE INDEX indexdateval ON t1dateval (date_created, uuid, value); ⑥uuid のみ CREATE TABLE t1uuid (id int PRIMARY KEY, uuid VARCHAR(40) NOT NULL, date_created timestamp NOT NULL, value int); CREATE INDEX indexuuid ON t1dateuuidval (uuid); テスト用のテーブル（2/2） 17
18. t1=> SELECT uuid, AVG(value) FROM t1noindex WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid; uuid | avg --------------------------------------+------------------------ 41406cb0-1bef-11eb-a84c-063e4b2ff148 | 100.0000000000000000 41410373-1bef-11eb-a84c-063e4b2ff148 | 99.0000000000000000 41418bde-1bef-11eb-a84c-063e4b2ff148 | 98.0000000000000000 4141f03b-1bef-11eb-a84c-063e4b2ff148 | 97.0000000000000000 41427fd0-1bef-11eb-a84c-063e4b2ff148 | 96.0000000000000000 414314f9-1bef-11eb-a84c-063e4b2ff148 | 95.0000000000000000 （中略） 4170f36e-1bef-11eb-a84c-063e4b2ff148 | 5.0000000000000000 41716ab0-1bef-11eb-a84c-063e4b2ff148 | 4.0000000000000000 4171edeb-1bef-11eb-a84c-063e4b2ff148 | 3.0000000000000000 41726790-1bef-11eb-a84c-063e4b2ff148 | 2.0000000000000000 4172e0b8-1bef-11eb-a84c-063e4b2ff148 | 1.00000000000000000000 (100 rows) クエリの実行結果（部分）※表示の例は PostgreSQL 18
19. テストの結果（クエリ 5 回以上実行・最後の 3 回の平均） 19 INDEX の種類 MySQL 5.7 PostgreSQL 12 ①なし 2,000 ms 268 ms ②日時のみ 270 ms 26 ms ③日時＋uuid 270 ms 29 ms ④日時＋uuid＋計測値 183 ms 30 ms ⑤日時＋計測値 277 ms 29 ms ⑥uuid のみ 8,560 ms 269 ms
20. MySQL で「uuid のみ」が極端に遅いのは？ • uuid のみでは、ソートはできても絞り込みができない • INDEX 全行スキャン→その後テーブル全行をフィルタ処理 ◦ 集約時のソート効果 <<< アクセス経路が長くなるデメリット ◦ 単純にフルテーブルスキャンするほうが速い 20 INDEX の種類 MySQL 5.7 PostgreSQL 12 ①なし 2,000 ms 268 ms ⑥uuid のみ 8,560 ms 269 ms
21. mysql> EXPLAIN FORMAT=JSON SELECT uuid, AVG(value) FROM t1uuid WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1301666.80" }, "grouping_operation": { "using_filesort": false, "table": { "table_name": "t1uuid", "access_type": "index", "possible_keys": [ "indexuuid" ], "key": "indexuuid", （後略） MySQL ではソートの効果はあるものの… 21 集約時の filesort が不要に
22. mysql> EXPLAIN SELECT uuid, AVG(value) FROM t1uuid WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1uuid partitions: NULL type: index possible_keys: indexuuid key: indexuuid key_len: 42 ref: NULL rows: 6360514 filtered: 11.11 Extra: Using where 1 row in set, 1 warning (0.00 sec) MySQL では INDEX＋テーブル全行スキャン→遅い 22
23. t1=> EXPLAIN SELECT uuid, AVG(value) FROM t1uuid WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid; QUERY PLAN ------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------- Finalize GroupAggregate (cost=116748.46..116774.04 rows=100 width=69) Group Key: uuid -> Gather Merge (cost=116748.46..116771.79 rows=200 width=69) Workers Planned: 2 -> Sort (cost=115748.43..115748.68 rows=100 width=69) Sort Key: uuid -> Partial HashAggregate (cost=115744.11..115745.11 rows=100 width=69) Group Key: uuid -> Parallel Seq Scan on t1uuid (cost=0.00..115433.15 rows=62192 width=41) Filter: ((date_created >= '2020-10-01 00:00:00'::timestamp without time zone) AND (date_created <= '2020-11-30 23:59:59'::timestamp without time zone)) (10 rows) ※テーブルを Parallel Seq Scan PostgreSQL では INDEX を使わない 23
24. 日時が先頭の INDEX（②③④⑤）の効果の違いは？ • MySQL は④でカバリング INDEX の効果あり • PostgreSQL は④も効果なし 24 INDEX の種類 MySQL 5.7 PostgreSQL 12 ②日時のみ 270 ms 26 ms ③日時＋uuid 270 ms 29 ms ④日時＋uuid＋計測値 183 ms 30 ms ⑤日時＋計測値 277 ms 29 ms
25. mysql> EXPLAIN SELECT uuid, AVG(value) FROM t1date WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1date partitions: NULL type: range possible_keys: indexdate key: indexdate key_len: 4 ref: NULL rows: 282244 filtered: 100.00 Extra: Using index condition; Using temporary; Using filesort 1 row in set, 1 warning (0.00 sec) MySQL では、（②日時のみ） 25
26. mysql> EXPLAIN SELECT uuid, AVG(value) FROM t1dateuuid WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1dateuuid partitions: NULL type: range possible_keys: indexdateuuid key: indexdateuuid key_len: 4 ref: NULL rows: 294202 filtered: 100.00 Extra: Using index condition; Using temporary; Using filesort 1 row in set, 1 warning (0.00 sec) MySQL では、（③日時＋uuid）※⑤日時＋計測値もほぼ同じ 26
27. mysql> EXPLAIN SELECT uuid, AVG(value) FROM t1dateuuidval WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1dateuuidval partitions: NULL type: range possible_keys: indexdateuuidval key: indexdateuuidval key_len: 4 ref: NULL rows: 302700 filtered: 100.00 Extra: Using where; Using index; Using temporary; Using filesort 1 row in set, 1 warning (0.00 sec) MySQL では、（④日時＋uuid＋計測値 / カバリング INDEX） 27
28. MySQL では、④のカバリング INDEX のみ効果あり • EXPLAIN を見ても差がわかりづらい ◦ むしろ悪化しているように見える… • ④のカバリング INDEX では、テーブル行を見ない分高速 ◦ 今回の例では差はわずか ◦ 主キーの並び順≠日時順でない場合は、もっと効果が高い ▪ クラスタインデックスであるため ◦ カバリングでなければ無効果（アクセス経路変わらず・ソート効果低） 28
29. t1=> EXPLAIN SELECT uuid, AVG(value) FROM t1date WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid; QUERY PLAN ------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------- Finalize GroupAggregate (cost=6014.99..6040.57 rows=100 width=69) Group Key: uuid -> Gather Merge (cost=6014.99..6038.32 rows=200 width=69) Workers Planned: 2 -> Sort (cost=5014.96..5015.21 rows=100 width=69) Sort Key: uuid -> Partial HashAggregate (cost=5010.64..5011.64 rows=100 width=69) Group Key: uuid -> Parallel Index Scan using indexdate on t1date (cost=0.43..4733.69 rows=55390 width=41) Index Cond: ((date_created >= '2020-10-01 00:00:00'::timestamp without time zone) AND (date_created <= '2020-11-30 23:59:59'::timestamp without time zone)) (10 rows) ※Parallel Index Scan PostgreSQL では、（②日時のみ） 29
30. t1=> EXPLAIN SELECT uuid, AVG(value) FROM t1dateuuid WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid; QUERY PLAN ------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Finalize GroupAggregate (cost=105685.08..105710.67 rows=100 width=69) Group Key: uuid -> Gather Merge (cost=105685.08..105708.42 rows=200 width=69) Workers Planned: 2 -> Sort (cost=104685.06..104685.31 rows=100 width=69) Sort Key: uuid -> Partial HashAggregate (cost=104680.74..104681.74 rows=100 width=69) Group Key: uuid -> Parallel Bitmap Heap Scan on t1dateuuid (cost=6545.33..104368.39 rows=62470 width=41) Recheck Cond: ((date_created >= '2020-10-01 00:00:00'::timestamp without time zone) AND (date_created <= '2020-11-30 23:59:59'::timestamp without time zone)) -> Bitmap Index Scan on indexdateuuid (cost=0.00..6507.85 rows=149929 width=0) Index Cond: ((date_created >= '2020-10-01 00:00:00'::timestamp without time zone) AND (date_created <= '2020-11-30 23:59:59'::timestamp without time zone)) (12 rows) PostgreSQL では、（③日時＋uuid）※⑤もほぼ同じ 30
31. t1=> EXPLAIN SELECT uuid, AVG(value) FROM t1dateuuidval WHERE date_created BETWEEN '2020-10-01 00:00:00' AND '2020-11-30 23:59:59' GROUP BY uuid; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------------------------------------- Finalize GroupAggregate (cost=106301.65..106327.24 rows=100 width=69) Group Key: uuid -> Gather Merge (cost=106301.65..106324.99 rows=200 width=69) Workers Planned: 2 -> Sort (cost=105301.63..105301.88 rows=100 width=69) Sort Key: uuid -> Partial HashAggregate (cost=105297.31..105298.31 rows=100 width=69) Group Key: uuid -> Parallel Bitmap Heap Scan on t1dateuuidval (cost=7065.83..104990.55 rows=61352 width=41) Recheck Cond: ((date_created >= '2020-10-01 00:00:00'::timestamp without time zone) AND (date_created <= '2020-11-30 23:59:59'::timestamp without time zone)) -> Bitmap Index Scan on indexdateuuidval (cost=0.00..7029.02 rows=147246 width=0) Index Cond: ((date_created >= '2020-10-01 00:00:00'::timestamp without time zone) AND (date_created <= '2020-11-30 23:59:59'::timestamp without time zone)) (12 rows) PostgreSQL では、（④日時＋uuid＋計測値） 31
32. PostgreSQL では、INDEX 列が増えると僅かに悪化 • ②日時（date_created）のみで十分 • ④でも高速化せずさらに（僅かながら）悪化 ◦ EXPLAIN を見ると、Index Only Scan になっていない 32
33. まとめ • MySQL は（現状では）多数の行をスキャンするのが苦手 ◦ 全体的に PostgreSQL より遅い • MySQL と PostgreSQL では INDEX 作成による挙動の　 変化に違いがある ◦ 一方では有効な INDEX が他方では無意味なことも ◦ 今回は絞り込み＋集約のパターンだったが、絞り込み＋ソート （ソート＋絞り込み）のパターンでもまた結果が違うかも 33