SQL Server多对多关系下高效查询优化方案问询

使用SQL Server 2014 SP3，数据库结构如下：

• Account与Customer通过Account_Customer构成多对多关系
• Customer与Car通过Customer_Car构成多对多关系
• Customer与Pet通过Customer_Pet构成多对多关系

需求：筛选出**所有关联客户均未养猫（PetName以Cat开头）且未开道奇车（CarName以Dodge开头）**的账户列表。现有查询可满足需求，但因多次访问同表，在千万级数据场景下效率不足，寻求更优方案。

表结构与测试数据脚本 #

USE tempdb;

-- 创建表
IF OBJECT_ID('Account') IS NOT NULL DROP TABLE Account;
CREATE TABLE Account (AccountId INT, AccountName VARCHAR(24))

IF OBJECT_ID('Customer') IS NOT NULL DROP TABLE Customer;
CREATE TABLE Customer (CustomerId INT, CustomerName VARCHAR(24))

IF OBJECT_ID('Pet') IS NOT NULL DROP TABLE Pet;
CREATE TABLE Pet (PetId INT, PetName VARCHAR(24))

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car;
CREATE TABLE Car (CarId INT, CarName VARCHAR(24))

IF OBJECT_ID('Account_Customer') IS NOT NULL DROP TABLE Account_Customer;
CREATE TABLE Account_Customer (AccountId INT, CustomerId INT)

IF OBJECT_ID('Customer_Pet') IS NOT NULL DROP TABLE Customer_Pet;
CREATE TABLE Customer_Pet (CustomerId INT, PetId INT)

IF OBJECT_ID('Customer_Car') IS NOT NULL DROP TABLE Customer_Car;
CREATE TABLE Customer_Car (CustomerId INT, CarId INT)

-- 插入测试数据
INSERT [dbo].[Account]([AccountId], [AccountName])
VALUES (1, 'Account1'), (2, 'Account2')

INSERT [dbo].[Customer]([CustomerId], [CustomerName])
VALUES (1, 'Customer1'), (2, 'Customer2'), (3, 'Customer3'),  (4, 'Customer4')

INSERT [dbo].[Pet]([PetId], [PetName])
VALUES (1, 'Cat1'), (2, 'Cat2'), (3, 'Dog3'),  (4, 'Dog4')

INSERT [dbo].[Car]([CarId], [CarName])
VALUES (1, 'Ford1'), (2, 'Ford2'), (3, 'Kia3'),  (4, 'Dodge4')

INSERT [dbo].[Account_Customer] ([AccountId], [CustomerId])
VALUES (1,1), (1,2), (2, 2), (2,3), (2,4)

INSERT [dbo].[Customer_Pet] ([CustomerId], [PetId])
VALUES (2,3), (3,1), (3, 2), (4,3), (4,4)

INSERT [dbo].[Customer_Car] ([CustomerId], [CarId])
VALUES (1,2), (2,2), (3,1), (3, 2), (3, 4)

-- 查看关联后的全量数据（非规范化）
SELECT [A].[AccountId], [A].[AccountName],
       [C].[CustomerId], [C].[CustomerName],
       [CP].[PetId], [P].[PetName],
        [C2].[CarId], [C2].[CarName]
FROM [dbo].[Customer] AS [C]
JOIN [dbo].[Account_Customer] AS [AC] ON [AC].[CustomerId] = [C].[CustomerId]
JOIN [dbo].[Account] AS [A] ON [A].[AccountId] = [AC].[AccountId]
LEFT JOIN [dbo].[Customer_Pet] AS [CP] ON [CP].[CustomerId] = [C].[CustomerId]
LEFT JOIN [dbo].[Pet] AS [P] ON [P].[PetId] = [CP].[PetId]
LEFT JOIN [dbo].[Customer_Car] AS [CC] ON [CC].[CustomerId] = [C].[CustomerId]
LEFT JOIN [dbo].[Car] AS [C2] ON [C2].[CarId] = [CC].[CarId]
ORDER BY [A].[AccountId], [AC].[CustomerId]

现有可满足需求但效率待优化的查询 #

-- 预期仅返回Account1
SELECT DISTINCT
       [A].[AccountId],
       [A].[AccountName]
FROM [dbo].[Customer] AS [C]
JOIN [dbo].[Account_Customer] AS [AC] ON [AC].[CustomerId] = [C].[CustomerId]
JOIN [dbo].[Account] AS [A] ON [A].[AccountId] = [AC].[AccountId]
EXCEPT
-- 筛选出关联客户养猫或开道奇的账户
SELECT 
       [A].[AccountId],
       [A].[AccountName]
