Help us understand the problem. What is going on with this article?

ビュー、マテリアライズド・ビューについて(PostgreSQL)

概要

現場でビュー, マテリアライズド・ビューを扱う機会があったため、備忘録としてまとめる。

環境

  • PostgreSQL 13.1

ビュー(View)とは

仮想的なテーブルを指す。データは内部に持たず、実態は「名前をつけたSELECT文」である。

ビューのメリット

  • 結合などを利用した複雑な検索処理をシンプルに記述できる。重複する検索処理にも有効。
  • 機密情報をアクセスしないようなビューを作成することで、データ参照を許可する範囲を適切に定めることができる。

ビューのデメリット

  • 実行されるSQL文は、送信するSQLから見ると想像以上に負荷がかかる処理になる可能性がある
  • データを持たないため、後述するマテリアライズド・ビューより処理速度は遅い
  • インデックスの付与ができない。

ビューの作成と削除

以下の家計簿テーブルを例に、10月分の家計簿だけを抽出した「家計簿10月」のビューを作成。

日付 メモ 収入 支出
2020-09-29 食費 0 1000
2020-10-10 映画 0 2000
2020-10-12 歯医者 0 1900
2020-10-20 給料 200000 0
2020-10-28 食費 0 1500
2020-11-12 洋服 0 5000
2020-11-20 給料 200000 0
-- ビューの作成
CREATE VIEW 家計簿10 AS 
SELECT * FROM 家計簿
WHERE 日付 >= '2020-10-01'
AND   日付 <= '2020-10-30';

上記のSQL文でビューを作成すると、検索文は以下のようにシンプルなものになる。

-- ビューを利用した検索
SELECT * FROM 家計簿10;

| 日付       | メモ   | 収入   | 支出 | 
| ---------- | ------ | ------ | ---- | 
| 2020-10-10 | 映画   | 0      | 2000 | 
| 2020-10-12 | 歯医者 | 0      | 1900 | 
| 2020-10-20 | 給料   | 200000 | 0    | 
| 2020-10-28 | 食費   | 0      | 1500 | 

ビューの削除は以下のSQL文で実施する。

DROP VIEW 家計簿10;

マテリアライズド・ビュー

SELECT文による検索結果をキャッシュして保持している仮想的なテーブル。
ビューの性能上の問題となる場合に、パフォーマンス向上を目的として使用される。
ビューはSQL文を処理する度にDBに再検索をかけているが、マテリアライズド・ビューでは検索結果を保持するため、あるビューを頻繁に参照する場合に、毎回検索処理を実行する必要がなくなり性能向上に繋がる。

また、マテリアライズド・ビューはインデックスの作成が可能である。
ただ、元のテーブルの構造やデータが更新された際、マテリアライズド・ビューも更新するにはリフレッシュ(バッチ更新)が必要となる。

ビューとマテリアライズド・ビューの比較

項目 ビュー マテリアライズド・ビュー
データの保持 しない する
参照の動作 SQL文を実行し処理結果を返す 保持しているSQL文の処理結果を返す
処理速度 遅い 速い
インデックスの作成 できない できる

リフレッシュについて

マテリアライズド・ビューは、マテリアライズド・ビュー作成時のテーブル情報を保持するだけであり、その後のテーブルへの更新処理(UPDATEやDELETE)によって更新されない。マテリアライズド・ビューの内容を最新のテーブルの状態と同じ状態にしたい場合にリフレッシュが必要となる。

-- リフレッシュのコマンド
REFRESH MATERIALIZED VIEW マテリアライズド・ビュー名;

マテリアライズド・ビューの動作確認

ビューでも使った家計簿テーブルでマテリアライズド・ビューを作成する。

-- テーブル作成/データ投入
CREATE TABLE 家計簿(
日付 DATE PRIMARY KEY, メモ TEXT,収入 INT,支出 INT);

INSERT INTO 家計簿(日付, メモ, 収入, 支出)
VALUES('2020-9-29', '食費', 0, 1000),
('2020-10-10', '映画', 0, 2000),
('2020-10-12', '歯医者', 0, 1900),
('2020-10-20', '給料', 200000, 0),
('2020-10-28', '食費', 0, 1500),
('2020-11-12', '洋服', 0, 5000),
('2020-11-20', '給料', 200000, 0);

-- マテリアライズド・ビューを作成
CREATE MATERIALIZED VIEW 家計簿10
AS --この下にSQLを書く
SELECT * FROM 家計簿
WHERE 日付 >= '2020-10-01'
AND   日付 <= '2020-10-30';

-- 検索
SELECT * FROM 家計簿10;
| 日付       | メモ   | 収入   | 支出 | 
| ---------- | ------ | ------ | ---- | 
| 2020-10-10 | 映画   | 0      | 2000 | 
| 2020-10-12 | 歯医者 | 0      | 1900 | 
| 2020-10-20 | 給料   | 200000 | 0    | 
| 2020-10-28 | 食費   | 0      | 1500 | 

リフレッシュの動作確認

-- 元のテーブル更新
UPDATE 家計簿 SET メモ = 映画鑑賞 where 日付 = '2020-10-10';

    日付    |   メモ   |  収入  | 支出
------------+----------+--------+------
 2020-09-29 | 食費     |      0 | 1000
 2020-10-12 | 歯医者   |      0 | 1900
 2020-10-20 | 給料     | 200000 |    0
 2020-10-28 | 食費     |      0 | 1500
 2020-11-12 | 洋服     |      0 | 5000
 2020-11-20 | 給料     | 200000 |    0
 2020-10-10 | 映画鑑賞 |      0 | 2000     -- 映画->映画鑑賞に更新
(7 rows)

元のテーブルが更新された状態でマテリアライズド・ビューを確認すると、更新が反映されていないことが確認できる。

SELECT * FROM 家計簿10;

    日付    |  メモ  |  収入  | 支出
------------+--------+--------+------
 2020-10-10 | 映画   |      0 | 2000      -- 更新されていない
 2020-10-12 | 歯医者 |      0 | 1900
 2020-10-20 | 給料   | 200000 |    0
 2020-10-28 | 食費   |      0 | 1500

このタイミングでリフレッシュコマンドを実行し、更新されたかを確認すると、更新が反映されていることがわかる。

-- マテリアライズド・ビューの更新
REFRESH MATERIALIZED VIEW 家計簿10;
REFRESH MATERIALIZED VIEW

SELECT * FROM 家計簿10;

   日付    |   メモ   |  収入  | 支出
------------+----------+--------+------
 2020-10-10 | 映画鑑賞 |      0 | 2000     -- メモの内容が正しく更新されていることが確認できる
 2020-10-12 | 歯医者   |      0 | 1900
 2020-10-20 | 給料     | 200000 |    0
 2020-10-28 | 食費     |      0 | 1500

まとめ

今回はビュー、マテリアライズド・ビューの概要と、PostgreSQLを使ったビュー・マテリアライズド・ビューの動作確認の様子を記述した。調査の中で、MySQL(8.0時点)ではマテリアライズド・ビューが非対応だったり、Oracle DBだとマテリアライズド・ビューの作成時にリフレッシュのタイミングの指定が可能だったり、DBMSの種類によって仕様が結構違うという点が見られた。

参考

スッキリわかるSQL入門
マテリアライズドビューの機能(PostgreSQL)
マテリアライズド・ビューについて
Oracle DB言語リファレンス CREATE MATERIALIZED VIEW

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away