MSSQL建索引后，如何使用才能提升查询效率？

在MSSQL中建立索引后,要充分发挥索引的性能优势，需要从查询优化、索引设计、维护策略等多个维度进行合理使用，索引的本质是一种数据结构（如B树、哈希表等），通过创建指向表中数据行的指针，加速数据的检索，但索引并非“万能药”，使用不当可能导致性能下降，因此需结合业务场景和查询特征进行优化。

索引的基本使用场景与查询优化

索引的核心作用是加速查询,尤其是针对WHERE、JOIN、ORDER BY、GROUP BY等子句中的列，当查询条件涉及索引列时，数据库引擎可通过索引快速定位数据，避免全表扫描（Table Scan），对于以下查询语句：

SELECT * FROM Users WHERE UserID = 1001;

若在UserID列上创建了聚集索引（Clustered Index），数据库引擎可直接通过索引的B树结构找到对应数据页，减少I/O操作，对于非聚集索引（Nonclustered Index），则需通过索引键回表（Key Lookup）获取完整数据行，因此常与覆盖索引（Covering Index）结合使用，避免回表操作。

覆盖索引是指索引包含了查询所需的所有列,

SELECT UserID, UserName FROM Users WHERE UserID = 1001;

若在UserID和UserName上创建非聚集索引，查询可直接从索引中获取数据，无需访问表数据页，显著提升性能。

索引设计与查询匹配

索引的使用效果高度依赖查询语句与索引结构的匹配度,以下设计原则可提升索引利用率：

1. 选择性高的列优先建索引：选择性（基数/行数）越高的列，索引的过滤效果越好，唯一列的选择性为1，适合作为索引键；而性别等低选择性列则不适合单独建索引。
2. 复合索引的顺序至关重要：复合索引（多列索引）的列顺序需遵循“最左前缀原则”，即查询条件需包含索引的最左列，对(A, B, C)创建的复合索引，查询条件包含A、或A和B、或A和B和C时，索引可生效；但仅包含B或C时，索引无效。
3. 避免索引列上的函数或表达式：若查询中对索引列使用函数（如WHERE SUBSTRING(UserName, 1, 3) = 'Adm'），会导致索引失效，可通过计算列或触发器预先处理数据。

执行计划与索引监控

使用索引后,需通过执行计划（Execution Plan）验证索引是否被正确使用，在SQL Server Management Studio（SSMS）中，执行查询后点击“包括实际的执行计划”，可查看以下关键信息：

• 表扫描（Table Scan）：表示未使用索引，全表扫描。
• 索引查找（Index Seek）：表示通过索引高效定位数据。
• 键查找（Key Lookup）/ RID查找（RID Lookup）：表示通过索引定位后，需回表获取数据，若频繁发生可能影响性能。

可通过动态管理视图（DMV）监控索引使用情况，

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(i.object_id) = 'Users';

若索引的user_seeks和user_scans为0，而user_updates较高，说明索引未被使用且增加了写入开销，可考虑删除。

索引维护与性能平衡

索引会占用存储空间,并降低INSERT、UPDATE、DELETE操作的速度（因需维护索引结构），因此需定期维护索引：

1. 重建与重组索引：对于碎片率（Fragmentation）超过30%的索引，可执行ALTER INDEX REBUILD重建；碎片率在10%-30%之间时，执行ALTER INDEX REORGANIZE重组。
2. 统计信息更新：统计信息（Statistics）是查询优化器选择索引的重要依据，可通过UPDATE STATISTICS TableName手动更新，或设置自动更新策略。
3. 避免过度索引：并非所有列都需要建索引，小表或高频更新的表需谨慎建索引，可通过sys.dm_db_index_operational_stats监控索引开销。

特殊索引类型的使用

除常规的聚集索引和非聚集索引外,MSSQL还提供特殊索引类型以适应特定场景：

1. 全文索引（Full-Text Index）：针对文本列（如文章内容）的关键词搜索，支持模糊匹配（如“包含‘数据库’的文档”），比LIKE ‘%关键词%’效率更高。
2. 列存储索引（Columnstore Index）：适用于数据仓库场景，通过列式存储压缩数据，加速聚合查询（如SUM、COUNT）。
3. 筛选索引（Filtered Index）：针对部分数据行创建索引，例如仅对“活跃用户”建索引，减少索引大小。

索引使用的最佳实践

1. 为外键列建索引：加速表连接操作，避免连接时的全表扫描。
2. 避免在索引列上使用隐式类型转换：如WHERE UserID = '1001'（ UserID为INT类型），会导致索引失效。
3. 分页查询优化：对于ORDER BY ... OFFSET ... FETCH分页语句，确保排序列和筛选列上有索引，避免排序操作。

相关问答FAQs

Q1: 为什么创建了索引后，查询仍然执行全表扫描？
A: 可能原因包括：查询条件对索引列使用了函数或表达式；复合索引未遵循最左前缀原则；查询条件中包含OR逻辑导致索引失效；统计信息过时导致优化器选择错误计划，可通过检查执行计划、优化查询语句或更新统计信息解决。

Q2: 索引是否越多越好？如何判断是否需要删除索引？
A: 索引并非越多越好，过多的索引会增加写入开销和存储成本，可通过以下指标判断是否删除索引：① 通过sys.dm_db_index_usage_stats查看索引的user_seeks和user_scans为0；② 索引的user_updates远大于user_seeks，说明写入频率高但查询使用率低；③ 执行DBCC SHOWCONTIG或sys.dm_db_index_physical_stats发现碎片率极低且无查询需求，满足条件时可删除索引以提升性能。