管理绑定变量 / 参数嗅探

Oracle

-- Use binds instead of literals (for plan sharing):
SELECT * FROM customer WHERE city = :city;
-- Share legacy code that uses literals:
ALTER SESSION SET cursor_sharing = FORCE;

绑定变量可减少硬解析。Adaptive Cursor Sharing 为分布倾斜的绑定值保留多个执行计划，从而缓解嗅探问题。cursor_sharing=FORCE 需谨慎使用。

PostgreSQL

-- Parameterize with a prepared statement:
PREPARE q (text) AS SELECT * FROM customer WHERE city = $1;
EXECUTE q('Ankara');
-- Set plan cache behavior:
SET plan_cache_mode = force_custom_plan;

plan_cache_mode=auto 在通用计划和定制计划之间选择；force_custom_plan 在每次执行时生成针对参数的计划（解决类似嗅探问题的方法）。

MySQL

-- Server-side prepared statement:
PREPARE stmt FROM 'SELECT * FROM customer WHERE city = ?';
SET @city = 'Ankara';
EXECUTE stmt USING @city;
DEALLOCATE PREPARE stmt;

预处理语句可防止 SQL 注入并降低解析开销。MySQL 也支持在驱动层进行参数化。

SQL Server

-- Parameterized query (sp_executesql):
EXEC sp_executesql N'SELECT * FROM dbo.customer WHERE city = @city', N'@city nvarchar(50)', @city = N'Ankara';
-- Handle sniffing with a hint:
SELECT ... OPTION (RECOMPILE);
-- or OPTION (OPTIMIZE FOR (@city = 'Ankara'));

OPTION(RECOMPILE) 在每次执行时生成针对参数的计划（CPU 开销大）。OPTIMIZE FOR 将计划固定到特定/典型值；OPTIMIZE FOR UNKNOWN 使用平均选择性。

MongoDB

// The query shape is the key of the plan cache:
db.customer.find({ city: "Ankara" }).explain("queryPlanner")
// Clear a stale plan:
db.customer.getPlanCache().clear()

形状相同（字段/运算符相同）的查询共享缓存的计划；不同的字面值不会生成新计划。如果值分布严重倾斜，用 planCacheClear 重置。

Redis

# Not applicable: there is no query planner or parameter sniffing.

命令直接执行；决定性能的是键设计，而不是参数化。

Elasticsearch

# Reuse the query with a parameterized search template:
POST _scripts/customer_city
{ "script": { "lang": "mustache", "source": { "query": { "term": { "city": "{{city}}" } } } } }
GET <index>/_search/template
{ "id": "customer_city", "params": { "city": "Ankara" } }

搜索模板（Mustache）对查询主体进行参数化；应用以不同的值调用固定模板，从而提高查询一致性和缓存利用率。