数据库执行计划缓存机制

可视化演示

展示Plan Cache核心机制：硬解析与软解析流程对比、SQL参数化匹配规则、以及参数嗅探导致的计划偏差与自适应修正。

执行计划生成

后厨第一次接到“水煮鱼”这道没人做过的菜，大厨得从选鱼、片鱼、腌渍、炒料、淋油一步步设计，反复掂量每种做法的火候与成本，最后把定稿工序抄成配方卡。数据库同样如此：一条全新 SQL 进来，优化器要回答“怎么查最快”——是全表扫、走索引，还是先关联再过滤？这套定稿的施工方案，就叫执行计划。

执行计划本质是一棵“操作树”：叶子节点读表或索引，内部节点做连接、过滤、排序。生成它要经过三个车间：语法解析把 SQL 文本变成语法树（好比按语法把点单拆成“主料、配料、做法”）；逻辑优化做等价变换，比如谓词下推——先把能过滤的条件提前执行，减少后面环节的原料量；物理优化则结合索引与统计信息，用代价模型选出最小成本的算子组合。

代价模型是优化器的“成本计算器”，它估计每条路径要读多少个数据页、处理多少行，再按权重折算成总代价，总代价最小的方案胜出：

研发一张卡片很贵，所以聪明的后厨绝不会做完就扔——把卡片挂上墙，下一个点同名菜的客人就能直接照做。这正是缓存存在的意义：把昂贵的“设计工序”沉淀下来，让后续请求坐享其成。

缓存命中条件

客人喊“宫保鸡丁，少辣”，后厨不会拿“宫保鸡丁，正常辣”的卡片应付——必须找到一张一字不差的卡片，连“少辣”这个备注都要对得上。数据库的命中规则比后厨更苛刻：不只是“大概一样”，而是字符级完全一致。

缓存命中的第一条件：SQL 文本逐字符一致。大小写不同、空格多一个、表名是否写全限定，都会让哈希值对不上。别小看这个，很多团队追问“为什么缓存不命中”时，答案就藏在语句格式的不一致里。

第二条件是会话环境一致：数据库名、Schema 相同，好比同名“鱼香肉丝”在不同分店其实是两道菜。第三条件是对象绑定一致：SQL 里引用的表、视图要解析到同一个物理对象，避免同名对象换了库仍被误认为可复用。工程实现上，数据库先对 SQL 文本做哈希，用哈希值快速定位候选，再逐项比对上下文：

点单用语不统一，卡片架再满也帮不上忙。这也是为什么规范 SQL 风格、让应用层统一生成语句，是提升命中率的第一块基石——后厨管这叫“接单前先对暗号”。

SQL参数化

后厨天天接到“少辣”“微辣”“加花生”“不要葱”的变体，每张点单都要重新研发一盘菜？大可不必——只需把口味备注抽成“辣度=?、花生=?”，一张工序卡就能服务所有微调。这就是参数化。

SQL 参数化，就是把查询中的字面量（具体常量值）替换成占位符？，让“结构相同、值不同”的 SQL 归一到同一个模板，从而命中同一份计划。不参数化的危害叫“字面量陷阱”：WHERE id = 5 和 WHERE id = 8 是两个不同的文本，各自硬解析，缓存被成千上万个一次性变体塞满。

数据库的补救手段有两级：简单参数化（自动把无风险的字面量替换为参数）和强制参数化（FORCE PARAMETERIZATION，把能用参数替换的都替换）。但参数化也引出“参数嗅探”风险：优化器在第一次硬解析时用当时的参数值做行数估算，定稿的计划可能不适配后来的数据分布——就像第一位客人的口味被写进了卡片，后面的客人未必觉得好吃：

统一点单口诀，后厨的卡片架利用率飙升；但也要记得，参数化不是银弹。面对数据分布极度不均的表，要结合监控及时“撤回”被带偏的次优卡片，别让一道菜把整本菜谱带沟里。

软硬解析对比

