SQL SERVER 2016/2019 非聚集列存储索引大表JOIN小表性能问题

Question

有一个两亿记录的大表和一个仅有一条记录的小表通过INNER JOIN 关联（大表中创建的非聚集列存储索引包含了关联字段）。两表关联查询，执行用时大概一分钟左右。如果使用小表中的关联值直接对大表过滤不到1秒。 通过查看执行计划，使用常数对创建了非聚集列存储索引的大表进行查询时，大表的列存储索引扫描中存在一个常量谓词；但与小表关联时，大表的非聚集列存储索引扫描中没有使用谓词。大表与小表关联，除了再针对关联字段创建一个普通的非聚集索引外，还有什么办法才能提高性能? 关联有限数据的小表时，包含了列存储索引的表能否像使用常量过滤一样获得优化？

Answer 1

你好，

如果可以的话，请分享一下语句和执行计划。

你可以尝试创建聚集索引; 也可以尝试用where in子句句查询，当INNER JOIN 表中列数据是重复的，此时IN性能要INNER JOIN要好

希望对你有用

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

感谢您的及时回复！

表已经有一个聚集索引。引入非聚集列式存储索引，是为了对数据进行分析时获得更好的性能。

可参见下面的图片（因无法上传，暂时贴了一个的链接地址，辛苦移步），其中DMS_DATA_SALE表是大表，T为仅5行记录的小表，他们通过ID进行关联。  不管是使用INNER JOIN，还是WHERE IN，在性能上都不太理想。 使用小表中的5个值，以常量形式对大表进行过滤，基本秒查。  我期望的是在关联类似小表时，优化器能识别出小表是几个有限值，能直接按照常量值的处理方式执行。尝试通过查找有无QUERY HINTS无果， 所以想问问官方能否对这个提供支持，谢谢！

1）INNER JOIN图片地址 https://social.microsoft.com/Forums/getfile/1595060

2) WHERE IN图片地址 https://social.microsoft.com/Forums/getfile/1595092

3) 使用常量图片地址 https://social.microsoft.com/Forums/getfile/1595095

Answer 3

你好，

查了相关文档，由于你的一个表很大，另一个表较小，我觉得你可以用inner Hash join。

Hash join可以有效处理未排序的大型非索引输入，它的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。你可以参考以下文章：

联接 (SQL Server)

多表连接的三种方式详解 hash join、merge join、 nested loop

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

改成INNER HASH JOIN性能还是不行的，执行时间也是在一分钟左右。

下面两种方式的执行计划显示用了 HASH MATCH

1）INNER JOIN图片地址 https://social.microsoft.com/Forums/getfile/1595060

2) WHERE IN图片地址 https://social.microsoft.com/Forums/getfile/1595092

Answer 5

Does non-clustered index include all columns used in where clause? 

Answer 6

不好意思，你的图片都看不了了，能否再发一下？

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

只是两个表的JOIN,小表里有5行数据，没有使用where条件。 大表里的除主键之外的字段全部字段创建了一个非聚集列存储索引。

Answer 8

把图片传到百度网盘上了,链接也无法插入，还得麻烦您复制到浏览器啊。

https://pan.baidu.com/s/1f0TSjciOkl4IQPk3w8qBow  提取码:2x47

https://pan.baidu.com/s/1Jg8VyOBfZLAe-1zhF4s0Ow  提取码:6y66

https://pan.baidu.com/s/1kvDqrVJ_4vyd-uXlrqoGVQ  提取码:hed9