怎样使用查询提示来影响优化器？

本文目录导读：

1. 核心原则：为什么需要查询提示？
2. 第一部分：SQL Server 查询提示实战
3. 第二部分：Oracle 查询提示实战
4. 第三部分：最佳实践与注意事项

这是一个非常专业且深入的话题，在数据库领域，查询提示是开发者或DBA在优化器自动选择的执行计划不理想时，用来强制或引导优化器做出特定选择的工具。

下面以最常用的 Microsoft SQL Server 和 Oracle 数据库为例，详细说明如何使用查询提示来影响优化器,并附上实战中的注意事项。

核心原则：为什么需要查询提示？

现代数据库优化器（如SQL Server的Cardinality Estimator，Oracle的CBO）在大多数情况下表现优异,但以下场景可能需要人工介入：

1. 统计信息过时或缺失：导致优化器估算行数严重错误。
2. 参数嗅探：第一次执行的参数生成的计划,对后续不同的参数值不适用。
3. 复杂的连接顺序：优化器选择了笛卡尔积或错误的嵌套循环。
4. 并行度问题：默认的并行计划导致CPU或内存压力过大。
5. 不稳定的计划：同一个查询在不同时间生成不同的计划,导致性能波动。

警告： 查询提示是“双刃剑”，在数据库版本升级或数据分布发生根本变化时，硬编码的提示可能导致性能灾难,使用前务必充分测试。

---

第一部分：SQL Server 查询提示实战

SQL Server的提示通常写在查询的 OPTION 子句中。

控制 Join 算法（连接提示）

优化器可能选择哈希连接,但你认为小表驱动大表的嵌套循环更好。

-- 强制使用嵌套循环连接（Loop Join）
SELECT *
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
OPTION (LOOP JOIN);
-- 强制使用哈希连接
SELECT *
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
OPTION (HASH JOIN);
-- 强制使用合并连接
SELECT *
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
OPTION (MERGE JOIN);

控制查询并行度（DOP，Degree of Parallelism）

当发现并行查询因为资源争用或分配不均匀而变慢时：

-- 强制串行执行（单CPU，禁止并行）
SELECT COUNT(*) FROM BigTable
OPTION (MAXDOP 1);
-- 限制最大并行度为2
SELECT COUNT(*) FROM BigTable
OPTION (MAXDOP 2);

解决参数嗅探问题

这是最常见的用例，当传入的参数值导致生成了仅适合该值的很差计划时，可以使用 OPTIMIZE FOR 提示。

-- 1. 强制使用特定的参数值来生成计划
-- 无论实际传入什么值，都按 @p = 'CommonValue' 来优化
CREATE PROCEDURE GetOrders (@p CustomerID)
AS
SELECT * FROM Orders WHERE CustomerID = @p
OPTION (OPTIMIZE FOR (@p = 'CommonValue'));
-- 2. 强制使用“未知”值（让优化器使用平均密度而非特定值）
-- 适用于传入值分布极不均匀的情况
SELECT * FROM Orders WHERE CustomerID = @p
OPTION (OPTIMIZE FOR UNKNOWN);
-- 3. 重新编译每次执行（最极端，避免计划缓存）
SELECT * FROM Orders WHERE CustomerID = @p
OPTION (RECOMPILE);

控制索引选择（表提示）

直接告诉优化器用哪个索引。

-- 强制使用指定索引（索引名或索引ID）
SELECT * FROM Orders WITH (INDEX(IX_Orders_OrderDate))
WHERE OrderDate > '2023-01-01';
-- 强制进行表扫描（如果索引不好用）
SELECT * FROM Orders WITH (INDEX(0))
WHERE OrderDate > '2023-01-01';

---

第二部分：Oracle 查询提示实战

Oracle的提示通常写在 SELECT、INSERT、UPDATE 或 DELETE 关键字之后，用 注释格式包裹。

控制访问路径（全表扫描 vs 索引扫描）

