库存计算场景下MySQL查询性能优化方案咨询

问题背景 #

数据库表结构 #

表：purchase_items

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(110)   | NO   | PRI | NULL    | auto_increment |
| purchase_id         | int(11)       | NO   | MUL | NULL    |                |
| product_id          | int(11)       | NO   | MUL | NULL    |                |
| units               | varchar(100)  | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

表：sales_items

+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| sales_id              | int(11)      | YES  | MUL | NULL    |                |
| product_id            | int(11)      | NO   | MUL | NULL    |                |
| purchase_item_id      | bigint(100)  | NO   | MUL | NULL    |                |
| qty                   | varchar(55)  | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+

另有与sales_items结构完全一致的sales_return_items表。

原查询与性能问题 #

原用于获取库存数据的查询语句如下：

SELECT
    pi.id,
    pi.product_id,
    pi.units,
    (
        SELECT SUM(si.qty) FROM sales_items si WHERE si.purchase_item_id = pi.id
    ) as sales_qty,
    (
        SELECT SUM(sri.qty) FROM sales_return_items sri WHERE sri.purchase_item_id = pi.id
    ) as sales_return_qty
FROM purchase_items pi

随着数据量增长，该查询出现请求超时或页面加载耗时超10分钟的问题，需针对性优化。

优化方案 #

1. 替换子查询为JOIN+预聚合 #

原查询中每条purchase_items记录会触发两次独立子查询，数据量越大重复计算越多。改用分组聚合后关联的方式，仅对销售和退货表各做一次聚合，大幅减少计算量：

SELECT
    pi.id,
    pi.product_id,
    pi.units,
    COALESCE(si.sales_qty, 0) AS sales_qty,
    COALESCE(sri.sales_return_qty, 0) AS sales_return_qty
FROM purchase_items pi
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS sales_qty
    FROM sales_items
    GROUP BY purchase_item_id
) si ON si.purchase_item_id = pi.id
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS sales_return_qty
    FROM sales_return_items
    GROUP BY purchase_item_id
) sri ON sri.purchase_item_id = pi.id

注：使用COALESCE确保无销售/退货记录时返回0，避免NULL值影响业务逻辑。

2. 修改qty字段为数值类型 #

当前sales_items和sales_return_items的qty字段为varchar类型，聚合时需隐式转换为数值，严重拖慢SUM计算速度。建议修改为适合业务的数值类型：

-- 根据实际业务需求选择DECIMAL或INT
ALTER TABLE sales_items MODIFY COLUMN qty DECIMAL(10,2) NOT NULL DEFAULT 0;
ALTER TABLE sales_return_items MODIFY COLUMN qty DECIMAL(10,2) NOT NULL DEFAULT 0;

3. 优化索引配置 #

确保关联字段存在有效索引，避免全表扫描：

• 为销售和退货表的purchase_item_id字段添加索引：

CREATE INDEX idx_sales_purchase_item ON sales_items(purchase_item_id);
CREATE INDEX idx_return_purchase_item ON sales_return_items(purchase_item_id);

如果业务中常结合product_id进行查询，可创建复合索引(purchase_item_id, qty)，进一步提升聚合效率。

4. 启用分页查询（按需） #

若业务无需一次性返回全量库存数据，添加分页逻辑限制单次查询数据量：

SELECT
    pi.id,
    pi.product_id,
    pi.units,
    COALESCE(si.sales_qty, 0) AS sales_qty,
    COALESCE(sri.sales_return_qty, 0) AS sales_return_qty
FROM purchase_items pi
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS sales_qty
    FROM sales_items
    GROUP BY purchase_item_id
) si ON si.purchase_item_id = pi.id
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS sales_return_qty
    FROM sales_return_items
    GROUP BY purchase_item_id
) sri ON sri.purchase_item_id = pi.id
LIMIT 100 OFFSET 0;

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