如何使用 GROUP BY 更快地更新？

我有一個典型的文章和 tag_map 表，

CREATE TABLE `articles` (
 `ID` int(11) unsigned NOT NULL,
 `Tags` tinyint(3) unsigned DEFAULT NULL,
 `Title` varchar(512) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 KEY `Tags` (`Tags`),
 FULLTEXT KEY `Title` (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'


CREATE TABLE `tag_map` (
 `TagID` int(11) unsigned NOT NULL,
 `ID` int(11) unsigned NOT NULL,
 `Number` tinyint(3) unsigned DEFAULT NULL,
 PRIMARY KEY (`TagID`,`ID`),
 KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'

我需要表格中UPDATE的article標籤COUNT數量tag_map為

UPDATE articles a JOIN (
SELECT ID,COUNT(*) AS C FROM tag_map GROUP BY ID
) b ON a.ID=b.ID 
SET a.Tags=b.C WHERE a.Tags IS NULL

問題是WHERE a.Tags IS NULL條件不適用於子查詢。當只更新表中的幾個角色時，會掃描articles整個表。tag_map

我執行 MariaDB 10.5，這是以下輸出EXPLAIN UPDATE：

+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
| id   | select_type     | table       | type | possible_keys | key        | key_len | ref                   | rows     | Extra       |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
|    1 | PRIMARY         | a           | ALL  | PRIMARY,Tags  | NULL       | NULL    | NULL                  | 34543450 | Using where |
|    1 | PRIMARY         | <derived2>  | ref  | key0          | key0       | 4       | texts.a.ID            | 2        |             |
|    2 | LATERAL DERIVED | tag_map     | ref  | ID            | ID         | 4       | texts.a.ID            | 3        | Using index |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
3 rows in set (0.001 sec)

UPDATE articles a JOIN ( 
 SELECT a2.ID,COUNT(*) AS C 
 FROM articles a2 JOIN tag_map b2 ON a2.id=b2.id 
 WHERE a2.tags IS NULL
 GROUP BY a2.ID 
) b ON a.ID=b.ID  
SET a.Tags=b.C 
WHERE a.Tags IS NULL;

當要更新的行的百分比很低時，相關子查詢是最好的選擇：

UPDATE articles a
SET a.Tags = ( SELECT COUNT(*)
              FROM tag_map b
              WHERE a.ID = b.ID )
WHERE a.Tags IS NULL;

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