MySql中數(shù)據(jù)表增加字段很慢問題及解決
MySql數(shù)據(jù)表增加字段很慢
由于數(shù)據(jù)庫表中有大量數(shù)據(jù),導(dǎo)致給表添加字段就會很慢。
所以,對于數(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字段和你查詢出來的字段一致,對應(yīng)順序也是需要一致的,如果你只是修改字段的屬性,或者添加了索引之類的,字段數(shù)量沒用變話,那么直接這樣寫
insert into new_tb select * from tb_old;
4.刪除原來的數(shù)據(jù)表,并且重新命名新的數(shù)據(jù)表:
drop table tb_old; alter table new_tb rename to tb_old;
MySql大表加字段問題
場景
現(xiàn)在 有個數(shù)據(jù)量大概40億的數(shù)據(jù),存在10個庫,總計80張表上面,每個表大概5000萬的數(shù)據(jù)量,
這個表通過一個MQ接口不斷地接收數(shù)據(jù),每天大概新增或更新數(shù)據(jù)量是幾十萬。
現(xiàn)在,需要在在這個表上加個新字段X,而且都有初始值,都存儲在對方系統(tǒng)的數(shù)據(jù)庫里。而他們這個新的字段一直就有。只是我方系統(tǒng)一直以來沒有接這個字段。
注意:加字段期間不能影響原有的查這張表的業(yè)務(wù)的使用。
問:如何新增。
首先,肯定不能在表上面直接加字段,數(shù)據(jù)量太大,直接加字段,sql一夜都跑不完。而且會影響查詢業(yè)務(wù)的使用
方案如下
- 第一步,我們同樣的數(shù)據(jù)庫的位置,在老表的_old下方創(chuàng)建新表_new ,字段結(jié)構(gòu)完全一樣,只是多了新字段X。
- 第二步,讓對方系統(tǒng)把老的40億數(shù)據(jù)推到hive上面,做準(zhǔn)備,時間節(jié)點記為1月1日零時,大概三天推上去;
- 第三步,在此之前,建立一個新接口,一個臨時表new_temporary從1月1日零時,就開始接數(shù)據(jù)到臨時表;
- 第四部,啟動拉數(shù)據(jù)任務(wù)流,每天半夜從hive拉數(shù)據(jù),拉倒新表new,拉的時候記得max_binlog_cache_size 改大,不然一次入庫會撐爆,大概10天左右拉完,截止時間1月15日零時。(這個時間節(jié)點要用)
- 第五步,拉數(shù)據(jù)完畢后,啟動一個定時任務(wù),把從臨時表new_temporary接收的數(shù)據(jù),更新到新表_new。更新條件是,只對數(shù)據(jù)庫更新時間是 1月15日零時之前的數(shù)據(jù)做更新
- 第六步,同時切換接口開關(guān),關(guān)閉接臨時表的接口,打開接新表接入數(shù)據(jù)的接口。同時入老表old的接口也不能關(guān),預(yù)防漏發(fā)數(shù)據(jù)隨時切回來。
- 第七步,修改系統(tǒng)中所有查詢老表old查詢方法,改成查詢新表,用redis加上開關(guān)。上線功能。
- 第八步,打開開關(guān),使用新表,若有漏發(fā)數(shù)據(jù)(少量),這提醒對方系統(tǒng)補發(fā)數(shù)據(jù),逐步補全數(shù)據(jù),
- 第九步,等系統(tǒng)運行平穩(wěn)以后,關(guān)掉接老表old數(shù)據(jù)的接口,刪除臨時表new_temporary,刪除老表_old
感悟:
本來加一個字段不算太大的事,可是因為數(shù)據(jù)量大,又要保證可用性,所以還是需要一套方案來做這個事情。
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
Hibernate4在MySQL5.1以上版本創(chuàng)建表出錯 type=InnDB
本文主要介紹解決Hibernate4在MySQL5.1自動創(chuàng)建表出錯的方法,簡單實用,需要的朋友可以參考下。2016-05-05MySQL中的自定義函數(shù)(CREATE FUNCTION)
這篇文章主要介紹了MySQL中的自定義函數(shù)(CREATE FUNCTION),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06