可用库存计算性能优化咨询：大数据量查询超时问题

数据库表结构 #

表：purchase_items #

+---------------------+---------------+------+-----+---------+----------------+
| 字段                | 类型          | 允许为空 | 索引 | 默认值   | 额外信息       |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(110)   | 否   | 主键 | NULL    | 自增           |
| purchase_id         | int(11)       | 否   | 外键 | NULL    |                |
| product_id          | int(11)       | 否   | 外键 | NULL    |                |
| units               | varchar(100)  | 是   |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

表：sales_items #

+-----------------------+--------------+------+-----+---------+----------------+
| 字段                  | 类型         | 允许为空 | 索引 | 默认值   | 额外信息       |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | 否   | 主键 | NULL    | 自增           |
| sales_id              | int(11)      | 是   | 外键 | NULL    |                |
| product_id            | int(11)      | 否   | 外键 | NULL    |                |
| purchase_item_id      | bigint(100)  | 否   | 外键 | NULL    |                |
| qty                   | varchar(55)  | 是   |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+

另有与sales_items结构类似的sales_return_items、purchase_return_items和stock_adjustment表。

各表数据量 #

• purchase_items表：10万+条数据
• sales_items表：约150万条数据
• stock_adjustment表：10万+条数据

原库存计算SQL语句 #

SELECT
    ped.*,
    COALESCE(si.qty, 0) AS sales_qty,
    COALESCE(sri.qty, 0) AS sales_return_qty,
    COALESCE(pri.qty, 0) AS purchase_return_qty,
    COALESCE(adj.qty, 0) AS adjustment_qty
FROM purchase_items ped
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS qty 
    FROM sales_items
    GROUP BY purchase_item_id
) si ON si.purchase_item_id = ped.id
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS qty
    FROM sales_return_item -- 注：此处疑似笔误，应为sales_return_items
    GROUP BY purchase_item_id
) sri ON sri.purchase_item_id = ped.id
LEFT JOIN  (
    SELECT purchase_item_id, SUM(qty) AS qty
    FROM purchase_return_items
    GROUP BY purchase_item_id
) pri ON pri.purchase_item_id = ped.id
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) as qty
    FROM adjustment_stock -- 注：此处疑似笔误，应为stock_adjustment
    GROUP BY purchase_item_id
) adj ON adj.purchase_item_id = ped.id
GROUP BY ped.id; -- 注：ped.id是主键，此分组多余，会导致临时表和文件排序

EXPLAIN 查询结果（翻译后） #

+------+-----------------+-----------------------+-------+---------------+----------+---------+----------------------------------+--------+-------------------------------------------------------------+
| 查询ID | 查询类型         | 表名                  | 访问类型 | 可能用到的索引 | 实际用的索引 | 索引长度 | 关联字段                          | 预估行数 | 额外信息                                                     |
+------+-----------------+-----------------------+-------+---------------+----------+---------+----------------------------------+--------+-------------------------------------------------------------+
|    1 | 主查询           | ped                   | 全表扫描 | NULL          | NULL     | NULL    | NULL                             | 111232 | 使用临时表；使用文件排序                                   |
|    1 | 主查询           | <derived2>            | 索引查找 | key0          | key0     | 9       | ped.id                           |      2 |                                                             |
|    1 | 主查询           | <derived3>            | 索引查找 | key0          | key0     | 9       | ped.id                           |     10 |                                                             |
|    1 | 主查询           | <derived4>            | 索引查找 | key0          | key0     | 9       | ped.id                           |      5 |                                                             |
|    1 | 主查询           | <derived5>            | 全表扫描 | NULL          | NULL     | NULL    | NULL                             | 161674 | 使用WHERE条件；使用连接缓冲区（BNL连接）                     |
|    5 | 派生查询         | adjustment_stock      | 索引扫描 | NULL          | purchase_item_id | 768     | NULL                             | 161674 |                                                             |
|    4 | 派生查询         | purchase_return_items | 全表扫描 | purchase_item_id | NULL     | NULL    | NULL                             |     50 | 使用临时表；使用文件排序                                   |
|    3 | 派生查询         | sales_return_item     | 全表扫描 | purchase_item_id | NULL     | NULL    | NULL                             |  32185 | 使用临时表；使用文件排序                                   |
|    2 | 横向派生查询     | sales_items           | 索引查找 | purchase_item_id | purchase_item_id | 8       | ped.id                           |      6 |                                                             |
+------+-----------------+-----------------------+-------+---------------+----------+---------+----------------------------------+--------+-------------------------------------------------------------+

