1,创建一个完整表
create table member( id bigint auto_increment primary key, name varchar(20), sex tinyint not null default '0' )engine=myisam default charset=utf8 auto_increment=1;
2给数据表添加数据
加入点数据:
insert into member(id,name,sex) values (1,'jacson','0');
insert into member(name,sex) select name,sex from member;//语句多执行几次就有了很多数据。
3,创建分表
DROP table IF EXISTS tb_member1; create table tb_member1( id bigint primary key auto_increment ,
name varchar(20), sex tinyint not null default '0' )ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP table IF EXISTS tb_member2; create table tb_member2( id bigint primary key auto_increment , name varchar(20), sex tinyint not null default '0' )ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
//创建tb_member2也可以用下面的语句
create table tb_member2 like tb_member1;
4,创建主表,这里主表的定义与要分的目标表有不同
DROP table IF EXISTS tb_member;
create table tb_member(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;
主表说明:
以上是采用merge 分表
merge简介分表就是把N条记录的表,分成若干个分表,各个分表记录的总和仍为N。
insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
主表只是一个外壳,存取数据发生在一个一个的分表里面。
以上就实现了简单的分表操作,针对水平分表操作。
分表原理解析如下:
假设现在有一个应用系统可能会有100亿的用户量,另外一个表一般存储量在不超过100万的时候基本能保持良好性能,计算下来,我们需要1万张表,即分表为1万个表。
我们可以设计成:user_0~user_9999
在用户表里面我们有唯一的标示是用户id,我们尅设计一个小算法来实现用户id与访问表名的对应:
function getTable($id)
{
return 'user_'.sprintf('%d',($id >>20));
}
解释一下:($id >> 20)表示将向右移位20位,(向右移动一位标示减少一半),printf('%d',$data)标示将数据按照十进制输出。
即id为1~1048575(2的20次幂-1)时均访问user_0,1048576~2097152时访问user_1,以此类推.....
那么问题来了,如果用户更多怎么办,现在需要一个可扩展的方法:
而博客的浏览量,回复数等,类似的统计信息,或者别的变化频率比较高的数据,我们把它叫做活跃数据。
我们进行纵向分表后:
1,存储引擎的使用不同,冷数据使用MyIsam 可以有更好的查询数据。活跃数据,可以使用Innodb ,可以有更好的更新速度。
2,对冷数据进行更多的从库配置,因为更多的操作是查询,这样来加快查询速度。对热数据,可以相对有更多的主库的横向分表处理。
3,对于一些特殊的活跃数据,也可以考虑使用memcache ,redis之类的缓存,等累计到一定量再去更新数据库.