同じ 20 行のレポート クエリが 5 つのダッシュボードにコピーされており、各コピーは少しずれています。 1 つはキャンセルされた注文をカウントしますが、もう 1 つはカウントしません。 SQL ビュー は、そのロジックを明確な名前でデータベースに 1 回定義することで、この問題を解決します。

以下のすべての例を SQLite 3.50 で実行し、表示された結果はその実行から得られたものです。ビューは標準 SQL ですが、ビューによる更新、具体化されたビュー、依存関係の処理などの詳細はデータベースごとに異なるため、それらを指摘します。

ビューとは何ですか?

ビューは、名前を付けてデータベースに保存される SELECT ステートメントです。ビューにクエリを実行すると、データベースはその保存されたクエリを実行します。これは読み取り用のテーブルのように機能しますが、通常のビューはデータの独自のコピーを保持しません。

スプレッドシートに保存されたフィルターを考えてみましょう。フィルターはシートの 2 番目のコピーではありません。これは同じデータを調べる方法であり、常に最新の値が反映されます。

サンプルテーブル

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

ビューを作成する

このビューには、各顧客の注文が要約されます。 結合、グループ化、および CASE 式を 1 つの単純な名前の後ろに隠します。

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

これで、仕組みを知らなくても誰でも使用できるようになりました。

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

ビューでは LEFT JOIN と COALESCE が使用されているため、Ben はゼロで表示されます。このような決定は、ビュー内で一度行われると、まさにレポートの一貫性を維持します。 NULL 処理についての理解を深めるには、SQL NULL 値 を参照してください。

ビューは常に最新です

通常のビューは毎回クエリを実行するため、新しいデータがすぐに表示されます。 Ben の有料注文を追加し、ビューを再度クエリしました。

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

更新手順や同期を保つためのコピーはありませんでした。これが、クエリ結果を別のテーブルに保存する場合との主な違いです。

列の非表示

ビューは、ユーザーまたはアプリケーションが必要とする列のみを公開するためによく使用されます。ここでは、電子メール アドレスとクレジット限度額は省略されています。

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

PostgreSQL、SQL Server、MySQL などの権限のあるデータベースでは、レポート ユーザーにビューへのアクセスを許可できますが、基になるテーブルへのアクセスは許可できません。 SQLite にはユーザー アカウントがないため、列制限自体を表示することしかできませんでした。ビューを 1 つの保護層として扱います。機密データには依然として適切なアクセス制御が必要であり、一部のデータベースでは、ユーザーごとに行をフィルタリングするための行レベルのセキュリティが提供されています。

パフォーマンス: ビューは速度向上にはなりません

よくある誤解は、ビューを使用するとクエリが高速になるというものです。通常のビューはクエリを実行するとその定義に置き換えられるため、データベースは完全なクエリを自分で作成した場合と同じ動作を実行します。実行計画では、ビューの結合とグループ化がクエリの一部として実行されることが示されています。

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

したがって、遅いクエリに対するビューは依然として遅いクエリです。 SQL クエリの最適化 と SQL インデックスの説明 で説明されているように、速度はインデックスと優れたクエリ設計から生まれます。他のビューに基づいて構築されたビューにも注意してください。ネストのレイヤーにより、気づかずに同じ大きなテーブルを何度も簡単に結合できるようになります。

マテリアライズドビュー

マテリアライズド ビューは、キャッシュされたテーブルと同様に、その結果を保存します。読み取りは高速ですが、データの最新性は最後の更新時と同じです。

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

サポートはさまざまです。 PostgreSQL と Oracle にはマテリアライズド ビューがあり、SQL Server は独自のルールを持つインデックス付きビューを提供します。 SQLite と MySQL にはこれらの機能がないため、スケジュールに従って更新される定期的なサマリー テーブルが使用されます。この記事では PostgreSQL の例を実行しませんでした。

ビューを更新できますか?

それはデータベースとビューによって異なります。グループ化や DISTINCT を行わない単一テーブルの単純なビューは、多くの場合、PostgreSQL、MySQL、SQL Server で更新でき、変更はテーブルに反映されます。結合または集計を含むビューは、データベースがどの行を変更するかを判断できないため、通常は実行できません。

SQLite では、ビューへの直接書き込みはまったく許可されません。私たちの試みは失敗しました:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

一部のデータベースはビューを書き込み可能にする INSTEAD OF トリガーをサポートしており、多くのデータベースはビューから見えない書き込みを停止する WITH CHECK OPTION を提供しています。実際には、ほとんどのチームはビューを読み取り専用にし、テーブルに書き込みます。

依存関係と破損

ビューは、読み取り元のテーブルに依存します。テーブルにビューを作成し、テーブルを削除しました。

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite はドロップを許可しましたが、後でビューが使用されたときにのみ失敗しました。 PostgreSQL などの他のデータベースは、ビューも削除する CASCADE を追加しない限り、ビューが依存するテーブルの削除を拒否します。いずれにせよ、列の名前を変更したり削除したりするとビューが壊れる可能性があるため、スキーマ変更後の回帰テストで確認してください。

SELECT *を視野に入れた微妙な罠もあります。 CREATE VIEW star AS SELECT * FROM customers を作成し、テーブルに列を追加しました。 SQLite では、ビューは新しい列を選択しました。 PostgreSQL などの他のデータベースでは、ビューの作成時に列リストが展開されるため、新しい列は表示されません。したがって、列を明示的にリストします。これはビューを文書化しており、どこでも同じように動作します。

ビューの管理

ビューを削除すると、保存されたクエリのみが削除されます。私たちのテストでは、public_customers を削除した後も、customers テーブルには 3 行すべてが残っていました。移行とともにビュー定義をバージョン管理に保持することで、他のコードと同様に変更がレビューされます。

ビュー、CTE、テーブル

よくある間違い

• 速度の向上が期待されます。 通常のビューは毎回クエリを実行します。
• ビューで SELECT * を使用します。 テーブルが変更されたときの動作はデータベースによって異なります。
• 深いネスト。 ビューに基づいて構築されたビューは、負荷の高い結合を隠します。
• ビュー内での ORDER BY の使用。 クエリを実行するときに順序は保証されないため、外側のクエリで並べ替えます。
• 不明瞭な名前。 v_ などの接頭辞、または customer_sales などの明確な名詞が役に立ちます。
• 移行中にビューを忘れる。 スキーマを変更するたびにビューをテストします。

まとめ

ビューは、重要なロジックをデータベースに保存する簡単な方法です。これらは、パフォーマンス上のトリックとしてではなく、レポートの一貫性を維持し、不要な列を非表示にするために使用します。まず、コード内の複数の場所に出現するクエリを見つけて、明示的な列を含むビューに変換し、各呼び出し元にそのクエリを指定します。

よくある質問

学び続ける