数据库查询优化中的统计信息与代价估算 题目描述： 数据库查询优化器的核心任务是为SQL查询选择最优的执行计划，而统计信息与代价估算是这一过程的基石。本题将深入探讨统计信息如何收集与维护、优化器如何利用统计信息估算查询代价，以及常见估算误差的成因与应对策略。 解题过程： 1. 统计信息的核心作用 定义 ：统计信息是描述表中数据分布特征的元数据，例如表的总行数、列的基数（不同值的数量）、数据分布直方图等。 重要性 ：优化器依赖统计信息估算查询结果集大小（基数）和计算不同执行计划的代价（如CPU、I/O开销），从而选择代价最低的计划。 示例 ：若 WHERE age > 30 的查询中，优化器通过直方图得知30岁以上数据占比50%，则可估算扫描一半数据页，进而比较索引扫描与全表扫描的代价。 2. 统计信息的收集与维护 自动收集 ：现代数据库（如MySQL/PostgreSQL）支持定期自动统计信息收集，通过采样数据页更新元数据。 手动触发 ：执行 ANALYZE TABLE （MySQL）或 VACUUM ANALYZE （PostgreSQL）可强制更新统计信息。 更新策略 ：当数据变更超过阈值（如10%行被修改）时触发重新收集，避免统计信息过期导致计划退化。 3. 代价估算的基本原理 代价模型 ：优化器将操作（如扫描、连接、排序）转化为CPU、内存、I/O代价的加权和。例如： 顺序扫描代价 ： 表数据页数 × 单页I/O成本 索引扫描代价 ： 索引高度 × 单页I/O成本 + 匹配行数 × 数据页访问成本 基数估算 ：通过统计信息估算条件筛选后的行数。例如： 等值查询 （ age = 25 ）：若年龄列基数低（值重复率高），则估算行数 = 总行数 / 列基数。 范围查询 （ salary > 5000 ）：利用直方图计算值落入区间的频率。 4. 常见估算误差与优化策略 误差成因 ： 数据偏斜 ：直方图无法完全捕捉极端分布（如90%数据集中在某区间）。 多列关联 ：若 age 和 city 独立统计，查询 age>30 AND city='北京' 可能低估结果集（实际两列相关性高）。 应对措施 ： 扩展统计信息 ：收集多列组合的统计信息（如PostgreSQL的 CREATE STATISTICS ）。 动态采样 ：对复杂条件实时采样少量数据校准估算（如Oracle的动态采样特性）。 查询提示 ：手动指定索引或连接方式（如 FORCE INDEX ），但需谨慎使用。 5. 实战案例分析 场景 ：查询 SELECT * FROM orders WHERE user_id = 100 AND status = 'shipped' 。 优化器决策流程 ： 检查 user_id 和 status 的单独统计信息： user_id 基数高（假设100万用户），估算匹配行数较少。 status 基数低（仅几种状态）， shipped 占比可能较高。 若缺乏多列统计信息，可能错误假设条件独立，低估实际匹配行数（如该用户恰好多为发货状态）。 若估算行数少，优化器选择索引扫描；若估算行数多，可能选择全表扫描。 调优方法 ：对 (user_id, status) 创建联合索引并收集扩展统计信息，提升估算准确性。 总结 ： 统计信息的准确性与代价估算的合理性直接决定查询性能。通过定期维护统计信息、利用扩展统计处理列关联性，并结合实际执行反馈（如慢查询日志）动态调整，可有效规避优化器误判，提升数据库整体效率。