数据库查询优化中的统计信息与代价估算 题目描述 在数据库查询优化过程中，优化器需要从多种可能的执行计划中选择一个"最优"计划。这个选择并非随机猜测，而是基于一套严密的"代价估算"模型。该模型的核心输入是"统计信息"——描述表中数据分布特征的系统数据。本题将详解统计信息如何收集与存储，优化器如何利用这些信息估算不同操作（如扫描、连接）的代价，并最终选出执行计划。 一、统计信息的作用与内容 统计信息是数据库对表中数据特征的量化描述，如同"地图"之于"地形"。优化器缺少它就无法预估查询需要处理的数据量，可能导致选择全表扫描而非索引扫描等低效计划。关键统计信息包括： 表级统计 ：总行数（cardinality）。 列级统计 ： 不同值的数量（NDV, Number of Distinct Values）。 空值比例。 数据分布直方图（例如等高直方图）：将数据按值范围划分为多个桶（bucket），每个桶记录数值范围、出现频率等，用于估算范围查询的选择性。 索引统计 ：索引的层数（高度）、叶子节点数量等。 二、统计信息的收集与更新 统计信息需定期更新以保持准确性，常见方式： 自动收集 ：数据库在空闲时或当数据变更超过阈值（如10%行被修改）后自动触发。 手动命令 ：如MySQL的 ANALYZE TABLE 、PostgreSQL的 VACUUM ANALYZE 。 动态采样 ：当统计信息缺失或过时，优化器可能临时随机采样少量数据估算。 三、代价估算的核心步骤 优化器通过组合不同操作的代价模型计算总代价（通常以I/O次数、CPU时间为单位）。以查询 SELECT * FROM users WHERE age > 30 为例： 估算选择率（Selectivity） ： 选择率=满足条件的行数/总行数，反映查询的筛选能力。 若 age 直方图显示30岁以上数据占比40%，则选择率=0.4。 计算基数（Cardinality） ： 基数=总行数×选择率。若表有1000行，则基数=1000×0.4=400行。 计算操作代价 ： 全表扫描代价 ：与数据页数量正比（假设每页100行，需10次I/O）。 索引扫描代价 ：=索引遍历代价+回表代价。若索引高度=3，需3次I/O找叶子节点；回表时若400行对应200个数据页（假设每页2行），总代价≈3+200=203次I/O。 对比后，优化器可能选择全表扫描（10次I/O < 203次I/O）。 四、多表连接查询的代价估算 以两表连接 SELECT * FROM A JOIN B ON A.id = B.id 为例： 估算连接结果基数 ： 若 A 有1000行， B 有2000行， id 在 A 中NDV=500，在 B 中NDV=1000。 假设数据均匀分布，连接基数≈(A行数×B行数)/max(NDV_ A, NDV_ B) = (1000×2000)/1000=2000行。 比较连接算法代价 ： 嵌套循环连接 ：适合小表驱动大表，代价=外表扫描代价+外表行数×内表扫描代价。 哈希连接 ：需构建哈希表，代价与两表数据量相关。 排序合并连接 ：涉及排序代价（与数据量对数相关）。 优化器会根据基数估算选择代价最低的算法。 五、统计信息不准确的优化陷阱 若统计信息过期（如 age 字段新增大量>30的数据但直方图未更新），优化器可能低估基数，错误选择索引扫描导致性能下降。此时需手动更新统计信息或提示优化器（如MySQL的 FORCE INDEX ）。 总结 统计信息是查询优化的"眼睛"，代价估算是"决策大脑"。通过直方图等技术量化数据分布，优化器能模拟不同执行计划的资源消耗，最终选出高效路径。实践中需结合监控确保统计信息时效性，必要时介入调整。