一、为什么需要索引想象一下你有一本 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 Scan3.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% 的表都应该有聚集索引但存在少数例外——比如极端插入性能要求的日志表堆表可能更快没有聚集索引的插入开销。建议除非有明确的理由否则为每张表创建聚集索引。❌ 误区 3WHERE 列建了索引就能加速事实以下情况索引可能被忽略对索引列使用函数WHERE YEAR(OrderDate) 2024数据类型隐式转换WHERE OrderID 123OrderID 是 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% 时建议重新组织或重建索引。六、核心总结要点说明索引的本质B-Tree 结构相当于书的目录聚集索引每表一个叶子节点存完整数据行非聚集索引每表最多 999 个叶子节点存指针Seek vs ScanSeek 是 O(log N)Scan 是 O(N)索引不是万能的维护有成本查询写法会影响使用定期检查清理无用索引补充缺失索引处理碎片一句话记住本期内容索引是查询加速的基石但缺少索引一定慢索引过多也一定慢——关键在于平衡。下一期预告复合索引与列顺序的奥秘为什么(A, B)和(B, A)完全不同如何选择索引列的先后顺序什么是覆盖索引如何避免 Key Lookup实战一个复合索引如何同时加速 5 种查询
