mysql原理和优化使用 > mysq分表分库
mysql 分库分表

单机单库 : 所有的表都放在一个数据库中,所有的数据库操作都请求这一个库.

优点: 部署维护简单,方便编程

缺点: 单台机器的内存,CPU,磁盘,网络,有限,数据库最大连接数, TPS有最大限制.

 

那么如何突破IO ,内存,CPU的瓶颈了,答案就是分表分库,那么问题有来了如果分表分库.

 

分库分表的方式方法:

垂直分库

场景:

(1) 数据库表数量比较多

(2) 业务比较多

(3) 单表数量比较少

其他表现如磁盘空间不足, 数据库进程连接数不够用 ,CPU很高

解决办法:

这种场景适合做数据库垂直拆分,按照业务逻辑拆分,然后将不同的库放到不同得机器上,如果压力还是大可以做一下数据库读写分离,多配置几个从库.

 

垂直分表:

场景:

(1) 单表数据量不大

(2) 但是单表字段过多

解决办法:

垂直分表将常用字段划分一个表,将大得字段或者不常用字段划分到另外一张表.这样做相当于将第一张表当作一个索引表, 第二张表就是扩展表. 这样做的优势是

1,在数据库的缓冲中缓冲更多的热数据,

2 根据b+树原理 ,相同的叶子节点可以存放更多的数据,在查询的时候相比之前能减少很多磁盘IO次数,从而加速查询.  

3 单表占用的磁盘空间变小

如果是因为单张表的数据量太大,这时要用水平切分,即把表的数据按某种规则切分成多张表,甚至多个库上的多张表。分库分表的顺序应该是先垂直分,后水平分。因为垂直分更简单,更符合我们处理现实世界问题的方式。

 举个简单的例子user表垂直拆分

原始表:

idnamepasswordtokenaddresssex

拆分成两个表: 

idnamepasswordtoken
user_idaddresssex


水平分库分表

场景:

(1) 单表数据库特别大

(2) 访问比较频繁

解决办法:

首先 针对数据量巨大的单张表(比如订单表),按照某种规则(RANGE,HASH取模等)切分到多张表,接着将这些表的分到多个服务器上去,每个服务器具有相应的库与表。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力,突破IO、连接数、硬件资源等的瓶颈。

 

水平分库分表切分规则

RANGE:       从0到10000一个表,10001到20000一个表;

HASH取模:   一个商场系统,一般都是将用户,订单作为主表,然后将和它们相关的作为附表,这样不会造成跨库事务之类的问题。 取用户id,然后hash取模,分配到不同的数据库上。

地理区域:   比如按照华东,华南,华北这样来区分业务,七牛云应该就是如此。

时间:       按照时间切分,就是将6个月前,甚至一年前的数据切出去放到另外的一张表,因为随着时间流逝,这些表的数据 被查询的概率变小,所以没必要和“热数据”放在一起,这个也是“冷热数据分离”。


那么分库分表多少合适呢?

经测试在单表1000万条记录以下,写入读取性能是比较好的. 这样在留点buffer,那么单表全是数据字型的保持在800万条记录以下, 有字符型的单表保持在500万以下。

如果按 100库100表来规划,如用户业务:

500万*128*128 = 50000000万 = 16384亿记录。

心里有一个数了,按业务做规划还是比较容易的。

id%(128*128) =  tmp ,

tmp/128 = db_index ,  确定这条数据放到那个库里面 db0 ,db1 .....

tmp%128 = table_index ,确定这条数据放到那个表里面 t1, t2 .....

刚开始的时候可以搞4台机器,  每一个库数据冗余一份,防止某个数据库挂掉的情况, 如果某个机器挂掉可以去他的下一个相邻机器去查找

机器1  [0-32)                    [32-64)

机器32  [32-64)         [64-96)

机器65  [64-96)               [96-128)

机器96  [96-128)                [0-32)

假如业务量增大 我们就可以在加4台机器 这时候只需要将 机器数量扩大2倍, 如此就可以实现水平扩展, 直到我们机器扩展到了 128台这时候这时候就需要考虑其他的扩展方式了.

机器1  [0-16)                       [32-48)

机器16  [16-32)                   [48-64)

机器32  [32-48)   [64-80)

机器48  [48-64)   [80-96)

机器64  [64-80)         [96-112)

机器80 [80-96)         [112-128)

机器96  [96-112)                 [0-16)

机器112  [112-128)             [16-32)