ビュー、マテリアライズド・ビューについて(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    | 

```sql
-- ビューの作成
CREATE VIEW 家計簿10月 AS 
SELECT * FROM 家計簿
WHERE 日付 >= '2020-10-01'
AND   日付 <= '2020-10-30';
```

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

```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文で実施する。

```sql
DROP VIEW 家計簿10月;
```

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

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

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


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

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

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

```sql
-- テーブル作成/データ投入
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 | 

```

### リフレッシュの動作確認

```sql
-- 元のテーブル更新
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)
```

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

```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
-- マテリアライズド・ビューの更新
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入門](https://amzn.to/3fCAZY9)
[マテリアライズドビューの機能（PostgreSQL）](https://qiita.com/jiyu58546526/items/84182206f9c908b47d38)
[マテリアライズド・ビューについて](https://docs.oracle.com/cd/E57425_01/121/REPLN/repmview.htm#BABIIDJC)
[Oracle DB言語リファレンス CREATE MATERIALIZED VIEW](https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_6002.htm)