FROM [dbo].[Customer] AS [C]
JOIN [dbo].[Account_Customer] AS [AC] ON [AC].[CustomerId] = [C].[CustomerId]
JOIN [dbo].[Account] AS [A] ON [A].[AccountId] = [AC].[AccountId]
WHERE 
(
    EXISTS (SELECT TOP (1) 1  
            FROM [dbo].[Customer] AS [C2]
            JOIN [dbo].[Customer_Pet] AS [CP2] ON [CP2].[CustomerId] = [C2].[CustomerId]
            JOIN [dbo].[Pet] AS [P2] ON [P2].[PetId] = [CP2].[PetId]
            WHERE [C2].[CustomerId] = [C].[CustomerId]
            AND [P2].[PetName] LIKE 'Cat%'
            )
    OR
    EXISTS (SELECT TOP (1) 1  
            FROM [dbo].[Customer] AS [C2]
            JOIN [dbo].[Customer_Car] AS [CP2] ON [CP2].[CustomerId] = [C2].[CustomerId]
            JOIN [dbo].[Car] AS [P2] ON [P2].[CarId] = [CP2].[CarId]
            WHERE [C2].[CustomerId] = [C].[CustomerId]
            AND [P2].[CarName] LIKE 'Dodge%'
            )
)

无法满足需求的错误查询 #

-- 此查询不符合需求：
SELECT DISTINCT 
       [A].[AccountId],
       [A].[AccountName]
FROM [dbo].[Customer] AS [C]
JOIN [dbo].[Account_Customer] AS [AC] ON [AC].[CustomerId] = [C].[CustomerId]
JOIN [dbo].[Account] AS [A] ON [A].[AccountId] = [AC].[AccountId]
WHERE 
(
    NOT EXISTS (SELECT TOP (1) 1  
            FROM [dbo].[Customer] AS [C2]
            JOIN [dbo].[Customer_Pet] AS [CP2] ON [CP2].[CustomerId] = [C2].[CustomerId]
            JOIN [dbo].[Pet] AS [P2] ON [P2].[PetId] = [CP2].[PetId]
            WHERE [C2].[CustomerId] = [C].[CustomerId]
            AND [P2].[PetName] LIKE 'Cat%'
            )
    AND
    NOT EXISTS (SELECT TOP (1) 1  
            FROM [dbo].[Customer] AS [C2]
            JOIN [dbo].[Customer_Car] AS [CP2] ON [CP2].[CustomerId] = [C2].[CustomerId]
            JOIN [dbo].[Car] AS [P2] ON [P2].[CarId] = [CP2].[CarId]
            WHERE [C2].[CustomerId] = [C].[CustomerId]
            AND [P2].[CarName] LIKE 'Dodge%'
            )
)

错误原因：该查询仅过滤了当前行的客户是否符合条件，但账户可能关联多个客户。只要有一个客户不符合要求（养猫/开道奇），整个账户就应该被排除，但此查询会因为存在符合条件的客户而错误保留这类账户（比如测试数据中的Account2）。

优化方案 #

方案1：从Account出发，直接排除关联问题客户的账户

SELECT a.AccountId, a.AccountName
FROM Account a
WHERE NOT EXISTS (
    SELECT 1
    FROM Account_Customer ac
    JOIN Customer c ON ac.CustomerId = c.CustomerId
    WHERE ac.AccountId = a.AccountId
    AND (
        -- 客户养猫
        EXISTS (
            SELECT 1
            FROM Customer_Pet cp
            JOIN Pet p ON cp.PetId = p.PetId
            WHERE cp.CustomerId = c.CustomerId
            AND p.PetName LIKE 'Cat%'
        )
        OR
        -- 客户开道奇车
        EXISTS (
            SELECT 1
            FROM Customer_Car cc
            JOIN Car car ON cc.CarId = car.CarId
            WHERE cc.CustomerId = c.CustomerId
            AND car.CarName LIKE 'Dodge%'
        )
    )
)

优势：

• 从Account表起始，仅扫描必要的关联表，避免重复访问同一张表
• NOT EXISTS逻辑直接排除存在问题客户的账户，减少中间结果集的生成
• 无需DISTINCT，因为Account表的主键保证唯一性

索引优化建议（千万级数据必备）

为了进一步提升查询效率，创建以下索引：

• CREATE NONCLUSTERED INDEX IX_Account_Customer_AccountId ON Account_Customer(AccountId, CustomerId);
• CREATE NONCLUSTERED INDEX IX_Customer_Pet_CustomerId ON Customer_Pet(CustomerId);
• CREATE NONCLUSTERED INDEX IX_Pet_PetId_PetName ON Pet(PetId) INCLUDE(PetName);
• CREATE NONCLUSTERED INDEX IX_Customer_Car_CustomerId ON Customer_Car(CustomerId);
• CREATE NONCLUSTERED INDEX IX_Car_CarId_CarName ON Car(CarId) INCLUDE(CarName);

这些索引可以大幅减少查询时的表扫描次数，提升关联和过滤的效率。

内容的提问来源于stack exchange，提问作者SQL_Guy