MySQL更新性能...which更快

Question

假设我有以下两个MySQL表：

CREATE TABLE Table1
(
    IndexNumber INT UNSIGNED NOT NULL AUTO_INCREMENT,
    FieldOne TINYINT UNSIGNED NOT NULL,
    FieldTwo VARCHAR(180) NOT NULL,
    FieldThree DATE,
    FieldFour INT,
    PRIMARY KEY (IndexNumber, FieldThree),
    UNIQUE KEY (FieldOne, FieldTwo)
) ENGINE=InnoDB;

和

CREATE TABLE Table2
(
    IndexNumber INT UNSIGNED NOT NULL,
    FieldFive TINYINT UNSIGNED NOT NULL,
    FieldSix TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (IndexNumber) REFERENCES Table1 (IndexNumber),
    KEY (IndexNumber),
    KEY (FieldFive, FieldSix)
) ENGINE=InnoDB;

现在，我想执行几个查询来检索一些匹配的记录，并且在每次SELECT查询之后，我希望更新匹配的记录，以便在明天之前不会再次检索它们。

SELECT查询如下：

SELECT table1.fieldone, 
       table1.fieldtwo 
FROM   table2 
       INNER JOIN table1 
               ON table2.indexnumber = table1.indexnumber 
WHERE  table2.fieldfive = 1 
       AND table2.fieldsix = 2; 

SELECT table1.fieldone, 
       table1.fieldtwo 
FROM   table2 
       INNER JOIN table1 
               ON table2.indexnumber = table1.indexnumber 
WHERE  table2.fieldfive = 1 
       AND table2.fieldsix = 3 
       AND table1.fieldthree <> Curdate(); 

我的问题是-在每次select查询之后更新匹配记录的最快方法是什么，以便下一个SELECT查询不匹配它？

我可以在SELECT查询中检索"IndexNumber“字段，然后执行更新查询，如下所示，传入"IndexNumber”字段值：

UPDATE table1 
SET    fieldthree = Curdate(), 
       fieldfour = (fieldfour + 1) 
WHERE  indexnumber IN (...indexnumbers...)ve = 1 
AND    table2.fieldsix = 3 
AND    table1.fieldthree <> curdate();

或者，我可以重复SELECT查询中的WHERE，并希望缓存能让它更快：

UPDATE table2 
       INNER JOIN table1 
               ON table2.indexnumber = table1.indexnumber 
SET    table1.fieldthree = Curdate(), 
       table1.fieldfour = ( table1.fieldfour + 1 ) 
WHERE  table2.fieldfive = 1 
       AND table2.fieldsix = 2; 

UPDATE table2 
       INNER JOIN table1 
               ON table2.indexnumber = table1.indexnumber 
SET    table1.fieldthree = Curdate(), 
       table1.fieldfour = ( table1.fieldfour + 1 ) 
WHERE  table2.fieldfive = 1 
       AND table2.fieldsix = 3 
       AND table1.fieldthree <> Curdate() 
       AND table1.fieldthree <> Curdate(); 

请注意，更新查询将在SELECT查询之后、下一个SELECT查询执行之前立即执行。

这两种方法中哪一种更有效？

Answer 1

首先，当InnoDB表中有id .. AUTO_INCREMENT时，PRIMARY KEY(id)和PRIMARY KEY(id, other_col)之间没有区别。

其次，您的table2没有显式的PRIMARY KEY；这是“坏的”。将为你提供一个隐藏的PK，但它不会像显式地拥有你自己的PK那样“好”。

对于此选择：

    INNER JOIN  table1  ON table2.indexnumber = table1.indexnumber
    WHERE  table2.fieldfive = 1
      AND  table2.fieldsix = 2;

它将以table2开头，因为有过滤(WHERE)。最优索引是

table2:  INDEX(fieldfive, fieldsix,   -- in either order
               indexnumber)           -- to make the index "covering"
table1:   (indexnumber)               -- it's already the PK, so good

对于此选择：

    INNER JOIN  table1  ON table2.indexnumber = table1.indexnumber
    WHERE  table2.fieldfive = 1
      AND  table2.fieldsix = 3
      AND  table1.fieldthree <> Curdate();  -- not easy to optimize

同样，它将从table2开始，与上面相同的索引是最优的。另一个提示..。由于对于给定的indexnumber (当为JOINing)，在table1中最多只有1行，所以<>所做的就是过滤掉一些行。不需要调整任何指标。

(您的第一个UPDATE是无效的，因为它只提到了fieldsix，而没有提到table2。)

关于第二个UPDATE，我们将通过讨论SELECT来介绍。

由于日期测试，第三个UPDATE命中的行数可能较少，但这意味着它与第二个UPDATE不同--因为它并不总是递增fieldfour。