MySQL之分库分表分区

MySQL之分库分表分区

一、分库

为了解决单台服务器的性能问题，当当单台数据库服务器无法支撑当前的数据量时，就需要根据业务逻辑紧密程度把表分成几撮，分别放在不同的数据库服务器中以降低单台服务器的负载。

分库策略也可以：垂直拆分和水平拆分


       垂直拆分，按照业务和功能划分，把数据分别放到不同的数据库
       水平拆分 把一张表的数据划分到不同的数据库，两个数据库的表结构一样。

比如一个论坛系统的数据库因当前服务器性能无法满足需要进行分库。 先垂直切分，按业务逻辑把用户相关数据表比如用户信息、积分、用户间私信等放入user数据库；论坛相关数据表比如板块，帖子，回复等放入forum数据库，两个数据库放在不同服务器上。


所以：分库的目的是降低单台服务器负载，切分原则是根据业务紧密程度拆分，缺点是跨数据库无法联表查询

二、分表

---

当数据表中的数据量超级大时。很多时候索引就力不从心。服务器需要根据索引扫描的结果返回表，再查询所有符合条件的记录。如果数据量巨大，这将产生大量随机I/O.逐渐数据库的响应时间将大到不可接受的程度

1.垂直分表

切分原则：把不常用或业务逻辑不紧密或存储内容比较多的字段分到新的表中可使表存储更多数据。即：把表中的字段进行拆分，即一张字段比较多的表拆分为多张表，这样使得行数据变小。


拆分建议：

1. 将不常用的字段单独拆分到另外一张扩展表。比如用户家庭地址
   将大文本的字段单独拆分到另外一张扩张表。例如blob和text字符串类型的字段，这样可以减少客户端程序和数据库之间的网络传输字节数。
   将不经常修改的字段放在同一张表中，将经常修改的字段放在另一张表中。例如最后登录时间.这个字段还会导致查询缓存被清空。建议放置另一个表中
   对于需要经常关联的字段，建议放在同一张表中。

2.水平分表

水平拆表，把表的行进行拆分。因为表的行数超过几百万行时，就会变慢，这时可以把一张表拆分成多张表来存放。 常用的策略：


1. 取模分表

$uid = 8;$member_table = 'member'.$uid%5;$sql = "select * from {$member_table}";

//查看所有会员信息$table = ['member0','member1','member2','member3','member4','member'];foreach($table as $tab){    $sql .= " select * from {$tab} union";}$sql = substr($sql,0,-5);

1. 时间维度分表

Like：log_2014-12-01_2014-12-15log_2014-12-16_2014-12-31log_2015-01-01_2015-01-16log_2015-01-17_2015-02-01log_2015-02-02_2015-02-17log_2015-02-18_2015-03-05log_2015-03-06_2015-03-21log_2015-03-22_2015-04-06

1. 自定义Hash分表

 -最初的userby表，扩展为100张相同表结构的userbuy_index表 -首先对userid进行hash计算，得到hash值，该hash值必然是0-99，目的是将对应的userid数据，写入到同一张分表中 -自定义hash算法function getStringHash($string, $tab_count)  {    $unsign = sprintf('%u', crc32($string));      if ($unsign > 2147483647)  // sprintf u for 64 & 32 bit      {          $unsign -= 4294967296;      }      return abs($unsign) % $tab_count;  }

实际上，垂直拆分后的表依然存在单表数据量过大的问题，需要进行水平拆分。

分区

所谓分区，就是把一个数据表的文件和索引分散存储在不同的物理文件中。（5.1+）

//确认是否支持分区SHOW VARIABLES LIKE '%partition%'

分区类型：Range、List、Hash、Key。range最常用


MySQL通过分区把数据保存到不同的数据文件里，同时索引也是分区的。相对未分区的表来说，分区后单独的数据文件和索引文件的大小都明显降低，效率则明显提升。