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

釣友寶 （微信小程序）：一款專門為 釣友 開發的 免費的 分享釣點地圖與實時天氣的軟件，地圖中標記了所有野釣、釣場、公共水域等的精確位置，支持導航、 預測釣魚位置的魚情 等功能。

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

格式 內容 statement 記錄同步在主庫上執行的每一條sql，日志量較少，減少io，但是部分函數sql會出現問題比如random row 記錄每一條數據被修改或者刪除的詳情，日志量在特定條件下很大，如批量delete、update mixed 以上兩種方式混用，一般的語句修改使用statement記錄，其他函數式使用row

我們當前線上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://')

上面肯定是不合理的會給主庫生成binlog、從庫接收binlog寫數據帶來很大的壓力，于是就想使用腳本分批處理如下所示：寫一個這樣的腳本，依次分批替換，limit的游標不斷增加。

大概一看是沒有問題的，但是仔細一想mysql的limit游標進行的范圍查找原理，是下沉到B+數的葉子節點進行的向后遍歷查找，在limit數據比較小的情況下還好，limit數據量比較大的情況下，效率很低接近于全表掃描，這也就是我們常說的“深度分頁問題”。

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

既然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有了一些新的理解，包括不限于下面幾個，共同學習。

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

• Mysql深分頁的效率問題；

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

Java精選面試題 （微信小程序）：5000+道面試題和選擇題，包含Java基礎、MQ、Redis、SpringBoot、Elasticsearch、Docker、K8s、Flink、Spark、架構設計、大廠真題等，在線隨時刷題！

來源：https://juejin.cn/post/6897185211340029966

公眾號“Java精選”所發表內容注明來源的，版權歸原出處所有（無法查證版權的或者未注明出處的均來自網絡，系轉載，轉載的目的在于傳遞更多信息，版權屬于原作者。如有侵權，請聯系，筆者會第一時間刪除處理！

最近有很多人問，有沒有讀者或者摸魚交流群！加入方式很簡單，公眾號Java精選，回復“加群”，即可入群！

文章有幫助的話，點在看，轉發吧！