数据库的统计信息与查询优化

题目描述：数据库的统计信息是查询优化器进行成本估算和生成高效执行计划的关键依据。它系统地收集和存储关于数据库中数据分布、数据量等关键指标，帮助优化器预测不同查询操作的代价。本知识点将详细讲解统计信息的核心内容、收集维护机制及其在查询优化中的具体应用。

一、统计信息的重要性与核心内容

1. 为什么需要统计信息？
   查询优化器的目标是找到"成本"最低的执行计划。成本主要指I/O、CPU和内存消耗。优化器需要数据特征来估算不同操作（如全表扫描、索引扫描、连接）的代价。没有准确的统计信息，优化器就像盲人摸象，可能选择低效计划。

2. 统计信息包含哪些核心内容？

   • 表级别的统计信息：
     • 行数（Cardinality）：表中的总记录数。
     • 数据页数（Page Count）：表数据占用的磁盘页数。
   • 列级别的统计信息：
     • 不同值数量（Number of Distinct Values, NDV）：该列上有多少个唯一值。这对于判断选择性的好坏至关重要。
     • 空值数量（Number of NULLs）：该列中为NULL的记录数。
     • 数据直方图（Histogram）：描述数据分布情况的最重要工具。它将列值的范围划分为一系列"桶（Bucket）"，并记录每个桶中的值范围和频率。
       • 示例：一个年龄列，值从0到100。直方图可能将其分为10个桶（0-10, 11-20, ...）。优化器通过直方图可以知道"年龄 BETWEEN 25 AND 35"的记录大约占总数的10%，而不是简单地猜测为(35-25)/(100-0) = 10%（这种等值假设在数据分布不均时误差很大）。
     • 最常用值（Most Common Values, MCV）及其频率：对于一些数据倾斜严重的列（如状态列，90%的值都是"有效"），直接记录最常出现的几个值及其出现频率，比直方图更精确。
   • 索引统计信息：
     • 索引的叶级页数。
     • 索引的层级数（B-Tree的高度）。

二、统计信息的收集与维护

1. 如何收集统计信息？
   通常通过专门的命令或任务来触发，例如：

   • 手动收集：执行如ANALYZE TABLE table_name（MySQL）或 EXEC sp_updatestats（SQL Server）或 DBMS_STATS.GATHER_TABLE_STATS（Oracle/PostgreSQL）等命令。
   • 自动收集：大多数数据库系统提供后台作业，在低峰期（如夜间）自动更新统计信息。

2. 何时需要更新统计信息？
   当数据的分布发生显著变化时，陈旧的统计信息会导致优化器产生错误的成本估算。主要场景包括：

   • 大量数据修改后：如执行了大批量的INSERT, UPDATE, DELETE操作。
   • 数据装载后：ETL过程加载了大量新数据后。
   • 索引创建或重建后。

3. 统计信息的采样策略：
   对超大型表进行全表扫描以计算精确的统计信息可能代价高昂。因此，数据库通常支持采样，即只分析一部分数据（如10%的数据块）来估算整体数据的分布，在准确性和性能之间取得平衡。

三、统计信息在查询优化中的工作流程

让我们通过一个具体例子，看优化器如何利用统计信息。

场景：查询部门ID（dept_id）= 10且工资（salary）> 5000的员工。

SELECT * FROM employees WHERE dept_id = 10 AND salary > 5000;

假设在dept_id和salary上都有索引。

优化器的决策过程：

1. 估算每个条件的过滤性（Selectivity）：

   • dept_id = 10：
     • 优化器查询dept_id列的统计信息。假设表有10000行，dept_id的NDV是10。
     • 基础过滤性 = 1 / NDV = 1/10 = 0.1。这意味着大概有10000 * 0.1 = 1000行满足dept_id = 10。
     • 如果存在MCV，优化器会直接查找dept_id=10的频率，可能更精确。
   • salary > 5000：
     • 优化器查询salary列的直方图。
     • 它找到值5000落在哪个桶里。假设5000落在第三个桶，这个桶的范围是4000-6000，包含20%的数据。
     • 优化器会估算salary > 5000的数据占比为 (6000-5000)/(6000-4000) * 20% = 10%。（这是一个简化模型，实际算法更复杂）。
     • 因此，满足该条件的行数估算为10000 * 10% = 1000行。

2. 估算组合条件的过滤性：

   • 优化器假设两个条件是独立的（这是另一个估算点，可能不准确）。
   • 组合过滤性 = 0.1 * 0.1 = 0.01。
   • 最终结果集行数估算 = 10000 * 0.01 = 100行。

3. 比较不同执行计划的成本：

   • 计划A：使用dept_id索引
     • 成本 ≈ 通过索引找到1000行dept_id=10记录的代价 + 回表1000次查找完整数据行的代价 + 在内存中对这1000行数据应用salary > 5000过滤的代价。
   • 计划B：使用salary索引
     • 成本 ≈ 通过索引找到1000行salary>5000记录的代价 + 回表1000次 + 过滤dept_id=10的代价。
   • 计划C：全表扫描
     • 成本 ≈ 顺序读取整个表所有数据页的I/O代价。
   • 优化器利用统计信息（如表页数、索引高度等）计算出每个计划的成本数值。如果表很小，全表扫描成本可能最低。如果dept_id=10的行非常少，计划A成本可能最低。

四、常见问题与最佳实践

1. 统计信息不准确的症状：

   • 查询性能不稳定，时快时慢。
   • 执行计划明显不合理（如大表本应走索引却选择了全表扫描，或反之）。

2. 最佳实践：

   • 定期更新：确保在大量数据变更后更新统计信息。
   • 选择合适的采样率：对大型表，使用足够的采样率以保证准确性，但不要过度影响性能。
   • 理解数据特征：对于数据倾斜严重的列，确保数据库收集了MCV等更精细的统计信息。

通过以上步骤，我们可以看到，统计信息是查询优化器做出明智决策的"眼睛"。维护准确、及时的统计信息是数据库性能调优中最基础且关键的一环。