概要
現場でビュー, マテリアライズド・ビューを扱う機会があったため、備忘録としてまとめる。
環境
- 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