mysql - 统一并简化 MySQL 命令

Question

我执行了一些MySQL命令，但是执行起来有点慢，因为同时执行了三个命令。

如何将所有内容整合到一个命令中并简化代码以使其更快？

UPDATE bidbutler b
    LEFT JOIN auction a on b.auc_id=a.auctionID
    LEFT JOIN auc_due_table c on b.auc_id=c.auction_id
    SET b.butler_status=0
    WHERE b.auc_id=a.auctionID
    AND b.auc_id=c.auction_id
    AND b.butler_status<>0
    AND a.auc_status=3
    AND c.auc_due_price<>a.auc_final_price
    AND c.auc_due_time=a.total_time;

DELETE t1
    FROM won_auctions t1
    LEFT JOIN auc_due_table t2 ON t1.auction_id = t2.auction_id
    LEFT JOIN auction t3 ON t1.auction_id = t3.auctionID
    WHERE t1.auction_id = t2.auction_id
    AND t1.auction_id = t3.auctionID
    AND t2.auc_due_price<>t3.auc_final_price
    AND t2.auc_due_time=t3.total_time
    AND t3.auc_status=3;

UPDATE auction a
    LEFT JOIN auc_due_table b on a.auctionID=b.auction_id
    SET a.auc_status=2
    WHERE a.auc_status=3
    AND a.auctionID=a.auctionID
    AND b.auc_due_price<>a.auc_final_price
    AND b.auc_due_time=a.total_time;

这三个命令将通过数据库过程同时执行，该过程每秒由一个事件执行。

新删除更新:

DELETE t1
    FROM won_auctions t1
    JOIN auction t2
        ON t1.auction_id = t2.auctionID
    LEFT JOIN auc_due_table t3
        ON t3.auction_id = t1.auction_id
        AND t3.auction_id = t2.auctionID
        AND t3.auc_due_price<>t2.auc_final_price
        AND t3.auc_due_time=t2.total_time
    WHERE t2.auc_status=3;

Answer 1

序言

重复我留下的注释作为对该问题的评论，因为我不确定这些点是否得到了足够的重视:

“我不认为缓慢是由于三个单独的语句造成的。”

“即使定义了适当的索引，这些语句也有可能会搅动大量行。”

“使用 EXPLAIN 查看执行计划，并确保使用合适的索引，...”

<小时/>

回答

DELETE 语句不能与 UPDATE 语句结合使用。 SQL 不是这样工作的。

如果两个 UPDATE 语句访问相同的行，并且条件相同，并且第二个 UPDATE 不相关，则可以组合这两个 UPDATE 语句在前面的 UPDATE 和 DELETE 语句中。

我们看到第一个 UPDATE 语句需要 bidbutler 表中的匹配行。第二个UPDATE语句没有这样的要求。

我们注意到 WHERE 子句中的谓词否定了 LEFT JOIN 的“外部性”。如果原始语句正确实现(并且正在执行所需的操作)，那么我们可以消除LEFT关键字。)

我们还发现了一个条件a.auctionID=a.auctionID，它归结为a.auctionID IS NOT NULL。我们已经有其他条件要求 a.auctionID 为非 NULL。 (为什么声明中包含这个条件？)

我们还看到重复的条件:b.auc_id=c.auction_id 出现在ON 子句和WHERE 子句中。该条件只需指定一次。 (为什么这么写？也许还有别的意思？)

第一个 UPDATE 语句可以重写为等效语句:

UPDATE auction a
  JOIN auc_due_table d 
    ON d.auction_id     = a.auctionID 
   AND d.auc_due_time   = a.total_time
   AND d.auc_due_price <> a.auc_final_price

  LEFT
  JOIN bidbutler b
    ON b.auc_id         = a.auctionID
   AND b.auc_id         = d.auction_id
   AND b.butler_status <> 0        

   SET b.butler_status  = 0
 WHERE a.auc_status     = 3

第二个 UPDATE 语句可以重写为等效语句:

UPDATE auction a
  JOIN auc_due_table d 
    ON d.auction_id     = a.auctionID
   AND d.auc_due_time   = a.total_time
   AND d.auc_due_price <> a.auc_final_price

   SET a.auc_status     = 2
 WHERE a.auc_status     = 3

区别在于 bidbutler 表的额外外连接以及 SET 子句。

但在我们组合这些之前，我们需要破译第一个 UPDATE 或 DELETE 语句中执行的操作是否会影响第二个 UPDATE > 声明。 (如果我们以不同的顺序运行这些语句，我们会得到不同的结果吗？)

一个简单的例子来说明我们试图揭示的依赖类型:

UPDATE foo  SET foo.bar = 1  WHERE foo.bar = 0; 
DELETE foo.*  FROM foo       WHERE foo.bar = 0; 
UPDATE foo  SET foo.qux = 2  WHERE foo.bar = 1;

在示例中，我们看到结果(可能)取决于语句的执行顺序。第一个 UPDATE 语句将修改不会被 DELETE 删除的行。如果我们首先运行DELETE，这将删除本应更新的行...语句执行的顺序会影响结果。

回到问题中的原始陈述。我们看到 auc_status 列被设置，而且同一列上也有一个条件。

如果语句之间没有依赖关系，那么我们可以将两个 UPDATE 语句重写为单个语句:

UPDATE auction a
  JOIN auc_due_table d 
    ON d.auction_id     = a.auctionID 
   AND d.auc_due_time   = a.total_time
   AND d.auc_due_price <> a.auc_final_price

  LEFT
  JOIN bidbutler b
    ON b.auc_id         = a.auctionID
   AND b.auc_id         = d.auction_id
   AND b.butler_status <> 0        

  SET b.butler_status   = 0
    , a.auc_status      = 2 

 WHERE a.auc_status     = 3

<小时/>