拆表用的MySQL存儲過程

MySQL存儲過程用途很廣泛，下面就為您介紹拆表用的MySQL存儲過程，希望對您學習MySQL存儲過程方面能夠有所幫助。

mysql表或分表的數據達到一定量也許是800w或者1000w..）這個時候非常需要再分表，簡單的辦法是直接寫
--假設根據user_id分表，分成64張

insert into table_new_0000 select * from table_old where mod(user_id,64)=0;  
insert into table_new_0001 select * from table_old where mod(user_id,64)=1;  
...  

一共64條sql，OK 搞定。  但是這個一張表被全表掃描了64次，做的無用功比較多，而且導致停機時間比較長。

雖然MySQL存儲過程不是很熟，稍稍學習了下寫了兩個腳本，一個全量+一個增量腳本完成表的拆分。
線上庫也實踐了下，8個分表，每個分表1000W記錄拆分到64個分表。
全量 時間 150分鐘，全量的時候幾個分表可以一起跑，我是同時跑3個分表
增量 時間 每個分表4分鐘 4個一起跑，一共是 8分鐘搞定。 這樣停機時間加上應用的發布一共只需要20分鐘就可以搞定了。

附腳本：

###################
delimeter //

-----------
--- 全量腳本：

CREATE PROCEDURE  sp_xf_move_item()  
begin  
declare v_exit int default 0;  
declare v_spid bigint;  
declare v_id bigint;  
declare i int default 0;  
declare c_table int;  

--定義游標要分拆的表，定義一個數量的截止時間）

declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified < '2010-8-25 00:00:00';  
declare  continue handler for not found set v_exit=1;  
open c_ids;  
repeat  

--將需要的值裝入變量

fetch c_ids into v_id,v_spid;  
if v_exit = 0 then  
set @vv_id = v_id;  

--根據取模字段獲取數據存在的表

select mod(v_spid,64) into c_table;  

--組裝動態sql

SET @SQL_CONTEXT =  
CONCAT('insert into item_record_',  
LPAD(c_table, 4, 0),  
' select * from item_records_0000 where id = ?');  
 
PREPARE STMT FROM @SQL_CONTEXT;  
--執行sql  
EXECUTE STMT using @vv_id;  
DEALLOCATE PREPARE STMT;  
end if;  
set ii=i+1;  
 

--100條提交一次，以提高效率，記得執行存儲過程前設置auto_commit

if mod(i,100)=0 then commit;  
end if;  
until v_exit=1 
end repeat;  
close c_ids;  
commit;  
end;  
//  
 
-----------  
set auto_commit=0;  
call  sp_xf_move_item();  
 
#### 增量腳本 ######  
 
CREATE PROCEDURE sp_xf_add_item()  
begin  
declare v_exit int default 0;  
declare v_spid bigint;  
declare v_id bigint;  
declare i int default 0;  
declare c_table int;  
declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified >= '2010-8-25 00:00:00';  
declare  continue handler for not found set v_exit=1;  
open c_ids;  
repeat  
 
fetch c_ids into v_id,v_spid;  
if v_exit = 0 then  
set @vv_id = v_id;  
set @v_row=0;  
select mod(v_spid,64) into c_table;  
 

--判斷數據是否已經存在

SET @SQL_C =  
CONCAT('select count(*) into @v_row from item_record_',  
LPAD(c_table, 4, 0),  
' where id = ?');  
 
PREPARE STMT_C FROM @SQL_C;  
EXECUTE STMT_C using @vv_id;  
DEALLOCATE PREPARE STMT_C;                         
 
SET @SQL_INSERT =  
CONCAT('insert into bbc_item_record_',  
LPAD(c_table, 4, 0),  
' select * from item_records_0000 where id = ?');  
 
PREPARE STMT_I FROM @SQL_INSERT;           
 
SET @SQL_DELETE =  
CONCAT('DELETE FROM bbc_item_record_',  
LPAD(c_table, 4, 0),  
' where id = ?');  
PREPARE STMT_D FROM @SQL_DELETE;       

--如果數據已經存在，則先delete在insert             

if @v_row>0 then   
 
EXECUTE STMT_D using @vv_id;  
DEALLOCATE PREPARE STMT_D;  
 
end if;  
EXECUTE STMT_I using @vv_id;  
DEALLOCATE PREPARE STMT_I;         
 
end if;  
set ii=i+1;  
if mod(i,100)=0 then commit;  
end if;  
until v_exit=1 
end repeat;  
close c_ids;  
commit;  
end;  
//  
 
-------  
 

如果全量和增量之間的時間拖的比較長，那麼可以設置時間，多做幾次增量已縮短最後的停機時間，你懂的。。。
call sp_xf_add_item()//
 

深入探討MySQL鎖機制

MySQL字段中的集合

MySQL字段類型簡介

Mysql外鍵用法分析

詳解MySQL數據表類型