如何优化MySQL多表库存计算查询以提升性能？

嘿，这个问题我太熟悉了——你现在的查询慢完全是因为相关子查询在拖后腿：每一条产品记录都要分别去stockinward和stockoutward里跑一遍SUM计算，1000条产品就是2000次独立查询，开销自然爆炸。给你几个靠谱的优化方案，保证能把耗时压到1-2秒以内：

方案一：预聚合出入库数据再关联（推荐） #

先一次性计算出每个产品的总入库量和总出库量，再和产品表做关联。这种方式只需要对出入库表各做一次聚合查询，彻底避免重复计算：

SELECT 
    p.Id, 
    p.Name, 
    p.UnitPrice, 
    COALESCE(si.TotalIn, 0) - COALESCE(so.TotalOut, 0) AS Quantity
FROM product p
LEFT JOIN (
    -- 预计算每个产品的总入库量
    SELECT ProductId, SUM(Quantity) AS TotalIn
    FROM stockinward
    GROUP BY ProductId
) si ON si.ProductId = p.Id
LEFT JOIN (
    -- 预计算每个产品的总出库量
    SELECT ProductId, SUM(Quantity) AS TotalOut
    FROM stockoutward
    GROUP BY ProductId
) so ON so.ProductId = p.Id;

方案二：合并出入库数据后单次聚合 #

如果你的数据库支持UNION ALL，可以把出入库数据合并成一个数据集，用标记区分类型后一次性计算库存：

SELECT 
    p.Id, 
    p.Name, 
    p.UnitPrice,
    -- 入库加数量，出库减数量，默认0
    COALESCE(SUM(CASE WHEN type = 'in' THEN Quantity ELSE -Quantity END), 0) AS Quantity
FROM product p
LEFT JOIN (
    -- 合并出入库数据，标记类型
    SELECT ProductId, Quantity, 'in' AS type FROM stockinward
    UNION ALL
    SELECT ProductId, Quantity, 'out' AS type FROM stockoutward
) combined ON combined.ProductId = p.Id
GROUP BY p.Id, p.Name, p.UnitPrice;

关键：添加覆盖索引 #

不管用哪个方案，索引优化都是必不可少的——给出入库表添加覆盖索引，让数据库不用回表就能拿到聚合需要的数据：

-- 给stockinward添加覆盖索引（MySQL写法）
CREATE INDEX idx_stockinward_product_quantity ON stockinward(ProductId, Quantity);

-- 给stockoutward添加覆盖索引（MySQL写法）
CREATE INDEX idx_stockoutward_product_quantity ON stockoutward(ProductId, Quantity);

如果是SQL Server等数据库，可以用INCLUDE语法：

CREATE INDEX idx_stockinward_product ON stockinward(ProductId) INCLUDE (Quantity);
CREATE INDEX idx_stockoutward_product ON stockoutward(ProductId) INCLUDE (Quantity);

进阶：预计算库存表（超大数据量场景） #

如果你的产品和出入库数据量特别大，且对库存实时性要求不是极高，可以考虑维护一张product_stock表，用触发器或者定时任务（比如每日/每小时）同步库存数据。查询时直接从这张表读取，性能能到毫秒级：

-- 示例预计算表结构
CREATE TABLE product_stock (
    ProductId INT PRIMARY KEY,
    Quantity INT NOT NULL DEFAULT 0,
    LastUpdated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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