千萬級數據的全表 update 正確姿勢

前言

有些時候在進行一些業務迭代時需要我們對 Mysql 表中數據進行全表 update，如果是在數據量比較小的情況下（萬級別），可以直接執行 sql 語句，但是如果數據量達到一個量級後，就會出現一些問題，比如主從架構部署的 Mysql，主從同步需要需要 binlog 來完成，而 binlog 格式如下，其中使用 statement 和 row 格式的主從同步之間 binlog 在 update 情況下的展示：

我們當前線上 mysql 是使用 row 格式 binlog 來進行的主從同步，因此如果在億級數據的表中執行全表 update，必然會在主庫中產生大量的 binlog，接着會在進行主從同步時，從庫也需要阻塞執行大量 sql，風險極高，因此直接 update 是不行的。本文就從我最開始的一個全表 update sql 開始，到最後上線的分批更新策略，如何優化和思考來展開說明。

正文

直接 update 的問題

我們前段時間需要將用戶的一些基本信息存儲從 http 轉換爲 https，庫中數據大概在幾千 w 的級別，需要對一些大表進行全表 update，最開始我試探性的跟 dba 同事拋出了一個簡單的 update 語句，想着流量低的時候執行，如下:

update tb_user_info set user_img=replace(user_img,'http://','https://')

這裏也給大家提一個醒，在存儲圖片等 path 路徑時，經歷不要存儲協議和域名之內的前綴部分。如果要改 http 協議、域名之類的就要涉及批量更新等操作，同時存儲的容量也會不必要的增加。

深度分頁問題

上面肯定是不合理的會給主庫生成 binlog、從庫接收 binlog 寫數據帶來很大的壓力，於是就想使用腳本分批處理如下所示：寫一個這樣的腳本，依次分批替換，limit 的遊標不斷增加。大概一看是沒有問題的，但是仔細一想 mysql 的 limit 遊標進行的範圍查找原理，是下沉到 B + 數的葉子節點進行的向後遍歷查找，在 limit 數據比較小的情況下還好，limit 數據量比較大的情況下，效率很低接近於全表掃描，這也就是我們常說的 “深度分頁問題”。

update tb_user_info set user_img=replace(user_img,'http://','https://') limit 1,1000;

in 的效率

既然 mysql 的深分頁有問題，那麼我就把這批 id 全部查出來，然後更新的 id in 這些列表，進行批量更新可以嗎？於是我又寫了類似下面 sql 的腳本。結果是還不行，雖然 mysql 對於 in 這些查找有一些鍵值預測，但是仍然是很低效。

select * from tb_user_info where id> {index} limit 100;
update tb_user_info set user_img=replace(user_img,'http','https')where id in {id1,id3,id2};

最終版本

最終在與 dba 的多次溝通下，我們寫了如下的 sql 及腳本，這裏有幾個問題需要注意，我們在 select sql 中使用了這個語法/*!40001 SQL_NO_CACHE */，這個語法的意思就是本次查詢不使用 innodb 的 buffer pool，也不會將本次查詢的數據頁放到 buffer pool 中作爲熱點數據的緩存。接着對於查詢強制使用主鍵索引 FORCE INDEX(PRIMARY) ，並且根據主鍵索引排序，排序後的數據進行 id 遊標的篩選。最後執行 update 更新時，由於我們在前面的 sql 中查詢到的就是已經排序後的主鍵，因此可以對 id 執行範圍查找。

select /*!40001 SQL_NO_CACHE */ id from tb_user_info FORCE INDEX(`PRIMARY`) where id> "1" ORDER BY id limit 1000,1;

update tb_user_info set user_img=replace(user_img,'http','https') where id >"{1}" and id <"{2}";

我們可以僅關注第一個 sql，如下圖所示，是 buffer pool 大概內容，我們可以通過這個 no cache 的關鍵字，對批量處理的數據進行強制指定不走 buffer pool，不把這些冷數據影響到正常使用的緩存內容，防止效率的降低，其實 mysql 在一些備份的動作中。使用的數據掃描 sql 也會帶上這個關鍵字，防止影響到正常的業務緩存；接着需要強制對當前查詢指定的主鍵索引，然後進行排序，否則 mysql 有可能在計算 io 成本進行索引選擇時，選擇其他的索引。

使用這樣的方式對數據庫進行批量更新可以通過一個接口來控制速率，對於數據庫主從同步、iops、內存使用率等關鍵屬性進行觀察，手動調整刷庫速率。這樣看是單線程阻塞的操作，其實接口也可以定義線程個數等屬性，接口中根據賦予的線程個數，通過線程池並行刷數據，從而提高全表更新速率的上限，同時對速率進行控制控制。

其他問題

如果我們使用 snowflake 雪花算法或者自增主鍵來生成主鍵 id 的話，插入的記錄都是根據主鍵 id 順序插入的，如果使用 uuid 這種我們怎麼處理？當然是業務中就預先處理了，先把入庫的數據提前進行替換，進行代碼上線後再進行的全量數據更新了。

結語

刷數據本來是一個異常枯燥的工作內容，但是從這次數據量較大的數據更新從而與 dba 同事的多次溝通後，也對 mysql 有了一些新的理解，包括不限於下面幾個，共同學習。

  1. binlog 格式帶來的大數據量更新的主從同步問題；

  2. Mysql 深分頁的效率問題；

  3. 全表掃數據如何防止對 buffer pool 污染到我們業務正常的熱點數據。

羣友問題：

Q：第一個 sql 如果不走 buffer pool，第二個更新 sql 也會把數據頁載入到 buffer pool 吧？

A：讀緩存和寫緩存是不一樣的。

Q：只要我知道 min_id、max_id，只要序列差不多連續是不是可以直接分片執行，不需要一定要每次 1000 條執行的吧？

A：min 和 max 這樣直接分片的話，除非是自增 id，否則是不能保證勻速的，後續多線程執行的任務分配也不能得到保證。

Q：寫緩存指的是 change buffer？這個修改應該用不了 change buffer 吧？

A：是，用得到。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/lri2ZVRDjRoxb_ZfzrisOQ

猜你喜歡