数据库分表操作实例

来源：互联网发布：华为校招知乎编辑：程序博客网时间：2024/06/16 17:01

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。

其中ENGINE = MERGE表示，使用merge引擎。另外ENGINE = MRG_MyISAM是一样的意思。

UNION = (tb_member1,tb_member2)表示，挂接了表tb_member1，tb_member2 。

INSERT_METHOD = LAST表示，插入方式。（0不允许插入，FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表）。

merge数据存储结构mysql中MyISAM引擎下每一张表都对应三个文件: .MYD数据文件，.MYI索引文件，.frm表结构文件.

但是Merge引擎下每一张表只有一个.MRG文件.MRG里面存放着分表的关系，以及插入数据的方式。

它就像是一个外壳，或者是连接池,

数据存放在分表里面.

CRUD对于增删改查，直接操作总表t就可以了。

5,接下来，我们把数据分到两个分表中去：

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;

以上tb_member1和member2表都写入数据，此时，tb_memeber主表也写入数据，数据和member2表数据一样，因为 insert_method=last。

主表只是一个外壳，存取数据发生在一个一个的分表里面。

以上就实现了简单的分表操作，针对水平分表操作。

注意事项：

a.重复记录 / 重复索引若建立merge表前，分表t1 / t2已经存在，并且t1 / t2中存在重复记录。查询时，遇到满足记录的条目就会返回。意思就是只会显示一条记录，同时不会报错.

若建立merge表后，insert / update时，出现重复索引，则会提示错误。MERGE表只对建表之后的操作负责

b.如何删除一个分表不能直接删除一个分表，这样会破坏merge表。

正确的方法是: alter table t ENGINE = MRG_MyISAM UNION = (t1) INSERT_METHOD = LAST;

drop table t1

c.误删merge总表误删merge表，是不会造成数据丢失的，只需重新创建总表

分表原理解析如下：

假设现在有一个应用系统可能会有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,以此类推.....

那么问题来了，如果用户更多怎么办，现在需要一个可扩展的方法：

function getTable($id,$bit,$seed){

return 'user_'.sprintf('%0{$bit}d',($id >> $seed));

}

其中：$id为用户id,$bit标示表后缀的位数,$seed表示要移位的位数即：单个表能存储的记录条数。

这样就可以任意分表了。

垂直分表：

举例说明，在一个博客系统中，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。
　　　　而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。
　　我们进行纵向分表后：
　　　　　　1,存储引擎的使用不同，冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用Innodb ,可以有更好的更新速度。
　　　　　　2,对冷数据进行更多的从库配置，因为更多的操作是查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。
　　　　　　3,对于一些特殊的活跃数据，也可以考虑使用memcache ,redis之类的缓存，等累计到一定量再去更新数据库.

0 0