mysql隐形分表_MySQL分表

mysql中有一种机制是表锁定和行锁定，为什么要出现这种机制，是为了保证数据的完整 性，如果有二个sql都要修改同一张表的同一条数据，mysql 对这种情况的处理是，一种是表锁定(myisam存储引擎)，一个是行锁定(innodb存储引擎)。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等这条数据操作完了，才能对这条数据进行操作。如果数据太多，一次执行的时间太长，等待的时间就越长，这也是我们为什么要分表的原因。

分表分为垂直切分，将一个库拆成两个或多个，将有相关联的表放在一个库里。和水平切分将数据量大的表按照一定逻辑进行拆分。垂直切分的相对来说降低高并发情况下，对于表的锁定。而水平切分，目的是减轻了表读写的压力。

垂直分割

是指按功能模块拆分，比如可以将群组相关表和照片相关表存放在不同的数据库中，这种方式多个数据库之间的表结构不同。

如果一个表中某些列常用，而另外一些列不常用，也可以采用垂直分割。其缺点是需要管理冗余列，查询所有数据需要join操作。

比如物料有很多属性,财务部门有财务的属性要求,采购部门有采购的属性要求,按部门要求不同拆分为不同的表,仅将公共属性放在主表中,根据不同的部门要求建不同的表及查询视图,性能要好一些。

(1)每个库(表)的结构都不一样

(2)一般来说，每个库(表)的属性至少有一列交集，一般是主键

(3)所有库(表)的并集是全量数据

水平分割

当一张的数据达到几千万时，你查询一次所花的时间会变多。

而水平拆分是将同一个表的数据进行分块保存到不同的数据库中，这些数据库中的表结构完全相同。

一般2.0网站都以用户为中心，数据基本都跟随用户，比如用户的照片、朋友和评论等等。因此一个比较自然的选择是根据用户来切分。每个用户都按规则对应到数据库，访问某个用户的数据时， 我们要先确定他/她所对应的数据库，然后连接到该数据库进行实际的数据读写。

(1)每个库(表)的结构都一样

(2)每个库(表)的数据都不一样，没有交集

(3)所有库(表)的并集是全量数据

垂直分割的方法：

当一个表属性很多时，如何来进行垂直拆分呢？如果没有特殊情况，拆分依据主要有几点：

(1)将长度较短，访问频率较高的属性尽量放在一个表里，这个表暂且称为主表

(2)将字段较长，访问频率较低的属性尽量放在一个表里，这个表暂且称为扩展表

如果1和2都满足，还可以考虑第三点：

(3)经常一起访问的属性，也可以放在一个表里

优先考虑1和2，第3点不是必须。另，如果实在属性过多，主表和扩展表都可以有多个。

一般来说，数据量并发量比较大时，数据库的上层都会有一个服务层。需要注意的是，当应用方需要同时访问主表和扩展表中的属性时，服务层不要使用join来连表访问，而应该分两次进行查询。

为何要将字段短，访问频率高的属性放到一个表内可以提升性能？

因为数据库有自己的内存buffer，会将磁盘上的数据load到内存buffer里(暂且理解为进程内缓存吧)，内存buffer缓存数据是以row为单位的。在内存有限的情况下，在数据库内存buffer里缓存短row，就能缓存更多的数据，提升缓存命中率，减少磁盘的访问。

水平分割的几种方法：

根据字段来实现分表：

使用MD5哈希，做法是对UID进行md5加密，然后取前几位(我们这里取前两位)，然后就可以将不同的UID哈希到不同的用户表(user_xx)中了。

还可根据时间放入不同的表，比如：article_201601，article_201602。

按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。

根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

相对第一种方法，后几种方法更具扩展性。在程序里往数据库insert之前，多执行一句判断表存在与否并创建表的语句，很实用，消耗也并不很大。

按索引/映射表对应

这种方法是指建立一个索引表，保存每个用户的ID和数据库ID的对应关系，每次读写用户数据时先从这个表获取对应数据库。新用户注册后，在所有可用的数据库中随机挑选一个插入。这种方法比较灵活，有很好的伸缩性。一个缺点是增加了一次数据库访问，所以性能上没有按算法对应好。可配合memcached使用， 因为索引数据基本不会改变的缘故，缓存命中率非常高。所以能很大程度上减少了性能损失。

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

相对前面的方法的优点是它对程序是透明的，但此方法只适用于MyISAM。采用InnoDB引擎的表不适用。

mysql> CREATE TABLE t1 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (

-> a INT NOT NULL AUTO_INCREMENT,

-> message CHAR(20), INDEX(a))

-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

mysql> SELECT * FROM total;

+---+---------+

| a | message |

+---+---------+

| 1 | Testing |

| 2 | table |

| 3 | t1 |

| 1 | Testing |

| 2 | table |

| 3 | t2 |

+---+---------+

1)ENGINE=MERGE指明使用MERGE引擎，ENGINE=MRG_MyISAM的例子也是对的，它们是一回事。

2)UNION=(t1, t2)指明了MERGE表中挂接了些哪表，可以通过alter table的方式修改UNION的值，以实现增删MERGE表子表的功能。alter table t ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;可以从MERGE表中去除t2。

3)INSERT_METHOD=LASTINSERT_METHOD指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。

4)MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。

5)一个 merge 表不能在整个表上维持 unique 约束。若MREGE后存在重复主键，按主键顺序查询，只出现一条查询记录即停止。但如果继续向MERGE表中插入数据，若数据主键已存在则无法插入。换言之，MERGE表只对建表之后的操作负责。

6)分表之后主键的维护。子表的主键就靠uuid维护。如果MERGE的子表中之前有记录，且有自增主键，则MERGE表创建后，以各表中的AUTO_INCREMENT最大值做为下一次插入记录的主键值。比如t1的自增ID至6，t2至4，则创建MERGE表后，插入的下一条记录ID将会是7。

7)推荐的使用方法是先有一个MERGE表，里面只包含一张表，当一个这个表的的大小增长到一定程度(比如200w)时，创建另一张空表，将其挂入MERGE表，然后继续插入记录。

分表后的查询：

如果表较少，可以使用union方法

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

SELECT xxx FROM (

(SELECT xx FROM table1 WHERE type=1) union

(SELECT xx FROM table1 WHERE type=1) union

(SELECT xx FROM table1 WHERE type=1) union

(SELECT xx FROM table1 WHERE type=1)

) t WHERE t.xxx....

从应用层来解决，多个查询、拼合结果。

如果使用了MERGE分表，可以直接查询主表。

另外可以考虑使用全文检索工具来解决某些需求， 我们使用Solr来提供全站标签检索和照片搜索服务。