浏览量

了解什么是视图以及什么时候使用它们。

🌐 Learn what views are and when to use them.

视图位于你在 Tables and data 创建的表格上方。

🌐 Views sit on top of the tables you create in Tables and data.

视图是查询的一个方便快捷方式。创建视图不涉及新的表或数据。当你运行视图时，Postgres 会执行底层查询并返回结果。

🌐 A view is a convenient shortcut to a query. Creating a view doesn't involve new tables or data. When you run a view, Postgres executes the underlying query and returns its results.

假设你有以下来自大学数据库的表：

🌐 Say you have the following tables from a university database:

students

courses

grades

创建一个包含这三张表的视图看起来像这样：

🌐 Creating a view that consists of all three tables looks like this:

1
create view transcripts as
2
    select
3
        students.name,
4
        students.type,
5
        courses.title,
6
        courses.code,
7
        grades.result
8
    from grades
9
    left join students on grades.student_id = students.id
10
    left join courses on grades.course_id = courses.id;
11
12
grant all on table transcripts to authenticated;

然后你就可以访问底层查询了：

🌐 Then you can access the underlying query with:

1
select * from transcripts;

查看安全 #

🌐 View security

默认情况下，Postgres 会检查视图底层表的权限，是针对视图的所有者而不是执行查询的角色。如果有特权角色创建了一个视图，所有读取它的人都是通过该角色的权限来访问的。可以用 security_invoker 修饰符来定义视图，这样就会检查查询角色的权限，同时也会让底层表的行级安全策略生效。

🌐 By default, Postgres checks permissions on a view's underlying tables against the view's owner rather than the role running the query. If a privileged role creates a view, everyone reading it does so through that role's permissions. Define the view with the security_invoker modifier to check the querying role's permissions instead, which is also what makes the underlying tables' row level security policies apply.

1
-- switch an existing view to the querying role's permissions
2
alter view <view name>
3
set (security_invoker = true);
4
5
-- create a view with the security_invoker modifier
6
create view <view name> with(security_invoker=true) as (
7
  select * from <some table>
8
);

什么时候使用视图 #

🌐 When to use views

视图提供了几个好处。

🌐 Views provide several benefits.

简单 #

🌐 Simplicity

当一个查询变得越来越复杂时，反复调用它会很乏味，尤其是当你经常运行它的时候。在上面的例子中，不要重复运行：

🌐 As a query becomes more complex, calling it repeatedly gets tedious, especially when you run it regularly. In the example above, instead of repeatedly running:

1
select
2
  students.name,
3
  students.type,
4
  courses.title,
5
  courses.code,
6
  grades.result
7
from
8
  grades
9
  left join students on grades.student_id = students.id
10
  left join courses on grades.course_id = courses.id;

你可以改跑这个:

🌐 You can run this instead:

1
select * from transcripts;

视图的行为也像一个普通的表。你可以安全地在表连接中使用它，或者从现有视图创建新的视图。

🌐 A view also behaves like a typical table. You can safely use it in table joins or create new views from existing views.

一致性 #

🌐 Consistency

在重复执行查询时，视图可以减少出错的可能性。在上面的例子中，你可能会决定排除课程 Introduction to Postgres。查询就变成了：

🌐 Views reduce the likelihood of mistakes when you execute a query repeatedly. In the example above, you might decide to exclude the course Introduction to Postgres. The query becomes:

1
select
2
  students.name,
3
  students.type,
4
  courses.title,
5
  courses.code,
6
  grades.result
7
from
8
  grades
9
  left join students on grades.student_id = students.id
10
  left join courses on grades.course_id = courses.id
11
where courses.code != 'PG101';

没有视图的话，你需要把新规则加到每个依赖的查询里。这会增加出错和不一致的几率，而且费时费力。有了视图，你只需修改 transcripts 视图里的基础查询，所有使用它的应用都会应用这个更改。

🌐 Without a view, you need to add the new rule to every dependent query. That increases the likelihood of errors and inconsistencies, and it takes considerable effort. With views, you alter the underlying query in the transcripts view, and the change applies to every application using it.

逻辑组织 #

🌐 Logical organization

通过视图，你可以给你的查询起个名字。这对于一起使用同一个数据库的团队非常有用。与其猜测一个查询的作用，不如一个命名清晰的视图直接说明它的用途。例如，transcripts 视图的名字就暗示了其底层查询涉及 students、courses 和 grades 表。

🌐 With views, you can give your query a name. This is useful for teams working with the same database. Instead of guessing what a query does, a well-named view explains it. For example, the name of the transcripts view suggests that the underlying query involves the students, courses, and grades tables.

安全 #

🌐 Security

视图可以限制向用户展示的数据量和类型。与其直接让用户访问一组表，不如给他们一个视图。你可以通过在底层查询中排除敏感列来阻止他们读取这些列。

🌐 Views can restrict the amount and type of data presented to a user. Instead of giving a user direct access to a set of tables, you give them a view. You can prevent them from reading sensitive columns by excluding those columns from the underlying query.

物化视图 #

🌐 Materialized views

一个物化视图是一种视图形式，它还会将结果存储到磁盘上。对物化视图的后续读取比普通视图快得多，因为数据已经可用了。普通视图每次调用时都会执行底层查询。

🌐 A materialized view is a form of view that also stores its results to disk. Subsequent reads of a materialized view return results much faster than a conventional view, because the data is already available. A conventional view executes the underlying query each time you call it.

使用上面的例子，你可以这样创建一个物化视图：

🌐 Using the example above, you can create a materialized view like this:

1
create materialized view transcripts as
2
  select
3
    students.name,
4
    students.type,
5
    courses.title,
6
    courses.code,
7
    grades.result
8
  from
9
    grades
10
    left join students on grades.student_id = students.id
11
    left join courses on grades.course_id = courses.id;

从物化视图读取数据和从普通视图读取数据是一样的：

🌐 Reading from the materialized view is the same as a conventional view:

1
select * from transcripts;

刷新物化视图 #

🌐 Refreshing materialized views

有一个权衡：物化视图中的数据并不总是最新的。要经常刷新它，防止数据变得过时。

🌐 There's a trade-off: data in a materialized view isn't always up to date. Refresh it regularly to prevent the data from becoming too stale.

1
refresh materialized view transcripts;

你多久刷新一次物化视图取决于你自己，而且每个视图可能根据其使用情况有所不同。

🌐 How often you refresh a materialized view is up to you, and it probably differs for each view depending on its use case.

物化视图 vs 常规视图 #

🌐 Materialized views vs conventional views

当查询或视图的执行时间太慢时，物化视图会很有用。这通常发生在涉及多张表和数十亿行数据的视图或查询中。只有当你可以接受过时的数据时才使用物化视图。内部仪表板和分析是常见的使用场景。

🌐 Materialized views are useful when execution times for queries or views are too slow. This happens in views or queries that involve multiple tables and billions of rows. Use a materialized view only when you can tolerate outdated data. Internal dashboards and analytics are common use cases.

创建物化视图并不能解决查询效率低的问题。即使你使用了物化视图，也要始终优化运行缓慢的查询。

🌐 Creating a materialized view isn't a solution to inefficient queries. Always optimize a slow-running query, even when you implement a materialized view.

资源 #

🌐 Resources

• 官方文档：创建视图
• Postgres 教程：视图