解决SQL Server中变量查询导致索引失效的优化策略

本文主要探讨了在SQL Server中遇到的索引失效问题，特别是在使用唯一非聚集索引时，当查询参数为变量时导致查询性能下降的情况。问题的核心在于SQL Server在处理变量查询时，即使存在针对`Mac2`字段的唯一非聚集索引`IX_SF_CP_Detail_MAC2`，由于变量的动态性质，可能导致索引失效，从而触发全表扫描。 场景一中，作者首先识别出一个慢速的SQL查询，其使用了变量`@SN`进行匹配，而该字段被定义为唯一非聚集索引。通过观察执行计划，发现尽管存在索引，但查询并未利用它，而是进行了全表扫描，导致逻辑读取了大量数据（1267,942次）。这表明在变量查询条件下，SQL Server没有正确使用索引来加速搜索。 解决方法尝试1是将唯一非聚集索引改为非聚集索引，这使得查询能够利用索引，解决了部分问题。然而，对于需要验证唯一性的场景，单纯改变索引类型并不能满足需求。 场景二中，作者面临的问题是如何在确保唯一性的同时，充分利用索引。尝试2中恢复唯一非聚集索引，并配合查询选项`option (recompile)`，虽然理论上这可以迫使SQL Server重新编译查询计划，但这样做并不适用于所有情况，且随着数据量增大，频繁重新编译会导致性能下降。 最终解决方案是保持原有的唯一非聚集索引，同时添加一个新的非聚集索引，如`IX_SF_Cp_Detail_Mac2`。这样，当需要验证唯一性时，可以使用第一个索引，而当变量查询时，可以利用第二个非聚集索引，从而兼顾了查询效率和唯一性验证的需求。这种策略避免了对每个程序都添加`option (recompile)`，减轻了性能压力。 总结来说，本案例重点在于理解SQL Server如何处理变量查询和索引的使用，以及如何在满足特定业务需求（如唯一性）的同时，优化查询性能。通过创建多个索引并合理利用，可以在变量查询场景下提升SQL Server的执行效率。