下面是一个脚本,可用于重现我面临的问题。基本上问题是这样的:为什么 (ID1 = 6, ID2 = 7) 的记录没有填充到最终的#tt2 表中?我可以手动插入它,所以它不会违反 UNIQUE 约束,但它不会作为 INSERT/SELECT 的一部分填充。
USE [tempdb]
GO
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
CREATE TABLE [#t1] ([ID] INT , [V] VARCHAR(10))
INSERT INTO #t1 (ID, V)
VALUES
(1,'A'),
(2,'B'),
(3,'B'),
(4,'C'),
(5,'E'),
(6,'E')
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2
CREATE TABLE [#t2] ([ID] INT , [V] VARCHAR(10))
INSERT INTO #t2 (ID, V)
VALUES
(1,'A'),
(2,'B'),
(3,'C'),
(4,'C'),
(5,'D'),
(6,'E'),
(7,'E')
IF OBJECT_ID('tempdb..#tt') IS NOT NULL DROP TABLE #tt
SELECT t1.ID AS ID1, t2.ID AS ID2, t1.V AS V
INTO #tt
FROM #t1 t1
JOIN #t2 t2 ON t1.V=t2.V
--SELECT * FROM #tt
IF OBJECT_ID('tempdb..#tt2') IS NOT NULL DROP TABLE #tt2
CREATE TABLE #tt2 (ID1 INT, ID2 INT, V VARCHAR(10))
CREATE UNIQUE INDEX IDX_TT_ID1 ON #tt2 (ID1) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE INDEX IDX_TT_ID2 ON #tt2 (ID2) WITH (IGNORE_DUP_KEY = ON)
-- Query 1 - this SELECT returns 9 records.
SELECT ID1, ID2, V
FROM #tt
ORDER BY ID1, ID2
-- Query 2 - this INSERT populates 4 records, but I would expect 5
INSERT INTO #tt2 (ID1, ID2, V)
SELECT ID1, ID2, V
FROM #tt
ORDER BY ID1, ID2
-- Why are there only 4 records in this table? (I would expect 5, but the record with ID1 = 6, ID2 = 7 is missing)
SELECT * FROM #tt2
-- I can INSERT the missing record manually:
INSERT INTO #tt2 (ID1, ID2, V)
VALUES (6, 7, 'E')
我认为出现混淆是因为索引分别定义为 ID1 和 ID2 上的单列。这意味着对每个单独执行“忽略重复”检查,并且只有当一行通过两项检查时,它才会被插入到最终的 #tt2 表中。我会一步一步来展示工作。
系统检查重复项的一种方法是对行进行排序,然后按顺序处理它们。这就是我的本地(2017)实例上发生的情况。那么让我们看一下#tt的内容,有序:
从第 i 行开始,系统会询问“我已经看到 ID1=1 还是已经看到 ID2 = 1?” 由于两者都是假行,因此将 i 传递到输出。与第二行类似。然而,对于第 iii 行,ID2=2 与已经看到的第 ii 行匹配。所以第三行被拒绝。接受第 iv 行。第 v 行被拒绝,因为它与第 iv 行具有相同的 ID1 值。接受第 vi 行。第 vii 行被拒绝 - ID1=5 与第 vi 行匹配。第 iix 行被拒绝,因为 ID2=6 匹配第 vi 行。最后,第 ix 行在 ID1(匹配 iix)和 ID2(匹配 vii)中都被拒绝。
输出是 i, ii, iv, vi 并且这些被插入到#tt2。
之后可以插入 {6,7,E},因为它不会与通过过滤并实际插入 #tt2 的任何行冲突。ID1=6 的所有行由于其 ID2 值而被消除,而 ID2=7 的行由于其 ID1 值而被消除。
以上是产生观察输出的逻辑算法的解释。正如在实际执行计划中所观察到的,该算法的物理实现是不同的,但等效的。这是计划: 图 1 - INSERT 的实际执行计划
物理计划按 ID1(图像中的 A)排序,并为每个值 (B) 取第一行。然后将其输出按 ID2 (C) 排序,并且再次保留每个值的第一行 (D)。无论哪一行通过两个过滤器,都将插入#tt2。
这里棘手的部分是“第一”这个词。关系数据库表没有内在顺序。查询优化器 (QO) 可以自由地以它选择的任何顺序处理行,只要它的结果在逻辑上等同于提交的 SQL。这让我想知道是否可以在不更改查询的情况下从相同的数据产生不同的结果。我可以。
排序很昂贵,因此如果 QO 有其他选项,它通常会选择其他选项。一种这样的选择是读取索引。我发现在#tt.ID1 上有一个索引,QO 会使用它并跳过排序。SQL Server 的排序是保留顺序的,因此如果我可以在备用排序中显示 ID2,将为每个 ID1 值选择不同的“第一”行,并且整体输出将不同。我创建了一个与#tt2 相同的新目标表#tt3 并运行了它(ORDER BY 不影响这个INSERT)
制作了这个
这与之前的结果 i、ii、iv、vi 不同。
您会注意到,如果您按 ID1 asc, ID1 desc .. 对 #tt 的内容进行排序。
.. 并应用逻辑“我见过”算法获得此替代结果。
问题仍然存在,为什么 QO 先处理 ID1 再处理 ID2?我相信这是由定义约束的顺序驱动的。我原以为将 #tt 聚集索引定义为 (ID2 asc, ID1 desc) 会导致 ID2 首先被处理,因为这样可以避免排序。它不是。它仍然首先处理 ID1,并带回排序运算符。实际上,在 V 列中创建另一个约束会相应地将其添加到计划中。似乎 sys.indexes.index_id 顺序中的处理可能在 QO 中被硬编码?
总之,我认为建议谨慎行事。物理层的变化会影响这个查询的结果。如果目标表的约束被删除并重新创建,我们最好希望它们以与以前相同的顺序返回。如果源表经过优化,比如使用索引,它可能会影响访问路径以及哪一行“首先”出现。这没有考虑分配顺序扫描或旋转木马扫描。
如果涉及更多行,则 QO 很可能会换成使用散列连接。天知道这会如何影响事情。
我认为我们在这里观察到的看起来像一个错误。文档指出,与
IGNORE_DUP_KEY = ON
基于此,无法插入源数据中的一行但之后可以插入而不会发生冲突的情况看起来是不可接受的(对我而言)。
我相信
INSERT
在这种情况下查询的正确逻辑应该是:“插入行是否违反了目标表中的任何约束?”。如果“否” - 应该插入行,否则跳过。优化器可以自由地更有效地实现这个简单的逻辑,但不会破坏其语义。假设我们按以下顺序一一插入行:
行
{1,1,A}
和{2,2,B}
可以插入,然后{3,2,B}
应该跳过行,因为它的ID2
列值与先前插入的{2,2,B}
行冲突。同样,row{4,3,C}
可以插入,{4,4,C}
应该跳过,{5,6,E}
可以插入,{5,7,E}
应该跳过,{6,6,E}
应该跳过,最后{6,7,E}
可以插入。按照这个算法,我们应该得到
在
#tt2
表中。显然,行处理顺序存在一些不确定性。例如,以倒序处理相同的行
会导致
插入到
#tt2
表中,这不是同一组行。然而,重要的是,在这两个示例中都不能在之后插入其余源行。不幸的是,
INSERT
查询的执行计划并没有遵循这个逻辑。相反,它针对一个索引获取非重复行,然后从它们获取关于另一个索引的非重复行。这是插入的行可能会丢失的地方。这是执行计划的图形表示:
在表扫描(从
#tt
表中读取数据)和表插入(将数据插入#tt2
表中)之间,优化器添加了两组相似的运算符,用于处理IGNORE_DUP_KEY
两个索引。鉴于目标表为空,以下是流向第一组运算符输出的行:
第二组输出的行是:
然后将这些行插入
#tt2
。如果您以不同的顺序创建索引
将更改执行计划,以便
ID2
首先检查索引过度。在这种情况下,第一组运算符的输出行如下:第二组运算符(插入
#tt2
then 中)输出的行是:在第一种情况下,
{6,7,E}
没有插入行(但可以在之后手动插入),我认为这是不正确的,因为它与所选行的插入不会违反唯一性约束。在第二种情况下,之后不能插入任何源行,这是正确的。我认为生成的计划形状是错误的,因为它不能确保文档中说明的逻辑。
最后一点是
ORDER BY ID1, ID2
在您的INSERT
查询中没有意义(并且被优化器忽略)。