索引原理与执行计划优化

4.1 B+Tree 索引内部结构

4.1.1 聚簇索引（Clustered Index）

SQL Server 表数据本身按聚簇索引键排列存储，聚簇索引就是数据。每张表只能有一个聚簇索引。

┌──────────────────── B+Tree 聚簇索引结构 ─────────────────────┐
│                                                               │
│  根节点（Root Page）                                          │
│  ┌──────────────────────────────────────┐                    │
│  │ KeyRange ≤1000 │ KeyRange ≤5000 │ >5000│ → 内部节点指针   │
│  └───────┬──────────────┬───────────────┘                    │
│          │              │                                     │
│  中间节点（Intermediate Pages）                               │
│  ┌────────────┐  ┌────────────┐                              │
│  │≤200 │≤600  │  │≤3000│≤4500 │                              │
│  └──┬────┬───┘  └──┬────┬────┘                              │
│     │    │         │    │                                    │
│  叶子节点（Leaf Pages = 实际数据行！）                         │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐                        │
│  │行1   │ │行201 │ │行601 │ │行1001│   ←→  双向链表           │
│  │行2   │ │行202 │ │行602 │ │行1002│                         │
│  │...   │ │...   │ │...   │ │...   │                         │
│  └──────┘ └──────┘ └──────┘ └──────┘                        │
└───────────────────────────────────────────────────────────────┘

-- 创建测试表演示索引结构
CREATE TABLE dbo.Orders (
    OrderID        INT          NOT NULL IDENTITY(1,1),
    CustomerID     INT          NOT NULL,
    OrderDate      DATE         NOT NULL,
    Status         TINYINT      NOT NULL DEFAULT 1,
    TotalAmount    DECIMAL(12,2) NOT NULL,
    ShippingAddr   NVARCHAR(200) NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)  -- 聚簇索引
);

-- 查看表的索引信息
SELECT
    i.name              AS IndexName,
    i.type_desc         AS IndexType,
    i.is_unique,
    i.is_primary_key,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns
FROM   sys.indexes i
JOIN   sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN   sys.columns c        ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE  i.object_id = OBJECT_ID('dbo.Orders')
  AND  ic.is_included_column = 0
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key;

-- 查看索引占用的页数和大小
SELECT
    i.name         AS IndexName,
    i.type_desc    AS IndexType,
    p.rows,
    SUM(a.total_pages)  * 8 AS TotalKB,
    SUM(a.used_pages)   * 8 AS UsedKB,
    SUM(a.data_pages)   * 8 AS DataKB
FROM   sys.indexes i
JOIN   sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN   sys.allocation_units a ON p.partition_id = a.container_id
WHERE  i.object_id = OBJECT_ID('dbo.Orders')
GROUP BY i.name, i.type_desc, p.rows;

4.1.2 非聚簇索引（Non-Clustered Index）

非聚簇索引是独立于数据的 B+Tree 结构，叶子节点存储索引键值 + 聚簇索引键（书签）。

-- 创建普通非聚簇索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
WITH (FILLFACTOR = 90,         -- 页填充率，90%=留10%空间给插入
      SORT_IN_TEMPDB = ON,     -- 在 TempDB 构建，减少对生产文件的IO
      ONLINE = ON,             -- 在线创建，不阻塞 DML（Enterprise版）
      DATA_COMPRESSION = ROW); -- 行压缩

-- 覆盖索引（Covering Index）：包含查询所需的所有列，避免 Key Lookup
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate_Cover
ON dbo.Orders (CustomerID, OrderDate)      -- 键列（用于查找/排序）
INCLUDE (TotalAmount, Status);             -- 包含列（存在叶子节点，不影响排序）
-- 此索引可以覆盖：
-- SELECT CustomerID, OrderDate, TotalAmount, Status
-- FROM Orders WHERE CustomerID = ? AND OrderDate BETWEEN ? AND ?

-- 过滤索引（Filtered Index）：只对满足条件的行建索引（更小、更高效）
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON dbo.Orders (OrderDate, CustomerID)
WHERE Status = 1   -- 只对活跃订单建索引
WITH (FILLFACTOR = 95);

4.1.3 列存储索引（Columnstore Index）

列存储索引将数据按列存储，使用 VertiPaq 压缩，特别适合 OLAP/数仓场景，查询速度可提升 10~100 倍。

