关于PHP记录影片播放记录数据量特别大 应该怎么分表 partition又如何分页查询

关于PHP记录影片播放记录数据量特别大 应该怎么分表

方案一: 水平分表

通过固定数量表来存播放记录

1.mysql单独建一张  **db_play**  表来获取自增ID

CREATE TABLE `tb_play` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `auto` char(1) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `auto` (`auto`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.分表设置了10个表，建表db_play_log_1，db_play_log_2，... db_play_log_10
  字段与 **db_play_log** 要一致，用来保存播放记录

CREATE TABLE `db_play_log_1` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,//用户id
  `mov_id` char(1) COLLATE utf8_bin NOT NULL DEFAULT '',//电影id
  `mov_title` varchar(155) NOT NULL COMMENT '电影名称',//电影名称
  //此处可增加记录 播放时长 等其他字段
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3.新建model层  代码如下

<?php
namespace app\index\model;
use think\Model;
class PlayLog extends Model
{
    protected $table = 'db_play_log';
    private function getRule(){
        return [
            'type' => 'mod', // 分表方式
            'num'  => 10     // 分表数量
        ];
    }
    //获取插入ID，根据ID来取模分表
    public function getLastId(){
        $data['auto']='a';
        return $this->insertGetId($data, true);
    }
 
    public function saveData($data, $id){
        return $this->partition(['id' => $id], "id", $this->getRule())->insert($data);
    }
    
    public function getPlayLogById($where, $field = "*", $id){
        return $this->partition(['id' => $id], "id", $this->getRule())->where($where)->field($field)->select();
    }
}

4.新建controller控制器  代码如下

<?php
namespace app\index\controller;
use think\Controller;
use app\index\model\PlayLog as PlayLogModel;

class PlayLog extends Controller
{
    //分表测试
    public function playLogAdd(){
    	$user_id = input('user_id');
    	$mov_id = input('mov_id');
    	
        $Article = new PlayLogModel();
        $id = $Article->getLastId();
        $data = [
            'id' => $id,
            'user_id' => $user_id ,
            'mov_id' => $mov_id ,
            'mov_title' => '电影名称',
            //其他参数...
        ];
        if($Article->saveData($data,$id)){
　　　　　　　　$this->success('播放记录 记录成功~');
        }
    }
    public function getPlaylog(){
        $id = input('id');
        if($id){
            $PlayLog = new PlayLogModel();
            $logs = $PlayLog->getPlayLogById(['id'=>$id],'*',$id);
            return json($logs);
        }
    }
}

ThinkPHP5内置了partition方法，可用于实现简单的分表。
新增，修改，删除，查询单条数据时，partition方法能事先明确的知道要操作的是哪一条记录。
但需要注意的是, 当一个大表，被拆分成若干个子表，分页查询就会出现问题 哪些数据属于第几页
所以**分页查询**需要增加下方逻辑 :

5.controller 增加方法  代码如下

    public function getList(){
        $PlayLog = new PlayLogModel();
        //1 先去获取相关数据的主键id 表名 主键id 要获取的字段 分表数量 条件
        $logs = $PlayLog->buildPartitionSql('db_play_log','id',$fields='',$num=3,$where='');
        //2 根据获取的主键id获取数据 表名 主键id id值 分表数量
        $logs = $PlayLog->buildPartitionListSql('db_play_log','id',$logs,$num=3);
        return $logs;//输出分页数据
    }

6.model增加方法  代码如下

/**
 * 获取总记录电影播放记录数量 及 主键ID的sql子查询语句
 * @param $table 主表名称
 * @param $idKey 主键id字段名称
 * @param string $fields 其它字段名称,多个字段用英文逗号分隔
 * @param int $num 子表数量
 * @param string $where 查询条件
 * @return array
 */
function buildPartitionSql($table,$idKey,$fields='',$num=10,$where='') {
    $offset=0;        //分页limit 第一个参数
    $rows=9;          //分页limit 第二个参数

    $countTable = [];
    $listTable = [];
    $fieldList = [$idKey];
    if ($fields) {
        $fieldList = array_merge($fieldList,explode(',',$fields));
        $fieldList = array_unique($fieldList);
    }
    $fieldStr = implode(',',$fieldList);
    for ($i = 0; $i < $num; $i++) {
        $countTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s', $idKey, $table, ($i + 1), $where);
        $listTable[] = sprintf('SELECT %s FROM %s_%s where 1=1 %s', $fieldStr,$table, ($i + 1), $where);
    }
    $countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;
    $listTable = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;
    $tables = ['countSql' => $countTable, 'listSql' => $listTable];

//这里返回的是两个sql语句,咱们简单点直接执行好,返回的就是数据的主键id
    return   Db::query("select * from". $tables['listSql']. " limit ".$offset.",".$rows);
}
/**
 * 构造获取指定id对应记录的sql子查询语句
 * @param $table 主表名称
 * @param $idKey 指定的id字段名称
 * @param $idValues 指定的id字段值
 * @param int $num 子表数量
 * @return string
 */
function buildPartitionListSql($table,$idKey,$idValues,$num=1) {
    $idValues=array_column($idValues,'id');
    $sql = '';
    $ids = is_array($idValues) ? implode(',',$idValues) : $idValues;

    if ($ids) {
        $listTable = [];
        for ($i = 0; $i < $num; $i++) {
            $listTable[] = sprintf('SELECT * FROM %s_%s where %s in (%s)', $table, ($i + 1), $idKey, $ids);
        }
        $sql = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;
    }
	//查询最终获取的数据
	$res = Db::query("select * from". $sql." order by id");
    return  $res;
}