要回答這個問題，首先我們要明確這個表的數(shù)據(jù)是否全部有用？使用MySQL的過程，經(jīng)常會遇到一個問題，比如說某張”log”表，用于保存某種記錄，隨著時間的不斷的累積數(shù)據(jù)，但是只有最新的一段時間的數(shù)據(jù)是有用的；這個時候會遇到性能和容量的瓶頸，需要將表中的歷史數(shù)據(jù)進(jìn)行歸檔。

也就是說，大部分情況，我們做數(shù)據(jù)歸檔就足以解決這個問題。只有那些全部很重要的業(yè)務(wù)數(shù)據(jù)，才需要做分庫分表。

利用存儲過程和事件來定期進(jìn)行數(shù)據(jù)的導(dǎo)出刪除操作

創(chuàng)建一個新表，表結(jié)構(gòu)和索引與舊表一模一樣：

create table table_new like table_old;

新建存儲過程，查詢30天的數(shù)據(jù)并歸檔進(jìn)新數(shù)據(jù)庫，然后把30天前的舊數(shù)據(jù)從舊表里刪除：

delimiter $
create procedure sp()
begin
insert into tb_new select * from table_old where rectime < NOW() - INTERVAL 30 DAY;
delete from db_smc.table_old where rectime < NOW() - INTERVAL 30 DAY;
end

創(chuàng)建EVENT，每天晚上凌晨00：00定時執(zhí)行上面的存儲過程：

create event if not exists event_temp
on schedule every 1 day
on completion preserve
do call sp();

備注：第一次執(zhí)行存儲過程的時候因為歷史數(shù)據(jù)過大, 可能發(fā)生意外讓該次執(zhí)行沒有成功。重新執(zhí)行時會遇到報錯ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，應(yīng)急解決方案如下：

• 執(zhí)行show full processlist;查看所有MySQL線程。
• 執(zhí)行SELECT * FROM information_schema.INNODB_TRX; 查看是否有錯誤線程，即線程id在show full processlist;的結(jié)果中，狀態(tài)為sleep的線程。
• kill進(jìn)程id。

另外寫存儲過程的時候可以控制事務(wù)的大小，比如說可以根據(jù)時間字段每次歸檔一天或者更小時間段的數(shù)據(jù)，這樣就不會有大事務(wù)的問題，里面還可以加入日志表，每次歸檔操作的行為都寫入日志表，以后查起來也一目了然。

實戰(zhàn)

首先，查看一下哪些表數(shù)據(jù)量特別大：

SELECT   
    TABLE_NAME AS '表名',  
    TABLE_ROWS AS '記錄數(shù)'  
FROM   
    information_schema.TABLES   
WHERE   
    TABLE_SCHEMA = 'tms' and TABLE_ROWS > 1000;  -- 這里替換為你的數(shù)據(jù)庫名

如圖，我要對原數(shù)據(jù)庫中的single_packaging表進(jìn)行歸檔，就先新建一個用于歸檔的數(shù)據(jù)庫doc_history:

然后建一張一模一樣的表在這個數(shù)據(jù)庫，編寫歸檔的存儲過程：

delimiter $
create procedure sp()
begin
insert into doc_history.single_packaging select * from old_schema.single_packaging  where create_time < NOW() - INTERVAL 7 DAY;
delete from old_schema.single_packaging where create_time < NOW() - INTERVAL 7 DAY;

end

注意老庫和新庫的區(qū)別。

最后，設(shè)置事件，每天定時跑：

create event if not exists event_temp
on schedule every 1 day
on completion preserve
do call sp();

這樣就OK了。