為什麼我的更新比 select 慢？

Amazon RDS InnoDB 上的 MariaDB 10.2.21

我正在執行一個複雜的更新（where 語句中有很多子查詢）。並且需要幾分鐘才能完成。如果我將更新更改為選擇，大約需要 1 秒。

如果我多次執行更新，它需要相同的時間，即使只有第一次執行實際上改變了任何東西（~4000 行）。

查詢在慢查詢中彈出，rows_examined 是數十億。鎖定時間為 0。

我不能自由分享確切的查詢，但它是這樣的：

update things
set status='hidden'
where id not in (select thing_id from other_table where some condition)
 and id not in (select thing_id from yet another table ...)

我完全意識到這不是那麼多資訊。

• 10.2 可能使用較舊的優化器用於UPDATE. 它不如SELECT. （不，我沒有比這更具體的了。）
• 避免NOT IN ( SELECT ... )——它因效率低下而臭名昭著。由於您看到“數十億”行_examined，我懷疑這證實了我上面的項目符號。舊的優化器每次需要測試時都會重新執行子查詢IN。
• 嘗試將查詢轉換為UPDATE使用LEFT JOIN ... WHERE ... IS NULL而不是NOT IN.
• 使用起來可能很實用NOT EXISTS( SELECT 1 FROM other_table WHERE some_condition AND thing_id = outer.id )

…完成需要幾分鐘。

如果我將更新更改為選擇，大約需要 1 秒。

你讀一本書需要多長時間？你要寫

多久

$$ a copy of $$同一本書？ 從數據庫中讀取數據意味著將數據塊放入緩衝區記憶體中，在記錄之間圍繞一些“指針”衝刺並將數據發送回客戶端。簡單的！

在數據庫中寫入內容涉及實際的磁碟寫入（寫入事務日誌），更新緩衝區記憶體中的數據塊，然後將這些塊寫回數據文件。很多[和很多]工作，這需要更長的時間。

如果我多次執行更新，它需要相同的時間，即使只有第一次執行實際上改變了任何東西（~4000 行）。

是什麼讓您認為數據庫在每次執行時都在做不同的事情？

您的數據庫不會比較每個欄位的“之前”和“之後”值，而只會更改真正不同的值。如果你說“更新”，那麼它將*“更新”——即使它以相同*的值結束——伴隨著所有的磁碟活動。

至於您的特定查詢，與僅一個子選擇相比，您可能會獲得更好的性能：

. . . 
where id not in 
( select thing_id from other_table where some-condition
 union all 
 select thing_id from yet-another-table ...
)

這可能允許數據庫使用更好的索引選項，而不必事後猜測自己，檢查每個 id 是否為not this and not that.

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