软解析像老厨师翻卡片：扫一眼、验证下食材，几十秒出餐。硬解析像研发新菜：试料、试火、试味，一上午就搭进去了。后厨最怕的是同一天来一百个客人，各点一道从没做过的新菜——这就是数据库的“解析风暴”。

术语上，硬解析（Hard Parse）指缓存未命中时走完整流程：语法解析、语义检查、逻辑/物理优化、生成计划并写入缓存；软解析（Soft Parse）指命中后跳过优化与生成，只做轻量校验直接复用。

硬解析是 CPU 密集且带锁的：优化器要做系统性代价估算，多个会话同时硬解析还会争抢共享内存的 latch，CPU 冲到 100% 而真正执行反而卡住。业界经验中，一次硬解析的开销可达软解析的百倍以上，具体差距视语句复杂度而定。软解析也非零成本——哈希查找、权限校验、对象解析仍在，只是量级小得多：

后厨防“研发风暴”靠的是卡片架上足够多的现成卡片；数据库防解析风暴，靠的是让每条业务 SQL 都能稳定命中——这是所有调优工作的原点，也是“复用价值”这四个字最直白的注脚。

计划失效机制

厨房换了新烤箱，旧卡片写“200 度烤 15 分钟”就不灵了；肉铺的进货价变了，原本“麻辣比清蒸便宜”的判断也要推翻。卡片挂久了，必须重写。计划失效正是数据库的自适应机制——它不是故障，而是纠偏。

导致执行计划失效的常见触发源有三个：统计信息更新（表的行数、数据分布变化超阈值，旧代价估算不再可信）、Schema/索引变更（重建索引、加列、删冗余索引，原计划可能不再可行或不再最优）、以及管理员显式刷新缓存（清空整个缓存池以释放内存或扫除污染）。

失效后会发生什么？缓存条目被标记无效或直接删除，下一次同样的 SQL 重新走硬解析。此外还有“软失效”：SQL Server 的重新编译阈值在数据量变化跨越一定百分比后触发自动重编译；计划老化则让长期不用的计划逐步降级直至被清除。用“有效期”来概括，就是三条倒计时谁先归零，计划就在那一刻作废：

卡片失效并不可怕，可怕的是失效后没有新卡片顶上。所以大促前预热高频计划、日常观察重编译次数，是后厨（DBA）的例行功课——世界变了，菜谱就要跟着变，这本就是自适应系统的本分。

缓存管理策略

卡片架不是无限的。常点的“宫保鸡丁”挂在顺手位，半年没人点的“九转大肠”要撤下来给新菜腾地方。后厨还要定期盘点：哪张卡片被翻得起了毛边（高频复用），哪张落满了灰（冷门）。

数据库的计划缓存是共享内存中的一块区域，有明确的大小上限。超过上限就必须淘汰——淘汰策略普遍是近似 LRU 的变体：越久没被复用的条目越先被清出。不少数据库还会做“代价加权”：一张虽不常用但重新硬解析极其昂贵的计划，值得比平庸的小计划多留一会儿。

缓存生命周期管理还包含冷热分层：热点计划长期驻留，冷计划逐步老化；有些系统支持“固定计划”，把关键 SQL 的计划钉在缓存里以防被挤出。内存配额要结合业务设置，太小则命中率上不去，太大则挤压数据页缓存，得不偿失。留存评分的直觉是：既看这张卡被翻了多少次，也看重写它要花多大代价：

好厨师长不会一味扩大卡片架，他懂得在“研发成本”与“占用空间”之间算账。缓存调优也是如此：在有限共享内存里平衡命中率与资源占用，而不是无脑加大内存。

调优实践方法

厨师长每天看操作日志：宫保鸡丁被点了三百次、卡片被翻了多少回、哪张卡片总在重新写。这些数据帮他决定该备什么料、该固定哪些菜谱。DBA 的调优同样是“看数据说话”。

