重要前提
安装AI Skills的关键前提是:必须科学上网,且开启TUN模式,这一点至关重要,直接决定安装能否顺利完成,在此郑重提醒三遍:科学上网,科学上网,科学上网。查看完整安装教程 →
index-strategies by josiahsiegel/claude-plugin-marketplace
npx skills add https://github.com/josiahsiegel/claude-plugin-marketplace --skill index-strategiesSQL Server 索引设计与优化的全面指南。
| 类型 | 描述 | 最佳适用场景 |
|---|---|---|
| 聚集索引 | 表数据顺序 | 主要访问路径,范围扫描 |
| 非聚集索引 | 独立结构 | 特定查询模式 |
| 列存储索引 | 基于列的存储 | 分析,聚合 |
| 筛选索引 | 部分索引 | 已知的子集 |
| 覆盖索引 | 包含所有所需列 | 避免键查找 |
理想的聚集键:
窄(小数据类型)
唯一或基本唯一
持续递增(标识列,顺序 GUID)
静态(很少更新)
-- 良好:标识列 CREATE CLUSTERED INDEX CIX_Orders ON Orders(OrderID);
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
-- 良好:顺序 GUID CREATE TABLE Orders ( OrderID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED );
-- 避免:宽复合键,频繁更新的列,GUIDs (NEWID)
-- 基本索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Orders(CustomerID);
-- 覆盖索引(避免键查找)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cover
ON Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);
-- 筛选索引(部分)
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders(CustomerID, OrderDate)
WHERE Status = 'Active';
-- 降序排序
CREATE NONCLUSTERED INDEX IX_Orders_DateDesc
ON Orders(OrderDate DESC, OrderID DESC);
| 模式 | 推荐索引 |
|---|---|
WHERE Col = value | 在 Col 上创建非聚集索引 |
WHERE Col = v1 AND Col2 = v2 | 在 (Col, Col2) 上创建非聚集索引 |
WHERE Col = v ORDER BY Col2 | 在 (Col, Col2) 上创建非聚集索引 |
WHERE Col BETWEEN x AND y | 将 Col 作为最左键 |
SELECT * WHERE Col = v | 聚集索引或覆盖非聚集索引 |
| 大型聚合 | 列存储索引 |
| 特定子集查询 | 筛选索引 |
-- 顺序很重要!从左到右匹配
CREATE INDEX IX_Example ON Table(A, B, C);
-- 这些查询可以使用索引:
WHERE A = 1
WHERE A = 1 AND B = 2
WHERE A = 1 AND B = 2 AND C = 3
WHERE A = 1 AND B > 5 ORDER BY B
-- 这些查询无法使用索引查找:
WHERE B = 2 -- 未指定 A
WHERE B = 2 AND C = 3 -- 未指定 A
WHERE A = 1 AND C = 3 -- 跳过 B(仅部分匹配)
-- 最适合数据仓库
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON FactSales;
-- 有序列存储索引 (SQL 2022+)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON FactSales
ORDER (DateKey, ProductKey);
-- 混合 OLTP/OLAP
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analysis
ON Orders(OrderDate, ProductID, Quantity, Amount)
WHERE Status = 'Completed';
-- 维护
ALTER INDEX CCI_FactSales ON FactSales REORGANIZE;
-- 检查碎片
SELECT
object_name(object_id) AS TableName,
index_id,
avg_fragmentation_in_percent,
fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
-- 仅索引活动订单
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders(CustomerID, OrderDate)
WHERE Status = 'Active';
-- 索引非 NULL 值
CREATE UNIQUE INDEX IX_Users_Email
ON Users(Email)
WHERE Email IS NOT NULL;
-- 限制:
-- - 不能在筛选器中使用变量
-- - 查询 WHERE 必须匹配或是筛选器 WHERE 的子集
-- - 可能导致参数嗅探问题
-- 消除键查找
-- 原始:在 CustomerID 上创建索引,查询选择 OrderDate, Amount
-- 执行计划显示键查找
-- 解决方案:覆盖索引
CREATE INDEX IX_Orders_CustomerID_Cover
ON Orders(CustomerID)
INCLUDE (OrderDate, Amount, Status);
-- INCLUDE 列:
-- - 不在键中(不排序)
-- - 仅存储在叶级别
-- - 不占用 900 字节的键限制
-- - 非常适合频繁选择的列
| 碎片化 % | 操作 |
|---|---|
| < 5% | 无需操作 |
| 5-30% | REORGANIZE |
30% | REBUILD
-- 重新组织(在线,锁定最小)
ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;
-- 重新生成(默认离线,更彻底)
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;
-- 在线重新生成(企业版)
ALTER INDEX IX_Orders_CustomerID ON Orders
REBUILD WITH (ONLINE = ON);
-- 可恢复的重新生成 (SQL 2017+)
ALTER INDEX IX_Orders_CustomerID ON Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);
-- 恢复中断的重新生成
ALTER INDEX IX_Orders_CustomerID ON Orders RESUME;
-- 索引更改后更新
UPDATE STATISTICS Orders;
-- 完全扫描以获取准确的统计信息
UPDATE STATISTICS Orders WITH FULLSCAN;
-- 检查最后更新时间
SELECT
OBJECT_NAME(object_id) AS TableName,
name AS StatsName,
STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats
WHERE object_id = OBJECT_ID('Orders');
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON i.object_id = ius.object_id
AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ius.user_seeks + ius.user_scans DESC;
SELECT
migs.avg_user_impact AS ImpactPercent,
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
ORDER BY migs.avg_user_impact DESC;
每周安装数
59
代码仓库
GitHub 星标数
21
首次出现
2026年1月24日
安全审计
安装于
opencode45
claude-code44
codex43
gemini-cli43
cursor41
antigravity38
Comprehensive guide to SQL Server index design and optimization.
| Type | Description | Best For |
|---|---|---|
| Clustered | Table data order | Primary access path, range scans |
| Nonclustered | Separate structure | Specific query patterns |
| Columnstore | Column-based storage | Analytics, aggregations |
| Filtered | Partial index | Well-known subsets |
| Covering | All columns needed | Avoiding key lookups |
Ideal Clustered Key:
Narrow (small data type)
Unique or mostly unique
Ever-increasing (identity, sequential GUID)
Static (rarely updated)
-- Good: Identity column CREATE CLUSTERED INDEX CIX_Orders ON Orders(OrderID);
-- Good: Sequential GUID CREATE TABLE Orders ( OrderID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED );
-- Avoid: Wide composite keys, frequently updated columns, GUIDs (NEWID)
-- Basic index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Orders(CustomerID);
-- Covering index (avoids key lookup)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cover
ON Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);
-- Filtered index (partial)
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders(CustomerID, OrderDate)
WHERE Status = 'Active';
-- Descending order
CREATE NONCLUSTERED INDEX IX_Orders_DateDesc
ON Orders(OrderDate DESC, OrderID DESC);
| Pattern | Recommended Index |
|---|---|
WHERE Col = value | Nonclustered on Col |
WHERE Col = v1 AND Col2 = v2 | Nonclustered on (Col, Col2) |
WHERE Col = v ORDER BY Col2 | Nonclustered on (Col, Col2) |
WHERE Col BETWEEN x AND y | Col as leftmost key |
SELECT * WHERE Col = v | Clustered or covering NC |
| Large aggregations | Columnstore |
| Specific subset queries |
-- Order matters! Left-to-right matching
CREATE INDEX IX_Example ON Table(A, B, C);
-- These queries CAN use the index:
WHERE A = 1
WHERE A = 1 AND B = 2
WHERE A = 1 AND B = 2 AND C = 3
WHERE A = 1 AND B > 5 ORDER BY B
-- These queries CANNOT use index seek:
WHERE B = 2 -- A not specified
WHERE B = 2 AND C = 3 -- A not specified
WHERE A = 1 AND C = 3 -- B skipped (partial match only)
-- Best for data warehousing
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON FactSales;
-- Ordered columnstore (SQL 2022+)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON FactSales
ORDER (DateKey, ProductKey);
-- Hybrid OLTP/OLAP
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analysis
ON Orders(OrderDate, ProductID, Quantity, Amount)
WHERE Status = 'Completed';
-- Maintenance
ALTER INDEX CCI_FactSales ON FactSales REORGANIZE;
-- Check fragmentation
SELECT
object_name(object_id) AS TableName,
index_id,
avg_fragmentation_in_percent,
fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
-- Index active orders only
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders(CustomerID, OrderDate)
WHERE Status = 'Active';
-- Index non-NULL values
CREATE UNIQUE INDEX IX_Users_Email
ON Users(Email)
WHERE Email IS NOT NULL;
-- Constraints:
-- - Cannot use variable in filter
-- - Query WHERE must match or be subset of filter WHERE
-- - May cause parameter sniffing issues
-- Eliminate key lookups
-- Original: Index on CustomerID, query selects OrderDate, Amount
-- Execution plan shows Key Lookup
-- Solution: Covering index
CREATE INDEX IX_Orders_CustomerID_Cover
ON Orders(CustomerID)
INCLUDE (OrderDate, Amount, Status);
-- INCLUDE columns:
-- - Not in key (not sorted)
-- - Stored at leaf level only
-- - Don't contribute to 900-byte key limit
-- - Perfect for frequently selected columns
| Fragmentation % | Action |
|---|---|
| < 5% | None needed |
| 5-30% | REORGANIZE |
30% | REBUILD
-- Reorganize (online, minimal locking)
ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;
-- Rebuild (offline by default, more thorough)
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;
-- Online rebuild (Enterprise Edition)
ALTER INDEX IX_Orders_CustomerID ON Orders
REBUILD WITH (ONLINE = ON);
-- Resumable rebuild (SQL 2017+)
ALTER INDEX IX_Orders_CustomerID ON Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);
-- Resume interrupted rebuild
ALTER INDEX IX_Orders_CustomerID ON Orders RESUME;
-- Update after index changes
UPDATE STATISTICS Orders;
-- Full scan for accurate stats
UPDATE STATISTICS Orders WITH FULLSCAN;
-- Check last update
SELECT
OBJECT_NAME(object_id) AS TableName,
name AS StatsName,
STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats
WHERE object_id = OBJECT_ID('Orders');
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON i.object_id = ius.object_id
AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ius.user_seeks + ius.user_scans DESC;
SELECT
migs.avg_user_impact AS ImpactPercent,
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
ORDER BY migs.avg_user_impact DESC;
Weekly Installs
59
Repository
GitHub Stars
21
First Seen
Jan 24, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
opencode45
claude-code44
codex43
gemini-cli43
cursor41
antigravity38
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
125,600 周安装
Conventional Commits 规范详解:结构化提交消息与自动化变更日志生成指南
117 周安装
uni-app 跨平台开发框架指南:Vue 语法一套代码编译 App/H5/小程序
118 周安装
Pueue任务编排指南:CLI任务管理、远程执行与批量作业自动化
54 周安装
DigitalOcean计算服务指南:Droplets虚拟机、App Platform、Kubernetes、GPU与无服务器函数
118 周安装
Groove Work Compound:AI辅助工作流复盘与经验学习工具,提升团队效率
122 周安装
SEC EDGAR 数据管道:自动化提取与分析美国上市公司财务文档(Python)
117 周安装
| Filtered index |