mysql - 为什么查询有时会挂起而不是其他人

Question

在我支持的遗留产品中，有一个对 mysql 的 PHP 查询，它有时有效，有时会挂起(可能是有限的，但如果是这样的话，持续时间过长)。我的 SQL 技能非常有限，但我能够在 mysql 上手动运行查询，这是我到目前为止所发现的。

给定表 'orders'、'lineItems' 和 'lineItemDefns'，

其中每个订单都是一对多的 lineItems 和lineItems 与 lineItemDefinitions 是一对一的

和表 OrderReports，它将每个报告 (reportId) 映射到一组订单及其 lineItem 数据和以下 SQl 查询:

SELECT SEC_TO_TIME(SUM(orders.itemCount*lineItems.itemCount*lineItemDefns.estimatedDuration)) as estimatedTotalDuration
FROM orders, lineItems, lineItemDefns
WHERE orders.id=lineItems.parentOrder
  AND lineItemDefns.id=lineItems.definitionId
  AND orders.id in 
      (SELECT DISTINCT orderId 
       FROM OrderReports
       WHERE OrderReports.reportId=98619);

(这是在调用 PHP 中的 DBI getAll 之前立即从查询字符串中转储的。)

当我单独运行第二个选择时，它几乎立即返回一行。当我运行第一个选择并将该 orderId 替换为第二个选择时，它会在不到一秒的时间内返回 NULL estimatedTotalDuration。此 reportId 只有两行，对应于此订单的两个 lineItem 行。 lineItems 的 estimatedDurations(在 lineItemDefns 中)均为 NULL。

查询中的所有 ID，主要的和外部的，都已编入索引。

所有数字都是整数，持续时间以秒为单位 (int(11))。本例中的 itemCounts 为 1。

但是当我按上面的方式运行它时，它可以在我的测试数据库中运行(慢 30 秒)，但是当为生产数据的等效报告留下不合理的时间(超过 50 分钟)时它不会完成.

似乎没有表被锁定，因为我可以在报告挂起时运行前两个部分查询测试。

有人能指出任何明显的原因吗(例如，处理 null estimatedDurations？)。同样，关于下一步要看什么的任何提示？这是一个生产数据库，所以我不想做任何可能对其他用户造成延迟的事情。

对于重写查询的任何建议也将不胜感激。

Fedora 7 上的 mysql 5.0.37(测试数据库是 Fedora 8 上的 mysql 5.0.45)

比如，生活大爆炸中的佩妮，这就是我所知道的。哦，无花果牛顿是以马萨诸塞州牛顿市命名的。 ;)

Answer 1

问题是旧版本的 MySQL 没有很好地优化带有子查询的 in。特别是，它正在为每一行可能的输出运行子查询。 . .一遍又一遍地执行 select distinct。

您可以将此子查询移动到 from 子句来解决问题:

SELECT SEC_TO_TIME(SUM(orders.itemCount*lineItems.itemCount*lineItemDefns.estimatedDuration)) as estimatedTotalDuration
FROM orders join
     lineItems
     on orders.id=lineItems.parentOrder join
     lineItemDefns
     on lineItemDefns.id=lineItems.definitionId join
     (SELECT DISTINCT orderId 
      FROM OrderReports
      WHERE OrderReports.reportId=98619
     ) orep
     on orders.id = orep.id

我还将您所有的连接移动到 from 子句中以使用标准 ANSI 连接语法。