假设我有以下 2 个 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 查询中检索"IndexNumber"字段,然后我可以按如下方式执行 UPDATE 查询,传入"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();
请注意,UPDATE 查询将在 SELECT 查询之后和下一个 SELECT 查询执行之前立即执行。
这两种方法中哪一种更有效?
首先,当您在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
最多 1 行table1
(JOINing
时(,<>
要做的就是过滤掉一些行。 无需调整任何索引。
(您的第一个UPDATE
无效,因为它提到了fieldsix
而没有说明table2
。
第二UPDATE
几乎涵盖了讨论SELECT
。
由于日期测试,第三个UPDATE
可能会命中更少的行,但这意味着它与第二个UPDATE
不同- 因为它不会总是增加fieldfour
。