分库和分表

1，背景：一个地址薄的应用程序，设计的用户量为2亿，统计出每个用户的地址薄为30个左右，整个数据量为60亿，使用mysql数据库
计划分为：1000个表，100个库

2，分库分表代码

    private function getDbNo($email)
    {
        $m = md5($email);
        $n = hexdec(substr($m, 0, 16));
        $tableNo = fmod($n, 1000);
        $dbNo = $tableNo % 100;
        return array($dbNo, $tableNo);
    }

3，配合的连接访问代码

require_once 'Db/Config.php';

class Db_Adapter
{
    const MASTER    = 0;
    const SLAVE     = 1;

    private static $instances = array();

    private $conf = array();

    private $conns = array();

    private $conn = NULL;
    private $stmt = NULL;
    
    public function __construct($conf)
    {
        $this->conf = $conf;
    }

    public function execute($sql, $params)
    {
        $cmd = substr(strtolower(trim($sql)), 0, 6);
        if ($cmd == 'select') {
            $conn = $this->getConn(self::SLAVE);
        } else {
            $conn = $this->getConn(self::MASTER);
        }

        $conn->prepare($sql);
        $stmt = $conn->execute($params);

        $this->conn = $conn;
        $this->stmt = $stmt;
    }

    public function fetch()
    {
        return $this->stmt->fetch();
    }

    public function fetchAll()
    {
        return $this->stmt->fetchAll();
    }

    public function lastInsertId($name = NULL)
    {
        return $this->conn->lastInsertId($name);
    }

    public function rowCount()
    {
        return $this->stmt->rowCount();
    }

    private function getConn($type)
    {
        if ($type == self::SLAVE && isset($this->conf[self::SLAVE])) {
            $id = 0;
        } else {
            $id = 1;
        }

        if (!isset($this->conns[$id])) {
            $conf = $this->conf[$id];
            $this->conns[$id] = new PDO(
                $conf['dsn'], $conf['user'], $conf['pass'],
                self::dbOptions);
        }

        return $this->conns[$id];
    }

    public static function getInstance($dbName, $dbNo = 0)
    {
        $key = $dbName . '_' . $dbNo;
        if (!isset(self::$instances[$key])) {
            $conf = Db_Config::getConfig($dbName, $dbNo); //连接配置参数
            self::$instances[$key] = new self($conf);
        }

        return self::$instances[$key];
    }
}

4，潜在问题
如果某个表中的那些用户的地址薄联系人超多，如每个人1000个，则可能出现该表超大，需要把该表区分为子表，暂时没有配置中心来处理该情况。
(若真的出现该情况，在连接参数这个地方继续作一次hash)。

评论

1 楼 hnraysir 2014-10-23  

必须登录评论下，谢谢。
by elesos.com