查询优化

在现代数据库系统中，查询优化是数据库管理系统（DBMS）核心组成部分之一，其任务是在保证查询语义正确性的前提下，自动选择一条（或近似最优的）执行计划，使查询能以最小的资源消耗、最高的性能高效完成。优化器需在众多可行的、等价的执行路径中依据统计信息和成本模型甄别优劣，最终生成成本最小的执行计划。本质上，查询优化关注于生成执行开销最小的查询策略，这一过程需要对查询语句逻辑结构的解析、底层数据分布的分析、索引和连接算法等物理实现细节的全面评估，并在保证结果正确性的前提下充分利用系统资源。

查询优化

查询优化器是数据库系统的“决策中枢”，负责将逻辑查询语句翻译成多种等价的物理执行计划（包括多种连接顺序和算法、访问路径等），并通过代价估算机制选择其中的最优方案。该过程对终端用户完全透明，却对系统整体性能起决定性作用。

以高校选课系统为例，假设需要查询“计算机系所有教授开设的课程信息”。对于这样一个多表级联查询，物理执行路径存在多种实现方式：

一种方式是首先获取全部教授信息，再和课程表进行联接，最后过滤出计算机系的记录；另一种方式则是先筛选出计算机系的教授，再与课程表联接。尽管两者在逻辑上等价，但由于涉及数据量和访问顺序的差异，实际执行效率可能相差数个数量级。

以具体场景为例：设教授表包含10万条记录，课程表包含50万条记录，其中计算机系教授仅有200人。若查询“计算机系所有教授开设的课程信息”时未进行优化，将可能采用全表连接，导致需要执行10万 × 50万 = 500亿次比对操作。而如果优化器能够识别先筛选计算机系的200位教授，再与课程表进行联接，实际比对次数将降为200 ×（其所授课程数量），大幅缩减计算量，实现数万倍的性能提升。

数据库查询优化面临的最大挑战是搜索空间的巨大性。对于一个涉及多个表的复杂查询，可能的执行计划数量呈指数级增长。以5个表的连接查询为例，仅仅是连接顺序就有超过1680种可能，如果考虑不同的连接算法和访问路径，可能的执行计划数量将达到数十万甚至数百万种。另一个重要挑战是成本估算的准确性。查询优化器需要在实际执行之前就预测每种执行计划的性能，这需要依赖统计信息和成本模型。然而，数据分布的复杂性和查询模式的多样性使得精确的成本估算变得极其困难。

即使是最先进的查询优化器，其选择的执行计划也只是“近似最优”而非“绝对最优”。但在实际应用中，这种近似已经足以带来巨大的性能提升。

查询优化的过程可以分为三个核心阶段：首先是等价变换阶段，通过关系代数的等价规则生成逻辑上等价但形式不同的查询表达式；其次是物理计划生成阶段，为每种逻辑表达式选择具体的实现算法；最后是成本评估与选择阶段，计算每种物理计划的预期成本并选择最优方案。这三个阶段相互交织，形成了现代数据库系统查询优化的基本框架。

等价变换

在关系型数据库查询优化中，等价变换是基于关系代数和集合论理论基础的一系列变换规则，其核心作用在于保证在查询语义完全等价的前提下，对查询表达式的结构和执行顺序进行系统性重写，以便为后续物理执行计划的生成提供更优的候选空间。等价变换是优化器规则集的理论支撑，为高级优化算法（如基于规则的优化、基于成本的优化）奠定了基础。

等价变换

严格地讲，若两个关系代数表达式在任意合法数据库实例D下，对于所有的输入数据总是返回相同的结果集合（忽略元组顺序），则认为这两个表达式在集合语义下是等价的。关系代数的等价性通常不涉及投影属性的排列顺序，只要求最终结果集元素无差异。这一专业定义为后续的查询优化提供了严密的数学保障，并极大地扩展了查询重写的操作空间。

以图书管理系统为例，考虑以下典型业务查询：“检索所有‘计算机’类别且价格大于100元的图书信息。”该语义目标可通过多种逻辑等价的关系代数表达式描述：

这两种查询路径在逻辑上完全等价，但执行效率可能大不相同。如果计算机类图书占总图书数量的5%，而价格超过100元的图书占30%，那么先按类别筛选显然更加高效。

核心等价规则

对于由多个逻辑条件组成的复合选择，可以拆分为一系列单一条件的选择操作（如将“年龄在25-35岁且系别为计算机”的查询拆解为“年龄≥25”→“年龄≤35”→“系别=计算机”的级联），每步均有机会利用不同索引以提升检索效率。相反，多个简单选择条件也可合并为一个复合条件，从而在部分场景下减少操作复杂度。多重选择操作之间具有交换性，优化器可以基于各条件的选择性（即约束筛选后数据量所占比例）动态调整执行顺序，优先推行筛选效果最强的条件，尽早缩减后续操作处理的数据规模。

连接操作的优化规则

连接的交换律 意味着对于任意关系R和S，有R ⋈ S ≡ S ⋈ R，即二者连接的逻辑顺序互为等价。这一性质保证了优化器在不更改查询结果语义的前提下，可自由调整双表连接的顺序。在实际应用场景如学生选课系统，"学生表 ⋈ 选课表"与"选课表 ⋈ 学生表"逻辑等价，执行结果完全一致。然而，在具体执行层面，不同的连接顺序会显著影响中间结果集的规模、I/O 访问和内存消耗，进而影响整个查询的性能表现。

连接的结合律 指的是对于任意三个关系R、S、T，有 (R ⋈ S) ⋈ T ≡ R ⋈ (S ⋈ T)，即多表连接的嵌套顺序可以任意重新组合，但不影响最终结果的集合语义。此性质对于三表及以上的复杂连接查询尤为关键，因为它极大地丰富了连接顺序的优化空间。

例如，对于“学生（Student）-选课（Enrollment）-课程（Course）”三表连接，可以选择先执行 (学生 ⋈ 选课)，再与课程连接；或者先执行 (选课 ⋈ 课程)，再与学生连接。不同的连接括号顺序将直接决定中间结果集的体量。若选课与课程表的连接大幅度过滤数据，则优先计算 (选课 ⋈ 课程) 能显著降低后续 (学生 ⋈ …) 的处理负担。

因此，基于结合律，查询优化器可对所有可能的连接顺序进行代价估算（结合基数、过滤条件选择性等统计信息），选取产生最小中间结果集、最低I/O和CPU成本的连接方式。

投影操作的优化规则

投影操作的主要职责在于限定查询结果的输出列集合。虽然其表面看似简单，但在关系查询优化中具有重要的理论和实际意义。首先，投影的级联消除律 表明：对于连续多个投影算子链（如 π_X(π_Y(R)) ），仅保留最后一个投影即可，不影响结果的语义正确性，因而任何冗余的中间投影皆可由优化器直接消除，以简化算子树结构及降低系统开销。

此外，投影下推策略是优化过程中一项关键技术。其核心思想在于：只要投影与其他算子（如选择、连接）可交换，即可将投影运算尽可能提前至算子树的底部，缩减后续中间结果的数据宽度（即属性数）。在图书管理系统示例中，若最终查询仅涉及“书名”与“价格”两个属性，则应在尽可能早的阶段对中间结果集施加投影，仅保留相关字段，从而减少网络传输、内存占用及后续算子的处理负担，提高整体查询效率。

选择操作的下推策略

选择下推是关系查询优化领域中极为重要的启发式优化原则之一，其核心思想在于将选择操作（σ）尽可能下推至算子树中靠近数据源的位置，优先对原始关系施加过滤，以最早、最大化地减少中间结果的基数，从而降低后续连接、投影等操作的处理负载与I/O消耗。该策略背后的理论基础在于，大多数选择条件本身具有较高的选择性，能够有效过滤大量无关数据，大幅收缩数据集规模。

以在线零售系统为例，若需检索“2023年北京地区用户购买的电子产品订单”，涉及用户表、订单表、商品表三者。不采用选择下推时，执行计划可能首先直接连接三表（即执行全表笛卡尔积或宽连接），继而对巨型中间结果集做选择，实际处理的数据量将呈指数级膨胀，严重浪费存储与计算资源。而应用选择下推后，我们可以：

首先从用户表中筛选出北京地区的用户
从订单表中筛选出2023年的订单
从商品表中筛选出电子产品
最后将这些已经大幅缩减的数据集进行连接

这种优化可能将数据处理量从数十亿条减少到数万条，性能提升可达数千倍。

选择下推是查询优化中最有效的策略之一，它体现了“先减后算”的优化思想。在设计查询时，我们应该主动将筛选条件写得尽可能具体，以便优化器能够更好地应用这个策略。

然而，选择下推并非总是有益的。在某些特殊情况下，推迟选择操作可能更加高效。比如当选择条件涉及的列没有索引，而连接条件涉及的列有高效索引时，先执行连接可能更好。

连接顺序

在多表连接查询中，连接顺序的选择是影响执行效率的核心因素之一。合理的连接顺序能够极大地降低中间结果集的基数，提升I/O与CPU利用率，并有效支撑后续算子（如索引、物理连接算法）的利用。连接顺序直接决定中间结果的规模和最终的访问路径，对查询性能有数量级的影响。

多表连接顺序优化在查询优化器中属于NP-hard问题，其搜索空间指数级增长。对n个表进行连接，所有可能的二叉树型（left-deep、right-deep、bushy）连接顺序数可用卡塔兰数（Catalan number）刻画，实际可能组合高达n!数量级。例如，n=5时连接顺序方案就多于1000种，n=10时更是上千万量级。

主流数据库优化器通常采用基于动态规划的连接顺序优化算法，如经典的Selinger优化器。DP算法的基本思想是利用最优子结构性质：对于任意关系子集S的最优连接计划π，若π是S内局部最优，则全局最优连接序列必以π为子序列嵌入。优化器会计算所有规模更小子集的最优连接成本，并自底向上递归组合，逐步逼近全体关系的全局最优连接序列。此外，现代优化器还会结合启发式剪枝、连接谓词下推、物理算子选择等策略进一步压缩搜索空间。

