[MySQL] 使用 update_all & subquery 可能會鎖住全表

團隊最近遇到一個 subquery + update_all 會鎖整張表的問題，花了一點時間研究

注意：以下為 MySQL 5.6 的實驗探討，在 MySQL 8.0.21 的這個機制已經被優化了

問題

假設有一份 schema 長這樣，有 users 和 posts 兩張表。

  create_table "posts", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.string "title"
    t.boolean "active", null: false
    t.integer "user_id", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["user_id"], name: "index_posts_on_user_id"
  end

  create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", force: :cascade do |t|
    t.string "name"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

從 MySQL 看長這樣

CREATE TABLE `posts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_posts_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4;

users 新增了 100 萬筆資料，posts 新增了 1000 筆資料。

假設今天使用了一個 subquery 做 update_all

User.where(id: Post.where(id: [1,2]).select(:user_id)).update_all(name: 'Momo')

MySQL 的 statement 長這樣

UPDATE `users` SET `users`.`name` = 'Momo' WHERE `users`.`id` IN (SELECT `posts`.`user_id` FROM `posts` WHERE `posts`.`id` IN (1, 2))

感覺上因為 subquery 只會拿到兩筆資料，執行起來應該很快吧？但是執行起來卻很慢。來 explain 一下：

發現竟然掃描了整張 users 表，是怎麼回事呢。

MySQL 的 Subqueries 的 Optimization 策略

根據官方文件說明， 對於 IN subquery 有三種方式去優化，分別是 Semijoin、Materialization、EXISTS strategy。但是在下面有的附註寫到：

A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semijoin or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.

其實 UPDATE 搭配 subquery 是會使用 EXISTS strategy 的。

這件事可以透過調整 optimizer_switch 用 EXPLAIN EXTENDED + SHOW WARNINGS 來驗證 SELECT 時的 query ：

set optimizer_switch='semijoin=off,materialization=off'
EXPLAIN EXTENDED SELECT * FROM `users` WHERE `users`.`id` IN (SELECT `posts`.`user_id` FROM `posts` WHERE `posts`.`id` IN (1, 2))

發現看起來跟上面 update 的 explain 很像：

執行 SHOW WARNINGS ，可以看到 IN query 是被改寫為 EXISTS：

/* select#1 */
 select 
`update_subquery_development`.`users`.`id` AS `id`,
`update_subquery_development`.`users`.`name` AS `name`,
`update_subquery_development`.`users`.`created_at` AS `created_at`,
`update_subquery_development`.`users`.`updated_at` AS `updated_at` from 
`update_subquery_development`.`users` 
where 
<in_optimizer>(
`update_subquery_development`.`users`.`id`,
<exists>(
<index_lookup>(<cache>(`update_subquery_development`.`users`.`id`)   in posts on index_posts_on_user_id
 where
 ((`update_subquery_development`.`posts`.`id` in (1,2)) 
 and
 (<cache>(`update_subquery_development`.`users`.`id`) = `update_subquery_development`.`posts`.`user_id`)))
)
)

所以這個 update statement 其實會以掃描整張 users 的方式來進行，在 transaction 進行中是會鎖住整張 users 表的。因此如果使用 update + subquery 時要注意如果外表是很大的表，可能會有問題。

使用 JOIN 改寫

一個優化的方式是可以使用改寫成 join clause 的方式來避開這個問題，例如：

User.joins(:posts).where(posts: { id: [1,2] }).update_all(name: 'Momo')

MySQL 的 statement 長這樣

UPDATE `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` SET `users`.`name` = 'Momo' WHERE `posts`.`id` IN (1, 2)

explain 看看：

MySQL 8.0.21 之後有優化了

從官方文件這邊提到 8.0.21 之後，UPDATE 也可以用 semijoin transformation：

In MySQL 8.0.21 and later, a semijoin transformation can also be applied to a single-table UPDATE or DELETE statement that uses a [NOT] IN or [NOT] EXISTS subquery predicate, provided that the statement does not use ORDER BY or LIMIT, and that semijoin transformations are allowed by an optimizer hint or by the optimizer_switch setting.

實驗了一下的確有優化了：

EXPLAIN UPDATE `users` SET `users`.`name` = 'Momo' WHERE `users`.`id` IN (SELECT `posts`.`user_id` FROM `posts` WHERE `posts`.`id` IN (1, 2))