D.Horiyama's output

OSSDB標準教科書8章 パフォーマンスチューニング

PostgreSQLRDBSQL勉強メモ

OSSDB標準教科書

[http://oss-db.jp/ossdbtext/text.shtml:embed:cite]

インデックス

  • シーケンシャルスキャン・フルスキャン

    • 表全体を検索

  • インデックススキャン

    • インデックスから検索

  • 【補】インデックスは探索木

    • B木
    • 検索の計算オーダーはO(log(n))

主キーのインデックス

  • 最も有効

    • 検索条件や結合条件などで利用されることが多いから
  • 自動的についてる
 \d prod
                 Table "public.prod"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 prod_id   | integer |           | not null |
 prod_name | text    |           |          |
 price     | integer |           |          |
Indexes:
    "prod_pkey" PRIMARY KEY, btree (prod_id)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)

インデックスの作成

\d orders

                           Table "public.orders"
   Column    |            Type             | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
 order_id    | integer                     |           | not null |
 order_date  | timestamp without time zone |           |          |
 customer_id | integer                     |           |          |
 prod_id     | integer                     |           |          |
 qty         | integer                     |           |          |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
  • orders表のcustomer_id列にインデックスを作成
CREATE INDEX orders_customer_id_idx
    ON orders(customer_id);
  \d orders
                             Table "public.orders"
     Column    |            Type             | Collation | Nullable | Default
  -------------+-----------------------------+-----------+----------+---------
   order_id    | integer                     |           | not null |
   order_date  | timestamp without time zone |           |          |
   customer_id | integer                     |           |          |
   prod_id     | integer                     |           |          |
   qty         | integer                     |           |          |
  Indexes:
      "orders_pkey" PRIMARY KEY, btree (order_id)
+     "orders_customer_id_idx" btree (customer_id)
  Foreign-key constraints:
      "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
      "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)

  • 複合インデックス

    • 複数列からなるインデックス
    • 【補】順番に使わないと効かないので注意する

インデックスを削除する

DROP INDEX orders_customer_id_idx;
  \d orders
                             Table "public.orders"
     Column    |            Type             | Collation | Nullable | Default
  -------------+-----------------------------+-----------+----------+---------
   order_id    | integer                     |           | not null |
   order_date  | timestamp without time zone |           |          |
   customer_id | integer                     |           |          |
   prod_id     | integer                     |           |          |
   qty         | integer                     |           |          |
  Indexes:
      "orders_pkey" PRIMARY KEY, btree (order_id)
-     "orders_customer_id_idx" btree (customer_id)
  Foreign-key constraints:
      "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
      "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)

インデックスは万能ではない

  • UPDATEのたびにインデックスも更新される

    • 行が多いと負荷が増す
    • あまり書き換わらない列のほうが向いている
  • 列の値が適度にばらついていないと、シーケンシャルスキャンの方が効率がよいこともある

SQL実行プランの分析

  • EXPLAIN

    • SQLがDB内部で実際にどのように処理されるか確認

インデックスが存在しない場合のSQL実行プラン

EXPLAIN SELECT * FROM zip WHERE newzip = '1500002';
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on zip  (cost=0.00..4212.12 rows=1 width=140)
   Filter: (newzip = '1500002'::bpchar)
(2 rows)
  • Seq Scan … シーケンシャルなフルスキャン

インデックスが存在する場合のSQL実行プラン

CREATE INDEX zip_newzip_idx
    ON zip(newzip);
                    Table "public.zip"
  Column   |     Type     | Collation | Nullable | Default
-----------+--------------+-----------+----------+---------
 lgcode    | character(5) |           |          |
 oldzip    | character(5) |           |          |
 newzip    | character(7) |           |          |
 prefkana  | text         |           |          |
 citykana  | text         |           |          |
 areakana  | text         |           |          |
 pref      | text         |           |          |
 city      | text         |           |          |
 area      | text         |           |          |
 largearea | integer      |           |          |
 koaza     | integer      |           |          |
 choume    | integer      |           |          |
 smallarea | integer      |           |          |
 change    | integer      |           |          |
 reason    | integer      |           |          |