-- 非聚簇列存储索引（可与行存储索引共存）
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
ON dbo.Orders (OrderDate, CustomerID, TotalAmount, Status);

-- 聚簇列存储索引（整张表以列格式存储，无行存储）
-- 适合纯分析型表（历史数据、事实表）
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales
WITH (DROP_EXISTING = OFF);

-- 查看列存储索引状态
SELECT
    i.name,
    rg.state_desc,
    rg.total_rows,
    rg.deleted_rows,
    rg.size_in_bytes / 1024.0 / 1024 AS SizeMB
FROM sys.indexes i
JOIN sys.column_store_row_groups rg
    ON i.object_id = rg.object_id AND i.index_id = rg.index_id
WHERE i.object_id = OBJECT_ID('dbo.FactSales');

4.2 执行计划解读

4.2.1 获取执行计划

-- 方式1：SSMS 快捷键（Ctrl+M 开启实际执行计划，Ctrl+L 估计执行计划）

-- 方式2：在查询中请求 XML 执行计划
SET STATISTICS IO ON;      -- 显示逻辑读/物理读
SET STATISTICS TIME ON;    -- 显示CPU和耗时

-- 方式3：捕获到变量
DECLARE @PlanXML XML;
SELECT @PlanXML = query_plan
FROM sys.dm_exec_query_plan(
    (SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = @@SPID)
);
SELECT @PlanXML;

4.2.2 常见执行计划算子

算子	含义	好/坏
Clustered Index Seek	按聚簇索引键精确查找	✅ 最优
Index Seek	按非聚簇索引精确查找	✅ 很好
Key Lookup	非聚簇索引找到行后回聚簇索引取其他列	⚠️ 大量时需优化（加覆盖索引）
Clustered Index Scan	扫描整张表	❌ 大表时性能差
RID Lookup	堆表（无聚簇索引）的行标识符查找	⚠️ 建议建聚簇索引
Hash Match (Join)	哈希连接，适合大表	中等
Nested Loops (Join)	嵌套循环，适合小表驱动大表+索引	视情况
Merge Join	归并连接，要求两侧已排序	✅ 合适场景最优
Sort	排序，消耗内存/TempDB	⚠️ 大量时考虑索引排序
Spill to TempDB	内存不足溢出到TempDB	❌ 性能杀手

-- 分析 Key Lookup 的优化案例
-- 假设有索引：IX_Orders_CustomerID ON Orders(CustomerID)
-- 执行下面的查询会产生 Key Lookup
SELECT CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 1001;
-- 执行计划：Index Seek → Key Lookup（取 OrderDate, TotalAmount）

-- 优化：将 OrderDate, TotalAmount 加入 INCLUDE
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cov
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount)
WITH (DROP_EXISTING = ON);
-- 现在变为：Index Seek（无需 Key Lookup）

4.2.3 读取 STATISTICS IO 输出

SET STATISTICS IO ON;
SELECT * FROM Orders WHERE CustomerID = 1001;
-- 输出：
-- Table 'Orders'. Scan count 1, logical reads 5, physical reads 0,
-- read-ahead reads 0, lob logical reads 0, lob physical reads 0.

-- logical reads：逻辑读（从 Buffer Pool 读取的页数）
-- physical reads：物理读（从磁盘读取，不在缓存中）
-- 优化目标：最小化 logical reads（减少页扫描）

4.3 统计信息与基数估计

4.3.1 统计信息原理

SQL Server 查询优化器依赖统计信息（Statistics）来估算每个操作符返回的行数（基数），从而选择最佳执行计划。

-- 查看统计信息列表
SELECT
    st.name         AS StatName,
    c.name          AS ColumnName,
    st.auto_created,
    st.is_incremental,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM   sys.stats st
JOIN   sys.stats_columns sc ON st.object_id = sc.object_id AND st.stats_id = sc.stats_id
JOIN   sys.columns c        ON sc.object_id = c.object_id AND sc.column_id = c.column_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
WHERE  st.object_id = OBJECT_ID('dbo.Orders');

-- 查看某列统计信息的直方图
DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_CustomerDate_Cover');

-- 手动更新统计信息
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;        -- 全表扫描，最精确
UPDATE STATISTICS dbo.Orders WITH SAMPLE 30 PERCENT; -- 30%采样

