重要前提
安装AI Skills的关键前提是:必须科学上网,且开启TUN模式,这一点至关重要,直接决定安装能否顺利完成,在此郑重提醒三遍:科学上网,科学上网,科学上网。查看完整安装教程 →
query-optimization by josiahsiegel/claude-plugin-marketplace
npx skills add https://github.com/josiahsiegel/claude-plugin-marketplace --skill query-optimizationT-SQL 查询优化技术综合指南。
| 非可搜索参数模式(差) | 可搜索参数模式(好) |
|---|---|
WHERE YEAR(Date) = 2024 | WHERE Date >= '2024-01-01' AND Date < '2025-01-01' |
WHERE LEFT(Name, 3) = 'ABC' | WHERE Name LIKE 'ABC%' |
WHERE Amount * 1.1 > 1000 | WHERE Amount > 1000 / 1.1 |
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
WHERE ISNULL(Col, 0) = 5 | WHERE Col = 5 OR Col IS NULL |
WHERE VarcharCol = 123 | WHERE VarcharCol = '123' |
| 连接类型 | 最佳适用场景 | 特性 |
|---|---|---|
| 嵌套循环 | 外部表小,内部表有索引 | 内存占用低,适用于小数据集 |
| 合并连接 | 输入已排序,大小相似 | 对已排序数据效率高 |
| 哈希连接 | 大型未排序输入 | 内存占用高,适用于大数据集 |
| 提示 | 用途 |
|---|---|
OPTION (RECOMPILE) | 每次执行都生成新计划 |
OPTION (OPTIMIZE FOR (@p = value)) | 针对特定值进行优化 |
OPTION (OPTIMIZE FOR UNKNOWN) | 使用平均统计信息 |
OPTION (MAXDOP n) | 限制并行度 |
OPTION (FORCE ORDER) | 使用精确的连接顺序 |
WITH (NOLOCK) | 读取未提交数据(脏读) |
WITH (FORCESEEK) | 强制索引查找 |
可搜索参数 = 搜索参数。可搜索参数查询可以使用索引查找:
-- 非可搜索参数:对列使用函数
WHERE DATEPART(year, OrderDate) = 2024
WHERE UPPER(CustomerName) = 'JOHN'
WHERE OrderAmount + 100 > 500
-- 可搜索参数:保持列原样
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE CustomerName = 'john' COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE OrderAmount > 400
避免数据类型不匹配:
-- 差:隐式转换(varchar 列与 int 比较)
WHERE VarcharColumn = 12345
-- 好:类型完全匹配
WHERE VarcharColumn = '12345'
-- 在执行计划中检查隐式转换
-- 查找 CONVERT_IMPLICIT 警告
在不同列上使用 OR 会阻止查找:
-- 低效:在不同列上使用 OR
SELECT * FROM Orders
WHERE CustomerID = 1 OR ProductID = 2
-- 更好:使用 UNION 优化 OR
SELECT * FROM Orders WHERE CustomerID = 1
UNION ALL
SELECT * FROM Orders WHERE ProductID = 2 AND CustomerID <> 1
-- EXISTS:最适合半连接(检查存在性)
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)
-- IN:适用于小型静态列表
SELECT * FROM Products WHERE CategoryID IN (1, 2, 3)
-- JOIN:当需要两个表的数据时最佳
SELECT c.*, o.OrderDate
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
-- 首次执行 CustomerID=1(10 行)创建计划
-- 后续执行 CustomerID=999(100 万行)使用相同计划
CREATE PROCEDURE GetOrders @CustomerID INT AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID
CREATE PROCEDURE GetOrders @CustomerID INT AS
SELECT * FROM Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE)
-- 最佳适用:不频繁查询,数据分布变化大
-- 针对特定值优化
OPTION (OPTIMIZE FOR (@CustomerID = 1))
-- 针对未知值优化(平均统计信息)
OPTION (OPTIMIZE FOR UNKNOWN)
CREATE PROCEDURE GetOrders @CustomerID INT AS
BEGIN
DECLARE @LocalID INT = @CustomerID
SELECT * FROM Orders WHERE CustomerID = @LocalID
END
-- 对优化器隐藏参数,类似于 OPTIMIZE FOR UNKNOWN
EXEC sys.sp_query_store_set_hints
@query_id = 12345,
@hints = N'OPTION (RECOMPILE)'
-- 无需更改代码即可应用提示
-- 启用参数敏感计划优化
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160
-- 根据参数值自动创建多个计划
| 运算符 | 警告信号 | 应对措施 |
|---|---|---|
| 表扫描 | 缺少索引 | 添加适当的索引 |
| 索引扫描 | 非可搜索参数谓词 | 重写查询 |
| 键查找 | 缺少覆盖索引 | 添加 INCLUDE 列 |
| 排序 | 缺少 ORDER BY 索引 | 添加排序索引 |
| 哈希匹配 | 大量内存授予 | 考虑添加索引 |
| 假脱机 | 重复扫描 | 重构查询 |
-- 差异大表示统计信息问题
-- 检查是否需要更新统计信息:
UPDATE STATISTICS TableName WITH FULLSCAN
-- 或启用自动更新:
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS ON
SELECT
CONVERT(DECIMAL(18,2), migs.avg_user_impact) AS AvgImpact,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC
DBCC SHOW_STATISTICS('TableName', 'IndexName')
-- 更新表上的所有统计信息
UPDATE STATISTICS TableName
-- 使用完全扫描更新(最准确)
UPDATE STATISTICS TableName WITH FULLSCAN
-- 更新特定统计信息
UPDATE STATISTICS TableName StatisticsName
-- 启用异步自动更新(更适合 OLTP)
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS_ASYNC ON
如需深入了解性能诊断,请参阅:
references/dmv-diagnostic-queries.md - 用于性能分析的 DMV 查询每周安装次数
69
代码仓库
GitHub 星标数
21
首次出现时间
2026 年 1 月 24 日
安全审计
安装于
opencode55
claude-code53
codex50
gemini-cli50
cursor46
github-copilot43
Comprehensive guide to T-SQL query optimization techniques.
| Non-SARGable (Bad) | SARGable (Good) |
|---|---|
WHERE YEAR(Date) = 2024 | WHERE Date >= '2024-01-01' AND Date < '2025-01-01' |
WHERE LEFT(Name, 3) = 'ABC' | WHERE Name LIKE 'ABC%' |
WHERE Amount * 1.1 > 1000 | WHERE Amount > 1000 / 1.1 |
WHERE ISNULL(Col, 0) = 5 | WHERE Col = 5 OR Col IS NULL |
WHERE VarcharCol = 123 | WHERE VarcharCol = '123' |
| Join Type | Best For | Characteristics |
|---|---|---|
| Nested Loop | Small outer, indexed inner | Low memory, good for small sets |
| Merge Join | Sorted inputs, similar sizes | Efficient for sorted data |
| Hash Join | Large unsorted inputs | High memory, good for large sets |
| Hint | Purpose |
|---|---|
OPTION (RECOMPILE) | Fresh plan each execution |
OPTION (OPTIMIZE FOR (@p = value)) | Optimize for specific value |
OPTION (OPTIMIZE FOR UNKNOWN) | Use average statistics |
OPTION (MAXDOP n) | Limit parallelism |
OPTION (FORCE ORDER) | Use exact join order |
WITH (NOLOCK) |
SARG = Search ARGument. SARGable queries can use index seeks:
-- Non-SARGable: Function on column
WHERE DATEPART(year, OrderDate) = 2024
WHERE UPPER(CustomerName) = 'JOHN'
WHERE OrderAmount + 100 > 500
-- SARGable: Preserve column
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE CustomerName = 'john' COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE OrderAmount > 400
Avoid data type mismatches:
-- Bad: Implicit conversion (varchar column compared to int)
WHERE VarcharColumn = 12345
-- Good: Match types exactly
WHERE VarcharColumn = '12345'
-- Check for implicit conversions in execution plan
-- Look for CONVERT_IMPLICIT warnings
OR on different columns prevents seek:
-- Inefficient: OR on different columns
SELECT * FROM Orders
WHERE CustomerID = 1 OR ProductID = 2
-- Better: UNION for OR optimization
SELECT * FROM Orders WHERE CustomerID = 1
UNION ALL
SELECT * FROM Orders WHERE ProductID = 2 AND CustomerID <> 1
-- EXISTS: Best for semi-joins (checking existence)
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)
-- IN: Good for small static lists
SELECT * FROM Products WHERE CategoryID IN (1, 2, 3)
-- JOIN: Best when you need data from both tables
SELECT c.*, o.OrderDate
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
-- First execution with CustomerID=1 (10 rows) creates plan
-- Subsequent execution with CustomerID=999 (1M rows) uses same plan
CREATE PROCEDURE GetOrders @CustomerID INT AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID
CREATE PROCEDURE GetOrders @CustomerID INT AS
SELECT * FROM Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE)
-- Best for: Infrequent queries, highly variable data distribution
-- Optimize for specific value
OPTION (OPTIMIZE FOR (@CustomerID = 1))
-- Optimize for unknown (average statistics)
OPTION (OPTIMIZE FOR UNKNOWN)
CREATE PROCEDURE GetOrders @CustomerID INT AS
BEGIN
DECLARE @LocalID INT = @CustomerID
SELECT * FROM Orders WHERE CustomerID = @LocalID
END
-- Hides parameter from optimizer, similar to OPTIMIZE FOR UNKNOWN
EXEC sys.sp_query_store_set_hints
@query_id = 12345,
@hints = N'OPTION (RECOMPILE)'
-- Apply hints without code changes
-- Enable Parameter Sensitive Plan optimization
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160
-- Automatically creates multiple plans based on parameter values
| Operator | Warning Sign | Action |
|---|---|---|
| Table Scan | Missing index | Add appropriate index |
| Index Scan | Non-SARGable predicate | Rewrite query |
| Key Lookup | Missing covering index | Add INCLUDE columns |
| Sort | Missing index for ORDER BY | Add sorted index |
| Hash Match | Large memory grant | Consider index |
| Spools | Repeated scans | Restructure query |
-- Large difference indicates statistics problem
-- Check if stats need updating:
UPDATE STATISTICS TableName WITH FULLSCAN
-- Or enable auto-update:
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS ON
SELECT
CONVERT(DECIMAL(18,2), migs.avg_user_impact) AS AvgImpact,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC
DBCC SHOW_STATISTICS('TableName', 'IndexName')
-- Update all statistics on table
UPDATE STATISTICS TableName
-- Update with full scan (most accurate)
UPDATE STATISTICS TableName WITH FULLSCAN
-- Update specific statistics
UPDATE STATISTICS TableName StatisticsName
-- Enable async auto-update (better for OLTP)
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS_ASYNC ON
For deeper coverage of performance diagnostics, see:
references/dmv-diagnostic-queries.md - DMV queries for performance analysisWeekly Installs
69
Repository
GitHub Stars
21
First Seen
Jan 24, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
opencode55
claude-code53
codex50
gemini-cli50
cursor46
github-copilot43
GSAP时间轴动画教程:创建多步骤序列动画与关键帧控制
3,800 周安装
Playwright Network HAR 录制器 - 录制回放网络流量,生成API模拟,提升测试确定性
Cubox集成技能:通过Python脚本和Open API自动保存网页与笔记到Cubox
GitHub Agentic Workflows 听写指令:AI驱动工作流语音输入技术指南
GraphQL Schema Stitching & Federation Agent - Apollo Federation v2 超图组合与验证工具
GraphQL Schema Stitcher:Apollo Federation v2 模式缝合工具,统一联邦网关
GraphQL Schema Introspector - 模式自省、查询复杂度分析与API差异报告工具
| Read uncommitted (dirty reads) |
WITH (FORCESEEK) | Force index seek |