mysql - 大型 AWS RDS MySQL 更新断开连接

Question

我正在尝试对包含 279,480 行的表执行批量更新。更新必须查询另一个表1,113,770。更新将选择一组记录，然后计算它们的总和并将结果应用到正在更新的表中的列。

该查询对于单行来说效果很好，但是当应用于整个表时，它会失败:

Error Code: 2013. Lost connection to MySQL server during query

这是在函数内完成的。这是实际的查询。发票标题表是较小的表:

LEGACYINVOICENO 是发票标题的 PK。 SELLINGDEPARTMENT 也是一个索引字段，发票标题中的 POSTDATE 也是如此。

SELECT SUM(EXTENDEDPRICE)+SUM(TAX) FROM invoicedetail 
    WHERE LEGACYINVOICENO IN(
    SELECT LEGACYINVOICENO FROM invoiceheader 
    WHERE SELLINGDEPARTMENT = _department
    AND POSTDATE < _postdate
       AND LOTJOB_ID = _ljID) into _balance_used;

更新信息。以下是使用单个实例实际运行的查询示例:

 SELECT SUM(EXTENDEDPRICE)+SUM(TAX) FROM invoicedetail 
    WHERE LEGACYINVOICENO IN(
    SELECT LEGACYINVOICENO FROM invoiceheader 
    WHERE SELLINGDEPARTMENT = 2
    AND POSTDATE < '2013-06-06'
       AND LOTJOB_ID = '45497100FOXLAND1640') 

执行计划表明嵌套循环导致对invoiceheader 表进行全表扫描。这似乎是问题的可能原因，但我不确定需要如何优化内部查询。

出于测试目的，我简化了查询:

 SELECT SUM(EXTENDEDPRICE)+SUM(TAX) FROM invoicedetail 
    WHERE LEGACYINVOICENO IN(
    SELECT LEGACYINVOICENO FROM invoiceheader 
    WHERE LOTJOB_ID = '45497100FOXLAND1640') 

LEGACYINVOCENO 是 PK，LOTJOB_ID 已建立索引，但 WHERE 子句仍然导致全表扫描。

Answer 1

不要使用IN ( SELECT ... )，而是更改为JOIN ... ON。这可能会避免表扫描。

向我们展示SHOW CREATE TABLE，以便我们了解您正在使用的内容。

对于大型更新，chunk it .