带WHERE子查询与复合主键的T-SQL慢查询优化求助

首先，你的核心问题是关联子查询导致的性能瓶颈——原查询对每一条Invoice都执行一次子查询来计算Taking的总和，数据量上去后自然会慢。我们先从优化查询逻辑入手，再配套索引优化，最后解决Doctrine分页的问题。

一、重构查询逻辑：用JOIN + 聚合替代关联子查询 #

把原来的子查询改成预先计算每个发票的Taking总金额，再和Invoice关联对比，这样只需要扫描一次Taking表，而不是每条Invoice都扫一遍：

-- 统计未结发票数量
WITH InvoiceTakingTotals AS (
    SELECT 
        T.invoice_year,
        T.invoice_number,
        SUM(T.amount * S2.sign_value) AS total_taking
    FROM Taking T
    INNER JOIN Sign S2 ON T.sign = S2.sign_code
    GROUP BY T.invoice_year, T.invoice_number
)
SELECT 
    COUNT(*)
FROM Invoice I
INNER JOIN Sign S1 ON I.sign = S1.sign_code
LEFT JOIN InvoiceTakingTotals ITT 
    ON I.invoice_year = ITT.invoice_year 
    AND I.invoice_number = ITT.invoice_number
WHERE 
    -- 处理没有Taking记录的情况（视为未结）
    (I.amount * S1.sign_value) <> COALESCE(ITT.total_taking, 0);

-- 查询具体未结发票
WITH InvoiceTakingTotals AS (
    SELECT 
        T.invoice_year,
        T.invoice_number,
        SUM(T.amount * S2.sign_value) AS total_taking
    FROM Taking T
    INNER JOIN Sign S2 ON T.sign = S2.sign_code
    GROUP BY T.invoice_year, T.invoice_number
)
SELECT 
    I.*
FROM Invoice I
INNER JOIN Sign S1 ON I.sign = S1.sign_code
LEFT JOIN InvoiceTakingTotals ITT 
    ON I.invoice_year = ITT.invoice_year 
    AND I.invoice_number = ITT.invoice_number
WHERE 
    (I.amount * S1.sign_value) <> COALESCE(ITT.total_taking, 0);

这种方式把聚合操作放在CTE里一次性完成，避免了N次重复扫描Taking表，性能会有质的提升。

二、添加必要索引，加速聚合和关联 #

现有架构的外键虽然会自动生成索引，但针对我们的查询场景，需要补充以下索引来进一步优化：

1. Taking表的复合索引：针对分组和过滤的字段，让聚合查询直接走索引，避免全表扫描

CREATE NONCLUSTERED INDEX IX_Taking_Invoice ON Taking (invoice_year, invoice_number)
INCLUDE (amount, sign); -- 包含需要计算的字段，避免回表读取额外数据

这些索引会让GROUP BY invoice_year, invoice_number的操作效率大幅提升，尤其是数据量较大时。

三、处理Doctrine + knp-paginator-bundle的复合主键分页问题 #

knp-paginator默认依赖单一主键，但你的实体用了复合主键，需要做以下调整：

1. 自定义分页查询逻辑 #

在InvoiceRepository里用QueryBuilder构建支持复合主键的查询：

// 在InvoiceRepository.php中
public function getUnpaidInvoicesQueryBuilder()
{
    $qb = $this->createQueryBuilder('i')
        ->innerJoin('i.sign', 's1')
        ->leftJoin(
            '(SELECT t.invoiceYear, t.invoiceNumber, SUM(t.amount * s2.signValue) as totalTaking
              FROM App\Entity\Taking t
              JOIN t.sign s2
              GROUP BY t.invoiceYear, t.invoiceNumber)',
            'itt',
            'WITH',
            'i.invoiceYear = itt.invoiceYear AND i.invoiceNumber = itt.invoiceNumber'
        )
        ->where('(i.amount * s1.signValue) <> COALESCE(itt.totalTaking, 0)')
        ->orderBy('i.invoiceYear', 'ASC')
        ->addOrderBy('i.invoiceNumber', 'ASC'); // 用复合字段作为排序依据

    return $qb;
}

2. 配置分页器使用复合主键排序 #

在控制器中调用分页器时，指定排序字段为复合主键：

// 控制器代码
$qb = $invoiceRepository->getUnpaidInvoicesQueryBuilder();
$paginator = $this->get('knp_paginator')->paginate(
    $qb,
    $request->query->getInt('page', 1),
    20, // 每页显示条数
    [
        'defaultSortFieldName' => ['i.invoiceYear', 'i.invoiceNumber'],
        'defaultSortDirection' => 'asc',
    ]
);

3. 优化COUNT(*)统计性能 #

因为关联查询时Doctrine的自动计数可能不准确，建议用原生SQL单独统计总数，再手动设置给分页器：

// 先获取未结发票总数
$totalQuery = $this->getEntityManager()->createNativeQuery(
    'WITH InvoiceTakingTotals AS (
        SELECT T.invoice_year, T.invoice_number, SUM(T.amount * S2.sign_value) AS total_taking
        FROM Taking T
        INNER JOIN Sign S2 ON T.sign = S2.sign_code
        GROUP BY T.invoice_year, T.invoice_number
    )
    SELECT COUNT(*) FROM Invoice I
    INNER JOIN Sign S1 ON I.sign = S1.sign_code
    LEFT JOIN InvoiceTakingTotals ITT ON I.invoice_year = ITT.invoice_year AND I.invoice_number = ITT.invoice_number
    WHERE (I.amount * S1.sign_value) <> COALESCE(ITT.total_taking, 0)',
    new ResultSetMapping()
);
$total = $totalQuery->getSingleScalarResult();

// 设置分页器的总条数
$paginator->setTotalItemCount($total);

额外建议 #

• 定期更新统计信息：在SQL Server中执行UPDATE STATISTICS [TableName];，让查询优化器能生成更优的执行计划
• 考虑预计算带符号金额：在插入Invoice/Taking记录时，直接计算好amount * sign_value并存入单独字段，查询时无需重复计算，进一步提升性能

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