業(yè)務(wù)需求：今天從生成測試環(huán)境遷移了一批百萬級/千萬級表的數(shù)據(jù)，領(lǐng)導(dǎo)要求將這批數(shù)據(jù)進行脫敏處理（將真實姓名 、電話、郵箱、身份證號等敏感信息進行替換）。遷移數(shù)據(jù)記錄數(shù)如下（小于百萬級的全量更新不是本文重點）:

解決辦法：

第一種：使用Update 語句進行全表更新。

結(jié)論：update 更新時間慢長，單表百萬級數(shù)據(jù)更新少說幾小時起步。這種方案放棄

第二種：使用存儲過程，進行批量更新。

實戰(zhàn):

create procedure batch_update_house()
begin
	-- 定義變量
	declare i int default 1;
    -- 批次更新大小 10000
	declare pageSize int default 10000;
	declare j int default 1;
	-- 421 為房屋總記錄 數(shù)/10000 向上取整
	while i < 421 do
		if i = 1 then
            -- 溫馨提示：update 語句不能直接使用limit，必須使用子查詢
			update base_house set real_name ="******", real_phone="136****0511"  where id in(select id from (select id from base_house limit  i, pageSize ) as temp);
		else
			set j = i * pageSize + 1;
            -- 溫馨提示：update 語句不能直接使用limit，必須使用子查詢
			update base_house set real_name ="******", real_phone="136****0511"  where id in(select id from (select id from base_house limit  j, pageSize ) as temp);
		end if;
		set i = i + 1;
	end while;
end

功能說明： batch_update_house 房屋全量更新存儲過程。

結(jié)論：伴隨limit 偏移量量增大，每次花費尋找起始位置行的時間會延長，但能夠避免超長時間執(zhí)行。（入選清洗方案）? ?

第三種：使用中間表，通過大表關(guān)聯(lián)中間表進行update,通過where 條件命中索引，可以提升批量更新尋找起始位置行的時間? ? ?

第一步：設(shè)計房屋清洗中間表：base_middle_house，設(shè)計兩個字段（id 主鍵 自增長、house_id 房屋關(guān)聯(lián)主鍵，唯一主鍵）

create table "base_middle_house" (
    `id` int(11) not null auto_increment comment '主鍵',
    `house_id` varchar(200) not null comment '房屋關(guān)聯(lián)主鍵'，
    primary key (`id`),
    unique key `unique_house_id` (`house_id`)
) engine=InnoDB auto_increment=1 default charset=utf8mb4

第二步：創(chuàng)建批量更新存儲過程

create procedure batch_update_middle_house()
begin
	-- 定義變量
	declare i int default 1;
	declare pageSize int default 100000;
	declare j int default 1;
	
	while i < 43 do
		if i = 1 then
			update base_house h, base_middle_house t set  h.real_name ="******", h.real_phone="136****0511"  where t.house_id = h.id and t.id >=1 amd t.id <=100000;
		else
			set j = (i -1) * pageSize + 1;
			update base_house h, base_middle_house t set h.real_name ="******", h.real_phone="136****0511"  where t.house_id = h.id and t.id >=j amd t.id <= i*pageSize;
		end if;
		set i = i + 1;
	end while;
end

實戰(zhàn)：執(zhí)行batch_update_middle_house 存儲過程，base_house 全表4201183 記錄數(shù)在1小時10分鐘內(nèi)全部清洗完畢。

結(jié)論：第三種方案針對第二種方案對于尋找起始行位置通過索引進行了優(yōu)化，批量更新的時間也有明細的提示，達到單臺服務(wù)器每分鐘處理5000條記錄數(shù)據(jù)。初步達到領(lǐng)導(dǎo)的要求。（清洗方案s首推）?文章來源地址http://www.zghlxwxcb.cn/news/detail-454553.html

到了這里，關(guān)于MySQL 百萬級/千萬級表 全量更新的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！