SQL Server 性能优化实战（第一期）：索引——查询加速的基石

无论你是开发人员还是 DBA，面对 SQL Server 的性能问题时，第一个想到的优化手段往往是“加索引”。但你真的理解索引的工作原理吗？为什么加了索引查询还是慢？为什么索引反而拖慢了写入？这一期，我们从最基础也最重要的索引开始，建立正确的认知框架。

一、为什么需要索引？

想象一下：你有一本 1000 页的书，没有目录，也没有页码。你想找到“索引优化”这一节，唯一的办法就是从第 1 页开始，一页一页翻下去——直到翻到第 800 页才找到目标。

这就是全表扫描。

SQL Server 中的索引，本质上就是书的目录。它是一种 B-Tree（平衡树） 结构，能够以对数级别的时间复杂度定位到数据行，而不是线性扫描整个表。

索引的核心价值：

• 大幅减少数据读取量（从百万行缩小到几行）
• 避免排序和临时表
• 帮助查找唯一值
• 加速 JOIN、GROUP BY、ORDER BY

二、索引的两大核心类型

2.1 聚集索引（Clustered Index）

• 数据行的物理排序依据：聚集索引的叶子节点就是完整的数据行。
• 每张表只能有一个：因为数据行只能按一种物理顺序存储。
• 推荐每张表都有聚集索引：没有聚集索引的表称为堆表（Heap）。

-- 创建聚集索引（通常在主键上自动创建）
CREATE CLUSTERED INDEX IX_Orders_OrderDate 
ON Orders(OrderDate);

💡 常见问题：主键默认就是聚集索引，但这不是绝对的。你可以将主键设为非聚集索引，也可以在不做主键的列上创建聚集索引。

2.2 非聚集索引（Non-Clustered Index）

• 叶子节点存储的是指向数据行的指针（如果表有聚集索引，指针就是聚集索引键；如果是堆表，指针就是 RID）
• 每张表可以有多个（最多 999 个）
• 常用于频繁作为查询条件的列（WHERE、JOIN、ORDER BY）

-- 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID 
ON Orders(CustomerID);

2.3 核心区别对比

三、索引是如何工作的？Seek vs Scan

3.1 Seek（查找）

• 利用 B-Tree 结构直接定位到符合条件的行
• 复杂度：O(log N)
• 对于 100 万行数据，Seek 大约只需要 20 次逻辑读取

3.2 Scan（扫描）

• 遍历整个索引或整个表的所有行
• 复杂度：O(N)
• 100 万行数据 = 至少 100 万次读取

3.3 一个直观的演示

-- 准备测试数据（100万行）
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1),
    OrderDate DATE,
    CustomerID INT,
    Amount DECIMAL(10,2)
);

-- 插入100万条随机数据
WITH Numbers AS (
    SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO Orders (OrderDate, CustomerID, Amount)
SELECT 
    DATEADD(day, n % 3650, '2020-01-01'),
    (n % 10000) + 1,
    ROUND(RAND(CHECKSUM(NEWID())) * 10000, 2)
FROM Numbers;
GO

-- 第一次查询：无索引，全表扫描
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT * FROM Orders WHERE CustomerID = 12345;

-- 观察输出：逻辑读取次数很大（约 3000-4000 次）
-- 执行计划：Table Scan

-- 添加索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID);
GO

-- 再次查询
SELECT * FROM Orders WHERE CustomerID = 12345;

-- 观察输出：逻辑读取次数大幅降低（约 10-20 次）
-- 执行计划：Index Seek + Key Lookup

四、常见索引误区（以及正确做法）

❌ 误区 1：索引越多越好

事实：每增加一个非聚集索引，INSERT、UPDATE、DELETE 操作都要同时维护该索引。索引不是免费的。

建议：定期使用 DMV 检查未使用的索引，及时删除。

❌ 误区 2：所有表都应该有聚集索引

事实：90% 的表都应该有聚集索引，但存在少数例外——比如极端插入性能要求的日志表，堆表可能更快（没有聚集索引的插入开销）。

建议：除非有明确的理由，否则为每张表创建聚集索引。

❌ 误区 3：WHERE 列建了索引就能加速

事实：以下情况索引可能被忽略：

• 对索引列使用函数：WHERE YEAR(OrderDate) = 2024
• 数据类型隐式转换：WHERE OrderID = '123'（OrderID 是 INT）
• 前导通配符：WHERE Name LIKE '%Smith'
• 低选择性列（如性别：男/女），优化器可能认为扫描更便宜

建议：定期查看执行计划，确认索引是否被实际使用。

五、快速诊断：你的索引健康吗？

5.1 找出从未使用过的索引

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND (s.user_seeks + s.user_scans + s.user_lookups = 0 OR s.user_seeks IS NULL)
    AND i.name IS NOT NULL
ORDER BY ISNULL(s.user_updates, 0) DESC;

对于 user_seeks/scans/lookups 全为 0 的索引，说明自上次服务重启以来从未被查询使用过，建议评估后删除。

5.2 找出缺失的索引（SQL Server 自动推荐）

SELECT 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Score,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_total_user_cost
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY Score DESC;

按 Score 降序排列，Score 越高表示创建该索引的潜在收益越大。

5.3 检查索引碎片

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.avg_page_space_used_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 > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;

碎片率 > 30% 时建议重新组织或重建索引。

六、核心总结

一句话记住本期内容：

索引是查询加速的基石，但缺少索引一定慢，索引过多也一定慢——关键在于平衡。

下一期预告

复合索引与列顺序的奥秘

• 为什么 (A, B) 和 (B, A) 完全不同？
• 如何选择索引列的先后顺序？
• 什么是覆盖索引？如何避免 Key Lookup？
• 实战：一个复合索引如何同时加速 5 种查询？

📌 本文所有代码均在 SQL Server 2019+ 环境下验证通过。如果你在阅读中有任何疑问，或者想深入了解某个知识点，欢迎留言交流。

本系列持续更新中，点击关注不错过下一期。