如何加速关联两张各4亿行交易明细与主表的SQL查询？

架构老林

2026-8-23

大表关联查询性能优化问题

我拥有两张各含4亿行数据的交易明细表：trans_details_sell（卖出记录）和trans_details_buy（买入记录），二者通过唯一字段cdr_id关联；另有主表deal_master，仅包含约300条记录。

查询逻辑：通过deal_master关联trans_details_sell获取收入，同时通过trans_details_sell的cdr_id关联trans_details_buy获取成本。关联规则如下：

deal_master通过lcr_zone和customer_interconnect字段，关联trans_details_sell的lcr_zone和sig_netgroup字段；
trans_details_sell的聚集主键为(lcr_zone, sig_netgroup, cdr_id)；
trans_details_buy的聚集主键同样为(lcr_zone, sig_netgroup, cdr_id)；
两张交易明细表结构完全一致，且均已为cdr_id创建非聚集唯一索引。

当前仅关联deal_master与trans_details_sell时，查询速度正常；但加入trans_details_buy后，查询速度变得极慢。以下是我的SQL查询语句及三张表的建表语句：

原查询SQL

SELECT 
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    SUBSTRING(CAST(m.start_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 7, 2) start_date, 
    SUBSTRING(CAST(m.end_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 7, 2) end_date, 
    m.target_minutes, m.target_sell_rate, m.target_buy_rate, 
    m.target_sales, m.target_cost, m.target_profit, 
    SUM(s.quantized_duration) / 60 DG_minute, 
    SUM(s.charge) DG_sales, SUM(b.charge) DG_cost
FROM 
    deal_master m, trans_details_sell s, trans_details_buy b
WHERE
    m.lcr_zone = s.lcr_zone 
    AND m.customer_interconnect = s.sig_netgroup 
    AND m.swap_commitment = 'Sell' 
    AND s.cdr_id = b.cdr_id 
    AND s.start_position BETWEEN m.start_pos AND m.end_pos
GROUP BY
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    m.start_pos, m.end_pos, m.target_minutes, m.target_sell_rate, 
    m.target_buy_rate, m.target_sales, m.target_cost, m.target_profit
ORDER BY 
    1

建表语句

deal_master

CREATE TABLE [dbo].[deal_master]
(
    [agreement_no] [nchar](10) NOT NULL,
    [status] [nvarchar](20) NOT NULL,
    [sales_person] [nvarchar](50) NOT NULL,
    [swap_carrier] [nvarchar](100) NOT NULL,
    [start_pos] [numeric](18, 0) NOT NULL,
    [end_pos] [numeric](18, 0) NOT NULL,
    [swap_commitment] [nvarchar](10) NOT NULL,
    [zone] [nvarchar](200) NOT NULL,
    [target_minutes] [numeric](10, 0) NULL,
    [target_sell_rate] [decimal](13, 11) NULL,
    [target_buy_rate] [decimal](13, 11) NULL,
    [supplier_interconnect] [nvarchar](200) NOT NULL,
    [customer_interconnect] [nvarchar](200) NOT NULL,
    [target_sales] [numeric](10, 2) NULL,
    [target_cost] [numeric](10, 2) NULL,
    [target_profit] [numeric](10, 2) NULL,
    [partner] [nvarchar](50) NULL,
    [lcr_zone] [nvarchar](100) NOT NULL,
 CONSTRAINT [pk_deal_master] PRIMARY KEY CLUSTERED 
(
    [lcr_zone] ASC,
    [customer_interconnect] ASC,
    [supplier_interconnect] ASC,
    [start_pos] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

trans_details_sell

CREATE TABLE [dbo].[trans_details_sell]
(
    [cdr_id] [nchar](12) NOT NULL,
    [rate] [nvarchar](50) NOT NULL,
    [zone] [nvarchar](50) NOT NULL,
    [charge] [decimal](13, 11) NOT NULL,
    [quantized_duration] [numeric](8, 0) NOT NULL,
    [sig_carrier_group] [nvarchar](50) NOT NULL,
    [sig_netgroup] [nvarchar](50) NOT NULL,
    [lcr] [nvarchar](100) NOT NULL,
    [lcr_zone] [nvarchar](50) NOT NULL,
    [per_min_chg] [decimal](13, 11) NOT NULL,
    [trans_type] [nvarchar](10) NOT NULL,
    [start_position] [numeric](18, 0) NOT NULL,
    [end_position] [numeric](18, 0) NOT NULL,
    [filename] [nvarchar](50) NOT NULL,
 CONSTRAINT [pk_trans_details_sell] PRIMARY KEY CLUSTERED 
(
    [lcr_zone] ASC,
    [sig_netgroup] ASC,
    [cdr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

trans_details_buy

CREATE TABLE [dbo].[trans_details_buy]
(
    [cdr_id] [nchar](12) NOT NULL,
    [rate] [nvarchar](50) NOT NULL,
    [zone] [nvarchar](50) NOT NULL,
    [charge] [decimal](13, 11) NOT NULL,
    [quantized_duration] [numeric](8, 0) NOT NULL,
    [sig_carrier_group] [nvarchar](50) NOT NULL,
    [sig_netgroup] [nvarchar](50) NOT NULL,
    [lcr] [nvarchar](100) NOT NULL,
    [lcr_zone] [nvarchar](50) NOT NULL,
    [per_min_chg] [decimal](13, 11) NOT NULL,
    [trans_type] [nvarchar](10) NOT NULL,
    [start_position] [numeric](18, 0) NOT NULL,
    [end_position] [numeric](18, 0) NOT NULL,
    [filename] [nvarchar](50) NOT NULL,
 CONSTRAINT [pk_trans_details_buy] PRIMARY KEY CLUSTERED 
(
    [lcr_zone] ASC,
    [sig_netgroup] ASC,
    [cdr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

注：原建表语句中trans_details_buy的表名误写为trans_details_sell，已修正。

问题分析与优化方案

性能瓶颈原因

连接顺序不合理：旧式连接语法可能导致数据库先关联两张4亿行的大表，再用小表过滤，生成海量中间数据，耗时剧增。
索引利用不充分：trans_details_buy仅通过cdr_id关联时，无法利用聚集主键的lcr_zone、sig_netgroup过滤条件，可能触发全表扫描或大范围索引扫描。
聚合时机过早：先关联所有表再分组，需处理所有符合条件的关联数据后再聚合，海量数据的分组操作效率极低。

优化步骤

1. 改用显式连接，控制连接顺序

通过INNER JOIN明确连接逻辑，让数据库从小表deal_master出发，先过滤出符合条件的trans_details_sell记录，再关联trans_details_buy，大幅减少中间数据量：

SELECT 
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    SUBSTRING(CAST(m.start_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 7, 2) start_date, 
    SUBSTRING(CAST(m.end_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 7, 2) end_date, 
    m.target_minutes, m.target_sell_rate, m.target_buy_rate, 
    m.target_sales, m.target_cost, m.target_profit, 
    SUM(s.quantized_duration) / 60 DG_minute, 
    SUM(s.charge) DG_sales, SUM(b.charge) DG_cost
FROM 
    deal_master m
INNER JOIN trans_details_sell s 
    ON m.lcr_zone = s.lcr_zone 
    AND m.customer_interconnect = s.sig_netgroup 
    AND s.start_position BETWEEN m.start_pos AND m.end_pos
INNER JOIN trans_details_buy b 
    ON s.cdr_id = b.cdr_id
WHERE
    m.swap_commitment = 'Sell'
GROUP BY
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    m.start_pos, m.end_pos, m.target_minutes, m.target_sell_rate, 
    m.target_buy_rate, m.target_sales, m.target_cost, m.target_profit
ORDER BY 
    m.agreement_no

2. 优化`trans_details_buy`的索引

创建覆盖索引，包含关联字段cdr_id和聚合所需的charge字段，避免键查找：

CREATE UNIQUE NONCLUSTERED INDEX IX_trans_details_buy_cdr_id_charge
ON [dbo].[trans_details_buy] (cdr_id)
INCLUDE (charge)
WITH (DROP_EXISTING = ON);

3. 先聚合小范围数据再关联

先对trans_details_sell按关联字段和cdr_id聚合，减少后续关联trans_details_buy的数据量：

WITH sell_agg AS (
    SELECT 
        s.lcr_zone, s.sig_netgroup, s.cdr_id,
        SUM(s.quantized_duration) AS total_duration,
        SUM(s.charge) AS total_sales
    FROM trans_details_sell s
    INNER JOIN deal_master m
        ON m.lcr_zone = s.lcr_zone 
        AND m.customer_interconnect = s.sig_netgroup 
        AND s.start_position BETWEEN m.start_pos AND m.end_pos
        AND m.swap_commitment = 'Sell'
    GROUP BY s.lcr_zone, s.sig_netgroup, s.cdr_id
)
SELECT 
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    SUBSTRING(CAST(m.start_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.start_pos AS nvarchar), 7, 2) start_date, 
    SUBSTRING(CAST(m.end_pos AS nvarchar), 1, 4) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 5, 2) + '-' +
       SUBSTRING(CAST(m.end_pos AS nvarchar), 7, 2) end_date, 
    m.target_minutes, m.target_sell_rate, m.target_buy_rate, 
    m.target_sales, m.target_cost, m.target_profit, 
    SUM(sa.total_duration) / 60 DG_minute, 
    SUM(sa.total_sales) DG_sales, SUM(b.charge) DG_cost
FROM deal_master m
INNER JOIN sell_agg sa
    ON m.lcr_zone = sa.lcr_zone 
    AND m.customer_interconnect = sa.sig_netgroup
INNER JOIN trans_details_buy b
    ON sa.cdr_id = b.cdr_id
WHERE m.swap_commitment = 'Sell'
GROUP BY
    m.agreement_no, m.status, m.sales_person, m.swap_carrier, 
    m.swap_commitment, m.zone, m.lcr_zone, m.customer_interconnect, 
    m.start_pos, m.end_pos, m.target_minutes, m.target_sell_rate, 
    m.target_buy_rate, m.target_sales, m.target_cost, m.target_profit
ORDER BY m.agreement_no

4. 更新统计信息并检查查询计划

确保表统计信息最新，帮助数据库生成最优执行计划：

UPDATE STATISTICS dbo.deal_master;
UPDATE STATISTICS dbo.trans_details_sell;
UPDATE STATISTICS dbo.trans_details_buy;

查看查询计划，确认是否存在全表扫描、键查找等低效操作，针对性调整索引。

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

如何加速关联两张各4亿行交易明细与主表的SQL查询？

原查询SQL #

建表语句 #

deal_master #

trans_details_sell #

trans_details_buy #

性能瓶颈原因 #

优化步骤 #

1. 改用显式连接，控制连接顺序 #

2. 优化trans_details_buy的索引 #

3. 先聚合小范围数据再关联 #

4. 更新统计信息并检查查询计划 #