mysql分库分表实战及php代码操作完整实例

当单表达到几千万时，查询一次要很久，如果有联合查询，有可能会死在那

分库分表主要就是解决这个问题，减小数据库的负担，缩短查询时间

分库：

1）按功能分

用户类库、商品类库、订单类库、日志类、统计类库...

1）按地区分

每个城市或省市一个同样的库，加上后缀或前缀如：db_click_bj、db_click_sh...

分表：

1、横向分表 解决表记录太大问题

1）按某个字段分，

如：discuz的附件表分成10个附件分表pre_forum_attachment_0到pre_forum_attachment_9，还有1个附件索引表pre_forum_attachment存储tid和附件id关系

根据主题的tid最后一位来决定附件要保存在哪个分表，

2）按日期分表

一些日志、统计类的可以按年、月、日、周分表

如：点击量统计click_201601、click_201602

3）使用mysql的merge

先把分表创建好，然后创建总表指定engine= MERGE UNION=(table1,table2) INSERT_METHOD = LAST;

2、纵向分表 解决列过多问题

1）经常组合查询的列放在一个表，常用字段的表可考虑Memory引擎

2）不经常使用的字段单独成表

3）把text、blob等大字段拆分放在附表

如：phpcms的文章表分成主表v9_news和从表v9_news_data，主表存标题、关键字、浏览量等，从表存具体内容、模板等

很多主流mvc框架都提供了切换数据库访问方法

thinkphp切换数据库

$this->db(1,"mysql://root:123456@localhost:3306/test")->query("查询sql");//数据库连接信息可放在配置文件

$this->db(1)->query("查询sql");//下次直接使用编号1定位

分表的话 在查询前先根据分表规则把表名查出

这里用两台机子简单以同个业务库分库，同个表分表，演示插入、查询如何定位库和表并最终成功执行

两台机子：

server1：192.168.1.198

server2：192.168.1.199

两台机子都执行下面操作

1、先创建10个数据库，每个数据库10个表

当然也可以改成百库百表，也可手动创建，我为了方便写了个脚本批量创建

create.php

1.  <?php  
2.  ini_set('memory_limit', '-1');  
3.  $con=mysql_connect("192.168.1.198","open","123456");  
4.  if($con){     
5.  for($i=0;$i<10;$i++){//10个库  
6.  $sql="drop database cloude_{$i};";//删库 谨慎  
7.  mysql_query($sql);  
8.  $sql="create database cloude_{$i} default character set utf8 collate utf8_general_ci;";  
9.  $do=mysql_query($sql,$con)or die(mysql_error());  
10.  if($do){  
11.  mysql_select_db("cloude_{$i}",$con);  
12.  mysql_query("set name gtf8");  
13.  for($j=0;$j<10;$j++){        //10个表  
14.  $sql="drop table if exists user_{$j};";  
15.  mysql_query($sql);  
16.  $sql="create table user_{$j}  
17.  (  
18.  id char(36) not null primary key,  
19.  name char(15) not null default '',  
20.  password char(32) not null default '',  
21.  sex char(1) not null default '男'  
22.  )engine=InnoDB;";  
23.  $do=mysql_query($sql,$con) or die(mysql_error());  
24.  if($do){  
25.  //echo "create table user_{$j} successful! <br/>";  
26.  }else{  
27.  //echo "create error!";  
28.  }  
29.  }  
30.  }  
31.  }  
32.  }else{  
33.  echo "connect error!!!!";  
34.  }

2、分库分表路由实现

Config.php

1.  <?php  
2.  class Config{    
3.  public $dsn;    
4.  public $user;    
5.  public $password;    
6.  public $dbname; //分库分表后得到的数据库名  
7.  public $table; //分库分表后得到的表名  
8.  private static $config;//mysql配置数组  
9.  private static $configFile = 'mysql.php'; //配置文件路径   

11.  public function __construct($dbname, $table, $id = 0){    
12.  if (is_null(static::$config)) {    
13.  $config = include(static::$configFile);    
14.  static::$config = $config;    
15.  }    

17.  $config = static::$config;    
18.  if (isset($config['shared']) && isset($config['shared'][$dbname])) {    
19.     $dbconfig = $config['shared'][$dbname];    
20.    $id = is_numeric($id) ? (int)$id : crc32($id);    
21.     $database_id = ($id / $dbconfig['database_split'][0]) % $dbconfig['database_split'][1];    
22.    $table_id = ($id / $dbconfig['table_split'][0]) % $dbconfig['table_split'][1];    
24.   foreach ($dbconfig['host'] as $key => $conf) {    
25.      list($from, $to) = explode('-', $key);    
26.      if ($from <= $database_id && $database_id <= $to) {    
27.        $the_config = $conf;    
28.     }    
29.   }    
31.     $this->dbname = $dbname . '_' . $database_id;    
32.     $this->table = $table . '_' . $table_id;    
33.  } else {    
34.      $this->dbname = $dbname;    
35.      $this->table = $table;    
36.     $the_config = $config['db'][$dbname];    
37.  }    
38.  $c = $the_config;    
39.  if (isset($c['unix_socket']) && $c['unix_socket']) {    
40.      $this->dsn = sprintf('mysql:dbname=%s;unix_socket=%s', $this->dbname, $c['unix_socket']);    
41.  } else {    
42.     $this->dsn = sprintf('mysql:dbname=%s;host=%s;port=%s', $this->dbname, $c['host'], $c['port']);    
43.  }    
44.      $this->user = $c['user'];    
45.     $this->password = $c['password'];    
46.   }    
48.  }

