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

原文地址: http://blog.csdn.net/nuli888/article/details/52143065

当单表达到几千万时,查询一次要很久,如果有联合查询,有可能会死在那
分库分表主要就是解决这个问题,减小数据库的负担,缩短查询时间

分库:
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,$conor 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’//配置文件路径 
  10.     public function __construct($dbname$table$id = 0){
  11.         if (is_null(static::$config)) {
  12.             $config = include(static::$configFile);
  13.             static::$config = $config;
  14.         }
  15.         $config = static::$config;
  16.         if (isset($config[‘shared’]) && isset($config[‘shared’][$dbname])) {
  17.             $dbconfig = $config[‘shared’][$dbname];
  18.             $id = is_numeric($id) ? (int)$id : crc32($id);
  19.             $database_id = ($id / $dbconfig[‘database_split’][0]) % $dbconfig[‘database_split’][1];
  20.             $table_id = ($id / $dbconfig[‘table_split’][0]) % $dbconfig[‘table_split’][1];
  21.             foreach ($dbconfig[‘host’as $key => $conf) {
  22.                 list($from$to) = explode(‘-‘$key);
  23.                 if ($from <= $database_id && $database_id <= $to) {
  24.                     $the_config = $conf;
  25.                 }
  26.             }
  27.             $this->dbname = $dbname . ‘_’ . $database_id;
  28.             $this->table = $table . ‘_’ . $table_id;
  29.         } else {
  30.             $this->dbname = $dbname;
  31.             $this->table = $table;
  32.             $the_config = $config[‘db’][$dbname];
  33.         }
  34.         $c = $the_config;
  35.         if (isset($c[‘unix_socket’]) && $c[‘unix_socket’]) {
  36.             $this->dsn = sprintf(‘mysql:dbname=%s;unix_socket=%s’$this->dbname, $c[‘unix_socket’]);
  37.         } else {
  38.             $this->dsn = sprintf(‘mysql:dbname=%s;host=%s;port=%s’$this->dbname, $c[‘host’], $c[‘port’]);
  39.         }
  40.         $this->user = $c[‘user’];
  41.         $this->password = $c[‘password’];
  42.     }
  43. }

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

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. $userId=create_uuid();
  20. $user = new User($userId);
  21. $data=array(‘id’=>$userId,‘name’=>‘大明’.$userId,‘password’=>’14e1b600b1fd579f47433b88e8d85291′,‘sex’=>‘男’);
  22. if($result=$user->insert($data)){
  23.     echo ‘插入成功:’,‘<pre/>’;
  24.     print_r($result);
  25. }
  26. $condition=array(“id”=>$userId);
  27. $list=$user->select($condition);
  28. if($list){
  29.     echo ‘查询成功:’,‘<pre/>’;
  30.     print_r($list);
  31. }


6、结果

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

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注