SQL嵌套查询是日常数据库查询中常用的语法结构，允许在一个查询语句中嵌入另一个查询语句，实现复杂的数据筛选与关联逻辑。但在处理大量数据或高频查询场景时，嵌套查询尤其是相关子查询会带来额外的计算开销，导致读性能下降。物化视图作为预先计算并存储查询结果的数据库对象，能够有效减少重复计算，和嵌套查询结合使用可以从根源上优化读请求的执行效率。
SQL嵌套查询的性能痛点
嵌套查询分为非相关子查询和相关子查询两类，其中相关子查询的性能问题更为突出。非相关子查询的执行不依赖外层查询的结果，数据库通常会先执行子查询得到结果集，再和外层查询做关联，多次执行相同查询时仍会重复计算。相关子查询的子查询部分依赖外层查询的字段值，外层查询每遍历一行数据，子查询就需要执行一次，当外层数据量达到万级甚至十万级时，整体执行时间会呈倍数增长。
以下是一段典型的相关嵌套查询示例，用于查询每个部门中薪资高于部门平均薪资的员工信息：
-- 相关嵌套查询示例，查询薪资高于部门平均薪资的员工
SELECT
e1.department_id,
e1.employee_id,
e1.employee_name,
e1.salary
FROM employee e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employee e2
WHERE e2.department_id = e1.department_id
);
如果employee表有10万条数据，部门数量为50个，这段查询会执行10万次子查询，整体耗时可能超过数秒，无法满足高并发读场景的需求。
物化视图的核心特性
物化视图和普通视图的最大区别在于，它会将查询定义的结果集实际存储在磁盘上，而不是每次查询时都重新执行定义语句。物化视图的更新方式分为按需刷新和自动刷新两种，按需刷新需要手动触发更新操作，自动刷新可以设置为在基础表数据变更时触发，或者按照固定的时间间隔更新。
物化视图适合用于以下场景：
- 查询逻辑复杂，包含多表关联、聚合计算的场景
- 基础表数据更新频率低，读请求频率高的场景
- 对查询响应时间要求高，允许短时间数据延迟的场景
创建物化视图的基础语法如下：
-- 创建存储部门平均薪资的物化视图
CREATE MATERIALIZED VIEW mv_department_avg_salary
BUILD IMMEDIATE -- 立即构建物化视图数据
REFRESH ON DEMAND -- 按需刷新
AS
SELECT
department_id,
AVG(salary) AS avg_salary
FROM employee
GROUP BY department_id;
二者组合提升读性能的策略
策略一：将嵌套子查询改写为物化视图关联
对于频繁执行且子查询逻辑固定的嵌套查询，可以把子查询的逻辑提前定义为物化视图，外层查询直接和物化视图做关联，避免重复执行子查询。以上面的部门平均薪资查询为例，改写后的查询如下：
-- 改写后的查询，关联物化视图替代嵌套子查询
SELECT
e.department_id,
e.employee_id,
e.employee_name,
e.salary
FROM employee e
INNER JOIN mv_department_avg_salary mv
ON e.department_id = mv.department_id
WHERE e.salary > mv.avg_salary;
改写后，子查询的聚合逻辑只需要执行一次并存储在物化视图中，每次查询只需要做简单的表关联，执行时间可以从数秒降低到毫秒级。
策略二：多层嵌套查询的分层物化
如果嵌套查询包含多层子查询，且每层子查询都有被其他查询复用的可能，可以将每一层子查询的逻辑分别定义为物化视图，形成分层物化结构。例如先定义基础数据的聚合物化视图，再基于该物化视图定义上层业务查询的物化视图，减少每一层的计算量。
示例：先创建员工薪资基础聚合物化视图，再创建部门薪资等级物化视图：
-- 第一层物化视图：员工薪资基础聚合
CREATE MATERIALIZED VIEW mv_employee_salary_agg
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT
department_id,
employee_id,
salary,
CASE
WHEN salary >= 20000 THEN '高'
WHEN salary >= 10000 THEN '中'
ELSE '低'
END AS salary_level
FROM employee;
-- 第二层物化视图：部门薪资等级统计
CREATE MATERIALIZED VIEW mv_department_salary_level
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT
department_id,
salary_level,
COUNT(*) AS emp_count
FROM mv_employee_salary_agg
GROUP BY department_id, salary_level;
策略三：匹配物化视图的刷新机制
组合使用时需要根据业务的数据一致性要求选择物化视图的刷新策略。如果业务允许分钟级的数据延迟，可以设置物化视图每5分钟自动刷新一次，减少刷新对写性能的影响。如果业务要求数据实时一致，可以设置物化视图在基础表数据变更时自动刷新，但需要评估刷新的性能开销，避免影响写操作的效率。
手动刷新物化视图的语法如下：
-- 手动刷新单个物化视图
EXEC DBMS_MVIEW.REFRESH('mv_department_avg_salary');
-- 手动刷新多个物化视图
EXEC DBMS_MVIEW.REFRESH('mv_department_avg_salary,mv_employee_salary_agg');
注意事项
首先，物化视图会占用额外的存储空间，需要根据服务器的存储容量合理规划物化视图的数量和存储的数据范围，避免存储空间不足。其次，物化视图的刷新操作会消耗数据库的计算资源，高频刷新可能会影响写操作的性能，需要平衡数据一致性和性能的关系。最后，不是所有的嵌套查询都适合和物化视图组合，对于低频执行、逻辑临时变化的查询，直接使用嵌套查询反而更灵活，不需要额外维护物化视图。
| 方案 | 适用场景 | 优势 | 劣势 |
|---|---|---|---|
| 纯嵌套查询 | 低频查询、逻辑多变的查询 | 无需额外维护，灵活度高 | 高频执行时性能差 |
| 嵌套查询+物化视图 | 高频查询、逻辑固定的查询 | 读性能提升明显，支持复用 | 占用存储，需要维护刷新逻辑 |