SELECT vs UPDATE performance with index

Question

如果我先对ID执行SELECT操作，然后再使用这些ID执行UPDATE操作，那么UPDATE查询比使用SELECT中的条件执行UPDATE查询要快。

举例说明：

SELECT id FROM table WHERE a IS NULL LIMIT 10; -- 0.00 sec
UPDATE table SET field = value WHERE id IN (...); -- 0.01 sec

在相同条件下，上述方法大约比UPDATE快100倍：

UPDATE table SET field = value WHERE a IS NULL LIMIT 10; -- 0.91 sec

为什么？

注意：a列已编入索引。

Answer 1

第二个UPDATE语句很可能锁定更多的行，而第一个语句使用唯一键，只锁定要更新的行。

Answer 2

这两个查询不完全相同。您只知道ID在表中是唯一的。

更新...LIMIT 10最多只能更新10条记录。

更新...WHERE id IN (SELECT ...限制10)如果存在重复的ids，则可以更新10条以上的记录。

Answer 3

我不认为对你的“为什么？”有一个直接的答案。而不做任何分析和研究。

SELECT查询通常被缓存，这意味着如果您多次运行同一个SELECT查询，则第一个查询的执行时间通常比后面的查询长。请注意，这种行为只能在SELECT很重的情况下发生，而不是在即使是第一次SELECT也要快得多的情况下。因此，在您的示例中，由于缓存的原因，SELECT可能花费了0.00s。UPDATE查询使用不同的WHERE子句，因此它们的执行时间可能不同。

虽然对列a进行了索引，但在执行SELECT或UPDATE操作时，MySQL不必使用该索引。请研究解释输出。另外，查看SHOW INDEX的输出并检查是否有索引的"Comment“列显示为"disabled”？你可以在这里阅读更多- http://dev.mysql.com/doc/refman/5.0/en/show-index.html和http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html。

此外，如果我们暂时忽略SELECT而只关注UPDATE查询，那么很明显它们使用的不是相同的WHERE条件--第一个条件在id列上运行，而后者在a上运行。虽然这两列都建立了索引，但这并不意味着所有的表索引都执行得一样。某些索引可能比另一个更有效，这取决于索引的大小、索引列的数据类型或它是单列索引还是多列索引。当然还有其他原因，但我不是这方面的专家。

此外，我认为第二次更新做了更多的工作，因为与第一次更新相比，它可能会放置更多的行级锁。确实，这两个更新最终都更新了相同数量的行。但是在第一次更新中，有10行被锁定，我认为在第二次更新中，所有a为NULL (大于10)的行在执行更新之前都会被锁定。也许MySQL首先应用锁定，然后运行LIMIT子句仅更新有限的记录。

希望上面的解释是有意义的！