下面以企业管理系统为例说明连接顺序优化的重要性。假设查询请求为“检索销售部门经理管理且预算超过100万的项目详情”，涉及员工（Employee）、部门（Department）、项目（Project）三张表的数据联接。合理的连接顺序应优先对部门表进行高选择性筛选（如部门='销售部'），大幅缩减候选数据范围，然后关联员工与项目表，避免生成指数级庞大的中间结果集。如先做全表联接，则中间结果量将骤增，极大增加内存与CPU压力。此外，若相关字段具备索引，还应在连接计划中择优利用，以实现最优物理执行路径。

以企业管理系统的场景为例，若先在部门表筛选出“销售部”（仅1条记录），接着定位该部门的经理（假定仅有数人），再与项目表基于预算条件做连接，可以将整个查询处理量缩减到数百条，极大优化性能；反之，若不合理地先将员工表与项目表做笛卡尔积连接，中间结果会膨胀至数千万甚至更大，导致查询资源消耗剧增与效率下降。

实际工程中，连接顺序优化不仅依赖上述等价变换原则，还需综合考虑物理存储层面诸如索引是否可用、数据分布与聚集性、可用内存等因素。高效的查询优化器会同时分析逻辑与物理成本指标，针对实际场景生成最优执行路径。

统计信息与成本估算

统计信息在查询优化过程中起着至关重要的作用，是优化器进行成本估算和策略选择的基础。缺乏准确的统计信息，查询优化器将无法有效判断数据分布与表的实际状况，导致执行计划选择失误，整体查询性能大幅下降。因此，全面、精确的统计信息是高效查询优化不可或缺的前提。

统计信息与成本估算

数据库统计信息是指数据库系统在物理层面及逻辑层面对表与索引等对象采集和维护的各类元数据，包括但不限于表的元组数、某列不同值数量（基数）、极值范围、值分布（如直方图）、索引覆盖率及分布、空值比例等。查询优化器通过利用这些统计信息对不同执行策略（如访问路径、连接顺序及物理运算方式）的成本进行定量估算，从而生成全局最优或次优的执行计划。统计信息的准确性直接影响优化器对数据分布和操作选择性的判断，进而影响SQL语句的实际执行效率。

例如，在大型电商平台的业务场景下，如果用户发起“检索价格在500-1000元之间的手机产品”查询，查询优化器需依赖详细的统计信息来精确估算：商品表的总记录数、手机类别在所有商品中的占比、目标价格区间内记录的估算数量等。只有据此才能决策是采用索引扫描（若目标区间选择率较低、索引可用）还是全表扫描（若选择率高或无合适索引），以及后续各算子的访问和连接成本。

基础统计信息类型

表级别统计信息

表级别统计信息是查询优化器进行物理执行计划代价估算的基础。**表行数（nr）**直接参与多种操作（如全表扫描、连接、聚合等）的代价建模。对于笛卡尔积、连接顺序的决策，表的元组数量至关重要。例如，电商中的商品表若有100万条记录，而用户表仅有10万行，连接时优先处理小表可显著降低中间结果集的规模与资源消耗。

表的物理存储大小通常以数据块数（br）与块内行数（fr）刻画。优化器通过这些统计元数据，结合数据页大小（如8KB）与单行平均字节数，推算全表扫描、索引回表等操作所需的IO次数。磁盘IO是影响查询响应延迟的主瓶颈，因此准确的表块统计对成本模型收敛至现实值至关重要。

举例说明：订单表有500万记录，单行平均200字节，存储介质数据块为8KB，则每块约存40条记录，总共约需125,000块。查询优化器据此可以准确预测全表扫描的物理IO代价，并据此选择是否采用顺序扫描、并行扫描或索引访问路径。

列级别统计信息

列级统计信息为查询优化器提供关于单一列数据分布的细致洞见，主要包括基数和取值域等核心元数据。其中，基数V(A, r)指关系r中属性A的不同取值数，是评估选择操作选择性的基础指标。例如，某商品类别字段的基数常为数十，而商品ID字段的基数趋近于表的总记录数N——这或直接影响聚集或连接等算子的输出规模估算。基于基数，优化器可采用如下选择性估算公式：

等值谓词： $\text{Selectivity}(\text{A} = a) \approx \frac{1}{V(A, r)}$
IN谓词： $`\text{Selectivity}(\text{A} \in S) \approx \frac{|S|}{V(A, r)}`$

若商品类别字段基数为40，“手机”类占比5%，则 $\text{Selectivity}(\text{category} = '手机') = 5\%$ ，优化器据此判断索引访问或全表扫描何者更优。

取值域通常指某列的最小值与最大值，可用于范围查询选择性的估算。假设单调分布且无极端倾斜，优化器基于最小/最大值与谓词范围估算比例。例如，商品价格区间[1, 50000]，若筛选[1000, 2000]，则理论选择性为 $(2000-1000)/(50000-1) \approx 2\%$ 。在实际工程中，还会结合空值比例（Null Fraction）、最频繁值（Most Frequent Values, MFV）等补充信息修正估算精度。

列值的范围信息包括最小值和最大值，这对于范围查询的选择性估算非常重要。比如商品价格列的范围是1元到50000元，当用户查询价格在1000-2000元之间的商品时，优化器可以基于均匀分布假设估算出大约有20%的商品满足条件（假设价格均匀分布）。

虽然均匀分布假设在很多情况下并不准确，但它为优化器提供了一个合理的起点。现代数据库系统通过直方图等更精细的统计技术来改善这种估算的准确性。

直方图统计信息

直方图是现代关系型数据库系统统计信息模块的核心工具之一。通过将列上可能取值范围划分为不重叠的若干区间（桶，bucket/bin），并统计每个区间内元组出现频数或累计频率，直方图能够精细刻画列值的数据分布特征。相比基于简单均匀分布假设的估算，直方图为选择性评估与成本建模提供了更具现实性的支撑，尤其在面对强烈数据倾斜与异常分布情况下，其作用尤为突出。

例如，假设某在线教育平台的课程评分表，评分列的理论取值为1-5分。若仅依赖均匀分布假设，优化器会错误地认为各评分档位的课程数目大致均等，导致谓词选择性、聚集度等估算严重失真。而通过采样构建直方图可发现，大多数课程的评分主要集中于4-5分区间，3分者相对较少，1-2分区间则极为稀缺。基于这种精细分布，优化器能够更准确地推断如“评分大于4分的课程”这一谓词的实际选择性，并据此生成更优的物理执行方案。

直方图（Histogram）能够对数据分布的非均匀性进行精细建模，是现代查询优化器估算谓词选择性的核心手段。以“评分大于4分的课程”为例，若依赖直方图，优化器能够准确识别真实数据分布——如上案例中，直方图直接反映区间[4.0,5.0]累积频率为60%，故对应谓词估算选择性即为60%。相比之下，若仅采用简单均匀分布假设，优化器只能粗略认为该区间选择性为20%，导致实际计划代价严重偏差，甚至选错执行路径。

业界主流数据库系统实现了多种直方图类型。常见的有：

等宽直方图：将属性值域均分为若干区间，每个区间宽度一致，统计各区间内元组数。此方式简单，但对严重倾斜的数据分布敏感，易丧失高密度区域的表达精度。
等深直方图：将所有样本划分为出现频数近似相同的k个桶，每个桶的元组数基本一致。等深直方图对于反映数据倾斜和极端值表现更优，因为可以自适应分配桶的宽度以捕获密集区域。
混合型/高阶直方图：部分DBMS还支持基于高频值，最大/最小值补全，以及多维直方图协同建模，以进一步提升复杂谓词下的选择性估算精度。

等深直方图，凭借其在捕获分布异质性和保持样本代表性方面的优越性，成为处理高度数据倾斜（如“长尾”分布、热点值等）时的首选。

选择操作的成本估算

选择操作的成本估算是查询优化理论与实践中的核心环节，直接决定了执行计划生成与资源分配的合理性。准确的选择性预估，是后续算子的输出行数、IO成本、CPU资源消耗等一系列代价推断的基石。

等值选择的成本估算

对于典型的等值选择谓词（如WHERE 学号 = '2023001'）、在默认的均匀分布假设下，任何给定取值命中的概率被简化为 $1/v$ ，其中 $v$ 为该属性的基数（Cardinality）。预期输出行数即为表总行数 $N$ 除以基数：

E(\text{等值选择}) = \frac{N}{V(A, r)}

以学生表为例，若 $N=50,000$ ，ID列的基数也为 $50,000$ （每学生唯一），则ID等值选择理论上仅返回一条记录；如筛选专业字段（基数为20），单个专业预期返回 $50,000/20=2,500$ 条记录。

但实际数据分布往往偏离均匀，我们需要关注：

空值占比（Null Fraction）：如目标列空值多，选择性将低于理论估计；
数据倾斜：如“热点值”高频出现，均匀假设大幅低估真实输出。此类场景应优先利用直方图等精细统计。

范围选择的成本估算

范围谓词（如WHERE 年龄 >= 25）的选择性估算则涉及列的取值域信息与分布模型。标准公式为：

\text{Selectivity} = \frac{\text{谓词覆盖的取值区间}}{\text{总取值跨度}}

在均匀分布下，若“年龄”分布为[18, 65]，谓词为>= 25，则

\text{Selectivity} \approx \frac{65-25}{65-18} = \frac{40}{47} \approx 85\%

但如实际分布集中在25-45岁区间，则均匀假设失效，需采用直方图得出更精度选择性。

复合谓词的选择性估算

查询中的复合谓词（如AND/OR链式连接）显著加大选择性评估的复杂度。普遍方法如下：