-- 数据库级别：启用自动更新统计信息
ALTER DATABASE [YourDB] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [YourDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON;  -- 异步更新（推荐）

4.3.2 参数嗅探（Parameter Sniffing）

参数嗅探是 SQL Server 的一个优化机制，但在某些场景下会导致严重的性能问题。

-- 问题场景演示
-- 存储过程第一次执行时用参数 CustomerID=1（大客户，10000行）
-- 优化器选择了 Table Scan 的计划并缓存
-- 之后 CustomerID=9999（小客户，2行）复用了同一个计划
-- 本应 Index Seek 的查询变成了 Table Scan

CREATE PROCEDURE dbo.usp_GetCustomerOrders
    @CustomerID INT
AS
    SELECT CustomerID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;

-- 查看当前缓存的计划
SELECT
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgMs,
    qp.query_plan,
    qt.text
FROM   sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) qt
WHERE  OBJECT_NAME(ps.object_id) = 'usp_GetCustomerOrders';

-- 解决方案1：OPTION (RECOMPILE) 每次重新编译（适合参数分布差异大的场景）
CREATE PROCEDURE dbo.usp_GetCustomerOrders_v2
    @CustomerID INT
AS
    SELECT CustomerID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);

-- 解决方案2：OPTIMIZE FOR UNKNOWN（使用平均统计估算）
CREATE PROCEDURE dbo.usp_GetCustomerOrders_v3
    @CustomerID INT
AS
    SELECT CustomerID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- 解决方案3：拆分存储过程（按参数特征走不同代码路径）
CREATE PROCEDURE dbo.usp_GetCustomerOrders_v4
    @CustomerID INT
AS
    DECLARE @RowEst INT;
    SELECT @RowEst = rowcnt
    FROM sysindexes
    WHERE id = OBJECT_ID('Orders') AND indid IN (0,1);
    -- 大客户走特定逻辑
    ...

4.4 索引维护策略

4.4.1 索引碎片检测与整理

-- 检测索引碎片（针对指定表或全库）
SELECT
    DB_NAME()                                    AS DatabaseName,
    SCHEMA_NAME(t.schema_id) + '.' + t.name      AS TableName,
    i.name                                       AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.record_count,
    ips.avg_page_space_used_in_percent
FROM   sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED'     -- DETAILED更准确但更慢
) ips
JOIN sys.tables t  ON ips.object_id = t.object_id
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE  ips.database_id = DB_ID()
  AND  ips.page_count > 1000          -- 过滤小索引（维护小索引意义不大）
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 索引维护策略：
-- 碎片 < 5%：不处理
-- 碎片 5-30%：REORGANIZE（在线，不会阻塞，速度慢）
-- 碎片 > 30%：REBUILD（可在线ONLINE=ON，重建B+Tree）

-- 自适应索引维护脚本
DECLARE @TableName    NVARCHAR(256);
DECLARE @IndexName    NVARCHAR(256);
DECLARE @Frag         FLOAT;
DECLARE @SQL          NVARCHAR(MAX);

DECLARE idx_cursor CURSOR FOR
    SELECT
        SCHEMA_NAME(t.schema_id) + '.' + t.name,
        i.name,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.tables t  ON ips.object_id = t.object_id
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.page_count > 1000
      AND ips.avg_fragmentation_in_percent > 5
      AND i.type > 0;  -- 排除堆

OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName, @Frag;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Frag > 30
        SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) +
                   N' ON ' + @TableName + N' REBUILD WITH (ONLINE=ON, DATA_COMPRESSION=ROW)';
    ELSE
        SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) +
                   N' ON ' + @TableName + N' REORGANIZE';

    PRINT @SQL;
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName, @Frag;
END;

CLOSE idx_cursor;
DEALLOCATE idx_cursor;

4.4.2 缺失索引与多余索引分析

-- 系统推荐的缺失索引（慎用！仅供参考，不能无脑添加）
SELECT TOP 20
    ROUND(d.avg_total_user_cost *
          d.avg_user_impact *
          (d.user_seeks + d.user_scans), 0) AS Score,
    d.avg_user_impact AS EstimatedImpact_Pct,
    d.user_seeks,
    d.user_scans,
    DB_NAME(id.database_id)                  AS DatabaseName,
    id.statement                             AS TableName,
    id.equality_columns,
    id.inequality_columns,
    id.included_columns,
    -- 生成建议的 CREATE INDEX 语句
    N'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(id.object_id) +
    N'_' + CAST(d.avg_user_impact AS VARCHAR) +
    N' ON ' + id.statement +
    ISNULL(N' (' + id.equality_columns + N')', N'') +
    ISNULL(N', ' + id.inequality_columns, N'') +
    N' INCLUDE (' + ISNULL(id.included_columns, '') + N')' AS SuggestedSQL
