我正在SQL Server中准备一个存储过程。基本上,我这里有两个表,表 A 和 B,这两个表连接在Col4
上。此存储过程将根据表 B 中的属性筛选表 A,例如where
子句中的Col41
和Col42
(Col41
和Col42
都是float
数据类型(。
表 A:
Col1 Col2 Col3 Col4
Row1 ** ** ** **
Row2 ** ** ** **
表 B:
Col5 Col4 Col41 Col42
Row1 ** ** ** **
Row2 ** ** ** **
这两个表中有很多记录,因此我计划使用非聚集索引来帮助查询(两个表中的第一列都设置为主键(。
当我创建这个非聚集索引(如下所示(,然后在存储过程(临时索引(中使用后将其删除时,性能非常好。
CREATE NONCLUSTERED INDEX IX_1 ON tableB (Col41, Col42)
DROP INDEX IF EXISTS IX_1 ON tableB
但是,如果我直接将相同的非聚集索引添加到tableB(永久索引(,要么右键单击tableB的索引文件夹,要么运行以下代码,性能就会差很多。 似乎即使创建了非聚集索引,也没有使用它。
CREATE NONCLUSTERED INDEX IX_1 ON tableB (Col41, Col42)
无法弄清楚为什么运行结果是这样的。任何人都可以给出如何解决它的任何想法吗?谢谢。
对于您的情况,您需要两个索引 -
tableB(Col41, Col42)
tableA(Col4)
如果您仍然看到问题,如 AlwaysLearning 所述,请检查执行计划,它可以告诉您如何访问数据的详细信息。
正如@Mitch Wheat所提到的,添加索引并不能确保它始终被使用。还有一件事,不要在存储过程中创建和删除索引,除非您是在临时表上创建的。创建索引是一项成本高昂的操作。最好在开发代码之外单独执行此操作。
这取决于特定查询的查询和索引的选择性。有一种称为"临界点"的东西将检查使用索引或直接从表中读取是否适用于特定查询。 阅读更多关于金伯利·特里普的引爆点
关于您的查询,当您告诉 col41 和 col42 将始终用于过滤器并使用 Col4 与 TableA 连接时,我建议您像下面这样覆盖索引,以使索引可搜索。如果组合是唯一的,请创建唯一索引。
CREATE NONCLUSTERED INDEX Idx_TableB_Col41_Col42_Col4 ON TableB(Col41,Col42,Col4)
但是,在这里您必须查看列的选择性才能查看列的顺序:Col41 和 Col42。选择性更强的列将出现在左侧,然后是右侧的选择性较低的列。
要查找列的选择性,请参阅列统计信息。 将有第二个结果集,它告诉列的密度。 它越小,列的选择性就越强。
EXEC DBCC SHOW_STATISTICS(TableB, ColumnStatisticsName)
此外,在表 A 上为 Col4 创建索引。如果列是唯一的,请创建唯一索引。
CREATE NONCLUSTERED INDEX Idx_TableA_col4 ON TableA(Col4)
警告:以上也不能保证索引将始终被使用。没有看到执行计划和实际查询,很难判断。上述准则也专门针对一个查询。我建议您看看,是否可以将上述索引与现有查询合并到其他索引,以减少表上的索引占用量。