如果在更新語句中使用子查詢，為什麼 MySQL 會執行全表掃描

我有一個測試表：

create table t1 (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
   `description` varchar(120) CHARACTER SET utf8 DEFAULT NULL,
   PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into t1 (`description`)
        values ('d1'),('d2'),('d3'),('d4'),('d5'),
               ('d6'),('d7'),('d8'),('d9'),('d10');

現在讓我們看一下查詢計劃的以下說明：

A：

mysql> explain update t1 set description='new_description' where id in (5,6);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)

乙：

mysql> explain update t1 set description='new_description'
          where id in (select id
             from (select id from t1 where id in (5,6)) tmp);
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+------------------------------+
| id | select_type        | table      | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra                        |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+------------------------------+
|  1 | UPDATE             | t1         | NULL       | index          | NULL          | PRIMARY     | 4       | NULL |   10 |   100.00 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | <derived3> | NULL       | index_subquery | <auto_key0>   | <auto_key0> | 4       | func |    2 |   100.00 | Using index                  |
|  3 | DERIVED            | t1         | NULL       | range          | PRIMARY       | PRIMARY     | 4       | NULL |    2 |   100.00 | Using where; Using index     |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+------------------------------+
3 rows in set (0.01 sec)

這些查詢做同樣的事情，但查詢 B 使用子查詢。

您能否幫助我理解為什麼在查詢“B”中 MySQL 執行全表掃描（10 行）？由於子查詢僅返回 2 行。

更新：似乎它不僅與子查詢有關，還與“in”運算符有關：

mysql> explain update t1 set description='new_description'
             where id = (select id from
                (select id from t1 where id =5) tmp);
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t1         | NULL       | range  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | t1         | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set (0.00 sec)

AFAIS，帶有“=”運算符的 MySQL 程序只有 1 行。

IN ( SELECT ... )在 MySQL 中一直優化得很差。5.6 中有所改進，但似乎沒有涵蓋您的情況。如果您願意，請使用http://bugs.mysql.com 送出錯誤。

更改為 a 通常會更好，JOIN因為它可以更好地優化。（我假設您的實際查詢涉及更多，並且您為本論壇簡化了它。）

您可以驗證它是否進行表掃描：

FLUSH STATUS;
perform the operation
SHOW SESSION STATUS LIKE 'Handler%';

同時，在某些情況下，“行”EXPLAIN是悲觀的。如有疑問，我會相信處理程序的數量。我得到了 15 行而不是 10 行（不包括 0 個值）：

| Handler_commit             | 1     |
| Handler_external_lock      | 4     |
| Handler_read_first         | 1     |
| Handler_read_key           | 18    |
| Handler_read_rnd_next      | 16    |
| Handler_update             | 2     |
| Handler_write              | 2     |

這聞起來像 2 行的 tmp 表，加上 15 行的完整掃描（可能兩次？），以及其他一些雜項。

引用自：https://dba.stackexchange.com/questions/160396