当前位置： 首页 > news >正文

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

news 2026/4/22 9:20:01

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

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 核心区别对比

对比项 聚集索引 非聚集索引
每表数量 1 最多 999
叶子节点内容 完整数据行 指针（RID 或聚集键）
查找方式 直接定位 先找指针，再回表查数据
物理顺序 决定表存储顺序 不影响表存储顺序
空间占用 较大（包含所有列） 较小（仅索引列+指针）

三、索引是如何工作的？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 nFROM 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') = 1AND (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% 时建议重新组织或重建索引。

六、核心总结

要点 说明
索引的本质 B-Tree 结构，相当于书的目录
聚集索引 每表一个，叶子节点存完整数据行
非聚集索引 每表最多 999 个，叶子节点存指针
Seek vs Scan Seek 是 O(log N)，Scan 是 O(N)
索引不是万能的 维护有成本，查询写法会影响使用
定期检查 清理无用索引，补充缺失索引，处理碎片

一句话记住本期内容：

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

下一期预告

复合索引与列顺序的奥秘

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

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

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