分表分库方法总结
案例一：
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)。
转于：http://hanyh.iteye.com/blog/431323
案例二：
<?php
include 'config.php';
class Model{
//用户名
protected $user;
//密码
protected $pwd;
//主机
protected $host;
//库名，是一个数组
protected $dbName=array();
//字符集
protected $charset='utf8';
//连接资源是一个数组
protected $_link=array();
//通用表名
protected $tabName;
//真实表名
protected $trueTabName;
//表前缀
protected $prefix;
//字段缓存
protected $fields;
//创建表的sql语句
protected $createSql='CREATE TABLE IF NOT EXISTS __TABLENAME__(
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`username` char(15) NOT NULL,
`password` char(32) NOT NULL,
`createtime` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;';
//1,通过ID取余，得到真实表名 mod
//2,用户名截取前几位 substr
//3,md5 md5
//4,不带分库分表 none
protected $partition=array(
'type'=>'md5',
'rule'=>1,
);
public function __construct($tabName=''){
$this->user=DB_USER;
$this->host=DB_HOST;
$this->dbName[0]=DB_NAME;
$this->charset=DB_CHARSET;
$this->prefix=DB_PREFIX;
$this->pwd=DB_PWD;
if(empty($tabName)){
//userModel
//newModel
$this->tabName=$this->prefix.ucfirst(strtolower(substr(get_class($this),0,-5)));
}else{
$this->tabName=$this->prefix.$tabName;
}
$this->_link[0]=$this->connect($this->host,$this->user,$this->pwd,$this->dbName,$this->charset);
}
public function connect($host,$user,$pwd,$dbName,$charset,$linkId=0){
$conn=mysql_connect($host,$user,$pwd);
if(mysql_errno()){
$this->error(-1,$conn);
return false;
}
if(!$this->selectDb($dbName[$linkId],$conn)){
$this->error(-2,$conn);
return false;
}
if(!$this->setCharset($charset,$conn)){
$this->error(-3,$conn);
return false;
}
return $conn;
}
public function selectDb($dbName,$conn){
if(mysql_select_db($dbName,$conn)){
return true;
}else{
return false;
}
}
public function setCharset($charset,$conn){
if(mysql_set_charset($charset,$conn)){
return true;
}else{
return false;
}
}
public function addServer($host,$user,$pwd,$dbName,$charset,$linkId){
$this->dbName[$linkId]=$dbName;
$this->_link[$linkId]=$this->connect($host,$user,$pwd,$dbName,$charset,$linkId);
}
public function getTrueTable($content,$linkId=0){
switch($this->partition['type']){
case 'mod':
if(!is_int($content)){
$this->error(-4);
return false;
}
$string=$content%$this->partition['rule'];
break;
case 'substr':
$string=substr($content,0,$this->partition['rule']);
break;
case 'md5':
$string=substr(md5($content),0,$this->partition['rule']);
break;
case 'none':
$string=null;
break;
}
if(empty($string)){
$this->trueTableName=$this->tabName;
}else{
$this->trueTableName=$this->tabName.'_'.$string;
}
//第一，判断表是否存在，存在返回表字段缓存
//第二，不存在，则创建表，返回字段缓存
$this->existsTable($this->trueTableName,$linkId);
}
//表是否存在
//是否缓存了字段
protected function existsTable($tableName,$linkId=0){
$database=$this->dbName[$linkId];
$sql='select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`=\''.$database.'\' and `TABLE_NAME`=\''.$tableName.'\'';
if($this->execute($sql,$linkId)){
//表存在
if(file_exists('cache/'.md5($this->tabName).'.php')){
$this->fields=include 'cache/'.md5($this->tabName).'.php';
}else{
//暂时留着不写，待会来写
$this->fields=$this->getFieldCache($linkId);
}
}else{
//表不存在
$this->createTable($this->trueTableName,$linkId);
$this->fields=$this->getFieldCache($linkId);
}
}
protected function getFieldCache($linkId=0){
if(file_exists('cache/'.md5($this->tabName).'.php')){
$fields=include 'cache/'.md5($this->tabName).'.php';
return $fields;
}
$sql="desc $this->trueTableName";
$f=$this->query($sql,$linkId);
$fields=$this->writeFields($f);
return $fields;
}
protected function writeFields($f){
foreach($f as $key=>$value){
$fields[]=$value['Field'];
if($value['Key']=='PRI'){
$fields['_pk']=$value['Field'];
}
if($value['Extra']=='auto_increment'){
$fields['_auto']=$value['Field'];
}
}
$string="<?php \n return ".var_export($fields,true)."\n?>";
file_put_contents('cache/'.md5($this->tabName).'.php',$string);
return $fields;
}
protected function createTable($tabName,$linkId=0){
$sql=str_replace('__TABLENAME__',$tabName,$this->createSql);
$this->execute($sql,$linkId);
}
//不需要返回结果集我用execute方法
public function execute($sql,$linkId=0){
$conn=$this->_link[$linkId];
$result=mysql_query($sql,$this->_link[$linkId]);
if($result&&mysql_affected_rows()){
return mysql_affected_rows();
}else{
return false;
}
}
//需要返回结果集我用query方法
public function query($sql,$linkId=0){
$result=mysql_query($sql,$this->_link[$linkId]);
if($result&&mysql_affected_rows()){
while($row=mysql_fetch_assoc($result)){
$rows[]=$row;
}
}else{
return false;
}
return $rows;
}
public function error($num,$conn){
switch($num){
case -1:
$string='连接数据库服务器失败'.mysql_error($conn);
break;
case -2:
$string='选择数据失败';
break;
case -3:
$string='设置字符集失败';
break;
case -4:
$string='数据库路由时选择的是取余，传入的不是整型';
break;
}
}
//查最大值
public function max($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select max($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//查最小值
public function min($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select min($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//求和
public function sum($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select sum($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//最平均数
public function avg($field,$linkId=0){
if(!in_array($field,$this->fields)){
return false;
}
$sql="select avg($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//求总数
public function count($field='',$linkId=0){
if(empty($field)){
$field=$this->fields['_pk'];
}
$sql="select count($field) as re from $this->trueTableName";
$result=$this->query($sql,$linkId);
$row=$result['re'];
return $row;
}
//
//删除
public function delete($data,$where='',$linkId=0,$order='',$limit=''){
//delete from 表 where 字段 order by 字段 limit
if(is_array($data)){
$value=join(',',$data);
}else{
$value=(int)$data;
}
$fields=$this->fields['_pk'];
if(empty($where)){
$sql="delete from $this->trueTableName where $fields in ($value)";
}else{
$where='where '.$where;
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}
$sql="delete from $this->trueTableName $where $order $limit";
}
return $this->execute($sql,$linkId);
}
//
//修改
public function save($data,$where,$linkId=0,$order='',$limit=''){
//update 表 set 字段=值,字段=值 where 条件 order limit
$key=array_keys($data);
$newKey=array_intersect($key,$this->fields);
foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$update.=$key.'="'.$value.'",';
}
$update=rtrim($update,',');
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}
if(!empty($where)){
$where='where '.$where;
}
$sql="update $this->trueTableName set $update $where $order $limit";
echo $sql;
$result=$this->execute($sql,$linkId);
return $result;
}
//增加
public function add($data,$linkId=0){
//insert into 表(字段) values(值)
$key=array_keys($data);
$newKey=array_intersect($key,$this->fields);
foreach($data as $key=>$value){
if(!in_array($key,$newKey))
continue;
$values.="'".$value."',";
}
$values=trim($values,',');
$fields=join(',',$newKey);
$sql="insert into $this->trueTableName($fields) values($values)";
echo $sql;
$result=$this->execute($sql,$linkId);
return $result;
}
//单条查询
public function find($linkId=0,$where='',$order=''){
//select * from 表 where order limit 1
$field=join(',',$this->fields);
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
$sql="select $field from $this->trueTableName $where $order limit 1";
$result=$this->query($sql,$linkId);
return $result[0];
}
//多条查询
public function select($field='',$linkId=0,$where='',$order='',$limit=''){
//select * from 表 where order limit
if(empty($field)){
$fields=join(',',$this->fields);
}else{
if(is_array($field)){
$newKey=array_intersect($field,$this->fields);
$fields=implode(',',$newKey);
}else{
$fields=$field;
}
}
if(!empty($where)){
$where='where '.$where;
}
if(!empty($order)){
$order='order by '.$order;
}
if(!empty($limit)){
$limit='limit '.$limit;
}
$sql="select $fields from $this->trueTableName $where $order $limit";
$result=$this->query($sql,$linkId);
return $result;
}
//按照字段来查询数据
function __call($name,$param){
$key=substr($name,0,5);
if(strtolower($key)=='getby'){
$field=strtolower(substr($name,5));
if(!in_array($field,$this->fields)){
return false;
}
$f=join(',',$this->fields);
$value=$param[0];
$sql="select $f from $this->trueTableName where $field='$value'";
$result=$this->query($sql);
return $result[0];
}
}
}
?>
案例三：1亿条数据在PHP中实现Mysql数据库分表100张
首先创建100张表：
$i=0;
while($i<=99){
echo "$newNumber \r\n";
$sql="CREATE TABLE `code_".$i."` (
`full_code` char(10) NOT NULL,
`create_time` int(10) unsigned NOT NULL,
PRIMARY KEY (`full_code`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8";
mysql_query($sql);
$i++;
full_code作为主键，我们对full_code做hash
$table_name=get_hash_table('code',$full_code);
function get_hash_table($table,$code,$s=100){
$hash = sprintf("%u", crc32($code));
echo $hash;
$hash1 = intval(fmod($hash, $s));
return $table."_".$hash1;
}
最后我们使用merge存储引擎来实现一张完整的code表
CREATE TABLE IF NOT EXISTS `code` ( `full_code` char(10) NOT NULL, `create_time` int(10) unsigned NOT NULL, INDEX(full_code) ) TYPE=MERGE UNION=(code_0,code_1,code_2.......) INSERT_METHOD=LAST ;
这样我们通过select * from code就可以得到所有的full_code数据了。