3、数据库配置文件

mysql.php

1.  <?php  
2.  $default = array(    
3.  'unix_socket' => null,    
4.  'host' => '192.168.1.198',    
5.  'port' => '3306',    
6.  'user' => 'open',    
7.  'password' => '123456',    
8.  );    
9.  $db_199 = array(    
10.  'unix_socket' => null,    
11.  'host' => '192.168.1.199',    
12.  'port' => '3306',    
13.  'user' => 'open',    
14.  'password' => '123456',    
15.  );       
16.  $config = array(    
17.  // 不进行分库分表的数据库    
18.  'db' => array(    
19.  'hadoop' => $default,    
20.  ),    
21.  // 分库分表    
22.  'shared' => array(    
23.  'cloude' => array(    
24.  'host' => array(    
25.  /**  
26.  * 编号为 0 到 4 的库使用的链接配置  
27.  */   
28.  '0-4' => $default,    
29.  /**  
30.  * 编号为 5 到 9 的库使用的链接配置  
31.  */   
32.  '5-9' => $db_199,     

34.  ),    

36.  // 分库分表规则    
37.  /**  
38.  * 下面的配置对应10库10表 
39.  * 如果根据 uid 进行分表，假设 uid 为 224，对应的库表为：  
40.  *  (224 / 1) % 10 = 4 为编号为 4 的库  
41.  *  (224 / 10) % 10 = 1 为编号为 2 的表  
42.  */   
43.  'database_split' => array(1, 10),    
44.  'table_split' => array(10, 10),    
45.  ),    
46.  ),    
47.  );    
48.  return $config;

4、模型类操作数据库

Model.php

1.  <?php  
2.  require_once 'Config.php';//引入配置信息  
3.  class Model{    
4.  public $config;     //数据库配置  
5.  public $connection; //pdo  
6.  protected $dbnamePrefix; //库前缀如cloude_50 前缀为cloude   
7.  protected $tablePrefix;  //表前缀  
8.  protected $dbname;  //分库分表后对应的库  
9.  protected $table;   //分库分表后对应的库表  

11.  public function __construct($id){    
12.  $this->config = new Config($this->dbnamePrefix, $this->tablePrefix, $id);                  //根据id找到对应库和表  
13.  $this->connection = new Pdo($this->config->dsn, $this->config->user, $this->config->password);//实例化pdo    
14.  $this->connection->exec("set names utf8");     
15.  $this->dbname = $this->config->dbname;  
16.  $this->table = $this->config->table;    
17.  }    
18.
19.  public function update(array $data, array $where = array()){    
20.
21.  }    
22.
23.  public function select(array $condition){   
24.  $sqlwhere='';  
25.  if(!empty($condition)){     
26.  foreach ($condition as $field => $value) {    
27.  $where[] = '`'.$field.'`='."'".addslashes($value)."'";    
28.  }    
29.  $sqlwhere .= ' '.implode(' and ', $where);     
30.  }  
31.  $sql="select * from ".$this->dbname.'.'.$this->table;  
32.  if($sqlwhere){  
33.  $sql.=" where $sqlwhere";  
34.  }  
35.  $res=$this->connection->query($sql);  
36.  $data['data']=$res->fetchAll(PDO::FETCH_ASSOC);  
37.  $data['info']=array("dsn"=>$this->config->dsn,"dbname"=>$this->dbname,"table"=>$this->table,"sql"=>$sql);  
38.  return $data;     
39.  }    
40.  public function insert(array $arrData) {  
41.  $name = $values = '';  
42.  $flag = $flagV = 1;  
43.  $true = is_array( current($arrData) );//判断是否一次插入多条数据  
44.  if($true) {  
45.  //构建插入多条数据的sql语句  
46.  foreach($arrData as $arr) {  
47.  $values .= $flag ? '(' : ',(';  
48.  foreach($arr as $key => $value) {  
49.  if($flagV) {  
50.  if($flag) $name .= "$key";  
51.  $values .= "'$value'";  
52.  $flagV = 0;  
53.  } else {  
54.  if($flag) $name .= ",$key";  
55.  $values .= ",'$value'";  
56.  }  
57.  }  
58.  $values .= ') ';  
59.  $flag = 0;  
60.  $flagV = 1;  
61.  }  
62.  } else {  
63.  //构建插入单条数据的sql语句  
64.  foreach($arrData as $key => $value) {  
65.  if($flagV) {  
66.  $name = "$key";  
67.  $values = "('$value'";  
68.  $flagV = 0;  
69.  } else {  
70.  $name .= ",$key";  
71.  $values .= ",'$value'";  
72.  }  
73.  }  
74.  $values .= ") ";  
75.  }  
76.
77.  $sql = "insert into ".$this->dbname.'.'.$this->table." ($name) values $values";  
78.  if( ($rs = $this->connection->exec($sql) ) > 0 ) {  
79.  return array("dsn"=>$this->config->dsn,"dbname"=>$this->dbname,"table"=>$this->table,"sql"=>$sql);  
80.  }  
81.  return false;  
82.  }  
83.  public function query($sql){    
84.  return $this->connection->query($sql);    
85.  }    
86.  }

