第65章 SQL Server索引优化

课程目录第65章 SQL Server索引优化

65、SQL Server索引优化：聚集索引/非聚集索引、包含列索引、索引重建、统计信息更新

索引优化，说白了就是给数据表搭“快速通道”。你想想看，一张表几百万行数据，没有索引，SQL Server就得从头扫到尾，那叫一个慢。我早年接手过一个ERP系统，月底跑报表要等半小时，后来加了几个索引，直接缩到两分钟。嗯，这就是索引的威力。

但索引也不是越多越好。建多了，写入变慢，磁盘空间也吃紧。所以这一讲，我带你把这几个核心概念彻底捋清楚：聚集索引、非聚集索引、包含列索引、索引重建、还有统计信息更新。这些都是实战里天天要打交道的活儿。

1. 聚集索引：数据的“物理排序”

聚集索引决定了数据在磁盘上的物理存储顺序。每张表只能有一个聚集索引，因为数据行本身只能按一种顺序排列。我习惯把聚集索引想象成一本书的页码——你按页码找内容，直接翻到那一页就行。

在SQL Server里，如果你不指定，主键默认就是聚集索引。但这不是绝对的。我记得有一次做订单表，主键是自增ID，但业务查询经常按“下单时间”范围查。这时候把聚集索引建在时间列上，反而更合适。当然，前提是你要权衡插入性能。

  实战建议： 如果表经常做范围查询（BETWEEN、>、<），把聚集索引建在查询条件列上。如果频繁插入且主键是自增，用主键做聚集索引也没毛病。

-- 创建聚集索引（如果表没有主键，可以手动建）
CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders(OrderDate);
-- 注意：如果表已有主键，默认就是聚集索引，需要先删除约束再重建

2. 非聚集索引：逻辑上的“快速查找”

非聚集索引不改变数据物理顺序，它单独维护一套索引结构。叶子节点存的是索引键值 + 指向数据行的书签（聚集索引键或RID）。你可以建多个非聚集索引，但每个都会占用额外空间。

我见过最夸张的一个表，建了12个非聚集索引，写入慢得像蜗牛。后来砍掉一半，性能反而上来了。所以我的原则是：非聚集索引只给高频查询用，而且尽量窄——键列越少越好。

小技巧： 用 sys.dm_db_index_usage_stats 查看索引使用情况，把长期没被用到的索引果断删掉。

-- 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID);
-- 包含其他列避免回表（见下一节）

3. 包含列索引：覆盖查询的“秘密武器”

包含列索引是非聚集索引的增强版。它允许你把非键列加到索引的叶子节点，但不参与索引排序。这样查询需要的所有列都在索引里，SQL Server就不需要回表查数据页了，IO开销大大降低。

我曾经优化过一个报表查询，原来要扫描整个表，加了包含列索引后，执行计划从“表扫描”变成了“索引扫描”，耗时从8秒降到0.3秒。嗯，这就是覆盖查询的魅力。

-- 包含列索引示例：查询经常查CustomerID和OrderDate，但只返回Status
CREATE NONCLUSTERED INDEX IX_Orders_Covering
ON dbo.Orders(CustomerID, OrderDate)
INCLUDE (Status);
-- 这样查询 SELECT CustomerID, OrderDate, Status FROM Orders WHERE CustomerID = 123
-- 完全走索引，不用回表

注意： 包含列不要加太多，否则索引会变得很宽，影响写入性能。一般只包含查询中频繁返回的列。

4. 索引重建与重组：对抗碎片

索引用久了，会产生碎片。碎片率高了，查询性能就会下降。我见过一个系统，索引碎片率超过70%，查询慢得让人抓狂。重建索引相当于把索引彻底重新组织，碎片率归零；重组则是轻量级的碎片整理。

我个人习惯：碎片率 > 30% 用重建，5%~30% 用重组，低于5% 不用管。当然，重建会锁表，生产环境要安排在维护窗口。

-- 重建索引（碎片率高时使用）
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REBUILD;

-- 重组索引（碎片率中等时使用）
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

-- 查看碎片率
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

5. 统计信息更新：优化器的“眼睛”

统计信息告诉查询优化器数据分布情况。如果统计信息过时，优化器可能选错执行计划。比如明明有索引，它偏要走全表扫描。我曾经踩过这个坑：一张表数据从10万涨到100万，统计信息没更新，查询计划还是按10万行来估算，结果慢得离谱。

SQL Server默认会自动更新统计信息，但阈值是“20% + 500行”的变化。对于大表，这个阈值可能不够敏感。我建议对频繁变更的大表，手动设置更频繁的更新计划。

-- 手动更新统计信息
UPDATE STATISTICS dbo.Orders;
-- 或者只更新特定索引的统计信息
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID;

-- 查看统计信息上次更新时间
SELECT 
    name AS StatsName,
    STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats
WHERE object_id = OBJECT_ID('dbo.Orders');

  核心总结： 索引优化不是一次性工作。你需要持续监控碎片率、统计信息新鲜度、以及查询模式的变化。我每季度会做一次索引健康检查，把长期不用的索引下线，把碎片严重的重建。这样数据库才能保持“年轻”状态。

好了，这一讲的内容就到这里。索引优化是个实践活，多看看执行计划，多试试不同索引组合，慢慢你就有感觉了。

📚 课程目录

65、SQL Server索引优化：聚集索引/非聚集索引、包含列索引、索引重建、统计信息更新

1. 聚集索引：数据的“物理排序”

2. 非聚集索引：逻辑上的“快速查找”

3. 包含列索引：覆盖查询的“秘密武器”

4. 索引重建与重组：对抗碎片

5. 统计信息更新：优化器的“眼睛”