mysql大数据分表记录app用户的坐标数据

最近提到一个需求。需要记录app用户在使用app中的移动轨迹，即坐标值。每分钟上传一次XY坐标，有点类似跑步软件的描线轨迹。

不考虑app如何获取，反正api只要接受到坐标数据 就记录下来保存到数据库。接口接收3个参数X,Y,uid

1，建个新库。test 无论你是云DB还是同服务器下都可以

   'DB_CONFIG2'=>array(
        'db_type'=>'mysql',
        'db_user'=>'root',
        'db_pwd'=>'',
        'db_host'=>'localhost',
        'db_port'=>'3306',
        'db_name'=>'test',
    ),

　　

2，建一个保存自增主键ID的表

CREATE TABLE `create_id` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='自增ID表';

3，附上主要代码

class TestAction extends Action {
   
    function _initialize(){
        $this->db_2= M()->db(2,"DB_CONFIG2");
        $this->union_sql="CREATE TABLE IF NOT EXISTS `%s` (
  `id` int(11) NOT NULL,
  `longitude` decimal(10,6) NOT NULL COMMENT '经度',
  `latitude` decimal(10,6) NOT NULL COMMENT '纬度',
  `addtime` int(11) NOT NULL COMMENT '添加时间',
  `uid` int(11) NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`%s`);";
        $this->num_sql="CREATE TABLE IF NOT EXISTS `%s` (
  `id` int(11) NOT NULL,
  `longitude` decimal(10,6) NOT NULL COMMENT '经度',
  `latitude` decimal(10,6) NOT NULL COMMENT '纬度',
  `addtime` int(11) NOT NULL COMMENT '添加时间',
  `uid` int(11) NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
        
    }

    public function index() {
       $uid=228; 
       $id = 10000099;
       //$id = $this->create_id();//生成自增ID
       //$uid=trim($_GET['uid']);
       $union_table_name="point_".$uid;//总表表名
       //新增记录分配分表名
       $num_table_name= $this->get_data_table($uid,$id);
       //分表名拼接
       $union_table_string=$this->get_union_string($uid,$id);
       //分表语句 
       $num_sql=sprintf($this->num_sql,$num_table_name);
       $this->execute($num_sql);//新建分表
       //总表语句  
       $union_sql=sprintf($this->union_sql,$union_table_name,$union_table_string);    
       echo $union_sql;
       $re1= $this->execute($union_sql);//创建总表
       echo ($this->db_2->getDbError());
      
       //新增记录
       $re=$this->execute("INSERT INTO $num_table_name (`id`, `longitude`, `latitude`, `addtime`, `uid`) VALUES ('$id', '1111.000000', '2222.000000', '22222', '$uid');");
       
       if($re){
           echo json_encode(array("status"=>1,"info"=>true));
       }else{
           echo json_encode(array("status"=>0,"info"=>false));
       }
       
       
    }
     
   
    /**
     * 创建一个自增主键
     * @return int
     */
    private function create_id(){
      $sql = "insert into create_id (id) values('')";
      $this->db_2->execute($sql);
      return $this->db_2->getLastInsID();
    }
    
    /**
     * 获得表名
     * @return string
     */
    private function get_data_table($uid=null,$id=null){
        if(empty($uid)||empty($id)){
            return false;
        }
        return 'point_'.$uid.'_'.$this->get_table_num($id);
    }
    
    /**
     * 获得记录所在表序号
     * @param  $id  记录ID
     * @param  $max 表最大记录数
     * @return int
     */
    private function get_table_num($id,$max=10000000){
        $num = ($id<$max) ? 1 : intval($id/$max); //整除取整，默认1
        $add =  ($id%$max)>0 && ($id>$max)  ?1:0;//有余数，序号加1
        return $num+$add;
    }
    
    /**
     * 判断表是否存在
     */
    private function table_exit_create($table=null){
     return $this->db_2->query("show tables like '%{$table}%'"); 
//       return $this->db_2->query("desc {$table}"); 
    }
    
    
    /**
     * 建表
     * @return bool 
     */
    private function execute($sql){
        return $this->db_2->execute($sql); 
    }
    
    /**
     * 生成拼接分表名字符串
     * @param type $uid
     * @param type $id
     * @return string
     */
    private function get_union_string($uid=null,$id=null){
       $res = $this->table_exit_create("point_".$uid."_");
       if($res){
           if(count($res)>1){
               $arr=array();
               foreach($res as $v){
                   $arr[]=$v["Tables_in_test (%point_".$uid."_%)"];
               }
              $str= implode(',',$arr);
           }else{
            $str= "point_".$uid."_".$this->get_table_num($id);  
           }
        }else{
            $str= "point_".$uid."_".$this->get_table_num($id);
        }
        
         return $str;
      
       
    }
    

 

 4，分析

原理非常简单。

api接受到参数 -》主键表产生一个主键-》判断主键范围，分配分表名-》创建分表，并把这次接受参数插入分表（注意：所有分表的主键字段必须由主键表产生，确保唯一性）

-》创建总表（必须是ENGINE=MRG_MyISAM），把分表union关联起来，方便查询

分表必须MyISAM引擎，主键非自增

 

补充：1，注意完善参数验证 。UID真实性等

　　   2，每个分表我取1千万，更大的没有测试。

参考文章：百度来的http://soft.chinabyte.com/database/72/12620572.shtml