Mysql分表以及实现

Mysql数据库的分表以及实现

1.现象

项目开发中，我们的数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询书读变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。当出现这种情况时，我们可以考虑分表，即将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。

2.例子

我们可以进行简单的设想：现在有一个表products存储产品信息，现在有100万用户在线访问，就要进行至少100万次请求，现在我们如果将它分成100个表即products0~~products99,那么利用一定的算法我们就分担了单个表的访问压力，每个表只有1万个请求（当然，这是理想情况下！） 实现mysql 分表的关键在于：设计良好的算法来确定"什么时候情况下访问什么（哪个）表"。

2.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;

加入点数据：

insert into member(id,name,sex) values (1,'jacson','0');

insert into member(name,sex) select name,sex from member;

第二条语句多执行几次就有了很多数据。

2.2 下面我们进行分表：这里我们分两个表tbmember1,tbmember2

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 ;     

2.3 创建主表tb_member

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 ;  

2.4接下来，我们把数据分到两个分表中

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;

3.创建主表可能会出现下列错误

ERROR 1168 (HY000): Unable to open underlying table which is differently definedor of non-MyISAM type or doesn't exist

若遇到上面这种错误，一般从两方面来排查：（从这两方面一般可以解决这个问题，本人也遇到了。）

1，查看上面的分表数据库引擎是不是MyISAM.

2，查看分表与指标的字段定义是否一致。

4.Mysql分表的三种方法

4.1做mysql集群，例如：利用mysql cluster ，mysql proxy，mysql replication，drdb等等

做集群的意义是什么呢？为一个数据库减轻负担，说白了就是减少sql排队队列中的sql的数量，举个例子：有10个sql请求，如果放在一个数据库服务器的排队队列中，他要等很长时间，如果把这10个sql请求，分配到5个数据库服务器的排队队列中，一个数据库服务器的队列中只有2个，这样等待时间是不是大大的缩短了呢？
优点：扩展性好，没有多个分表后的复杂操作（php代码）
缺点：单个表的数据量还是没有变，一次操作所花的时间还是那么多，硬件开销大。

4.2预先估计会出现大数据量并且访问频繁的表，将其分为若干个表

这种预估大差不差的，论坛里面发表帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。 聊天室里面信息表，几十个人在一起一聊一个晚上，时间长了，这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表，我们就事先分出个N个表，这个N是多少，根据实际情况而定。
优点：扩展性好，没有多个分表后的复杂操作（php代码）
缺点：单个表的数据量还是没有变，一次操作所花的时间还是那么多，硬件开销大。

4.3利用merge存储引擎来实现分表

这种方法比较适合，那些没有事先考虑，而已经出现了得，数据查询慢的情况。这个时候如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，现在一张表要分成几十张表，甚至上百张表，这样sql语句是不是要重写呢。
注：
1.如果你使用 alter table 来把 merge 表变为其它表类型，到底层表的映射就被丢失了。取而代之的，来自底层 myisam 表的行被复制到已更换的表中，该表随后被指定新类型。
2.一个 merge 表不能在整个表上维持 unique 约束。当你执行一个 insert，数据进入第一个或者最后一个 myisam 表（取决于 insert_method 选项的值）。mysql 确保唯一键值在那个 myisam 表里保持唯一，但不是跨集合里所有的表。
3.,当你创建一个 merge 表之时，没有检查去确保底层表的存在以及有相同的机构。当 merge 表被使用之时，mysql 检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的 myisam 表创建一个 merge 表，你非常有可能撞见奇怪的问题。

注：通过看其他的博客整理的不完全是自己原创