优化-SQL:如何显示所有数据都存在于多个表中

Question

我有两个表。我想找到表2中存在的表一行，然后返回。我有答案，但我想要它更快。示例：

Create table One (ID INT, Value INT, location VARCHAR(10))
Create table Two (ID INT, Value INT, location VARCHAR(10))
INSERT INTO One VALUES(1,2,'Hanoi')
INSERT INTO One VALUES(2,1,'Hanoi')
INSERT INTO One VALUES(1,4,'Hanoi')
INSERT INTO One VALUES(3,5,'Hanoi')
INSERT INTO Two VALUES(1,5,'Saigon')
INSERT INTO Two VALUES(4,6,'Saigon')
INSERT INTO Two VALUES(5,7,'Saigon')
INSERT INTO Two VALUES(2,8,'Saigon')
INSERT INTO Two VALUES(2,8,'Saigon')

和答案：

SELECT * FROM One WHERE ID IN (SELECT ID FROM Two)
UNION ALL
SELECT *FROM Two WHERE ID IN (SELECT ID FROM One)

使用此查询，系统扫描表4次在此处输入图像描述

我希望系统扫描表两次（表一，表二一次）。

我疯了吗？

Answer 1

您可以尝试以下操作：

-- CREATE TABLES
IF OBJECT_ID ( 'tempdb..#One' ) IS NOT NULL
DROP TABLE #One;
IF OBJECT_ID ( 'tempdb..#Two' ) IS NOT NULL
DROP TABLE #Two;
CREATE TABLE #One (ID INT, Value INT, location VARCHAR(10))
CREATE TABLE #Two (ID INT, Value INT, location VARCHAR(10))
-- INSERT DATA
INSERT INTO #One VALUES(1,2,'Hanoi')
INSERT INTO #One VALUES(2,1,'Hanoi')
INSERT INTO #One VALUES(1,4,'Hanoi')
INSERT INTO #One VALUES(3,5,'Hanoi')
INSERT INTO #Two VALUES(1,5,'Saigon')
INSERT INTO #Two VALUES(4,6,'Saigon')
INSERT INTO #Two VALUES(5,7,'Saigon')
INSERT INTO #Two VALUES(2,8,'Saigon')
INSERT INTO #Two VALUES(2,8,'Saigon')
-- CREATE INDEX
CREATE NONCLUSTERED INDEX IX_One ON #One (ID) INCLUDE (Value, location)
CREATE NONCLUSTERED INDEX IX_Two ON #Two (ID) INCLUDE (Value, location)

-- SELECT DATA
SELECT o.ID
      ,o.Value
      ,o.location 
FROM #One o 
WHERE EXISTS (SELECT 1 FROM #Two t WHERE o.ID = t.ID)
UNION ALL
SELECT t.ID
      ,t.Value
      ,t.location 
FROM #Two t
WHERE EXISTS (SELECT 1 FROM #One o WHERE t.ID = o.ID)

，但这取决于您拥有的"大"数据。如果数据确实很大（数百万行）并且您正在运行SQL Server的企业版本，则可以考虑使用Columnstore索引。

Answer 2

您两次扫描表的原因是因为您是从表x读取并从表y中查找相应的值。y，然后在表y中寻找比赛。之后，两个结果都被合并并返回给呼叫者。

在某种程度上，这不是一件坏事，尽管如果桌子"宽"并且包含很多您不需要的列，那么您就没有充分的理由做很多IO。此外，在您的示例中，另一个表中对匹配ID的搜索需要扫描整个表，因为ID字段没有"逻辑"。它只是值列表。为了加快事项，您应该在ID字段上添加索引，以帮助系统找到特定ID值的速度要快得多。此外，这还限制了查找阶段需要读取的数据量：服务器仅从索引中读取仅包含ID值（**）而不是其他所有不需要字段的索引。

说实话，我发现您的要求有些奇怪，但是我猜这主要是由于简化的，因此可以在此上理解。我的第一个反应是建议在两个表之间使用JOIN，但是由于ID字段不是唯一的，因此会导致重复！为了解决该问题，我添加了DISTINCT，但后来事情会严重减慢。最后，仅执行WHERE ID IN (...)是最有效的方法。在ID字段上添加索引的速度更快了百万行）。

fyi：就我个人而言，我更喜欢WHERE EXISTS()而不是WHERE IN (...)，但它们既等效又产生了完全相同的查询计划。

（**：除了索引字段外，每个索引还包含群集索引 - 通常是表的主要键 - 其叶子数据中的字段。有关更多信息Kimberly L. Tripp有一些有趣的文章关于索引及其工作方式。）