Indexes:
    "zip_newzip_idx" btree (newzip)
EXPLAIN SELECT * FROM zip WHERE newzip = '1500002';
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using zip_newzip_idx on zip  (cost=0.42..8.44 rows=1 width=140)
   Index Cond: (newzip = '1500002'::bpchar)
(2 rows)
  • Index Scan using zip_newzip_idx … インデックスを使ってくれるようだ
  • 検索対象1行

インデックスが存在しても必ず使われるわけではない

  • 使っても効果がないので使わないでくれるケース
CREATE INDEX zip_largearea_idx
    ON zip(largearea);
EXPLAIN SELECT * FROM zip WHERE largearea = 0;
  • largearea列はほとんど0なので、検索条件largearea = 0では
    インデックスはあまり意味がない
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on zip  (cost=0.00..4212.12 rows=121504 width=140)
   Filter: (largearea = 0)
(2 rows)
  • largearea = 1ならバッチリ効く
EXPLAIN SELECT * FROM zip WHERE largearea = 1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using zip_largearea_idx on zip  (cost=0.42..696.15 rows=2746 width=140)
   Index Cond: (largearea = 1)
(2 rows)

  • 【補】<>だとインデックス効かない

    • インデックスの探索は木の探索で、大小比較で絞り込んでいくわけだから、そりゃそうだ
EXPLAIN SELECT * FROM zip WHERE largearea <> 0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on zip  (cost=0.00..4212.12 rows=2746 width=140)
   Filter: (largearea <> 0)
(2 rows)

バキューム処理

PostgreSQLのデータ管理方式

  • PostgreSQLのデータ削除は、「削除した印」をつけてるだけ

    • 功

      • 更新・削除処理の時間面

    • 罪

      • ストレージの空間面
      • 検索処理の時間面

VACUUMとVACUUM FULL

  • VACUUM

    • 不要になった行データを回収して再利用可能にする

      • 【補】GCみたいな気持ち
      • ファイルサイズは変わらない

  • VACUUM FULL

    • さらに行データの物理的な配置を移動させ、ファイルサイズを縮小
    • ロックを伴う

VACUUM ANALYZE

  • ANALYZE

    • EXPLAINに利用される統計情報の更新

  • VACUUM ANALYZE

    • ANALYZEをVACUUMと同時に発行

自動バキュームデーモン

  • VACUUMとANALYZEを自動実行してくれる
  • PostgreSQLサーバー側
ps ax | grep autovacuum
   26 ?        Ss     0:00 postgres: autovacuum launcher

クラスタ

  • 行データをインデックスに従って物理的に再配置

    • インデックスを使って検索される行データが物理的にまとまる
    • ディスクアクセスが減り、検索性能向上が期待される
CLUSTER orders;
  • 初回はどのインデックスを使うのかわからないのでエラー
ERROR:  there is no previously clustered index for table "orders"
  • どのインデックスを使うか、USING句で教えてやる
CLUSTER orders USING orders_pkey;
CLUSTER
  • どのインデックスを使ってクラスタ化されたか記録される
  \d orders
   
   
                             Table "public.orders"
     Column    |            Type             | Collation | Nullable | Default
  -------------+-----------------------------+-----------+----------+---------
   order_id    | integer                     |           | not null |
   order_date  | timestamp without time zone |           |          |
   customer_id | integer                     |           |          |
   prod_id     | integer                     |           |          |
   qty         | integer                     |           |          |
  Indexes:
-     "orders_pkey" PRIMARY KEY, btree (order_id)
+     "orders_pkey" PRIMARY KEY, btree (order_id) CLUSTER
  Foreign-key constraints:
      "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
      "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
  • 次回以降はUSING句不要
CLUSTER orders;
CLUSTER