如何锁定数据库的执行计划？

如何锁定数据库的执行计划？——从原理到实战的完整指南

目录导读

1. 什么是执行计划？为什么需要“锁定”？

   

   • 执行计划的动态性与稳定性矛盾
   • 锁定执行计划的核心价值

2. 锁定执行计划的五大核心方法

   • 使用plan_guide（查询计划指南）
   • plan_freeze与sp_control_query_plan
   • 强制索引与hints（提示）
   • 通过query_store锁定计划
   • 静态参数化与forced parameterization

3. 实战案例：锁定一个复杂查询的执行计划

   • 场景描述
   • 排查过程
   • 锁定步骤与验证

4. 常见问题与错误规避

   • 不推荐的做法
   • 锁定后的维护与更新

5. 问答环节

   • Q1：锁定执行计划会导致性能下降吗？
   • Q2：锁定的计划多久需要重新评估？
   • Q3：MySQL和SQL Server在锁定计划上有何不同？

---

什么是执行计划？为什么需要“锁定”？

执行计划是数据库优化器针对SQL语句生成的“操作步骤清单”——告诉数据库如何访问表、使用哪些索引、如何关联数据等，理想情况下，优化器会根据表数据分布、统计信息等动态选择最优计划，但在生产环境中，计划的突然变化往往带来灾难：

• 原本1秒的查询,执行计划跳变后变成30秒
• 高峰期因计划重编译导致CPU飙升
• 不同参数值触发不同计划,导致响应时间剧烈波动

锁定执行计划，就是人为干预，强制数据库在特定查询上始终使用我们已验证过的高效计划，而非让优化器“自由发挥”。

---

锁定执行计划的五大核心方法

使用plan_guide（查询计划指南）

适用数据库：SQL Server
原理：创建“计划向导”，将特定SQL文本与指定计划绑定。
步骤：

1. 获取目标查询的query_hash和plan_handle
2. 提取计划的XML格式（sys.dm_exec_query_plan）
3. 创建plan_guide：

   EXEC sp_create_plan_guide 
    @name = N'LockPlan_OrderQuery',
    @stmt = N'SELECT * FROM Orders WHERE OrderDate > @date',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION(USE PLAN XML内容)';

   优点：不影响SQL文本本身，透明。
   缺点：计划XML过长，维护麻烦。

plan_freeze与sp_control_query_plan

适用数据库：SQL Server 2019+（Azure SQL DB）
新增功能：sys.sp_control_query_plan允许直接“冻住”当前计划：

-- 获取计划句柄  
SELECT query_plan_hash, plan_handle 
FROM sys.dm_exec_query_stats 
WHERE query_hash = 0x...;
-- 冻结该计划  
EXEC sys.sp_control_query_plan @plan_handle = '0x...', @control_type = 'FREEZE';

注意事项：冻结后优化器不再对该查询生成新计划，直到手动解冻（UNFREEZE）。

强制索引与hints（提示）

适用数据库：MySQL、PostgreSQL、SQL Server
原理：在SQL中加入优化器提示，明确告诉数据库使用哪个索引或关联顺序。

• MySQL示例：

  SELECT * FROM Orders FORCE INDEX (idx_order_date) 
  WHERE OrderDate > '2025-01-01';

• SQL Server示例：

  SELECT * FROM Orders WITH (INDEX(idx_order_date))

• Oracle示例：

  SELECT /*+ INDEX(orders idx_order_date) */ * FROM orders;

  缺点：硬编码在SQL中，未来索引变更需修改代码。

通过query_store锁定计划

适用数据库：SQL Server、Azure SQL
原理：启用查询存储后，可通过UI或T-SQL将特定计划设为“强制计划”：

EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;

优点：可视化操作，无需修改SQL；可追踪计划变化历史。
维护：定期检查sys.query_store_plan中该计划是否仍被使用。

静态参数化与forced parameterization

适用数据库：SQL Server
场景：当同类查询仅因参数不同而产生不同计划时，开启数据库级的PARAMETERIZATION FORCED：

ALTER DATABASE YourDB SET PARAMETERIZATION FORCED;

副作用：所有简单查询都被参数化，可能引发计划缓存膨胀。

---

实战案例：锁定一个复杂查询的执行计划

场景描述

电商数据库Orders表中，一个每月报表查询：

SELECT o.OrderID, c.CustomerName, SUM(d.Quantity * d.Price) AS Total
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails d ON o.OrderID = d.OrderID
WHERE o.OrderDate BETWEEN @Start AND @End
GROUP BY o.OrderID, c.CustomerName

