如何在PHP+MySQL环境下加速大数据量的INSERT SELECT查询

咱们一步步拆解你的问题，找到这个插入查询耗时过长的核心原因，然后针对性优化：

1. 先解决最关键的索引问题 #

原查询的性能瓶颈很大程度来自purchase_order表的全表扫描和低效自连接。给它创建一个覆盖复合索引，让MySQL不需要回表就能获取所有需要的数据，同时加速过滤、排序和分组操作：

CREATE INDEX idx_pid_batch_qty ON purchase_order (pid, batch_number, tid, qty_avbl, product_code, product_name, pack_size, expiry_date, pur_price);

这个索引包含了查询中用到的所有过滤条件、排序字段和返回字段，能直接从索引中读取数据，避免访问主表，大幅提升查询速度。

2. 用窗口函数替换低效的自连接（MySQL 8.0+适用） #

原查询用自连接计算累计库存，在8000+记录的表上会产生大量临时数据，复杂度是O(n²)。如果你的MySQL版本是8.0及以上，用SUM() OVER()窗口函数可以把复杂度降到O(n log n)，高效完成累计计算：

INSERT INTO `temp_sale` (
    SELECT 
        null, 
        '$invoID', 
        '$image', 
        x.pid, 
        x.product_code, 
        x.product_name, 
        x.pack_size, 
        x.batch_number, 
        x.expiry_date, 
        x.qty_avbl, 
        0.00, 
        x.pur_price, 
        '$salePrice', 
        '$status', 
        now(), 
        '$cid', 
        '$uid', 
        GREATEST(
            SUM(x.qty_avbl) OVER (PARTITION BY x.pid ORDER BY x.batch_number) - $ttlQinTempSale, 
            0
        ) AS balance
    FROM purchase_order AS x
    WHERE x.pid = $pid
    ORDER BY x.batch_number ASC
    HAVING balance < x.qty_avbl
)

如果你的MySQL版本低于8.0，没法用窗口函数，可以用变量来计算累计值，比自连接高效很多：

SET @cumulative_qty = 0;
INSERT INTO `temp_sale` (
    SELECT 
        null, 
        '$invoID', 
        '$image', 
        x.pid, 
        x.product_code, 
        x.product_name, 
        x.pack_size, 
        x.batch_number, 
        x.expiry_date, 
        x.qty_avbl, 
        0.00, 
        x.pur_price, 
        '$salePrice', 
        '$status', 
        now(), 
        '$cid', 
        '$uid', 
        GREATEST(@cumulative_qty := @cumulative_qty + x.qty_avbl - $ttlQinTempSale, 0) AS balance
    FROM purchase_order AS x
    WHERE x.pid = $pid
    ORDER BY x.batch_number ASC
    HAVING balance < x.qty_avbl
)

3. 优化temp_sale表结构 #

• 给tid字段设置自增主键，避免手动插入null带来的性能开销：

  ALTER TABLE temp_sale MODIFY COLUMN tid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
  

• 将timeStamp字段从VARCHAR(30)改为DATETIME，避免类型转换的性能损耗：

  ALTER TABLE temp_sale MODIFY COLUMN timeStamp DATETIME NOT NULL;
  

4. 用预处理语句避免风险并提升性能 #

直接把PHP变量拼进SQL里不仅有SQL注入风险，还会让MySQL无法缓存执行计划。改用预处理语句：

// 初始化预处理语句
$stmt = $con->prepare("
    INSERT INTO `temp_sale` (
        SELECT 
            null, 
            ?, 
            ?, 
            x.pid, 
            x.product_code, 
            x.product_name, 
            x.pack_size, 
            x.batch_number, 
            x.expiry_date, 
            x.qty_avbl, 
            0.00, 
            x.pur_price, 
            ?, 
            ?, 
            now(), 
            ?, 
            ?, 
            GREATEST(SUM(x.qty_avbl) OVER (PARTITION BY x.pid ORDER BY x.batch_number) - ?, 0) AS balance
        FROM purchase_order AS x
        WHERE x.pid = ?
        ORDER BY x.batch_number ASC
        HAVING balance < x.qty_avbl
    )
");

// 绑定参数（根据变量实际类型调整）
$stmt->bind_param("sssiiii", $invoID, $image, $salePrice, $status, $cid, $uid, $ttlQinTempSale, $pid);

// 执行查询
$stmt->execute();
$stmt->close();

5. 辅助优化小技巧 #

• 如果temp_sale表有非必要的索引，插入前可以临时禁用，插入完成后再启用（减少索引维护开销）：

  ALTER TABLE temp_sale DISABLE KEYS;
  -- 执行插入语句
  ALTER TABLE temp_sale ENABLE KEYS;
  

• 确保purchase_order表的pid字段有单独索引（如果之前没建的话）：

  CREATE INDEX idx_pid ON purchase_order (pid);
  

按照这些步骤优化后，你的插入操作耗时应该能从40秒降到几秒甚至更短。

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