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分钟就可以搞定了。
附脚本：
[cc lang="sql"]###################
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 i=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;
//
[/cc]
-----------
set auto_commit=0;
call sp_xf_move_item();
#### 增量脚本 ######
[cc lang="sql"]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 i=i+1;
if mod(i,100)=0 then commit;
end if;
until v_exit=1
end repeat;
close c_ids;
commit;
end;
//
[/cc]
-------
如果全量和增量之间的时间拖的比较长，那么可以设置时间，多做几次增量已缩短最后的停机时间，你懂的。。。
call sp_xf_add_item()//
EOF
This work is licensed under a CC A-S 4.0 International License.