最核心的指标是缓存命中率（软解析占比）。命中率高说明大部分 SQL 在复用计划，但要清醒：命中率高不等于计划质量高——一份次优计划被反复复用，反而让慢查询长期潜伏。所以命中率之外，还要看重编译次数、缓存大小，以及 Top SQL 的单次耗时。

实操清单：应用层统一用预编译语句/绑定变量（JDBC PreparedStatement、MyBatis #{}）；统一 SQL 书写风格，杜绝大小写与空格的随机差异；批量写入用 executemany 而非循环拼接；避免在 SQL 里嵌入唯一性极高的常量（如时间戳）造成缓存污染；大促前主动预热高频计划；必要时对异常 SQL 刷新缓存，解除参数嗅探造成的次优绑定。衡量这一切的标尺，就是软解析占比：

让后厨的每一张卡片都“挂得对、用得上、不过期”，正是 SQL 优化的终局。你写出的每一行 SQL，都应该被数据库当作“熟客”对待——不用重新研发，直接按老规矩上菜，又快又稳。

AI 应用场景

Python 演示

# -*- coding: utf-8 -*-# 模拟数据库执行计划缓存: 参数化, 命中统计与 LRU 淘汰import refrom collections import OrderedDict  class PlanCache:    def __init__(self, capacity=3):        self.capacity = capacity        self._plans_map = OrderedDict()   # 归一化 SQL -> 执行计划        self._stats_map = {}              # 表名 -> 统计信息版本号        self.hit_count = 0        self.miss_count = 0     def normalize(self, sql):        # 把字符串常量和数字字面量替换为 ?, 结构相同的 SQL 归一到同一 key        sql = re.sub(r"'[^']*'", "?", sql)        sql = re.sub(r"\b\d+\b", "?", sql)        return re.sub(r"\s+", " ", sql.strip())     def invalidate(self, table):        # 模拟统计信息更新: 使涉及该表的缓存计划全部失效        if table not in self._stats_map:            self._stats_map[table] = 0        self._stats_map[table] += 1        for key in list(self._plans_map.keys()):            if table in key:                del self._plans_map[key]     def execute(self, sql):        key = self.normalize(sql)        if key in self._plans_map:  # 软解析: 命中缓存直接复用            self.hit_count += 1            self._plans_map.move_to_end(key)            return "soft parse, plan reused"        # 硬解析: 生成新计划并写入缓存, 超容量按 LRU 淘汰        self.miss_count += 1        self._plans_map[key] = "plan_of_" + key[:16]        if len(self._plans_map) > self.capacity:            self._plans_map.popitem(last=False)        return "hard parse, plan created"  if __name__ == "__main__":    pc = PlanCache(capacity=3)    sqls = [        "SELECT * FROM users WHERE id = 5",        "SELECT * FROM users WHERE id = 8",        "UPDATE orders SET status = 1 WHERE id = 100",        "SELECT * FROM users WHERE id = 12",        "DELETE FROM logs WHERE id = 2",    ]    for s in sqls:        print(pc.execute(s), "|", pc.normalize(s))    pc.invalidate("users")    print("after stats update on users:")    print(pc.execute("SELECT * FROM users WHERE id = 99"))    total = pc.hit_count + pc.miss_count    print("hit ratio:", round(pc.hit_count / total, 2))

1. sql = re.sub(r"'[^']*'", "?", sql)把所有字符串常量统一替换为问号，消除文本差异
2. sql = re.sub(r"\b\d+\b", "?", sql)把数字字面量也替换为问号，让 id = 5 与 id = 8 共享模板
3. if key in self._plans_map:判定命中，走软解析分支，直接复用已有计划
4. self._plans_map.move_to_end(key)命中后把条目移到末尾，模拟 LRU 的“最近使用”标记
5. self._plans_map.popitem(last=False)缓存超容量时，弹出最久没被使用的条目
6. pc.invalidate("users")模拟统计信息更新，涉及 users 表的计划全部失效，下次查询重新硬解析
7. round(pc.hit_count / total, 2)计算软解析占比，即文章反复强调的命中率指标

常见误区

面试题