如何优化基于关联表SUM计算的未付发票查询？

我有3张表：

• Invoices（约50万条记录）
• Invoice items，与Invoices为一对多关联（约1000万条记录）
• Invoice payments，与Invoices为一对多关联（约70万条记录）

我需要查询未付发票，当前使用的查询语句如下：

select * from invoices
    LEFT JOIN  (SELECT invoice_id, SUM(price) as totalAmount 
                FROM invoice_items 
                GROUP BY invoice_id) AS t1 
        ON t1.invoice_id = invoices.id
    LEFT JOIN  (SELECT invoice_id, SUM(payed_amount) as totalPaid 
                FROM invoice_payment_transactions 
                GROUP BY invoice_id) AS t2 
        ON t2.invoice_id = invoices.id
WHERE totalAmount > totalPaid

该查询耗时约30秒，速度过慢。我已为invoice_items和invoice_payment_transactions表的invoice_id字段创建索引，执行EXPLAIN后发现MySQL进行了全表扫描。我尝试过使用EXISTS或IN子查询等其他写法，但仍无法避免全表扫描。

我明确知晓缓存策略，但此问题仅聚焦于查询优化，目标是将查询耗时控制在±2秒内。

以下是简化后的表结构：

CREATE TABLE `invoices`
(
    `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `created_at`  timestamp           NOT NULL DEFAULT current_timestamp(),
    `date`        date                NOT NULL,
    `title`       enum ('M','F','Other')       DEFAULT NULL,
    `first_name`  varchar(191)                 DEFAULT NULL,
    `family_name` varchar(191)                 DEFAULT NULL,
    `street`      varchar(191)        NOT NULL,
    `postal_code` varchar(10)         NOT NULL,
    `city`        varchar(191)        NOT NULL,
    `country`     varchar(2)          NOT NULL,
    PRIMARY KEY (`id`),
    KEY `date` (`date`)
) ENGINE = InnoDB

CREATE TABLE `invoice_items`
(
    `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `invoice_id`  bigint(20) unsigned NOT NULL,
    `created_at`  timestamp           NOT NULL DEFAULT current_timestamp(),
    `name`        varchar(191)                 DEFAULT NULL,
    `description` text                         DEFAULT NULL,
    `reference`   varchar(191)                 DEFAULT NULL,
    `quantity`    smallint(6)         NOT NULL,
    `price`       int(11)             NOT NULL,
    PRIMARY KEY (`id`),
    KEY `invoice_items_invoice_id_index` (`invoice_id`),
) ENGINE = InnoDB

CREATE TABLE `invoice_payment_transactions`
(
    `id`                     bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `invoice_id`             bigint(20) unsigned NOT NULL,
    `created_at`             timestamp           NOT NULL DEFAULT current_timestamp(),
    `transaction_identifier` varchar(191)        NOT NULL,
    `payed_amount`           mediumint(9)                 DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `invoice_payment_transactions_invoice_id_index` (`invoice_id`),
) ENGINE = InnoDB

优化方案 #

1. 构建覆盖索引减少回表开销 #

当前单字段索引仅包含invoice_id，分组求和时MySQL需要回表读取price和payed_amount，IO成本极高。创建覆盖索引让索引直接包含求和所需字段：

• 给invoice_items创建联合索引：

  CREATE INDEX idx_invoice_items_id_price ON invoice_items(invoice_id, price);
  

• 给invoice_payment_transactions创建联合索引：

  CREATE INDEX idx_payments_id_amount ON invoice_payment_transactions(invoice_id, payed_amount);
  

这样分组求和时，MySQL可直接从索引中获取数据，无需回表，大幅降低IO操作耗时。

2. 调整查询逻辑，聚焦目标数据 #

原查询先对所有子表数据分组再关联筛选，属于"先全量处理再过滤"的低效逻辑。改为针对单条发票计算金额并筛选：

SELECT i.*, 
       (SELECT SUM(price) FROM invoice_items WHERE invoice_id = i.id) AS totalAmount,
       COALESCE((SELECT SUM(payed_amount) FROM invoice_payment_transactions WHERE invoice_id = i.id), 0) AS totalPaid
FROM invoices i
WHERE (SELECT SUM(price) FROM invoice_items WHERE invoice_id = i.id) > 
      COALESCE((SELECT SUM(payed_amount) FROM invoice_payment_transactions WHERE invoice_id = i.id), 0);

用COALESCE处理无付款记录的场景（默认金额为0），结合覆盖索引，MySQL仅对需要验证的发票执行计算，避免全表分组扫描。

3. 预计算汇总值（实时更新替代方案） #

如果业务允许，可在invoices表新增汇总字段，通过触发器自动更新：

• 新增字段：

  ALTER TABLE invoices ADD COLUMN total_amount INT(11) DEFAULT 0;
  ALTER TABLE invoices ADD COLUMN total_paid MEDIUMINT(9) DEFAULT 0;
  

• 创建触发器（示例：插入商品时更新发票总金额）：

  DELIMITER //
  CREATE TRIGGER trg_update_invoice_amount AFTER INSERT ON invoice_items
  FOR EACH ROW
  BEGIN
      UPDATE invoices SET total_amount = total_amount + NEW.price WHERE id = NEW.invoice_id;
  END //
  DELIMITER ;
  

同理创建付款记录的更新触发器。查询时直接使用：

SELECT * FROM invoices WHERE total_amount > total_paid;

该方案可将查询耗时降至毫秒级，但需维护触发器逻辑，适合写操作频率较低的场景。

4. 延迟关联减少数据处理量 #

先筛选出符合条件的发票ID，再关联获取完整数据，减少中间结果集大小：

SELECT i.*, t1.totalAmount, t2.totalPaid
FROM (
    SELECT inv.id
    FROM invoices inv
    JOIN (SELECT invoice_id, SUM(price) AS totalAmount FROM invoice_items GROUP BY invoice_id) t1 
        ON inv.id = t1.invoice_id
    LEFT JOIN (SELECT invoice_id, SUM(payed_amount) AS totalPaid FROM invoice_payment_transactions GROUP BY invoice_id) t2 
        ON inv.id = t2.invoice_id
    WHERE t1.totalAmount > COALESCE(t2.totalPaid, 0)
) AS inv_ids
JOIN invoices i ON inv_ids.id = i.id
LEFT JOIN (SELECT invoice_id, SUM(price) AS totalAmount FROM invoice_items GROUP BY invoice_id) t1 
    ON i.id = t1.invoice_id
LEFT JOIN (SELECT invoice_id, SUM(payed_amount) AS totalPaid FROM invoice_payment_transactions GROUP BY invoice_id) t2 
    ON i.id = t2.invoice_id;

子查询先缩小目标范围，后续仅关联符合条件的发票数据，结合覆盖索引可显著提升效率。

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