sql - 使用一张小表的值更新一张大表的性能

Question

首先，我知道使用 table_b 中的值更新 table_a 的 sql 语句的形式为:

甲骨文:

UPDATE table_a 
  SET (col1, col2) = (SELECT cola, colb 
                        FROM table_b 
                       WHERE table_a.key = table_b.key) 
WHERE EXISTS (SELECT * 
                FROM table_b 
               WHERE table_a.key = table_b.key)

MySQL:

UPDATE table_a 
INNER JOIN table_b ON table_a.key = table_b.key 
SET table_a.col1 = table_b.cola, 
    table_a.col2 = table_b.colb

据我所知，数据库引擎将遍历 table_a 中的记录，并使用 table_b 中匹配记录的值更新它们。

所以，如果我在 table_a 中有 1000 万条记录，而在 table_b 中只有 10 条记录:

1. 这是否意味着引擎将对 table_a 进行 1000 万次迭代，只是为了更新 10 条记录？ Oracle/MySQL/etc 是否足够聪明，可以通过 table_b 进行 10 次迭代？

2. 有没有办法强制引擎实际遍历 table_b 而不是 table_a 中的记录来进行更新？ sql 语句是否有替代语法？

假设 table_a.key 和 table_b.key 被索引。

Answer 1

任何一个引擎都应该足够聪明，可以根据表 b 中只有 10 行的事实来优化查询。引擎如何确定要做什么是基于索引和统计信息等因素。

如果“键”列是主键和/或被索引，引擎将只需要做很少的工作来运行这个查询。它基本上已经有点“知道”匹配行的位置，并且可以非常快速地查找它们。它根本不需要“迭代”。

如果键列上没有索引，引擎将不得不进行“表扫描”(大致相当于“迭代”)以找到正确的值并将它们匹配起来。这意味着它将必须扫描 1000 万行。

稍微阅读一下所谓的执行计划。这基本上是对引擎必须执行哪些工作才能运行您的查询的解释(一些数据库仅以文本形式显示，一些可以选择以图形方式查看)。学习如何解释执行计划将使您深入了解向表添加索引和优化查询。

如果它们不起作用，请查看它们(已经有一段时间了)，但它类似于:

• 在 MySQL 中，将工作“EXPLAIN”放在 SELECT 语句的前面
• 在 Oracle 中，在运行 SELECT 语句之前运行“SET AUTOTRACE ON”

我认为第一个 (Oracle) 查询最好用 JOIN 而不是 WHERE EXISTS 来编写。引擎可能足够聪明，可以以任何一种方式正确优化它。一旦掌握了解释执行计划的窍门，就可以两种方式运行它并亲自查看。 :)