AND连接(合取)： 假设各谓词独立，输出选择性为各自选择性的连乘。
$\text{Selectivity(AND)} = s_1 \times s_2 \times \ldots \times s_n$
OR连接(析取)： 需考虑互斥与重叠，基本模型为
$\text{Selectivity(OR)} = s_1 + s_2 - s_1 s_2$

例如，针对电商商品表查询“价格100-500元且类别为'电子产品'”，若价格谓词选择性 $s_1=30\%$ ，类别谓词 $s_2=15\%$ ，在独立性假设下总选择性为 $0.3 \times 0.15 = 0.045$ （即4.5%）。

然而，现实中各谓词可能高度相关，如“电子产品”的价格区间分布与整体商品不同。此时简单相乘会系统性误估。现代DBMS采用多维统计（如多列直方图、相关性矩阵）提升复合谓词的选择性亦即输出基数预估的准确率。

连接操作的成本估算

连接操作的成本估算远较简单谓词复杂，核心难点在于多表间的关系模式及连接属性分布。全面建模需要联合考量连接谓词、表基数、连接属性基数、数据分布统计信息、实际连接算法等多维因素。

自然连接的基数估计

自然连接的输出基数与连接属性在参与表中的约束条件密切相关。主要关注以下三种典型场景：

无连接属性（笛卡尔积）
若两个表无重叠属性，则自然连接退化为笛卡尔积，输出基数为 $|R| \times |S|$ ，即各自行数乘积。
单表主键连接
若连接属性在一表上具唯一约束（如主键），如表 $R$ 与 $S$ 在 $A$ 属性连接且 $A$ 为 $R$ 主键，则每个 $S$ 的连接值至多对应 $R$ 中唯一一行，最大输出基数为 $|S|$ （实际为 $s$ 中符合匹配条件的元组数，通常假设全匹配）。
无主键（普通属性连接）
若连接属性两侧皆无唯一性，需要统计估算其匹配概率。
设 $A$ 为连接属性， $R$ 表有 $n_r$ 行， $S$ 表有 $n_s$ 行， $A$ 在 $S$ 中的基数为 $V(A,S)$ 。若数据满足均匀分布且值集完全重叠，估算结果为： `
$E(|R \bowtie S|) = \frac{n_r \times n_s}{\max(V(A,R), V(A,S))}$
`

若只知一侧的基数，通常假定 $`|R \bowtie S| \approx \frac{n_r \times n_s}{V(A,S)}`$ 。

