mysql分表实战
本文主要讲述如何使用存储过程完成本表.并不讨论其他问题.首先我们得看看手册上关于meger引擎的说明:
MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表。而且,任何或者所有的表可以用myisampack来压缩。
CREATE TABLE `t3` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`post` text,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- Procedure "insert_isam" DDL
CREATE PROCEDURE `insert_isam`(in item integer)
begin
declare counter int;
set counter = item;
while counter >= 1 do
insert into t3 values(counter,concat('mysqlsystems.com',counter),repeat('bla',10));
set counter = counter - 1;
end while;
end;
call insert_isam(5000000);
这样就有了测试数据,注意这个过程有点慢,曾经尝试插入1亿条记录,结果死机了.-- Procedure "split_table" DDL
CREATE PROCEDURE `split_table`(in pagesize int)
begin
set @totalRecord=(select count(id) from t3);
set @totalPage=ceiling(@totalRecord/pagesize);
set @page=1;
while @page<=@totalPage do
set @tableName=concat("tab_",@page);
set @droptable=concat("drop table if exists ",@tableName);
prepare s from @droptable;
execute s;
set @sql=concat("
CREATE TABLE ",@tableName," (
`id` int(11) auto_increment,
`name` varchar(50),
`post` text,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM;");
PREPARE sta from @sql;
execute sta;
set @from=(@page-1)*pagesize+1;
set @end=@page*pagesize;
set @sql=concat("insert into ",@tableName," select * from t3 where id between ",@from," and ",@end);
PREPARE sta from @sql;
execute sta;
set @page=@page+1;
end while;
end;
call split_table(250000);
CREATE TABLE `taball` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`post` text,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MRG_MyISAM union=(tab_1,tab_2,tab_3,tab_4,tab_5,tab_6,tab_7,tab_8,
tab_9,tab_10,tab_11,tab_12,tab_13,tab_14,tab_15,tab_16,tab_17,tab_18,tab_19,tab_20);
posted on 2015-08-15 13:25 nevergiveupzeng 阅读(397) 评论(0) 编辑 收藏 举报