数据库查询优化中的统计信息与代价估算 题目描述 在数据库查询优化过程中，优化器需要根据统计信息对不同执行计划的代价进行估算，从而选择最优计划。本题将详解统计信息的类型、收集机制，以及优化器如何利用统计信息估算查询代价（如选择率、基数、代价模型），最终完成执行计划选择。 一、统计信息的作用与类型 作用 ：统计信息是描述表中数据分布特征的元数据，帮助优化器预估查询操作（如扫描、过滤、连接）涉及的数据量，为代价估算提供依据。 核心类型 ： 表级统计 ：行数（ num_rows ）、块数（ num_blocks ）。 列级统计 ： 不同值数量（ num_distinct ）、空值数量（ num_nulls ）。 数据分布直方图（等宽、等高）：记录值的频率分布，解决数据倾斜问题。 索引统计 ：索引层级、叶子块数、聚簇因子（索引顺序与表数据顺序的匹配度）。 二、统计信息的收集与维护 收集方式 ： 手动命令：如Oracle的 DBMS_STATS.GATHER_TABLE_STATS ，MySQL的 ANALYZE TABLE 。 自动任务：数据库定期触发统计信息更新（例如在数据修改量超过阈值时）。 更新策略 ： 全量收集：精度高但资源消耗大，适用于静态表。 增量收集：仅更新变化部分，适合大表。 采样收集：随机抽取部分数据计算统计信息，平衡效率与准确性。 三、代价估算的核心指标 选择率（Selectivity） ： 定义：查询条件过滤后剩余行数占总行数的比例。 计算公式示例： 等值查询（ col = value ）：若值均匀分布，选择率 ≈ 1 / num_distinct 。 范围查询（ col > value ）：利用直方图或假设均匀分布计算比例。 多条件组合： AND条件：选择率 = 各条件选择率的乘积。 OR条件：选择率 = 1 - (1-sel1)(1-sel2)... 基数（Cardinality） ： 定义：操作符输出结果集的预估行数。 公式： 基数 = 表行数 × 选择率 。 示例：表有1000行， status 列有10个不同值，查询 status='active' 的基数 ≈ 1000 × (1/10) = 100 。 四、代价模型与执行计划选择 代价组成 ： I/O代价：数据块读取次数（受索引类型、聚簇因子影响）。 CPU代价：条件计算、排序等操作消耗。 网络代价（分布式数据库）。 优化器工作流程 ： 步骤1：解析SQL，生成逻辑计划（如关系代数树）。 步骤2：利用统计信息计算各物理操作（全表扫描、索引扫描、嵌套循环连接等）的代价。 步骤3：通过动态规划或启发式算法，选择总代价最低的执行计划。 实例分析 ： 查询： SELECT * FROM orders WHERE customer_id=100 AND amount>50 。 优化器对比： 全表扫描代价：直接读取所有数据块。 索引扫描代价：若 customer_id 有索引，先通过索引定位行，再回表过滤 amount 。 选择依据：若 customer_id=100 的选择率低（即满足条件的行少），索引扫描代价更低。 五、统计信息不准确的优化问题 常见场景 ： 数据倾斜：直方图缺失时，等值查询的选择率估算错误。 关联列相关性：如 年=2023 AND 月=12 ，若优化器假设条件独立，会低估选择率。 解决方案 ： 扩展统计信息：收集多列组合的统计信息或表达式统计。 动态采样：执行时实时采集样本数据修正估算。 提示（Hint）：强制指定索引或连接方式（需谨慎使用）。 总结 统计信息是查询优化器的“眼睛”，其准确性直接决定代价估算的可靠性。通过定期收集统计信息、理解数据分布特征，并结合直方图解决倾斜问题，可有效避免执行计划偏差，提升查询性能。