示例：
借阅系统中，读者表 $R$ ( $n_r=10^4$ )与借阅记录表 $S$ ( $n_s=10^5$ )， $S$ 中读者ID基数 $V=5000$ ，估算输出基数： `

|R \bowtie S| \approx \frac{10^4 \times 10^5}{5000} = 2 \times 10^5

典型连接算法的成本分析

主流连接算法的I/O及CPU成本特征如下：

嵌套循环连接（Nested Loop Join）
算法机制：外表每扫描一行，内表全表扫描。
I/O成本（无索引）： $`B_{\text{outer}} + N_{\text{outer}} \times B_{\text{inner}}`$ ，其中 $B$ 为块数， $N$ 为元组数。若外表较大或内表无索引时成本极高。

例：读者表( $100$ 块)为外表，借阅记录表( $2000$ 块)为内表，外表 $10000$ 行，则
$\text{I/O代价} = 100 + 10000 \times 2000 = 2 \times 10^7\; \text{块访问}$
排序归并连接（Sort-Merge Join）
算法机制：先对两表按连接键排序（如外排序），再归并连接。
成本=排序I/O代价（ $O(B\log B)$ ）+ 单次线性归并扫描。
例： $2100$ 块数据，排序I/O约 $2100\times \log_2(2100) \approx 25000$ ，归并扫描忽略不计。
哈希连接（Hash Join）
算法机制：对较小输入表建哈希桶，扫描大表做哈希匹配。假设内存足够、无溢出，I/O成本约为两表块数之和。
例： $100+2000=2100$ 块，是该场景下最优选择。

因此，实际优化器会根据表规模、内存、统计信息、自带索引等多维因素，动态选择连接算法和连接顺序，以最小化总体I/O与CPU资源消耗。

统计信息的维护机制

统计信息的准确性对查询优化结果有决定性影响，而统计信息的收集与维护又会引入额外的系统开销。现代数据库系统需要在统计信息的新鲜度、准确性和维护成本之间实现合理的权衡。

统计信息的更新策略

最直接的更新策略是在每次数据变更后立即同步统计信息，但这种做法会极大增加事务延迟和系统负载。在高并发生产环境下，实时更新往往不可取。

更具工程可行性的做法是采用延迟批量更新模型，即在数据库空闲时段按需批量刷新统计信息。主流数据库（如Oracle、SQL Server、PostgreSQL等）通常内置自动统计信息更新功能，能够在检测到表的变更比例超出设定阈值（如10%或收集窗口内行数变化）后自动触发更新。这种机制可以在保证统计信息有效性的同时，显著降低运行时维护开销。

此外，部分系统还支持基于查询偏差反馈的更新机制。查询优化器在发现实际运行结果与基于当前统计信息估算的基数偏差较大时，会触发相关表的统计信息重新收集，从而动态维持高频表或关键查询路径的统计信息精度。

统计信息的采样与抽样优化

针对海量数据表，采用全量扫描收集统计信息在时空开销上难以接受。因此，高效的统计信息收集高度依赖于采样算法。

通用做法是采用分层随机采样（如系统抽样、分区抽样、分块抽样等），利用较小比例（通常低于1%）的样本数据推断全表的直方图、分布等统计特性。合理设计的采样策略可在极低概率下获得高置信度的统计估算，平衡了精度和性能。例如，针对1千万行的商品表，仅抽取10万行样本即能稳健刻画属性分布，采集总耗时可从数小时压缩至数分钟。

统计信息自动化维护已成为现代数据库系统的标准能力，但数据库架构师和运维工程师仍需深入理解其原理，在系统配置和应用设计阶段合理设定统计信息的收集策略、采样精度与刷新时机，确保优化器决策基础的准确性和系统整体资源利用率的最优化。

查询执行计划的选择

查询执行计划的选择是查询优化过程的核心与关键步骤。优化器需要在众多可能的物理执行计划中，基于数据分布、资源利用率以及系统代价模型，系统化地评估不同方案，最终选择能够以最优资源消耗（如I/O、CPU、内存等）获得正确查询结果的计划。该选择过程直接决定了SQL语句的实际运行效率与系统整体性能。

查询执行计划的选择

基于成本的优化框架

基于成本的查询优化是现代数据库系统的标准方法。这种方法的核心思想是为每种可能的执行计划计算一个数值化的成本估计，然后选择成本最低的计划。这个看似简单的思路背后，隐藏着复杂而精密的技术实现。

成本模型通常包括CPU成本、I/O成本和网络成本三个主要组成部分。在大多数情况下，I/O成本占主导地位，因为磁盘访问的延迟比内存操作高几个数量级。一次磁盘读取可能需要10毫秒，而内存访问只需要几十纳秒，两者相差数十万倍。

让我们通过一个在线视频平台的例子来理解成本计算。假设我们需要查询“用户观看过的所有科幻类电影信息”，涉及用户表、观看记录表和电影表三个表的连接。

在这个例子中，用户表有50万条记录，观看记录表有5000万条记录，电影表有10万条记录。如果采用不当的嵌套循环连接，可能需要进行海量的磁盘I/O操作。而哈希连接通过构建内存哈希表，能够大幅减少I/O次数，成为最优选择。

动态规划在连接优化中的应用

对于涉及多个表的连接查询，连接顺序的选择空间呈指数级增长。三个表的连接有12种不同的顺序，五个表的连接有1680种不同的顺序，而十个表的连接则有超过176亿种可能的顺序。显然，我们不能逐一评估所有可能的方案。

动态规划算法通过巧妙的状态压缩和子问题重用，将这个指数级的搜索空间压缩到可处理的范围内。算法的核心洞察是：如果我们已经知道了某个表集合的最优连接方案，那么包含这个集合的更大表集合的最优方案必然会使用这个已知的最优子方案。

让我们用一个企业ERP系统来说明动态规划优化。假设我们需要查询“2023年IT部门员工参与的高优先级项目的详细信息”，涉及员工表、部门表、项目参与表、项目表四个表。传统的暴力搜索需要评估数千种连接顺序，而动态规划算法只需要评估几十个子问题：

首先计算每个单表的最优访问方案
然后计算每对表的最优连接方案
接着计算每个三表组合的最优连接方案
最后计算四表连接的最优方案

在我们的ERP例子中，如果IT部门只有100名员工，而项目参与表有100万条记录，那么先连接员工表和部门表（筛选出IT部门员工），再与项目参与表连接，会比直接处理全部数据效率高得多。动态规划算法能够自动发现这种最优的连接策略。

启发式优化技术

尽管理论上基于成本的全局最优优化能够获得最佳执行计划，但现实中的查询优化器通常需要在优化效果与优化开销之间寻求平衡。启发式优化依赖一系列业界验证的经验法则，能够在有限的资源和时间约束下，快速生成高质量、接近最优的执行计划。

选择下推

选择下推是查询优化中的核心启发式策略。其原理在于将选择操作（即过滤条件）尽可能提前到执行计划的初始阶段，以便最大程度地减少后续算子需处理的数据量。这样既降低了I/O和CPU负载，也显著提升了查询整体性能。

例如，在视频点播平台的典型场景下，若用户仅查询“2023年上映的科幻电影”，优化器应在连接执行前先对"电影表"进行筛选，只保留满足条件的极小子集，再参与多表连接。此举可将中间结果规模从10万条降至数千条，优化收益明显。

投影下推

投影下推同样是提升查询性能的重要启发式技术。该策略要求在可能的情况下，尽早裁剪掉无用字段，仅保留后续计算和最终结果所需的必要列。这既减少了中间结果的存储和网络传输压力，也提升了运算效率。

以企业人力资源系统为例，若查询只需员工姓名和薪资信息，则应在扫描阶段即剔除地址、联系电话、入职时间等无关字段。对于分布式数据库场景，提前减少字段数量能大幅优化跨节点的数据传输。

连接顺序优化

连接顺序对多表查询的整体性能影响极大。启发式优化常采用如下经验法则：

“以小表驱动大表”：在嵌套循环连接等场景下，应优先将数据量较小的表作为外表，以最小化整体匹配和I/O次数。
“优先连接选择性高的连接”：若某个连接能够大幅缩减中间结果规模，应优先执行该连接，从而为后续操作创造更高效的基础。

这些规则虽然未必保证理论上的全局最优，但在绝大多数真实业务场景下能够显著提升查询执行效率，为复杂查询提供高效、可扩展的优化支持。

现代优化器

现代数据库查询优化器已远超传统的基于固定代价估算的系统，融合了多项前沿技术以应对复杂多变的生产环境。

早期的优化器仅于执行前静态生成执行计划，难以应对实际环境下的动态变化。自适应查询优化通过在执行阶段根据实时统计与运行反馈动态调整执行策略。例如，若优化器估算某连接操作将产生1万条结果，实际仅输出100条，自适应机制可实时调整后续算子，如动态切换哈希连接为嵌套循环连接，从而提升资源利用率与整体性能表现。

现代数据库普遍部署于具备多核处理器的服务器之上，优化器需智能决定并行策略，将任务分解为可并发执行的子计划。并行化核心包括并行度估算与分区方式选择。对大表扫描，可按数据块分区，由多个线程并行处理。对多表连接，可采用分区连接、流水线并行等手段，有效提高吞吐量并降低响应时间。

基于学习的代价估算

伴随机器学习技术的发展，部分先进优化器已引入自适应学习能力，能够基于历史查询反馈持续校正成本模型，突破传统统计信息假设的局限。特别是在数据分布高度倾斜且列间高度相关的场景下，自学习优化器能显著提升估算准确性，优化执行计划质量。

现代查询优化器是数据库内核中最复杂且关键的模块之一。虽然开发者通常无需直接干预其内部逻辑，但理解其原理有助于编写高性能SQL、诊断性能瓶颈、合理利用优化器相关提示，从而显著提升业务系统的数据库性能。

物化视图与查询加速

物化视图是一种将复杂查询结果物理化存储的持久化数据库对象，本质上相当于查询结果集的“快照”或“缓存”。通过将频繁或高计算代价的查询结果预先计算并保存，数据库可以在后续接收到相同或相关查询请求时，直接访问物化视图而无需重新执行底层复杂的聚合和连接操作，从而极大提升查询响应性能。

物化视图与查询加速

以大型电商平台为例，日常业务需要生成多维度、高频次的数据统计报表，如按地域统计销售额、按商品类别聚合订单量、按时间粒度分析用户活跃度等。传统方式下，针对数千万、甚至上亿条明细数据的实时聚合计算不仅消耗大量IO和CPU资源，还可能导致查询延迟过高，难以满足实时性和高并发性需求。通过物化视图，平台可将关键统计指标的聚合结果进行周期性或实时物化，极大缩短报表查询的响应延迟。例如，对于每日销售汇总类分析，物化视图可以提前计算并持久化当天订单的数量、总销售额、平均订单金额等核心维度指标，业务层用户查询时仅需访问已物化的视图，查询响应从原始的分钟级提升至毫秒级，显著提升用户体验及系统吞吐能力。

以下给出典型应用场景：针对电商平台的日销售统计需求，需要对当日订单数据进行汇总，提取订单数量、总金额及平均订单金额等字段。

|
-- 原始复杂查询
SELECT 
    DATE(order_time) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value
FROM orders 
WHERE order_time >= '2023-01-01'
GROUP BY DATE(order_time);

如果订单表有5000万条记录，这个查询可能需要扫描整个表并进行分组聚合，耗时可能达到数分钟。但如果我们创建了对应的物化视图：

|
-- 物化视图定义
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE(order_time) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value
FROM orders 
GROUP BY DATE(order_time);

那么查询日销售数据就变成了简单的表查找操作，响应时间可以缩短到毫秒级别。

增量视图维护的技术原理

物化视图在实际应用中面临的核心挑战是如何高效、准确地保持其内容与底层数据表（基表）的强一致性。在生产环境中，随着基础表数据的不断插入、更新和删除，依赖全量重建物化视图的方式不仅资源消耗巨大、不可扩展，且难以满足时效性与大规模数据环境下的性能要求。

针对上述挑战，数据库系统普遍采用增量视图维护机制，其基本原理是针对基表的数据变动，仅计算和应用这些变更对物化视图产生的“增量”影响，实现最小范围、最小开销的视图同步更新。

聚合视图的增量维护方法

对于包含如 COUNT、SUM、AVG 等聚合操作的物化视图，增量维护需要精确追踪每一条基础数据变化对聚合结果集的影响。以电商场景下的日销售报表为例：

插入（INSERT）操作：新增如金额为500元的订单时，系统需定位对应的日期聚合分组，将 order_count 增加1，total_sales 增加500，并据此基于最新的总和与数量动态重算 avg_order_value。
删除（DELETE）操作：删除订单时，需要将目标分组的 order_count 减1，total_sales 减去该订单金额，重新计算平均值，确保聚合指标的准确性。
更新（UPDATE）操作：订单更新相当于先删除旧值、再插入新值，维护时需依次处理上述两个步骤对聚合视图的影响。

值得注意的是，出于精确维护平均值（AVG）等派生聚合的需要，系统通常不会直接存储平均值本身，而是维护原始的数量（COUNT）和总和（SUM）信息，以便支持任何时刻的及时、正确重算。这要求在视图及其增量维护逻辑中，设计专门的数据结构和计算路径，以兼顾性能与一致性。

连接视图的增量维护

对于包含连接操作的物化视图，增量维护变得更加复杂。考虑一个包含用户信息和订单信息的物化视图：

|
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT 
    u.user_id,
    u.user_name,
    u.user_level,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name, u.user_level;

在订单表发生插入操作时，增量视图维护流程包括：

精确定位对应用户（通过主键 user_id 匹配）
增量更新该用户的聚合指标，如 order_count（订单数）和 total_spent（总消费金额）
若该用户首次出现订单，需在物化视图中新建对应用户汇总记录

对于用户表的信息更新（如用户等级变更），处理方式为：

定位物化视图中的目标用户记录
实时同步更新相关维度属性（如用户等级、姓名等）
保持聚合度量项（订单数、总金额等）不变，因为订单数据未发生变更

查询重写与视图匹配

现代数据库系统的查询优化器具有智能的视图匹配能力，能够自动识别哪些查询可以通过已存在的物化视图来加速。这个过程被称为查询重写。

自动视图匹配

考虑我们已经创建了按日期统计的销售汇总视图，现在用户提交了一个查询“2023年3月的总销售额”。虽然用户的查询与物化视图的定义并不完全相同，但智能的优化器可以识别出这个查询可以通过汇总物化视图中2023年3月的所有记录来获得答案。

|
-- 用户查询
SELECT SUM(total_amount) 
FROM orders 
WHERE order_time BETWEEN '2023-03-01' AND '2023-03-31';
 
-- 优化器重写后的查询
SELECT SUM(total_sales) 
FROM daily_sales_summary 
WHERE order_date BETWEEN '2023-03-01' AND '2023-03-31';

这种重写不仅避免了扫描5000万条订单记录，还避免了复杂的聚合计算，查询性能可以提升数百倍。

部分匹配与视图合并

更高级的优化器还能处理部分匹配的情况。比如用户查询需要按地区和日期统计销售额，而我们只有按日期统计的物化视图。在这种情况下，优化器可能会选择使用物化视图来减少部分计算，然后补充执行剩余的计算。这种部分匹配策略需要权衡使用物化视图的收益和额外计算的成本。如果物化视图能够显著减少需要处理的数据量，即使需要额外的计算步骤，但总体性能仍可能有所提升。

物化视图选择策略

在实际应用中，出于存储空间、计算资源和系统复杂性的综合考量，我们无法也不应为每个可能的查询都创建物化视图。因此，需根据查询的重要性、频率、对系统资源的消耗等多维度因素，科学评估最具价值的候选视图进行物化，实现性能与资源占用的最佳平衡。

查询频率与业务重要性

高频次查询更适合物化视图优化。在实际系统设计中，需优先考虑优化执行频率高的核心报表或分析型查询。例如，每日运行数千次的财务报表查询，其优化价值显著高于仅偶尔执行的历史分析类查询。同时，需结合业务关键性评估：对业务决策影响重大的查询（如高层管理仪表盘）优先级应高于一般性报表。

计算复杂度与数据处理规模

查询逻辑的复杂性及其涉及的数据规模是物化视图收益评估的重要指标。对于需关联大量表、包含复杂分组与聚合逻辑，或需处理海量数据的查询，通过物化视图可大幅降低计算资源消耗，提高响应性能。反之，对于单表、轻计算类查询，物化视图带来的边际收益有限。

基础数据更新频率

基础表的数据变更频率直接决定物化视图的维护代价。高频变更的数据会显著增加物化视图的刷新和一致性维护压力，甚至影响系统整体性能。因此，物化视图更适用于数据变动较小、或历史归档数据的统计与聚合场景。例如，历史订单数据通常稳定，基于其构建的统计视图维护成本低且性能提升明显；而如实时库存等高频变更的业务，物化视图则需谨慎使用，以避免维护开销过高。

现代物化视图技术

现代关系型数据库和分布式数据平台通常提供多种物化视图刷新机制，可针对不同业务场景进行最优配置：

刷新方式	描述	适用场景	优缺点说明
完全刷新	定期全量重建物化视图表。实现简单。	源数据变动频繁、视图逻辑简单或无高实时性要求	优点：方式简单；缺点：大数据量下性能和资源消耗高
增量刷新	只处理自上次刷新以来的变更数据（如日志增量、变更捕获）。支持细粒度更新，提升刷新效率。	大数据量、低延迟要求场景	优点：显著降低维护开销、提升效率；缺点：实现与一致性控制复杂
延迟刷新	多次数据变更合并后分批刷新，降低系统负载但会有暂时性不一致。	对实时性要求较低、需资源利用率优化的场景	优点：节省资源；缺点：数据短暂不一致，影响实时性

智能物化视图推荐与管理

主流数据平台（如 Snowflake、BigQuery、阿里云 AnalyticDB 等）已逐渐实现自动化物化视图推荐与管理，系统会基于历史查询日志、负载分析、表结构及数据分布等多维度信息，自动识别高频访问、复杂计算和资源消耗较大的热点查询，并综合考虑查询延迟、资源（CPU、I/O、内存）消耗、聚合和扫描数据量，以及视图物化带来的存储与一致性维护成本，智能生成推荐视图清单或直接自动创建相应物化视图，实现整体查询性能最优。此类智能化机制显著提升了系统吞吐与关键报表交互性能，同时有效降低人工调优和维护开销。

物化视图本质上是以空间换取时间的高阶优化手段。策略制定时，需详细权衡查询性能增益、存储资源消耗及刷新维护的系统开销。针对不同业务场景，灵活选择物化视图粒度、刷新模式与维护周期，配合索引、分区等其他数据库优化机制，方可实现资源利用和系统性能的双重最优。

高级查询优化技术

随着数据规模持续扩展与查询复杂性的增加，传统查询优化方法在性能和可扩展性方面面临日益严峻的挑战。高级查询优化技术通过引入创新算法、深度结合现代硬件架构、以及智能化优化策略，从而显著提升数据库系统的整体查询性能与资源利用效率。

高级查询优化技术

Top-K 查询优化

在实际业务场景中，用户常常只关注查询结果的“Top-K”部分（即前K条最优结果），无需获取和处理全部数据。例如，电商平台查询销量排名前十的商品、社交媒体获取最新的N条动态，或搜索引擎返回最相关的前若干结果。

传统处理流程通常先拉取并排序全部结果集，最后选取前K项。当原始结果集中元素数量极大时（如千万级商品列表），这种处理方式会造成极大的计算和资源浪费。

Top-K 查询优化的核心在于：在执行查询的过程中实时维护一个大小为K的最优（或最小）堆，仅保留当前发现的K个最佳候选，从而避免了全量数据排序和存储的高昂开销。该方法可以显著降低排序操作的算法复杂度，综合提高查询的响应能力和系统吞吐。

以电商平台查询“价格在1000-5000元之间销量最高的5款手机”为例，传统方式需先筛选出所有符合价格区间的手机（样本量可能极大），再对所有候选机型按销量进行全量排序，最后取出前5条记录，整个过程不仅计算耗时，资源开销也较高。而采用Top-K查询优化，系统通过扫描价格索引筛选合格手机的同时，实时维护一个容量为5的最小堆，每发现一款销量更高的手机即动态更新堆内容，最终仅需一次扫描即可获得销量排名前5的机型。该方法可将算法的时间复杂度由O(n log n)显著降为O(n log k)，当k远小于n时，极大提升了查询效率与系统吞吐能力。

嵌套子查询优化

SQL查询中的嵌套子查询是表达复杂业务逻辑的重要工具，但也是查询优化中最具挑战性的问题之一。嵌套子查询的性能问题主要源于其“逐行计算”的执行模式——外层查询的每一行都需要重新执行一次内层子查询。

考虑一个在线教育平台的查询：“找出所有选课人数超过平均选课人数的课程”。

|
-- 问题查询：效率低下
SELECT course_name, enrollment_count
FROM courses c1
WHERE enrollment_count > (
    SELECT AVG(enrollment_count) 
    FROM courses c2
);
 
-- 优化后：子查询去相关化
SELECT course_name, enrollment_count
FROM courses c1
CROSS JOIN (
    SELECT AVG(enrollment_count) as avg_enrollment
    FROM courses
) avg_table
WHERE c1.enrollment_count > avg_table.avg_enrollment;

子查询去相关化是解决这类问题的关键技术。它将相关子查询转换为独立的子查询或连接操作，从而避免重复计算。

存在性子查询的优化

存在性子查询（使用EXISTS或IN）是另一类常见的优化场景。考虑查询“所有至少被一名学生选择的课程”：

|
-- 原始查询
SELECT DISTINCT course_name
FROM courses c
WHERE EXISTS (
    SELECT 1 
    FROM enrollments e 
    WHERE e.course_id = c.course_id
);
 
-- 优化转换为半连接
SELECT DISTINCT c.course_name
FROM courses c
SEMI JOIN enrollments e ON c.course_id = e.course_id;

现代查询优化器能够自动识别这种模式，并将其转换为更高效的半连接操作，避免了逐行执行子查询的开销。

并行查询优化

现代服务器通常具有多核CPU和大容量内存，并行查询优化通过将查询分解为可以并发执行的子任务，充分利用这些硬件资源。

并行扫描与分区处理

对于大表的扫描操作，最直接的并行化方式是将表按行分区，让多个线程并行处理不同的分区。在我们的电商系统中，如果订单表有1亿条记录，我们可以将其分为10个分区，用10个线程并行扫描，理论上可以获得接近10倍的性能提升。

然而，并行化并非只是简单地增加线程数量，其核心挑战在于实现负载均衡、有效处理数据倾斜以及合理的内存管理。负载均衡要求将并行任务均匀分配，避免部分线程空闲而其他线程过载；数据倾斜意味着实际分区间的数据量和计算压力可能存在较大差异，需要动态调整任务划分以优化资源利用；同时，并行执行会显著增加整体内存消耗，因此必须科学地分配和管控内存资源，以防止因内存不足而影响系统性能。

管道化并行执行

除了数据并行，现代数据库系统还支持管道化并行执行，通过将查询拆分为多个阶段并以流水线方式进行处理，不同阶段通过管道高效衔接，实现并发加速。例如在电商分析场景下，流水线中的各阶段可依次包括：扫描订单表并筛选特定时间段的订单、与商品表进行连接获取商品信息、再按商品类别分组并统计销售情况。管道化并行的显著优势在于，后续阶段可以在前一阶段产生部分结果时立即开始处理，实现阶段间的重叠；

自适应查询优化

传统的查询优化是静态的，在查询开始执行前就确定了完整的执行计划。但在复杂的现实环境中，实际的数据分布和系统状态可能与优化器的预期存在较大差异，导致选择的执行计划并非最优。自适应查询优化通过在查询执行过程中监控实际的执行状况，动态调整执行策略。这种技术特别适用于长时间运行的复杂查询。

运行时统计信息收集

自适应优化器在查询执行过程中会专业且持续地收集如各操作实际执行时间、中间结果集大小、内存使用情况以及I/O操作真实成本等统计信息，并在检测到实际情况与预期存在显著偏差时，能够动态调整后续执行策略，提高整体查询性能和资源利用效率。

执行计划的动态切换

考虑一个涉及多表连接的复杂查询。优化器初始选择了哈希连接，但在执行过程中发现其中一个表的实际大小比预期小得多，这时切换到嵌套循环连接可能更加高效。自适应优化器具备这种动态切换的能力，可以在查询执行的关键节点重新评估执行策略，选择更合适的算法。

查询优化的未来趋势

趋势	说明	关键考虑因素
云原生优化	查询优化器需适应弹性资源、多租户环境与分布式存储等云原生场景，不仅要关注计算成本，还需兼顾网络传输、存储访问和资源弹性伸缩。	弹性资源、网络/存储开销、资源动态分配
异构硬件支持	数据中心中的CPU、GPU、FPGA等多种计算设备各有所长，未来优化器要能智能地将任务分配到最适合的硬件上，以提升查询性能。	硬件多样性、任务智能分解与映射
实时优化	面向流式数据和实时查询，优化策略需兼顾历史数据与数据流动态，能够快速响应数据特征变化，满足实时处理需求。	数据流变化、自适应优化、实时反应

实战练习

习题1：学生选课系统的查询优化

假设我们有一个学生选课系统，包含以下表结构：

学生表 (students)：

student_id (主键，整数)
student_name (字符串，学生姓名)
department (字符串，院系)
enrollment_year (整数，入学年份)

课程表 (courses)：

course_id (主键，字符串)
course_name (字符串，课程名称)
teacher_id (整数，外键)
credits (整数，学分)
semester (字符串，学期，如 '2023-秋季')

选课表 (enrollments)：

enrollment_id (主键，整数)
student_id (整数，外键)
course_id (字符串，外键)
grade (字符串，成绩，如 'A', 'B', 'C')
enrollment_date (日期)

教师表 (teachers)：

teacher_id (主键，整数)
teacher_name (字符串，教师姓名)
department (字符串，所属院系)

请写出查询“计算机系2023级学生选修的数据库课程及其成绩”的SQL语句，并说明这个查询涉及哪些连接操作以及可能的优化策略。

|
-- 优化后的查询，使用等价变换和连接顺序优化
SELECT
    s.student_name,
    c.course_name,
    e.grade,
    t.teacher_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
JOIN teachers t ON c.teacher_id = t.teacher_id
WHERE s.department = '计算机系'
    AND s.enrollment_year = 2023
    AND c.course_name LIKE '%数据库%';
 
-- 优化策略：
-- 1. 先筛选计算机系2023级学生（选择下推）
-- 2. 连接选课表获取选课记录
-- 3. 连接课程表筛选数据库课程
-- 4. 连接教师表获取教师信息
-- 5. 可以考虑为 department、enrollment_year、course_name 创建索引

习题2：电商平台的销售统计查询

假设有一个电商平台，包含以下表结构：

订单表 (orders)：

order_id (主键，字符串)
customer_id (整数，外键)
order_date (日期)
total_amount (数值，总金额)
status (字符串，订单状态)

订单明细表 (order_items)：

item_id (主键，整数)
order_id (字符串，外键)
product_id (整数，外键)
quantity (整数，数量)
unit_price (数值，单价)

商品表 (products)：

product_id (主键，整数)
product_name (字符串，商品名称)
category (字符串，商品类别)
price (数值，商品价格)
stock_quantity (整数，库存数量)

客户表 (customers)：

customer_id (主键，整数)
customer_name (字符串，客户姓名)
city (字符串，城市)
registration_date (日期)

查询“2023年上海市客户购买的电子产品销售Top 5”，请写出相应的SQL语句，并分析查询的成本估算因素。

|
-- Top-K查询优化版本
SELECT
    c.customer_name,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) as total_sales
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.city = '上海'
    AND o.order_date >= '2023-01-01'
    AND o.order_date < '2024-01-01'
    AND p.category = '电子产品'
    AND o.status = '已完成'
GROUP BY c.customer_id, c.customer_name, p.product_id, p.product_name
ORDER BY total_sales DESC
LIMIT 5;
 
-- 成本估算因素：
-- 1. 选择性：上海市客户比例、电子产品类别比例、2023年订单比例
-- 2. 连接顺序：建议先筛选城市和时间，然后连接订单和商品
-- 3. 索引：customer(city), orders(order_date, status), products(category)
-- 4. Top-K优化：使用堆排序，只维护前5名，避免全量排序

习题3：物化视图的增量维护

基于习题2的电商平台表结构，如果我们要创建一个物化视图来存储月度销售汇总，请设计物化视图的结构，并说明当有新订单插入时如何进行增量维护。

|
-- 创建月度销售汇总物化视图
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', o.order_date) as sale_month,
    p.category,
    c.city,
    COUNT(DISTINCT o.order_id) as order_count,
    SUM(oi.quantity * oi.unit_price) as total_sales,
    AVG(oi.quantity * oi.unit_price) as avg_order_value,
    COUNT(DISTINCT c.customer_id) as customer_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = '已完成'
GROUP BY DATE_TRUNC('month', o.order_date), p.category, c.city;
 
-- 新订单插入时的增量维护逻辑：
-- 假设新订单：order_id='ORD123', customer_id=1001, order_date='2023-12-15',
-- 包含商品：电子产品，数量2，单价500，总金额1000，城市='上海'
 
-- 增量更新步骤：
UPDATE monthly_sales_summary
SET
    order_count = order_count + 1,
    total_sales = total_sales + 1000,
    avg_order_value = (total_sales + 1000) / (order_count + 1),
    customer_count = customer_count + 1  -- 如果是新客户
WHERE sale_month = '2023-12-01'
    AND category = '电子产品'
    AND city = '上海';
 
-- 如果该月该类别该城市还没有记录，则插入新记录
INSERT INTO monthly_sales_summary (sale_month, category, city, order_count, total_sales, avg_order_value, customer_count)
SELECT '2023-12-01', '电子产品', '上海', 1, 1000, 1000, 1
WHERE NOT EXISTS (
    SELECT 1 FROM monthly_sales_summary
    WHERE sale_month = '2023-12-01' AND category = '电子产品' AND city = '上海'
);

习题4：统计信息在查询优化中的应用

使用习题1的学生选课系统表结构，假设以下统计信息：

students表：10000行，department基数为20，enrollment_year基数为10
courses表：500行，course_name中包含"数据库"的记录约50行
enrollments表：50000行，student_id基数为8000，course_id基数为400

请估算查询“计算机系2023级学生选修数据库课程的人数”的选择性，并说明如何优化这个查询。

|
-- 原始查询
SELECT COUNT(DISTINCT s.student_id)
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.department = '计算机系'
    AND s.enrollment_year = 2023
    AND c.course_name LIKE '%数据库%';
 
-- 选择性估算：
-- 1. department = '计算机系'的选择性：1/20 = 0.05 (500名学生)
-- 2. enrollment_year = 2023的选择性：1/10 = 0.1 (1000名学生)
-- 3. course_name LIKE '%数据库%'的选择性：50/500 = 0.1
-- 4. 综合选择性（AND条件）：0.05 * 0.1 * 0.1 = 0.0005
 
-- 预期结果行数：10000 * 0.0005 = 5名学生
 
-- 优化策略：
-- 1. 创建复合索引：students(department, enrollment_year)
-- 2. 创建索引：courses(course_name)
-- 3. 连接顺序：先筛选学生表，然后连接选课表，最后连接课程表
-- 4. 考虑使用DISTINCT优化，避免不必要的排序
 
-- 优化后的查询计划：
-- 1. 索引扫描students表，筛选计算机系2023级学生 (约500行)
-- 2. 连接enrollments表，获取这些学生的选课记录 (约1000-2000行)
-- 3. 连接courses表，筛选数据库课程 (约5-10行)
-- 4. 去重计数得到最终结果

习题5：复杂查询的等价变换

基于电商平台表结构（习题2），将以下嵌套子查询转换为等价的连接查询，并分析两种写法的性能差异。

|
-- 原始嵌套子查询
SELECT DISTINCT c.customer_name
FROM customers c
WHERE c.city = '北京'
  AND EXISTS (
      SELECT 1
      FROM orders o
      JOIN order_items oi ON o.order_id = oi.order_id
      JOIN products p ON oi.product_id = p.product_id
      WHERE o.customer_id = c.customer_id
        AND p.category = '电子产品'
        AND o.order_date >= '2023-01-01'
        AND o.total_amount > (
            SELECT AVG(o2.total_amount)
            FROM orders o2
            WHERE o2.customer_id = c.customer_id
        )
  );
 
-- 转换为等价连接查询
WITH customer_avg_order AS (
    SELECT
        customer_id,
        AVG(total_amount) as avg_order_amount
    FROM orders
    GROUP BY customer_id
)
SELECT DISTINCT c.customer_name
FROM customers c
JOIN customer_avg_order cao ON c.customer_id = cao.customer_id
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.city = '北京'
    AND p.category = '电子产品'
    AND o.order_date >= '2023-01-01'
    AND o.total_amount > cao.avg_order_amount;
 
-- 性能分析：
-- 嵌套子查询问题：
-- 1. 对于每个北京客户，都要重新计算该客户的平均订单金额
-- 2. EXISTS子查询需要逐行执行，效率低下
-- 3. 可能导致大量重复计算
 
-- 连接查询优化：
-- 1. 先一次性计算所有客户的平均订单金额（CTE）
-- 2. 使用连接替代嵌套子查询，避免重复计算
-- 3. 可以利用索引进行优化
-- 4. 减少了子查询的执行次数
 
-- 建议索引：
-- customers(city, customer_id)
-- orders(customer_id, order_date, total_amount)
-- order_items(order_id, product_id)
-- products(product_id, category)

查询优化

以高校选课系统为例，假设需要查询“计算机系所有教授开设的课程信息”。对于这样一个多表级联查询，物理执行路径存在多种实现方式：

即使是最先进的查询优化器，其选择的执行计划也只是“近似最优”而非“绝对最优”。但在实际应用中，这种近似已经足以带来巨大的性能提升。

等价变换

核心等价规则

连接操作的优化规则

投影操作的优化规则

选择操作的下推策略

首先从用户表中筛选出北京地区的用户
从订单表中筛选出2023年的订单
从商品表中筛选出电子产品
最后将这些已经大幅缩减的数据集进行连接

这种优化可能将数据处理量从数十亿条减少到数万条，性能提升可达数千倍。

连接顺序

统计信息与成本估算

基础统计信息类型

表级别统计信息

列级别统计信息

等值谓词： $\text{Selectivity}(\text{A} = a) \approx \frac{1}{V(A, r)}$
IN谓词： $`\text{Selectivity}(\text{A} \in S) \approx \frac{|S|}{V(A, r)}`$

若商品类别字段基数为40，“手机”类占比5%，则 $\text{Selectivity}(\text{category} = '手机') = 5\%$ ，优化器据此判断索引访问或全表扫描何者更优。

直方图统计信息

业界主流数据库系统实现了多种直方图类型。常见的有：

等宽直方图：将属性值域均分为若干区间，每个区间宽度一致，统计各区间内元组数。此方式简单，但对严重倾斜的数据分布敏感，易丧失高密度区域的表达精度。
等深直方图：将所有样本划分为出现频数近似相同的k个桶，每个桶的元组数基本一致。等深直方图对于反映数据倾斜和极端值表现更优，因为可以自适应分配桶的宽度以捕获密集区域。
混合型/高阶直方图：部分DBMS还支持基于高频值，最大/最小值补全，以及多维直方图协同建模，以进一步提升复杂谓词下的选择性估算精度。

等深直方图，凭借其在捕获分布异质性和保持样本代表性方面的优越性，成为处理高度数据倾斜（如“长尾”分布、热点值等）时的首选。

选择操作的成本估算

等值选择的成本估算

E(\text{等值选择}) = \frac{N}{V(A, r)}

但实际数据分布往往偏离均匀，我们需要关注：

空值占比（Null Fraction）：如目标列空值多，选择性将低于理论估计；
数据倾斜：如“热点值”高频出现，均匀假设大幅低估真实输出。此类场景应优先利用直方图等精细统计。

范围选择的成本估算

范围谓词（如WHERE 年龄 >= 25）的选择性估算则涉及列的取值域信息与分布模型。标准公式为：

\text{Selectivity} = \frac{\text{谓词覆盖的取值区间}}{\text{总取值跨度}}

在均匀分布下，若“年龄”分布为[18, 65]，谓词为>= 25，则

\text{Selectivity} \approx \frac{65-25}{65-18} = \frac{40}{47} \approx 85\%

但如实际分布集中在25-45岁区间，则均匀假设失效，需采用直方图得出更精度选择性。

复合谓词的选择性估算

查询中的复合谓词（如AND/OR链式连接）显著加大选择性评估的复杂度。普遍方法如下：

AND连接(合取)： 假设各谓词独立，输出选择性为各自选择性的连乘。
$\text{Selectivity(AND)} = s_1 \times s_2 \times \ldots \times s_n$
OR连接(析取)： 需考虑互斥与重叠，基本模型为
$\text{Selectivity(OR)} = s_1 + s_2 - s_1 s_2$

连接操作的成本估算

自然连接的基数估计

自然连接的输出基数与连接属性在参与表中的约束条件密切相关。主要关注以下三种典型场景：

无连接属性（笛卡尔积）
若两个表无重叠属性，则自然连接退化为笛卡尔积，输出基数为 $|R| \times |S|$ ，即各自行数乘积。
单表主键连接
若连接属性在一表上具唯一约束（如主键），如表 $R$ 与 $S$ 在 $A$ 属性连接且 $A$ 为 $R$ 主键，则每个 $S$ 的连接值至多对应 $R$ 中唯一一行，最大输出基数为 $|S|$ （实际为 $s$ 中符合匹配条件的元组数，通常假设全匹配）。
无主键（普通属性连接）
若连接属性两侧皆无唯一性，需要统计估算其匹配概率。
设 $A$ 为连接属性， $R$ 表有 $n_r$ 行， $S$ 表有 $n_s$ 行， $A$ 在 $S$ 中的基数为 $V(A,S)$ 。若数据满足均匀分布且值集完全重叠，估算结果为： `
$E(|R \bowtie S|) = \frac{n_r \times n_s}{\max(V(A,R), V(A,S))}$
`

若只知一侧的基数，通常假定 $`|R \bowtie S| \approx \frac{n_r \times n_s}{V(A,S)}`$ 。

示例：
借阅系统中，读者表 $R$ ( $n_r=10^4$ )与借阅记录表 $S$ ( $n_s=10^5$ )， $S$ 中读者ID基数 $V=5000$ ，估算输出基数： `

