SQL 物化视图：提升查询性能

物化视图是 SQL 数据库中的一项强大功能，通过将查询结果以物理形式存储在磁盘上，避免反复计算，从而加速查询性能。它们对涉及连接、聚合和大型数据集的复杂、资源密集型查询尤其有用。

在开始之前，建议您学习 DataCamp 的 Introduction to SQL 课程和 SQL Fundamentals 技能路径，掌握 SQL 基础以及如何通过查询提取数据。您还可以参考 SQL Basics Cheat Sheet，了解常用的 SQL 过滤与聚合函数。

什么是 SQL 物化视图？

物化视图是一种特殊的数据库对象，它将查询的结果以物理方式存储，而不是像常规视图那样在访问时即时计算。常规 SQL 视图是一个已保存的 SQL 查询，每次访问都会动态生成结果；而物化视图会预计算并将数据存储为类似表的结构。

通过将结果集存储到磁盘，物化视图可以显著降低数据库负载、提升查询性能，并有助于简化计算密集型操作的处理。

在 SQL 中创建物化视图

创建物化视图需要使用 CREATE MATERIALIZED VIEW 语法，不同 SQL 数据库的语法略有差异。以下展示在 SQL Server、PostgreSQL 和 Oracle 中创建物化视图的方法。

PostgreSQL 中的物化视图

在 PostgreSQL 中，您可以使用如下语法创建物化视图。此示例创建名为 sales_summary 的物化视图，用于汇总各产品的总销量与总收入。

-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, 
       SUM(quantity) AS total_quantity, 
       SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;

SQL Server 中的物化视图

在 SQL Server 中，物化视图称为“索引视图”（Indexed Views）。要创建索引视图，底层表需满足特定要求，例如启用 WITH SCHEMABINDING 选项。

WITH SCHEMABINDING 选项可确保在索引视图存在期间架构不会变更。要将视图物化，必须为其创建唯一聚集索引。

-- Create an indexed view with schema binding to summarize sales data
CREATE VIEW sales_summary
WITH SCHEMABINDING 
AS
SELECT product_id, 
       COUNT_BIG(*) AS record_count,
       SUM(ISNULL(quantity, 0)) AS total_quantity,
       SUM(ISNULL(price, 0) * ISNULL(quantity, 0)) AS total_revenue
FROM sales
GROUP BY product_id;
GO
-- Create a unique clustered index to materialize the view
CREATE UNIQUE CLUSTERED INDEX IX_sales_summary 
ON sales_summary (product_id);
GO

如果您想进一步了解 SQL Server，建议查看我们的 SQL Server Fundamentals 技能路径，熟悉用于数据分析的多种 SQL 技能。

Oracle 中的物化视图

在 Oracle 中创建物化视图的语法与 PostgreSQL 类似。我们还可以指定刷新选项，例如 ON DEMAND 或 ON COMMIT。

-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;

分布式数据库中的物化视图

在支持分布式数据仓库的数据库（如 Azure Synapse 或 Amazon Redshift）中，物化视图还可以指定分布方式以获得更好的性能。

Amazon Redshift 的哈希分布

下面的查询创建名为 sales_summary 的物化视图，按 product_id 汇总总销量与总收入。DISTSTYLE KEY 和 DISTKEY(product_id) 选项确保数据基于 product_id 在各节点间分布，从而提升在该列上进行连接查询时的性能。

-- Create a materialized view with key-based distribution for efficient joins
CREATE MATERIALIZED VIEW sales_summary
DISTSTYLE KEY
DISTKEY(product_id)
AS
SELECT product_id, 
       SUM(quantity) AS total_quantity, 
       SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;

Azure Synapse 的轮询分布

此查询创建名为 sales_summary 的物化视图，按 product_id 汇总总销量与总收入。ROUND_ROBIN 分布会将数据平均分布到各节点，适用于不严重依赖连接的场景。

-- Create a materialized view with round-robin distribution for balanced data storage
CREATE MATERIALIZED VIEW sales_summary
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT product_id, 
       SUM(quantity) AS total_quantity, 
       SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;