FROM   sys.dm_db_missing_index_details id
JOIN   sys.dm_db_missing_index_groups ig ON id.index_handle = ig.index_handle
JOIN   sys.dm_db_missing_index_group_stats d ON ig.index_group_handle = d.group_handle
WHERE  id.database_id = DB_ID()
ORDER BY Score DESC;

-- 查找从未使用的索引（可考虑删除）
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    i.name AS IndexName,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM   sys.indexes i
JOIN   sys.tables t ON i.object_id = t.object_id
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE  i.type > 0
  AND  i.is_primary_key = 0
  AND  i.is_unique = 0
  AND  (ius.user_seeks IS NULL OR ius.user_seeks = 0)
  AND  (ius.user_scans IS NULL OR ius.user_scans < 10)
  AND  ISNULL(ius.user_updates, 0) > 100   -- 一直在被更新但从不被查询
ORDER BY ISNULL(ius.user_updates, 0) DESC;

4.5 小结

概念	关键结论
聚簇索引	表数据即索引；每表只能有1个；首选自增整数主键
非聚簇索引	单独B+Tree；叶子节点含聚簇键，需回表
覆盖索引	INCLUDE 将所需列加入叶子，消灭 Key Lookup
过滤索引	WHERE 条件缩小索引范围，减少大小，提高效率
列存储索引	OLAP 首选；压缩率高；批量扫描快
Key Lookup	非聚簇索引 Seek 后回表，大量出现需加 INCLUDE 优化
参数嗅探	RECOMPILE / OPTIMIZE FOR UNKNOWN 解决计划错误复用
碎片整理	5-30% REORGANIZE；>30% REBUILD WITH ONLINE=ON

下一章：事务、锁与并发控制

索引原理与执行计划优化

索引原理与执行计划优化

4.1 B+Tree 索引内部结构

4.1.1 聚簇索引（Clustered Index）

4.1.2 非聚簇索引（Non-Clustered Index）

4.1.3 列存储索引（Columnstore Index）

4.2 执行计划解读

4.2.1 获取执行计划

4.2.2 常见执行计划算子

4.2.3 读取 STATISTICS IO 输出

4.3 统计信息与基数估计

4.3.1 统计信息原理

4.3.2 参数嗅探（Parameter Sniffing）

4.4 索引维护策略

4.4.1 索引碎片检测与整理

4.4.2 缺失索引与多余索引分析

4.5 小结

相关阅读

最新文章

还在为服务器上的问题烦恼？有了智能终端，我再也不怕了！

告别 Token 焦虑，让 AI Agent 24 小时为你打工

在 CNB 平台部署 OpenClaw，API Key 免费用，30秒搞定！

LinkUp：打破数据孤岛，新一代轻量级企业级数据集成平台深度解析

记一次投影连接网络存储

第6章数据库集成

热门文章

本栏目文章

索引原理与执行计划优化

索引原理与执行计划优化

4.1 B+Tree 索引内部结构

4.1.1 聚簇索引（Clustered Index）

4.1.2 非聚簇索引（Non-Clustered Index）

4.1.3 列存储索引（Columnstore Index）

4.2 执行计划解读

4.2.1 获取执行计划

4.2.2 常见执行计划算子

4.2.3 读取 STATISTICS IO 输出

4.3 统计信息与基数估计

4.3.1 统计信息原理

4.3.2 参数嗅探（Parameter Sniffing）

4.4 索引维护策略

4.4.1 索引碎片检测与整理

4.4.2 缺失索引与多余索引分析

4.5 小结

相关阅读

最新文章

还在为服务器上的问题烦恼？有了智能终端，我再也不怕了！

告别 Token 焦虑，让 AI Agent 24 小时为你打工

在 CNB 平台部署 OpenClaw，API Key 免费用，30秒搞定！

LinkUp：打破数据孤岛，新一代轻量级企业级数据集成平台深度解析

记一次投影连接网络存储

第6章 数据库集成

热门文章

本栏目文章

第6章数据库集成