|R \bowtie S| \approx \frac{10^4 \times 10^5}{5000} = 2 \times 10^5

典型连接算法的成本分析

主流连接算法的I/O及CPU成本特征如下：

嵌套循环连接（Nested Loop Join）
算法机制：外表每扫描一行，内表全表扫描。
I/O成本（无索引）： $`B_{\text{outer}} + N_{\text{outer}} \times B_{\text{inner}}`$ ，其中 $B$ 为块数， $N$ 为元组数。若外表较大或内表无索引时成本极高。

例：读者表( $100$ 块)为外表，借阅记录表( $2000$ 块)为内表，外表 $10000$ 行，则
$\text{I/O代价} = 100 + 10000 \times 2000 = 2 \times 10^7\; \text{块访问}$
排序归并连接（Sort-Merge Join）
算法机制：先对两表按连接键排序（如外排序），再归并连接。
成本=排序I/O代价（ $O(B\log B)$ ）+ 单次线性归并扫描。
例： $2100$ 块数据，排序I/O约 $2100\times \log_2(2100) \approx 25000$ ，归并扫描忽略不计。
哈希连接（Hash Join）
算法机制：对较小输入表建哈希桶，扫描大表做哈希匹配。假设内存足够、无溢出，I/O成本约为两表块数之和。
例： $100+2000=2100$ 块，是该场景下最优选择。

