釣友寶 （微信小程序）：一款專門為 釣友 開(kāi)發(fā)的 免費(fèi)的 分享釣點(diǎn)地圖與實(shí)時(shí)天氣的軟件，地圖中標(biāo)記了所有野釣、釣場(chǎng)、公共水域等的精確位置，支持導(dǎo)航、 預(yù)測(cè)釣魚位置的魚情 等功能。前言
有些時(shí)候在進(jìn)行一些業(yè)務(wù)迭代時(shí)需要我們對(duì)Mysql表中數(shù)據(jù)進(jìn)行全表update，如果是在數(shù)據(jù)量比較小的情況下（萬(wàn)級(jí)別），可以直接執(zhí)行sql語(yǔ)句，但是如果數(shù)據(jù)量達(dá)到一個(gè)量級(jí)后，就會(huì)出現(xiàn)一些問(wèn)題，比如主從架構(gòu)部署的Mysql，主從同步需要需要binlog來(lái)完成，而binlog格式如下，其中使用statement和row格式的主從同步之間binlog在update情況下的展示：
我們當(dāng)前線上mysql是使用row格式binlog來(lái)進(jìn)行的主從同步，因此如果在億級(jí)數(shù)據(jù)的表中執(zhí)行全表update，必然會(huì)在主庫(kù)中產(chǎn)生大量的binlog，接著會(huì)在進(jìn)行主從同步時(shí)，從庫(kù)也需要阻塞執(zhí)行大量sql，風(fēng)險(xiǎn)極高，因此直接update是不行的。
本文就從我最開(kāi)始的一個(gè)全表update sql開(kāi)始，到最后上線的分批更新策略，如何優(yōu)化和思考來(lái)展開(kāi)說(shuō)明。
直接update的問(wèn)題
我們前段時(shí)間需要將用戶的一些基本信息存儲(chǔ)從http轉(zhuǎn)換為https，庫(kù)中數(shù)據(jù)大概在幾千w的級(jí)別，需要對(duì)一些大表進(jìn)行全表update，最開(kāi)始我試探性的跟dba同事拋出了一個(gè)簡(jiǎn)單的update語(yǔ)句，想著流量低的時(shí)候執(zhí)行，如下:
update tb_user_info set user_img=replace(user_img,'http://','https://')上面肯定是不合理的會(huì)給主庫(kù)生成binlog、從庫(kù)接收binlog寫數(shù)據(jù)帶來(lái)很大的壓力，于是就想使用腳本分批處理如下所示：寫一個(gè)這樣的腳本，依次分批替換，limit的游標(biāo)不斷增加。
大概一看是沒(méi)有問(wèn)題的，但是仔細(xì)一想mysql的limit游標(biāo)進(jìn)行的范圍查找原理，是下沉到B+數(shù)的葉子節(jié)點(diǎn)進(jìn)行的向后遍歷查找，在limit數(shù)據(jù)比較小的情況下還好，limit數(shù)據(jù)量比較大的情況下，效率很低接近于全表掃描，這也就是我們常說(shuō)的“深度分頁(yè)問(wèn)題”。
update tb_user_info set user_img=replace(user_img,'http://','https://') limit 1,1000;既然mysql的深分頁(yè)有問(wèn)題，那么我就把這批id全部查出來(lái)，然后更新的id in這些列表，進(jìn)行批量更新可以嗎？
于是我又寫了類似下面sql的腳本。結(jié)果是還不行，雖然mysql對(duì)于in這些查找有一些鍵值預(yù)測(cè)，但是仍然是很低效。
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及腳本，這里有幾個(gè)問(wèn)題需要注意，我們?cè)趕elect sql中使用了這個(gè)語(yǔ)法法/*!40001 SQL_NO_CACHE */，這個(gè)語(yǔ)法的意思就是本次查詢不使用innodb的buffer pool，也不會(huì)將本次查詢的數(shù)據(jù)頁(yè)放到buffer pool中作為熱點(diǎn)數(shù)據(jù)的緩存。
接著對(duì)于查詢強(qiáng)制使用主鍵索引 FORCE INDEX(PRIMARY)，并且根據(jù)主鍵索引排序，排序后的數(shù)據(jù)進(jìn)行id游標(biāo)的篩選。最后執(zhí)行update更新時(shí)，由于我們?cè)谇懊娴膕ql中查詢到的就是已經(jīng)排序后的主鍵，因此可以對(duì)id執(zhí)行范圍查找。
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}";我們可以僅關(guān)注第一個(gè)sql，如下圖所示，是buffer pool大概內(nèi)容，我們可以通過(guò)這個(gè)no cache的關(guān)鍵字，對(duì)批量處理的數(shù)據(jù)進(jìn)行強(qiáng)制指定不走buffer pool，不把這些冷數(shù)據(jù)影響到正常使用的緩存內(nèi)容，防止效率的降低，其實(shí)mysql在一些備份的動(dòng)作中。
使用的數(shù)據(jù)掃描sql也會(huì)帶上這個(gè)關(guān)鍵字，防止影響到正常的業(yè)務(wù)緩存；接著需要強(qiáng)制對(duì)當(dāng)前查詢指定的主鍵索引，然后進(jìn)行排序，否則mysql有可能在計(jì)算io成本進(jìn)行索引選擇時(shí)，選擇其他的索引。
使用這樣的方式對(duì)數(shù)據(jù)庫(kù)進(jìn)行批量更新可以通過(guò)一個(gè)接口來(lái)控制速率，對(duì)于數(shù)據(jù)庫(kù)主從同步、iops、內(nèi)存使用率等關(guān)鍵屬性進(jìn)行觀察，手動(dòng)調(diào)整刷庫(kù)速率。這樣看是單線程阻塞的操作，其實(shí)接口也可以定義線程個(gè)數(shù)等屬性，接口中根據(jù)賦予的線程個(gè)數(shù)，通過(guò)線程池并行刷數(shù)據(jù)，從而提高全表更新速率的上限，同時(shí)對(duì)速率進(jìn)行控制控制。
其他問(wèn)題
如果我們使用snowflake雪花算法或者自增主鍵來(lái)生成主鍵id的話，插入的記錄都是根據(jù)主鍵id順序插入的，如果使用uuid這種我們?cè)趺刺幚恚?/p>
當(dāng)然是業(yè)務(wù)中就預(yù)先處理了，先把入庫(kù)的數(shù)據(jù)提前進(jìn)行替換，進(jìn)行代碼上線后再進(jìn)行的全量數(shù)據(jù)更新了。
結(jié)語(yǔ)
刷數(shù)據(jù)本來(lái)是一個(gè)異常枯燥的工作內(nèi)容，但是從這次數(shù)據(jù)量較大的數(shù)據(jù)更新從而與dba同事的多次溝通后，也對(duì)mysql有了一些新的理解，包括不限于下面幾個(gè)，共同學(xué)習(xí)。
binlog格式帶來(lái)的大數(shù)據(jù)量更新的主從同步問(wèn)題；
Mysql深分頁(yè)的效率問(wèn)題；
全表掃數(shù)據(jù)如何防止對(duì)buffer pool污染到我們業(yè)務(wù)正常的熱點(diǎn)數(shù)據(jù)。
Java精選面試題 （微信小程序）：5000+道面試題和選擇題，包含Java基礎(chǔ)、MQ、Redis、SpringBoot、Elasticsearch、Docker、K8s、Flink、Spark、架構(gòu)設(shè)計(jì)、大廠真題等，在線隨時(shí)刷題！
來(lái)源：https://juejin.cn/post/6897185211340029966
公眾號(hào)“Java精選”所發(fā)表內(nèi)容注明來(lái)源的，版權(quán)歸原出處所有（無(wú)法查證版權(quán)的或者未注明出處的均來(lái)自網(wǎng)絡(luò)，系轉(zhuǎn)載，轉(zhuǎn)載的目的在于傳遞更多信息，版權(quán)屬于原作者。如有侵權(quán)，請(qǐng)聯(lián)系，筆者會(huì)第一時(shí)間刪除處理！
最近有很多人問(wèn)，有沒(méi)有讀者或者摸魚交流群！加入方式很簡(jiǎn)單，公眾號(hào)Java精選，回復(fù)“加群”，即可入群！
文章有幫助的話，點(diǎn)在看，轉(zhuǎn)發(fā)吧！
熱門跟貼