MySQLとPostgreSQLとINDEX

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

Transcript

1. MySQL と PostgreSQL と INDEX
   （良いタイトルが思い浮かばなかったので考えるのを諦めた）
   第 18 回 PostgreSQL アンカンファレンス@オンライン　
   2020/11/02
   まつひさ（hmatsu47）
   

   View Slide

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
   

   View Slide

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
   

   View Slide

4. 余談① ???
   4
   

   View Slide

5. 余談① !!!（原因：なぜかはてブのホッテントリ入りしてた）
   5
   

   View Slide

6. 余談② 今秋の IPA DB スペシャリスト試験で…
   ● 前回のネタでの予想が一部当たって（？）、午後 Ⅱ 問 1
   に IoT っぽいデータを扱う出題が !?
   ● クラスタリングも含まれていたが、こちらは試験問題の
   レベルを上げない配慮か、かなり簡略化された扱いに
   6
   

   View Slide

7. 余談③ Qiita で…
   ● 前回ネタのおまけ記事が
   デイリートレンドの下の
   ほうに… !?
   7
   

   View Slide

8. 今日は…
   ● PostgreSQL アンカンファレンスのゆるふわ枠存続の　
   ために、登壇枠で参加（3 回目）
   ● だがしかし、ネタがない
   ○ 10/31（土）までネタ選定に悩む…
   8
   

   View Slide

9. そーだいさんの Tweet を思い出した
   →残念ながらカンファレンスには出れないけれど、今回のネタは　
   とりあえず MySQL と PostgreSQL の違いをテーマにしてみる
   9
   

   View Slide

10. また、とある Twitter の FF さんが…
    ● 「MySQL で INDEX 作ったら SELECT が遅くなった」
    ● 「MySQL で 10 秒以上掛かったのに、PostgreSQL では
    1 秒で終わった」
    ● 「最適な INDEX を作ったら MySQL でも 1 秒で終わった
    けれど、INDEX 職人するのつらい」
    （すべて意訳）
    10
    

    View Slide

11. というわけで、
    ● FF さん事例を少しアレンジして、
    ● 同じ構造のテーブルを作って同じデータを入れて、
    ● 同じように INDEX を数パターン作ってみて
    比較してみた
    11
    

    View Slide

12. テストの内容（1/3）
    ● 100 台の機器（uuid で識別）から、毎日 1 時間おきに　
    約 7 年半の間に届いた2 ヶ月分の計測値を、機器別に　
    集計して平均値を求める
    →6,553,600 行のデータから、100 (台) × 24 (時間) × 61 (日)
    　=146,400 行を抽出・グループ化して平均値を取得
    12
    

    View Slide

13. テストの内容（2/3）
    ● 比較する INDEX は以下の 6 種類
    ○ ①なし
    ○ ②日時（date_created）のみ
    ○ ③日時（date_created）＋uuid
    ○ ④日時（date_created）＋uuid＋計測値（value）
    ○ ⑤日時（date_created）＋計測値（value）
    ○ ⑥uuid のみ
    13
    

    View Slide

14. テストの内容（2/3）
    ● 比較する INDEX は以下の 6 種類
    ○ ①なし
    ○ ②日時（date_created）のみ
    ○ ③日時（date_created）＋uuid
    ○ ④日時（date_created）＋uuid＋計測値（value）
    ○ ⑤日時（date_created）＋計測値（value）
    ○ ⑥uuid のみ
    14
    ※③~⑤については、絞り込み範囲
    を日時のみからさらに限定すること
    を意図したものではありません。
    （④はカバリングになるかの確認）
    

    View Slide

15. テストの内容（3/3）
    ● テスト環境は以下のとおり
    ○ AWS RDS（MySQL 5.7.31 / PostgreSQL 12.3 R1）
    ○ db.m5.xlarge（4vCPU・16GiB Mem）
    ○ Single AZ
    ○ デフォルトパラメータグループ
    15
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

20. MySQL で「uuid のみ」が極端に遅いのは？
    ● uuid のみでは、ソートはできても絞り込みができない
    ● INDEX 全行スキャン→その後テーブル全行をフィルタ処理
    ○ 集約時のソート効果 <<< アクセス経路が長くなるデメリット
    ○ 単純にフルテーブルスキャンするほうが速い
    20
    INDEX の種類 MySQL 5.7 PostgreSQL 12
    ①なし 2,000 ms 268 ms
    ⑥uuid のみ 8,560 ms 269 ms
    

    View Slide

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 が不要に
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

28. MySQL では、④のカバリング INDEX のみ効果あり
    ● EXPLAIN を見ても差がわかりづらい
    ○ むしろ悪化しているように見える…
    ● ④のカバリング INDEX では、テーブル行を見ない分高速
    ○ 今回の例では差はわずか
    ○ 主キーの並び順≠日時順でない場合は、もっと効果が高い
    ■ クラスタインデックスであるため
    ○ カバリングでなければ無効果（アクセス経路変わらず・ソート効果低）
    28
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

32. PostgreSQL では、INDEX 列が増えると僅かに悪化
    ● ②日時（date_created）のみで十分
    ● ④でも高速化せずさらに（僅かながら）悪化
    ○ EXPLAIN を見ると、Index Only Scan になっていない
    32
    

    View Slide

33. まとめ
    ● MySQL は（現状では）多数の行をスキャンするのが苦手
    ○ 全体的に PostgreSQL より遅い
    ● MySQL と PostgreSQL では INDEX 作成による挙動の　
    変化に違いがある
    ○ 一方では有効な INDEX が他方では無意味なことも
    ○ 今回は絞り込み＋集約のパターンだったが、絞り込み＋ソート
    （ソート＋絞り込み）のパターンでもまた結果が違うかも
    33
    

    View Slide