因此，实际优化器会根据表规模、内存、统计信息、自带索引等多维因素，动态选择连接算法和连接顺序，以最小化总体I/O与CPU资源消耗。

统计信息的维护机制

统计信息的更新策略

最直接的更新策略是在每次数据变更后立即同步统计信息，但这种做法会极大增加事务延迟和系统负载。在高并发生产环境下，实时更新往往不可取。

统计信息的采样与抽样优化

针对海量数据表，采用全量扫描收集统计信息在时空开销上难以接受。因此，高效的统计信息收集高度依赖于采样算法。

查询执行计划的选择

基于成本的优化框架

动态规划在连接优化中的应用

首先计算每个单表的最优访问方案
然后计算每对表的最优连接方案
接着计算每个三表组合的最优连接方案
最后计算四表连接的最优方案

启发式优化技术

选择下推

投影下推

连接顺序优化

连接顺序对多表查询的整体性能影响极大。启发式优化常采用如下经验法则：

“以小表驱动大表”：在嵌套循环连接等场景下，应优先将数据量较小的表作为外表，以最小化整体匹配和I/O次数。
“优先连接选择性高的连接”：若某个连接能够大幅缩减中间结果规模，应优先执行该连接，从而为后续操作创造更高效的基础。

这些规则虽然未必保证理论上的全局最优，但在绝大多数真实业务场景下能够显著提升查询执行效率，为复杂查询提供高效、可扩展的优化支持。