5、测试

使用主键id作为分表字段，那最好就不要使用自增了，可使用uuid

User.php

1.  <?php  
2.  require 'Config.php';    
3.  require 'Model.php';    
4.  class User extends Model    
5.  {    
6.  protected $dbnamePrefix = 'cloude';    
7.  protected $tablePrefix = 'user';    
8.  }   
9.  //生成唯一uuid  
10.  function create_uuid($prefix = ""){    //可以指定前缀  
11.  $str = md5(uniqid(mt_rand(), true));     
12.  $uuid  = substr($str,0,8) . '-';     
13.  $uuid .= substr($str,8,4) . '-';     
14.  $uuid .= substr($str,12,4) . '-';     
15.  $uuid .= substr($str,16,4) . '-';     
16.  $uuid .= substr($str,20,12);     
17.  return $prefix . $uuid;  
18.  }  
19.
20.  $userId=create_uuid();  
21.  $user = new User($userId);  
22.  $data=array('id'=>$userId,'name'=>'大明'.$userId,'password'=>'14e1b600b1fd579f47433b88e8d85291','sex'=>'男');    
23.  if($result=$user->insert($data)){  
24.  echo '插入成功：','<pre/>';  
25.  print_r($result);  
26.  }  
27.
28.  $condition=array("id"=>$userId);  
29.  $list=$user->select($condition);  
30.  if($list){  
31.  echo '查询成功：','<pre/>';  
32.  print_r($list);  
33.  }

6、结果

插入成功会返回插入到哪个库哪个表，查询成功返回从哪个库哪个表查的

807161129233.png

分库分表注意事项：

1、维度问题

假如用户购买了商品,需 要将交易记录保存取来，如果按照用户的纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的购买情况，但是某商品被购买的情 况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找到买人的交易记录比较麻烦。

所以常见的解决方式有：

通过扫表的方式解决，此方法基本不可能，效率太低了。

记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。

通过搜索引擎解决，但如果实时性要求很高，又得关系到实时搜索

2、避免分表join操作 因为关联的表有可能不在同一数据库中

3、避免跨库事务

避免在一个事务中修改db0中的表的时候同时修改db1中的表，一个是操作起来更复杂，效率也会有一定影响

4、分表宜多不宜少；这样做主要是为了尽量避免后期可能遇到的二次拆分

5、尽量把同一组数据放到同一DB服务器上

例如将卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 157,298评论 4赞 360
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 66,701评论 1赞 290
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 107,078评论 0赞 237
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,687评论 0赞 202
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,018评论 3赞 286
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,410评论 1赞 211
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,729评论 2赞 310
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,412评论 0赞 194
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,124评论 1赞 239
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,379评论 2赞 242
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,903评论 1赞 257
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,268评论 2赞 251
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,894评论 3赞 233
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,014评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,770评论 0赞 192
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,435评论 2赞 269
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,312评论 2赞 260

推荐阅读更多精彩内容

  • oracle错误集合
    ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 O...
    我想起个好名字阅读 4,865评论 0赞 9
  • Mysql分库分表方案
    Mysql分库分表方案 1.为什么要分表： 当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询...
    Java小铺阅读 954评论 1赞 22
  • mysql大数据解决方案--分表分库
    引言 对于一个大型的互联网应用，海量数据的存储和访问成为了系统设计的瓶颈问题，对于系统的稳定性和扩展性造成了极大的...
    dreambloom阅读 11,937评论 0赞 18
  • 20190429关于左值和右值
    一言以蔽之：可以取地址的谓之左值，反之我们称之为右值。 Q1:如何用int变量初始化double&类型的变量？ 用...
    Eternal_Po阅读 191评论 0赞 0
  • 2019-04-18
    第四期学员培训上午打卡在东原 中午吃饭时间，太阳太大打卡西西佛休息片刻 第四期学员培训下午打卡在东原 下课后打卡一...
    32774430182c阅读 136评论 0赞 0