服务器信息 #

服务器: Localhost via UNIX socket
服务器类型: MariaDB
服务器版本: 10.3.34-MariaDB-0ubuntu0.20.04.1 - Ubuntu 20.04
协议版本: 10
数据库客户端版本: libmysql - mysqlnd 7.4.3
PHP扩展: mysqli、curl、mbstring
PHP版本: 7.4.3

解决方案 #

从EXPLAIN结果和SQL写法来看，超时的核心原因是全表扫描、不必要的临时表/文件排序、大临时表关联，以下是针对性优化方案：

1. 移除多余的GROUP BY #

原SQL中GROUP BY ped.id完全多余，因为ped.id是主键，每行数据唯一，分组操作会强制生成临时表并触发文件排序，直接删除该语句即可减少不必要的性能开销。

2. 修正SQL中的表名笔误 #

原SQL中存在两处表名错误：

• FROM sales_return_item → 改为FROM sales_return_items
• FROM adjustment_stock → 改为FROM stock_adjustment
  笔误会导致查询异常或无法使用正确的索引。

3. 优化索引策略 #

从EXPLAIN可见，sales_return_items、purchase_return_items等表的派生查询执行了全表扫描，未利用purchase_item_id索引，需确保：

• 所有关联表（sales_items、sales_return_items、purchase_return_items、stock_adjustment）的purchase_item_id字段已建立单独索引：

  CREATE INDEX idx_sri_purchase_item_id ON sales_return_items(purchase_item_id);
  CREATE INDEX idx_pri_purchase_item_id ON purchase_return_items(purchase_item_id);
  CREATE INDEX idx_sa_purchase_item_id ON stock_adjustment(purchase_item_id);
  

• 若要进一步优化聚合性能，可创建覆盖索引（包含qty字段），避免回表查询：

  CREATE INDEX idx_si_purchase_item_qty ON sales_items(purchase_item_id, qty);
  CREATE INDEX idx_sri_purchase_item_qty ON sales_return_items(purchase_item_id, qty);
  

• 统一数据类型：purchase_items.id是bigint(110)，而关联表的purchase_item_id是bigint(100)，虽同属bigint，但长度定义差异可能导致索引失效，建议统一改为标准bigint(20)或bigint。

4. 重写SQL，避免大临时表关联 #

原SQL先聚合所有子表数据再关联，会生成大临时表，改为行级子查询（利用索引逐个查找），大幅减少临时表数据量：

SELECT
    ped.*,
    COALESCE((SELECT SUM(qty) FROM sales_items si WHERE si.purchase_item_id = ped.id), 0) AS sales_qty,
    COALESCE((SELECT SUM(qty) FROM sales_return_items sri WHERE sri.purchase_item_id = ped.id), 0) AS sales_return_qty,
    COALESCE((SELECT SUM(qty) FROM purchase_return_items pri WHERE pri.purchase_item_id = ped.id), 0) AS purchase_return_qty,
    COALESCE((SELECT SUM(qty) FROM stock_adjustment adj WHERE adj.purchase_item_id = ped.id), 0) AS adjustment_qty
FROM purchase_items ped;

这种写法会利用每个表的purchase_item_id索引，针对每个ped.id执行高效的索引查找，避免大临时表的创建与关联。

5. 调整数据库配置（可选） #

若上述优化后仍有性能问题，可适当调整MariaDB的临时表配置，减少磁盘写入：

# my.cnf 配置文件中添加
tmp_table_size = 64M
max_heap_table_size = 64M

调整后重启数据库，让临时表优先在内存中创建，避免磁盘IO瓶颈。

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