现代优化器

现代数据库查询优化器已远超传统的基于固定代价估算的系统，融合了多项前沿技术以应对复杂多变的生产环境。

基于学习的代价估算

物化视图与查询加速

以下给出典型应用场景：针对电商平台的日销售统计需求，需要对当日订单数据进行汇总，提取订单数量、总金额及平均订单金额等字段。

|
-- 原始复杂查询
SELECT 
    DATE(order_time) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value
FROM orders 
WHERE order_time >= '2023-01-01'
GROUP BY DATE(order_time);

如果订单表有5000万条记录，这个查询可能需要扫描整个表并进行分组聚合，耗时可能达到数分钟。但如果我们创建了对应的物化视图：

|
-- 物化视图定义
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE(order_time) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value
FROM orders 
GROUP BY DATE(order_time);

那么查询日销售数据就变成了简单的表查找操作，响应时间可以缩短到毫秒级别。

增量视图维护的技术原理

聚合视图的增量维护方法

插入（INSERT）操作：新增如金额为500元的订单时，系统需定位对应的日期聚合分组，将 order_count 增加1，total_sales 增加500，并据此基于最新的总和与数量动态重算 avg_order_value。
删除（DELETE）操作：删除订单时，需要将目标分组的 order_count 减1，total_sales 减去该订单金额，重新计算平均值，确保聚合指标的准确性。
更新（UPDATE）操作：订单更新相当于先删除旧值、再插入新值，维护时需依次处理上述两个步骤对聚合视图的影响。

连接视图的增量维护

对于包含连接操作的物化视图，增量维护变得更加复杂。考虑一个包含用户信息和订单信息的物化视图：

|
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT 
    u.user_id,
    u.user_name,
    u.user_level,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name, u.user_level;

在订单表发生插入操作时，增量视图维护流程包括：

精确定位对应用户（通过主键 user_id 匹配）
增量更新该用户的聚合指标，如 order_count（订单数）和 total_spent（总消费金额）
若该用户首次出现订单，需在物化视图中新建对应用户汇总记录

对于用户表的信息更新（如用户等级变更），处理方式为：

定位物化视图中的目标用户记录
实时同步更新相关维度属性（如用户等级、姓名等）
保持聚合度量项（订单数、总金额等）不变，因为订单数据未发生变更

查询重写与视图匹配

现代数据库系统的查询优化器具有智能的视图匹配能力，能够自动识别哪些查询可以通过已存在的物化视图来加速。这个过程被称为查询重写。

自动视图匹配

|
-- 用户查询
SELECT SUM(total_amount) 
FROM orders 
WHERE order_time BETWEEN '2023-03-01' AND '2023-03-31';
 
-- 优化器重写后的查询
SELECT SUM(total_sales) 
FROM daily_sales_summary 
WHERE order_date BETWEEN '2023-03-01' AND '2023-03-31';

这种重写不仅避免了扫描5000万条订单记录，还避免了复杂的聚合计算，查询性能可以提升数百倍。

部分匹配与视图合并

物化视图选择策略

查询频率与业务重要性

计算复杂度与数据处理规模

基础数据更新频率

现代物化视图技术

现代关系型数据库和分布式数据平台通常提供多种物化视图刷新机制，可针对不同业务场景进行最优配置：

刷新方式	描述	适用场景	优缺点说明
完全刷新	定期全量重建物化视图表。实现简单。	源数据变动频繁、视图逻辑简单或无高实时性要求	优点：方式简单；缺点：大数据量下性能和资源消耗高
增量刷新	只处理自上次刷新以来的变更数据（如日志增量、变更捕获）。支持细粒度更新，提升刷新效率。	大数据量、低延迟要求场景	优点：显著降低维护开销、提升效率；缺点：实现与一致性控制复杂
延迟刷新	多次数据变更合并后分批刷新，降低系统负载但会有暂时性不一致。	对实时性要求较低、需资源利用率优化的场景	优点：节省资源；缺点：数据短暂不一致，影响实时性

智能物化视图推荐与管理

高级查询优化技术

Top-K 查询优化

嵌套子查询优化

考虑一个在线教育平台的查询：“找出所有选课人数超过平均选课人数的课程”。

|
-- 问题查询：效率低下
SELECT course_name, enrollment_count
FROM courses c1
WHERE enrollment_count > (
    SELECT AVG(enrollment_count) 
    FROM courses c2
);
 
-- 优化后：子查询去相关化
SELECT course_name, enrollment_count
FROM courses c1
CROSS JOIN (
    SELECT AVG(enrollment_count) as avg_enrollment
    FROM courses
) avg_table
WHERE c1.enrollment_count > avg_table.avg_enrollment;

子查询去相关化是解决这类问题的关键技术。它将相关子查询转换为独立的子查询或连接操作，从而避免重复计算。

存在性子查询的优化

存在性子查询（使用EXISTS或IN）是另一类常见的优化场景。考虑查询“所有至少被一名学生选择的课程”：