-- 强制使用全表扫描
SELECT /*+ FULL(employees) */ * FROM employees WHERE department_id = 10;
-- 强制使用指定索引
SELECT /*+ INDEX(employees emp_dept_idx) */ * FROM employees WHERE department_id = 10;
-- 强制使用快速全索引扫描
SELECT /*+ INDEX_FFS(employees emp_dept_idx) */ department_id FROM employees;

控制连接操作

-- 强制使用嵌套循环连接
SELECT /*+ USE_NL(e d) */ * FROM employees e JOIN departments d ON e.department_id = d.department_id;
-- 强制使用哈希连接
SELECT /*+ USE_HASH(e d) */ * FROM employees e JOIN departments d ON e.department_id = d.department_id;
-- 指定驱动表（小表）的顺序
SELECT /*+ LEADING(e) USE_NL(d) */ * FROM employees e JOIN departments d ON e.department_id = d.department_id;
-- 含义：先访问employees(e)，然后用嵌套循环连接departments(d)

控制并行度

-- 强制并行查询，DOP=4
SELECT /*+ PARALLEL(employees, 4) */ * FROM employees;
-- 禁用并行（串行执行）
SELECT /*+ NO_PARALLEL(employees) */ * FROM employees;

解决基数估算问题（CBO提示）

当统计信息数据不佳时，可以“欺骗”优化器的基数。

-- 告诉优化器，这个表很大
SELECT /*+ CARDINALITY(employees 1000000) */ * FROM employees WHERE status = 'ACTIVE';
-- 强制使用动态采样
SELECT /*+ DYNAMIC_SAMPLING(employees 4) */ * FROM employees WHERE status = 'ACTIVE';

---

第三部分：最佳实践与注意事项

✅ 何时使用提示？

1. 作为临时救火：当生产出现慢查询，统计信息更新需要时间或无法立即完成时,使用提示快速止血。
2. 作为计划稳定性手段：当数据库升级后，某个关键查询的计划变差，但统计信息正常时，使用提示 “锁定” 一个好的计划。
3. 作为测试工具：测试不同执行计划的性能差异。

❌ 何时避免使用提示？

1. 作为默认方案：绝大多数情况,优化器比人做得更好。
2. 当数据分布频繁变化时：如果一个表从100行增长到1000万行，硬编码的FULL SCAN提示可能从好变坏。
3. 当没有理解提示含义时：错误使用（如在Oracle中同时使用FULL和INDEX）可能导致语法错误或无效果。

调试与验证技巧

• 查看实际执行计划：
  • SQL Server：SET STATISTICS XML ON; 或 SET SHOWPLAN_XML ON;
  • Oracle：EXPLAIN PLAN FOR 或 DBMS_XPLAN.DISPLAY_CURSOR;
• 对比行估计与实际行数：如果提示生效，执行计划中会显示你期望的算法（如Nested Loops），但实际行数可能与估计行数差距很大，这是判断提示是否“帮倒忙”的关键。
• 回滚测试：始终留一份不带提示的原始查询,方便对比。

场景	推荐做法
统计信息过时	优先更新统计信息，如果不行，用 OPTIMIZE FOR UNKNOWN (SQL) 或 DYNAMIC_SAMPLING (Oracle)。
参数嗅探	使用 OPTION (RECOMPILE) 或 OPTIMIZE FOR UNKNOWN。
连接顺序不正确	明确指定驱动表和连接算法（如 LEADING + USE_NL）。
并行导致CPU高	使用 MAXDOP 1 或 NO_PARALLEL 强制串行。
测试特定策略	使用 FULL SCAN 或 INDEX 来验证猜想。

最后的建议： 在添加查询提示之前，先尝试通过更新统计信息、重建索引、重写查询逻辑（如使用 NOT EXISTS 代替 NOT IN）来解决问题，提示是最后的“手术刀”，而非常规的“创可贴”。