某月该查询突然变慢（5秒→45秒），原因是优化器选择了错误的Hash Join而非Nested Loop。

排查过程

1. 检查sys.dm_exec_query_stats：发现last_plan_generation时间与问题出现时间吻合。
2. 对比原计划和新计划：新计划对OrderDetails表进行了全表扫描（因统计信息偏差）。
3. 决定锁定原高效计划。

锁定步骤（使用plan_guide）

1. 获取高效计划的XML：

   SELECT query_plan 
   FROM sys.dm_exec_query_stats 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
   CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
   WHERE query_hash = 0x...;

2. 创建计划指南：

   EXEC sp_create_plan_guide 
    @name = 'MonthlyReport_LockedPlan',
    @stmt = N'SELECT o.OrderID, c.CustomerName, SUM(d.Quantity * d.Price) AS Total
   FROM Orders o
   JOIN Customers c ON o.CustomerID = c.CustomerID
   JOIN OrderDetails d ON o.OrderID = d.OrderID
   WHERE o.OrderDate BETWEEN @Start AND @End
   GROUP BY o.OrderID, c.CustomerName',
    @type = N'SQL',
    @hints = N'OPTION(USE PLAN ''[XML内容]'')';

3. 验证：执行原查询，检查sys.dm_exec_query_stats中plan_handle是否指向锁定的计划。

验证与结果

• 查询响应时间稳定在1.5秒
• 通过query_store看到该计划被“强制”标识

---

常见问题与错误规避

❌ 不推荐的做法

• 直接删除统计信息：防止优化器变计划，但会影响其他查询。
• 长期使用FORCE INDEX：索引重命名或删除会导致查询失败。
• 对所有查询都锁定计划：过度锁定会丧失优化器自适应能力。

✅ 最佳实践

• 仅对关键业务查询（报表、高频率查询）锁定计划。
• 设立计划锁定监控：每周检查query_store中强制计划的有效性（如索引是否已重建）。
• 配合索引维护：重建索引后，需重新评估锁定计划是否仍然最优。

什么时候需要解除锁定？

• 索引结构发生重大变化（如增加覆盖索引）
• 数据分布变化导致原计划已不优（可通过actual execution plan对比）
• 数据库版本升级后优化器能力提升

---

问答环节

Q1：锁定执行计划会导致性能下降吗？

答：可能会，锁定计划是一把双刃剑——它解决了计划跳变问题，但也牺牲了优化器的动态适应能力，如果锁定的是一个“死记硬背”的糟糕计划，性能反而不如不锁定。建议：锁定前通过estimated subtree cost、logical reads等指标确认当前计划为最优，并定期（如每月）对比锁定计划与优化器新生成计划的成本。

Q2：锁定的计划多久需要重新评估？

答：建议在以下三种情况下重新评估：

1. 索引维护后（重建、重组、新增索引）
2. 数据量级发生跃迁（如订单表从100万行增长到1000万行）
3. 数据库版本升级后（新优化器算法可能更优）
   一般生产环境可以设置季度评估周期，配合自动化脚本检查锁定的计划是否仍在sys.query_store_plan中保持“低成本”。

Q3：MySQL和SQL Server在锁定计划上有何不同？

维度	MySQL	SQL Server
原生锁定方法	FORCE INDEX / USE INDEX（SQL层）	plan_guide/query_store强制
计划稳定性支持	无“冻结”概念，依赖hint	有完整的计划库（plan_freeze）
可视化工具	无内置、需第三方	SSMS中的查询存储UI
锁定粒度	表级索引提示	完整查询计划级别的绑定
统计信息依赖	统计信息陈旧仍可能变计划	锁定后完全忽略统计信息

SQL Server提供了更成熟、更细粒度的锁定机制；MySQL主要依赖hint，且不支持直接锁定同一条SQL的不同执行计划，建议结合pg_hint_plan（PostgreSQL插件）或optimizer_switch等辅助手段。

---

锁定执行计划不是常规操作,而是应对计划不稳定事件的关键止损手段，在实际应用中，建议按“先监控→后分析→再锁定”的流程操作，并始终辅以自动化监控（如通过sys.dm_exec_query_stats追踪计划变化频率）。锁住计划是为了赢回稳定性，但别忘了定期释放优化学机会——让数据库保持“有韧性的稳定”，才是最终目标。