|
-- 原始查询
SELECT DISTINCT course_name
FROM courses c
WHERE EXISTS (
    SELECT 1 
    FROM enrollments e 
    WHERE e.course_id = c.course_id
);
 
-- 优化转换为半连接
SELECT DISTINCT c.course_name
FROM courses c
SEMI JOIN enrollments e ON c.course_id = e.course_id;

现代查询优化器能够自动识别这种模式，并将其转换为更高效的半连接操作，避免了逐行执行子查询的开销。

并行查询优化

现代服务器通常具有多核CPU和大容量内存，并行查询优化通过将查询分解为可以并发执行的子任务，充分利用这些硬件资源。

并行扫描与分区处理

管道化并行执行

自适应查询优化

运行时统计信息收集

执行计划的动态切换

查询优化的未来趋势

趋势	说明	关键考虑因素
云原生优化	查询优化器需适应弹性资源、多租户环境与分布式存储等云原生场景，不仅要关注计算成本，还需兼顾网络传输、存储访问和资源弹性伸缩。	弹性资源、网络/存储开销、资源动态分配
异构硬件支持	数据中心中的CPU、GPU、FPGA等多种计算设备各有所长，未来优化器要能智能地将任务分配到最适合的硬件上，以提升查询性能。	硬件多样性、任务智能分解与映射
实时优化	面向流式数据和实时查询，优化策略需兼顾历史数据与数据流动态，能够快速响应数据特征变化，满足实时处理需求。	数据流变化、自适应优化、实时反应

实战练习

习题1：学生选课系统的查询优化

假设我们有一个学生选课系统，包含以下表结构：

学生表 (students)：

student_id (主键，整数)
student_name (字符串，学生姓名)
department (字符串，院系)
enrollment_year (整数，入学年份)

课程表 (courses)：

course_id (主键，字符串)
course_name (字符串，课程名称)
teacher_id (整数，外键)
credits (整数，学分)
semester (字符串，学期，如 '2023-秋季')

选课表 (enrollments)：

enrollment_id (主键，整数)
student_id (整数，外键)
course_id (字符串，外键)
grade (字符串，成绩，如 'A', 'B', 'C')
enrollment_date (日期)

教师表 (teachers)：

teacher_id (主键，整数)
teacher_name (字符串，教师姓名)
department (字符串，所属院系)

请写出查询“计算机系2023级学生选修的数据库课程及其成绩”的SQL语句，并说明这个查询涉及哪些连接操作以及可能的优化策略。

|
-- 优化后的查询，使用等价变换和连接顺序优化
SELECT
    s.student_name,
    c.course_name,
    e.grade,
    t.teacher_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
JOIN teachers t ON c.teacher_id = t.teacher_id
WHERE s.department = '计算机系'
    AND s.enrollment_year = 2023
    AND c.course_name LIKE '%数据库%';
 
-- 优化策略：
-- 1. 先筛选计算机系2023级学生（选择下推）
-- 2. 连接选课表获取选课记录
-- 3. 连接课程表筛选数据库课程
-- 4. 连接教师表获取教师信息
-- 5. 可以考虑为 department、enrollment_year、course_name 创建索引

习题2：电商平台的销售统计查询

假设有一个电商平台，包含以下表结构：

订单表 (orders)：

order_id (主键，字符串)
customer_id (整数，外键)
order_date (日期)
total_amount (数值，总金额)
status (字符串，订单状态)

订单明细表 (order_items)：

item_id (主键，整数)
order_id (字符串，外键)
product_id (整数，外键)
quantity (整数，数量)
unit_price (数值，单价)

商品表 (products)：

product_id (主键，整数)
product_name (字符串，商品名称)
category (字符串，商品类别)
price (数值，商品价格)
stock_quantity (整数，库存数量)

客户表 (customers)：

customer_id (主键，整数)
customer_name (字符串，客户姓名)
city (字符串，城市)
registration_date (日期)

查询“2023年上海市客户购买的电子产品销售Top 5”，请写出相应的SQL语句，并分析查询的成本估算因素。

|
-- Top-K查询优化版本
SELECT
    c.customer_name,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) as total_sales
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.city = '上海'
    AND o.order_date >= '2023-01-01'
    AND o.order_date < '2024-01-01'
    AND p.category = '电子产品'
    AND o.status = '已完成'
GROUP BY c.customer_id, c.customer_name, p.product_id, p.product_name
ORDER BY total_sales DESC
LIMIT 5;
 
-- 成本估算因素：
-- 1. 选择性：上海市客户比例、电子产品类别比例、2023年订单比例
-- 2. 连接顺序：建议先筛选城市和时间，然后连接订单和商品
-- 3. 索引：customer(city), orders(order_date, status), products(category)
-- 4. Top-K优化：使用堆排序，只维护前5名，避免全量排序

习题3：物化视图的增量维护

|
-- 创建月度销售汇总物化视图
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', o.order_date) as sale_month,
    p.category,
    c.city,
    COUNT(DISTINCT o.order_id) as order_count,
    SUM(oi.quantity * oi.unit_price) as total_sales,
    AVG(oi.quantity * oi.unit_price) as avg_order_value,
    COUNT(DISTINCT c.customer_id) as customer_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = '已完成'
GROUP BY DATE_TRUNC('month', o.order_date), p.category, c.city;
 
-- 新订单插入时的增量维护逻辑：
-- 假设新订单：order_id='ORD123', customer_id=1001, order_date='2023-12-15',
-- 包含商品：电子产品，数量2，单价500，总金额1000，城市='上海'
 
-- 增量更新步骤：
UPDATE monthly_sales_summary
SET
    order_count = order_count + 1,
    total_sales = total_sales + 1000,
    avg_order_value = (total_sales + 1000) / (order_count + 1),
    customer_count = customer_count + 1  -- 如果是新客户
WHERE sale_month = '2023-12-01'
    AND category = '电子产品'
    AND city = '上海';
 
-- 如果该月该类别该城市还没有记录，则插入新记录
INSERT INTO monthly_sales_summary (sale_month, category, city, order_count, total_sales, avg_order_value, customer_count)
SELECT '2023-12-01', '电子产品', '上海', 1, 1000, 1000, 1
WHERE NOT EXISTS (
    SELECT 1 FROM monthly_sales_summary
    WHERE sale_month = '2023-12-01' AND category = '电子产品' AND city = '上海'
);

习题4：统计信息在查询优化中的应用

使用习题1的学生选课系统表结构，假设以下统计信息：

students表：10000行，department基数为20，enrollment_year基数为10
courses表：500行，course_name中包含"数据库"的记录约50行
enrollments表：50000行，student_id基数为8000，course_id基数为400

请估算查询“计算机系2023级学生选修数据库课程的人数”的选择性，并说明如何优化这个查询。

|
-- 原始查询
SELECT COUNT(DISTINCT s.student_id)
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.department = '计算机系'
    AND s.enrollment_year = 2023
    AND c.course_name LIKE '%数据库%';
 
-- 选择性估算：
-- 1. department = '计算机系'的选择性：1/20 = 0.05 (500名学生)
-- 2. enrollment_year = 2023的选择性：1/10 = 0.1 (1000名学生)
-- 3. course_name LIKE '%数据库%'的选择性：50/500 = 0.1
-- 4. 综合选择性（AND条件）：0.05 * 0.1 * 0.1 = 0.0005
 
-- 预期结果行数：10000 * 0.0005 = 5名学生
 
-- 优化策略：
-- 1. 创建复合索引：students(department, enrollment_year)
-- 2. 创建索引：courses(course_name)
-- 3. 连接顺序：先筛选学生表，然后连接选课表，最后连接课程表
-- 4. 考虑使用DISTINCT优化，避免不必要的排序
 
-- 优化后的查询计划：
-- 1. 索引扫描students表，筛选计算机系2023级学生 (约500行)
-- 2. 连接enrollments表，获取这些学生的选课记录 (约1000-2000行)
-- 3. 连接courses表，筛选数据库课程 (约5-10行)
-- 4. 去重计数得到最终结果

习题5：复杂查询的等价变换

基于电商平台表结构（习题2），将以下嵌套子查询转换为等价的连接查询，并分析两种写法的性能差异。

|
-- 原始嵌套子查询
SELECT DISTINCT c.customer_name
FROM customers c
WHERE c.city = '北京'
  AND EXISTS (
      SELECT 1
      FROM orders o
      JOIN order_items oi ON o.order_id = oi.order_id
      JOIN products p ON oi.product_id = p.product_id
      WHERE o.customer_id = c.customer_id
        AND p.category = '电子产品'
        AND o.order_date >= '2023-01-01'
        AND o.total_amount > (
            SELECT AVG(o2.total_amount)
            FROM orders o2
            WHERE o2.customer_id = c.customer_id
        )
  );
 
-- 转换为等价连接查询
WITH customer_avg_order AS (
    SELECT
        customer_id,
        AVG(total_amount) as avg_order_amount
    FROM orders
    GROUP BY customer_id
)
SELECT DISTINCT c.customer_name
FROM customers c
JOIN customer_avg_order cao ON c.customer_id = cao.customer_id
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.city = '北京'
    AND p.category = '电子产品'
    AND o.order_date >= '2023-01-01'
    AND o.total_amount > cao.avg_order_amount;
 
-- 性能分析：
-- 嵌套子查询问题：
-- 1. 对于每个北京客户，都要重新计算该客户的平均订单金额
-- 2. EXISTS子查询需要逐行执行，效率低下
-- 3. 可能导致大量重复计算
 
-- 连接查询优化：
-- 1. 先一次性计算所有客户的平均订单金额（CTE）
-- 2. 使用连接替代嵌套子查询，避免重复计算
-- 3. 可以利用索引进行优化
-- 4. 减少了子查询的执行次数
 
-- 建议索引：
-- customers(city, customer_id)
-- orders(customer_id, order_date, total_amount)
-- order_items(order_id, product_id)
-- products(product_id, category)