mysql 分库分表的方法

为什么要分库分表和读写分离
类似淘宝网这样的网站，海量数据的存储和访问成为了系统设计的瓶颈问题，日益增长的业务数据，无疑对数据库造成了相当大的负载，同时对于系统的稳定性和扩展性提出很高的要求。随着时间和业务的发展，数据库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作的开销也会越来越大；另外，无论怎样升级硬件资源，单台服务器的资源（CPU、磁盘、内存、网络IO、事务数、连接数）总是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。分表、分库和读写分离可以有效地减小单台数据库的压力

1、什么是读写分离
读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
2、为什么要读写分离呢？
因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。
但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）。
所以读写分离，解决的是，数据库的写入，影响了查询的效率。
3、什么时候要读写分离？
数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库 主从同步 。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是 表折分，或是搜索引擎。都是解决方法。

数据库垂直拆分与水平拆分

当我们使用读写分离、缓存后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。
数据库拆分简单来说，就是指通过某种特定的条件，按照某个维度，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面以达到分散单库（主机）负载的效果。
切分模式： 垂直（纵向）拆分、水平拆分。

数据库的垂直拆分
专库专用:一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

表的垂直拆分
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 通常会按照一下原则拆分：
1. 把不常用的字段单独放在附表中;
2. 把text，blob等字段拆分出来放在附表中;
3. 经常组合查询的列放在⼀张表中;

优点：
1. 拆分后业务清晰，拆分规则明确。
2. 系统之间整合或扩展容易。
3. 数据维护简单。
4.解决了表与表之间的io竞争。
缺点：
1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
3. 事务处理复杂。

 

水平拆分

垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中 的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式，如图

 

优点：
1. 不存在单库大数据，高并发的性能瓶颈。
2. 对应用透明，应用端改造较少。
3. 按照合理拆分规则拆分，join操作基本避免跨库。
4. 提高了系统的稳定性跟负载能力。
缺点：
1. 拆分规则难以抽象。
2. 分片事务一致性难以解决。
3. 数据多次扩展难度跟维护量极大。
4. 跨库join性能较差。

 

分割策略

取模

此种分割策略比较适合用在数据均分灵活且数据分散的需求

 

范围

id range代表当前记录ID的大小范围，比如：0~9999。

/**
 * 根据UID分表算法
 *
 * @param int $uid //用户ID
 * @param int $bit //表后缀保留几位
 * @param int $seed //向右移动位数
 */
function getTable($uid, $bit = 4, $seed = 20) {
    return "user_" . sprintf("%0{$bit}d", ($uid >> $seed));
}

我们将uid向右移动20位，这样我们就可以把大约前100万的用户数据放在第一个表user_0000,第二个100万的用户数据放在第二个表user_0001中，这样一直下去，如果我们的用户越来越多，直接添加用户表就行了。由于我们保留的表后缀是四位，这里我们可以添加1万张用户表，即user_0000,user_0001 ...... user_9999。一万张表，每张表100万数据，我们可以存100亿条用户记录。当然，如果你的用户数据比这还多，也不要紧，你只要改变保留表后缀来增加可以扩展的表就行了，如如果有1000亿条数据，每个表存100万，那么你需要10万张表，我们只要保留表后缀为6位即可

 

按时间分表

这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。

insertData($data){
    $table = "table_".date("Ym",time()); //生成当天表名
    insert($data,$table);  // 插入新的表中
}

优点：可部分迁移
缺点：数据分布不均，可能2015年01月的订单有100W，2015年02月的有500W。

 

哈希hash取模

<?php
function get_hash_table($table, $userid)
{
    $str = crc32($userid);
    if ($str < 0) {
        $hash = "0" . substr(abs($str), 0, 1);
    } else {
        $hash = substr($str, 0, 2);
    }
    return $table . "_" . $hash;
}
//echo get_hash_table('message', 'user18991'); //结果为message_10
//echo get_hash_table('message', 'user34523'); //结果为message_13
function calc_hash_db($u, $s = 4) {
    $h = sprintf("%u", crc32($u));
    $h1 = intval(fmod($h, $s));
    return $h1;
}

for ($i = 1; $i < 40; $i++) {
    echo calc_hash_tbl($i);
    echo "<br>";
    echo calc_hash_db($i);
    echo "<br>";
}

function calc_hash_tbl($u, $n = 256, $m = 16) {
    $h = sprintf("%u", crc32($u));
    $h1 = intval($h / $n);
    $h2 = $h1 % $n;
    $h3 = base_convert($h2, 10, $m);
    $h4 = sprintf("%02s", $h3);

    return $h4;
}
#################
function getTable( $uid ){
	$ext = substr ( md5($uid) ,0 ,2 );
	return "user_".$ext;
}
###################
private function getDbNo($email)
{
    $m = md5($email);
    $n = hexdec(substr($m, 0, 16));
    $tableNo = fmod($n, 1000);
    $dbNo = $tableNo % 100;
    return array($dbNo, $tableNo);
}

优点：数据分布均匀
缺点：数据迁移的时候麻烦;不能按照机器性能分摊数据 。

查询切分

将id和库的mapping关系记录在一个单独的库中

 

数据迁移的方式

当一些很久之前的数据，很少再查询。比如员工工资表，我们可以只存今年的工资情况。而历史数据我们可以迁移到一张salary_old表中,保证数据不会丢失。但也可以用来查询。每天定期把今年中的最早一天的记录归入旧表中。这样一方面可以解决性能问题，最多也只需要读2张表就完成了。

按热度拆分

典型的像贴吧这种有高点击率的词条，也有低点击率的词条，如果一个词条一张表，那得多少表啊，所以一般这种情况就会对高点击率的词条生成 一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，比如1W条，再把低热度的表单独拆分成一张表。

 

全局主键避重问题
在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：
1、利用数据库自增ID:auto_increment_increment auto_increment_offset
优点：最简单
缺点：单点风险、单机性能瓶颈
2、利用数据库集群并设置相应的步长(Flickr方案)
优点：高可用、ID较简洁
缺点：需要单独的数据库集群
3、Twitter snowflake
优点：高性能高可用、易拓展
缺点：需要独立的集群以及ZK
4、一大波GUID、Random算法
优点：简单
缺点：生成ID较长，有重复几率

 

分库分表产生的问题，及注意事项
1. 分库分表维度的问题
假如用户购买了商品,需要将交易记录保存取来，如果按照用户的纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的购买情况，但是某商品被购买的情况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找到买人的交易记录比较麻烦。
所以常见的解决方式有：
     a.通过扫表的方式解决，此方法基本不可能，效率太低了。
     b.记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。
     c.通过搜索引擎解决，但如果实时性要求很高，又得关系到实时搜索。
2. 联合查询的问题
联合查询基本不可能，因为关联的表有可能不在同一数据库中。
3.   避免跨库事务
避免在一个事务中修改db0中的表的时候同时修改db1中的表，一个是操作起来更复杂，效率也会有一定影响。
4.   尽量把同一组数据放到同一DB服务器上
例如将卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据。