MySql中數(shù)據(jù)表增加字段很慢問(wèn)題及解決

更新時(shí)間：2024年04月11日 09:16:07 作者：獅子君的石榴

這篇文章主要介紹了MySql中數(shù)據(jù)表增加字段很慢問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

MySql數(shù)據(jù)表增加字段很慢

由于數(shù)據(jù)庫(kù)表中有大量數(shù)據(jù)，導(dǎo)致給表添加字段就會(huì)很慢。

所以，對(duì)于數(shù)據(jù)量很大的表，需要添加所有或者修改字段的做法是如下：

1.先創(chuàng)建一張一樣的表

create table new_tb like tb_old;

2.修改創(chuàng)建表的字段

alter table new_tb add COLUMN new_column varchar(32) DEFAULT null;

3.原始數(shù)據(jù)插入到新的數(shù)據(jù)表中

insert into new_tb (字段) select 字段 from tb_old;

這里需要注意下，你的insert字段和你查詢出來(lái)的字段一致，對(duì)應(yīng)順序也是需要一致的，如果你只是修改字段的屬性，或者添加了索引之類的，字段數(shù)量沒用變?cè)挘敲粗苯舆@樣寫

insert into new_tb select * from tb_old;

4.刪除原來(lái)的數(shù)據(jù)表，并且重新命名新的數(shù)據(jù)表:

drop table tb_old;
alter table new_tb rename to tb_old;

MySql大表加字段問(wèn)題

場(chǎng)景

現(xiàn)在有個(gè)數(shù)據(jù)量大概40億的數(shù)據(jù)，存在10個(gè)庫(kù)，總計(jì)80張表上面，每個(gè)表大概5000萬(wàn)的數(shù)據(jù)量，

這個(gè)表通過(guò)一個(gè)MQ接口不斷地接收數(shù)據(jù)，每天大概新增或更新數(shù)據(jù)量是幾十萬(wàn)。

現(xiàn)在，需要在在這個(gè)表上加個(gè)新字段X，而且都有初始值，都存儲(chǔ)在對(duì)方系統(tǒng)的數(shù)據(jù)庫(kù)里。而他們這個(gè)新的字段一直就有。只是我方系統(tǒng)一直以來(lái)沒有接這個(gè)字段。

注意：加字段期間不能影響原有的查這張表的業(yè)務(wù)的使用。

問(wèn)：如何新增。

首先，肯定不能在表上面直接加字段，數(shù)據(jù)量太大，直接加字段，sql一夜都跑不完。而且會(huì)影響查詢業(yè)務(wù)的使用

方案如下

第一步，我們同樣的數(shù)據(jù)庫(kù)的位置，在老表的_old下方創(chuàng)建新表_new ，字段結(jié)構(gòu)完全一樣，只是多了新字段X。
第二步，讓對(duì)方系統(tǒng)把老的40億數(shù)據(jù)推到hive上面，做準(zhǔn)備，時(shí)間節(jié)點(diǎn)記為1月1日零時(shí)，大概三天推上去；
第三步，在此之前，建立一個(gè)新接口，一個(gè)臨時(shí)表new_temporary從1月1日零時(shí)，就開始接數(shù)據(jù)到臨時(shí)表；
第四部，啟動(dòng)拉數(shù)據(jù)任務(wù)流，每天半夜從hive拉數(shù)據(jù)，拉倒新表new，拉的時(shí)候記得max_binlog_cache_size 改大，不然一次入庫(kù)會(huì)撐爆，大概10天左右拉完，截止時(shí)間1月15日零時(shí)。（這個(gè)時(shí)間節(jié)點(diǎn)要用）
第五步，拉數(shù)據(jù)完畢后，啟動(dòng)一個(gè)定時(shí)任務(wù)，把從臨時(shí)表new_temporary接收的數(shù)據(jù)，更新到新表_new。更新條件是，只對(duì)數(shù)據(jù)庫(kù)更新時(shí)間是 1月15日零時(shí)之前的數(shù)據(jù)做更新
第六步，同時(shí)切換接口開關(guān)，關(guān)閉接臨時(shí)表的接口，打開接新表接入數(shù)據(jù)的接口。同時(shí)入老表old的接口也不能關(guān)，預(yù)防漏發(fā)數(shù)據(jù)隨時(shí)切回來(lái)。
第七步，修改系統(tǒng)中所有查詢老表old查詢方法，改成查詢新表，用redis加上開關(guān)。上線功能。
第八步，打開開關(guān)，使用新表，若有漏發(fā)數(shù)據(jù)（少量），這提醒對(duì)方系統(tǒng)補(bǔ)發(fā)數(shù)據(jù)，逐步補(bǔ)全數(shù)據(jù)，
第九步，等系統(tǒng)運(yùn)行平穩(wěn)以后，關(guān)掉接老表old數(shù)據(jù)的接口，刪除臨時(shí)表new_temporary，刪除老表_old

感悟：

本來(lái)加一個(gè)字段不算太大的事，可是因?yàn)閿?shù)據(jù)量大，又要保證可用性，所以還是需要一套方案來(lái)做這個(gè)事情。