ThinkPHP5 按年度水平分表实例

moTzxx

已于 2022-09-07 10:53:26 修改

阅读量2.2k

点赞数 5

分类专栏： ThinkPHP 数据库文章标签：数据库

于 2022-09-06 17:02:02 首次发布

本文链接：https://blog.csdn.net/u011415782/article/details/126422213

版权

ThinkPHP 同时被 2 个专栏收录

44 篇文章 11 订阅

订阅专栏

数据库

24 篇文章 1 订阅

订阅专栏

背景

近期网站运营人员、产品反馈：查询数据耗时太长，网站交互上出现明显加载缓慢的情况
分析问题发现：

随着流量的增长，部分数据表已达到 800万、1000万
尤其因为数据表之间会有众多的联表操作
如此一来，造成查询效率变低，影响使用

CPU瓶颈：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表

方案

针对前面的问题的出现，
因此，需要对一些数据表做水平拆分处理
常用的方案，按照一定策略（hash、range 等）,根据我们的业务需求，选用了按年度分表

水平分表结果：

每个表的结构都一样；
每个表的数据都不一样，没有交集；
所有表的并集是全量数据；

即：根据要添加记录的创建时间，写入对应年度的数据表中，比如时间戳 1662372169 对应的订单记录，会在表 task_order_2022中

框架

ThinkPHP5、mysql 5.7

`☞ 实现步骤`

在此，鄙人根据自己的操作经验，以常见的订单表为对象，整理一番，欢迎指摘

梳理一下实现思路：

1. 确认好分表策略，是按ID取模 还是按年度 或者按记录数 ...
2. 设计 全局 id生成器 (用以控制 订单ID的生成，以及反向确定数据所在具体表)
3. 历史数据转储 （根据分表规则，提取历史数据到具体的表）
4. 如果插入新记录，先确认生成了订单ID,再根据当前时间戳，对应写入数据表中
5. 如果已知了某条订单记录ID,可以根据当时的时间戳，找到对应的表，然后再获取具体订单信息
6. 如果修改某条指定的订单记录，先根据ID或时间戳，找到对应的表，再执行update语句
7. ......

`①. 设计全局 id 生成器`

由于我们一般用主键作为分片键，在不同表中，如果用主键 id 自增的方式，会导致主键重复的问题。所以需要引入全局 id 生成器

建表语句如下：

DROP table IF EXISTS _task_order_id;
CREATE TABLE `_task_order_id` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` int(10) NOT NULL DEFAULT '0' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_select` (`create_time`) COMMENT '索引'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='任务订单id表';

[备注]

此表用于记录订单表的 ID,以及创建时间；
在插入订单记录前，订单 ID 都由该表产生，同时也会根据（订单id+创建时间）反向找到真实存储的订单表,如 task_order_2021

一般都是在项目运行一段时间后，
拥有了大量的历史数据后，才会考虑进行水平分表的处理
所以，先将历史数据的主键和创建时间提取出来，插入到新表

insert into _task_order_id(id,create_time) select id,createtime from mz_task_order;

`②. 数据分表存储`

首先对历史数据做一下处理
可以根据订单记录的创建时间，先执行筛选语句，将对应的记录转储到新建的对应年度订单表

比如提取 2021年度的订单数据，存到 _task_order_2021 中
执行 sql 语句如下：

  create table _task_order_2021 select * from task_order where createtime BETWEEN 1609430400 and 1640966400;

参考文章：【mysql一个表数据转移到另外一个表的2种方法4种情况】

[提示]：

另一种情况，
也是我遇到的场景：为了避免历史数据的转储时，造成丢失，和减少对原始数据完整性的验证
可以考虑选取一个时间节点，
只对时间节点后面的数据做分表处理，原始数据依然保留在原表
如此一来，最大的好处：减少了对原始数据的检测（当然，可能也不存在问题，毕竟为了减少数据变动）
缺点：在查询数据时，都要考虑下时间节点前后的规则，避免查询异常，其次可能原表数据有点多，历史数据查询时效率稍慢些！

`☞ 核心代码实现，仅做参考`

根据我的具体场景，提取几个核心处理方法，可供参考；如果使用，注意对方法的提取和数据表名称、字段信息的替换

`♢ 根据起始时间戳，返回操作的数据表数组`

简单描述：根据起止时间戳，确认一下能操作的数据表数组（可能有多个）
[注意]：得到数据表后，一般要检验一下是否存在，所以参考后面补充的方法 checkIsExistTable()，即时进行建表操作

    /**
     * 根据起始时间戳，返回操作的 数据表数组
     * @param int $start_time
     * @param int $end_time
     * @return array
     */
    public function getNeedOpTabYearArr($start_time = 0, $end_time = 0){
        $arr_year_tab = [];
        $start_year = date('Y',$start_time);
        $end_year = date('Y',$end_time);
        $curr_year = date('Y',time());
		
		//分表确认的开始时间节点 $this->tab_separate_start_time
        $db_separate_start_time = $this->tab_separate_start_time;
        //必须满足 起始时间 小于 结束时间
        if ($start_time < $end_time){
            if (($start_year <= $curr_year)&&($end_year <= $curr_year)){
                //起始时间 符合规范
                if ($start_time < $db_separate_start_time){
                    $arr_year_tab[] = '_task_order';
                    if ($end_time >= $db_separate_start_time){
                        $start_year = date('Y',$db_separate_start_time)-1;
                        while ($start_year < $end_year){
                            $start_year = $start_year+1;
                            $arr_year_tab[] = '_task_order_'.$start_year;
                        }
                    }
                }else{
                    //起始表都 为年度分表
                    $arr_year_tab[] = '_task_order_'.$start_year;
                    while ($start_year < $end_year){
                        $start_year = $start_year+1;
                        $arr_year_tab[] = '_task_order_'.$start_year;
                    }
                }
                $arr_year_tab = array_unique($arr_year_tab);
            }
            if ($arr_year_tab){rsort($arr_year_tab);}
        }
        //此处需要检查 数据表是否存在
        foreach ($arr_year_tab as $tab_value){
            $this->checkIsExistTable($tab_value);
        }
        return $arr_year_tab;
    }


	/**
     * 检查数据表 是否存在，没有则创建新表
     * @param string $tab_name
     * @return bool
     */
    public function checkIsExistTable($tab_name = ''){
        $exist_flag = false;
        if (!empty($tab_name)){
            $prefix = config('database.prefix')??'';
            $isTable = Db::query("SHOW TABLES LIKE '{$prefix}{$tab_name}'");

            if(!$isTable ){
                //如果不存在，创建新表，SQL 语句自行补充完整
                $sql = "CREATE TABLE `{$prefix}{$tab_name}` () ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';";
                //执行建表语句
                try {
                    Db::execute($sql);
                    $exist_flag = true;
                }catch (\Exception $e){
                    $exist_flag = false;
                }
            }else{
                $exist_flag = true;
            }
        }
        return $exist_flag;
    }

`♢ 插入新记录前，生成唯一ID`

注意：此方法中会调用 getOrderTableName()，同时获得对应的数据表名

    /**
     * 当我们需要插入数据的时候，由该方法产生唯一的id值
     * @param int $curr_time 当前时间戳
     * @return array
     */
    public function getNewOrderRecordID($curr_time = 0){
        $curr_time = $curr_time?$curr_time:time();
        $tab_name = '';

        $insertData = [
            'create_time'=>$curr_time
        ];
        $new_id = Db::name('_task_order_id')->insertGetId($insertData);
        if ($new_id){
            //匹配对应的数据表名
            $tab_name = $this->getOrderTableName($new_id);
        }
        return [$new_id,$curr_time,$tab_name];
    }

`♢ 根据ID或时间戳，获取表名`

只要确认了要操作的数据表名，后面就可以进行具体的查询、更新等操作

	/**
     * 用于根据id获取表名
     * @param int $id ID值，如果 $time_stamp 传参，则以后者为准
     * @param int $time_stamp 时间戳/时间串，用以判断 操作哪张数据表
     * @return string
     */
    public function getOrderTableName($id = 0,$time_stamp = 0) {
        if ($time_stamp){
            if (is_numeric($time_stamp)){
                $create_time = $time_stamp;
            }else{
                $create_time = strtotime($time_stamp);
            }
        }else{
            if ($id){
                $create_time = Db::name('_task_order_id')->where('id',$id)->value('create_time');
            }else{
                //如果 参数都为空，时间默认为当前年份，避免查询出错
                $create_time = strtotime(date('Y'));
            }
        }

        if ($create_time > $this->tab_separate_start_time){
            //匹配对应的数据表名
            $year = date('Y',$create_time);
            $curr_year = date('Y');
            if ($year > $curr_year){
                $tab_name = '_task_order_'.$curr_year;;
            }else{
                if ($year >= 2022){
                    $tab_name = '_task_order_'.$year;
                    $exist_flag = $this->checkIsExistTable($tab_name);
                    if (!$exist_flag){
                        $tab_name = '_task_order';
                    }
                }else{
                    //不符合建表要求
                    $tab_name = '_task_order';
                    //die('所选时间，无法对应数据表，请联系管理员...');
                }
            }
        }else{
            $tab_name = '_task_order';
        }
        return $tab_name;
    }

`♢ 根据订单ID，获取到单一一条订单数据`

    /**
     * 获取指定ID的 订单数据
     * TODO 待优化
     * @param int $order_id
     * @param string $field_str
     * @return array|\PDOStatement|string|Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getSingleOrderInfoByID($order_id = 0,$field_str = '*'){
        //首先获取其 记录时间戳
        $resInfo = [];
        $id_timestamp = Db::name('_task_order_id')->where('id',$order_id)->value('create_time');
        if ($id_timestamp){
            //判断 订单记录存放的表名
            if ($id_timestamp > $this->tab_separate_start_time){
                //此为年度表
                $rule = [
                    'type' => 'year', // 分表方式
                    'expr'=> 1,
                ];

                $resInfo = Db::name('_task_order')
                    ->partition(['create_time'=>$id_timestamp], "create_time", $rule)
                    ->alias('o')
                    ->where([['id','=',$order_id]])
                    ->field($field_str)
                    ->find();
            }else{
                //此为原始表
                $resInfo = Db::name('_task_order')->alias('o')->where('id',$order_id)->field($field_str)->find();
            }
        }else{
            //此时没有对应的记录
        }
        return $resInfo;
    }

`♢ 获取union联表所得表名`

用于分页数据读取，先得到组合的表名

    /**
     * 分页操作时，需处理union所得的 表名（有前缀的！）
     * @param int $create_time_start
     * @param int $create_time_end
     * @param string $field
     * @param string $str_where
     * @return array|\PDOStatement|string|\think\Collection|\think\model\Collection
     * @throws \think\exception\DbException
     */
    public function getUnionOrderTableNameForOpPage($create_time_start = 0,$create_time_end = 0,
                                               $field = '',$str_where = ''){
        //确保时间范围合理性
        if ($create_time_end < $create_time_start){
            $create_time_end = $create_time_start+10;
        }

        $_tab_name_start = $this->getOrderTableName(0,$create_time_start);
        $_tab_name_end = $this->getOrderTableName(0,$create_time_end);

        if ($_tab_name_start == $_tab_name_end){
            $prefix = config('database.prefix')??'';
            $_tab_name = $prefix.$_tab_name_start;
        }else{
            if ($create_time_start > $this->tab_separate_start_time_end){
                $a = getBuildSqlMz($_tab_name_start,$field,$str_where);
                $_tab_name = getBuildSqlMz($_tab_name_end,$field,$str_where,[$a]);
            }else{
                if ($create_time_start > strtotime('2023-01-01')){
                    $a = getBuildSqlMz('_task_order_2023',$field,$str_where);
                    $b = getBuildSqlMz('_task_order_2022',$field,$str_where);
                    $_tab_name= getBuildSqlMz('_task_order',$field,$str_where,[$a,$b]);
                }else{
                    $a = getBuildSqlMz('_task_order_2022',$field,$str_where);
                    $_tab_name= getBuildSqlMz('_task_order',$field,$str_where,[$a]);
                }
            }
        }
        return $_tab_name;
    }

	/**
	 * 构建 sql语句 (表名也可以是一个子查询)
	 * @param string $tab_name
	 * @param string $field
	 * @param string $str_where
	 * @param array $arr_union
	 * @return array|\PDOStatement|string|\think\Collection|\think\model\Collection
	 * @throws DbException
	 */
	function getBuildSqlMz($tab_name = '',$field = '',$str_where = '',$arr_union = []){
	    if ($arr_union){
	        $res_sql = Db::name($tab_name)
	            ->field($field)
	            ->where($str_where)
	            ->unionAll($arr_union)
	            ->buildSql();
	    }else{
	        $res_sql = Db::name($tab_name)
	            ->field($field)
	            ->where($str_where)
	            ->buildSql();
	    }
	    return $res_sql;
	}

`♢ 获取订单数量`

根据搜索条件，获取订单数量，注意要遍历需要统计的数据表，最后叠加得到结果

/**
     * 获取订单数量
     * @param array $where_mz
     * @param string $op_name
     * @param string $group_tag
     * @param int $start_time
     * @param int $end_time
     * @return int
     */
    public function getOrderCountByWhere($where_mz = [],$op_name = '',$group_tag = '',
                                         $start_time = 0, $end_time = 0){
        $start_time = $start_time?$start_time:($this->tab_separate_start_time - 3600);
        $end_time = $end_time?$end_time:time();
        $arr_year_tab = $this->getNeedOpTabYearArr($start_time,$end_time);
        $sum_count = 0;
        switch ($op_name){
            case 'group_count':
                //按组统计所有 数据
                $res_arr_ = [];
                foreach ($arr_year_tab as $key => $_tab_name){
                    $db_arr_ = Db::name($_tab_name)->alias('o')->where($where_mz)
                        ->group($group_tag)->column($group_tag);
                    //将符合条件的所有用户ID,统计到数组中，去重
                    if ($db_arr_){
                        $res_arr_ = array_merge($res_arr_,$db_arr_);
                    }
                }
                $res_arr_ = array_unique($res_arr_);
                $sum_count = count($res_arr_)??0;
                break;
            case 'join_task':
                //部分需要 关联任务表计算总数
                foreach ($arr_year_tab as $key => $_tab_name){
                    $db_count = Db::name($_tab_name)->alias('o')
                        ->join('_task t', 'o.taskid = t.id')
                        ->where($where_mz)->count('o.id');
                    if ($db_count){$sum_count += $db_count;}
                }
                break;
            default:
                //默认情况
                foreach ($arr_year_tab as $key => $_tab_name){
                    $db_count = Db::name($_tab_name)->alias('o')->where($where_mz)->count('o.id');
                    if ($db_count){$sum_count += $db_count;}
                }
                break;
        }
        return $sum_count;
    }

`♢ 分页数据读取订单数据`

一般都是用于后台管理
分表后都会影响分页查询
为了减少联表造成的查询耗时，建议不要跨年查询

同时为了提高查询效率，建议去掉 join 语句，先得到订单数据后，再进行内部关联查询
但具体还是要考虑实际情况，一般后台根据用户信息查询，想不联表查询都很难
只能尽量减少联表情况，优化索引，也可参考【附录 - 跨片的排序分页】

/**
     * 分页数据 读取
     * @param string $createtime_start
     * @param string $createtime_end
     * @param $where
     * @param $query
     * @param $limit
     * @param string $order
     * @return \think\Paginator
     * @throws \think\exception\DbException
     */
    public function queryOrderListForPage($createtime_start = '',$createtime_end = '',
                                          $where = [], $query = [], $limit = 10, $order = 'o.id desc')
    {
        $create_time_start = $createtime_start?strtotime($createtime_start):strtotime("-1 month");
        $create_time_end = $createtime_end?strtotime($createtime_end):time();

        $str_where = " id > 0 and (createtime between {$create_time_start} and {$create_time_end})";
        $field_union = '*';

        $_tab_name = $this->getUnionOrderTableNameForOpPage($create_time_start,$create_time_end,$field_union,$str_where);
        $field = 'o.*, t.taskname, u.truename, u.code, u.tel, t.platid, u.tel, t.typeid, t.task_type';

        $list = Db::table($_tab_name)
            ->alias('o')
            ->join('wechat_task t', 'o.taskid = t.id')
            ->join('wechat_member u', 'o.managerid = u.id')
            ->field($field)
            ->where($where)
            ->order($order)
            ->paginate($limit, false, ['query' => $query]);

        //内部数据处理
        $list =  $list->each(function($val){
            $val['taskname'] = $taskInfo['taskname']??'';
            return $val;
        });
        return  $list;
    }

`☞ 总结`

分库分表，首先得知道瓶颈在哪里，然后才能合理地拆分（分库还是分表？水平还是垂直？分几个？）。且不可为了分库分表而拆分。

1、选key很重要，既要考虑到拆分均匀，也要考虑到非 partition key的查询。
2、只要能满足需求，拆分规则越简单越好。

跨片的联表的问题
目前没有进行分库，可以不进行考虑。好的设计和切分是可以减少甚至杜绝此种情况的发生的。现在都崇尚单表操作，简单的做法就是分两次查询进行，第一次查询出关联关系，第二次就根据关联关系进行请求到关联数据，在应用中再进行组合。

`☞ 附录`

▷ 文章参考

经验值高的文章

`▶ 冷数据、热数据的考量`

热数据：2个星期内的订单数据，查询实时性较高;
冷数据：归档订单数据，查询频率不高;

根据实际业务场景，用户基本不会操作或查询2个星期以上的数据，如果这部分数据存储在DB中，那么成本会非常高，而且也不方便维护。另外，如果有特殊情况需要访问归档数据，可以走离线数据查看。

对于这2类数据，规划如下：

热数据：使用MySQL进行存储，分库分表；
冷数据：ES 或 TiDB或Hive存储；

`▶ 跨片的数据统计问题`

可以参考前面的代码 ——【获取订单数量】，就是对每个分片的数据统计后再做叠加处理

在产品设计上，应尽量避免此种的需求，在每种统计的范畴下，都限制为半年一个维度。
当然，会有无法避免需要统计年度的数据，就是跨半年的数据统计。

可以在各个切片上得到结果，在服务端再进行合并处理。
和单表处理的不同是，这种处理是可以并行执行的，所以基本上都会比单一的大表会快很多，
但是如果结果集很大，会给服务端造成比较大的内存消耗。

`▶ 跨片的排序分页`

这个问题比较大，所有的分库分表都会有这个问题。

第一页的数据比较好解决。查询所有分片的第一页数据，再通过服务端进行处理即可
一般在这种情况下，产品设计只做排序前面几页的展示，因为排序后，后面页数数据并没有太多的意义，绝大多数人不会翻到排序 10 页以后的数据去看

推荐参考文章数据库分表方案(分表分区分库策略)

也可以参考我的处理方法，不允许一次跨两年以上的查询

[提示]

参考到网上经验，
如果目标待拆分表不要求事务，可设定引擎类型： "ENGINE=MyISAM"
创建一个全表，关联所有的分表，起到一个中间辅助功能，方便分页查询
因为我们的业务需求，要求订单表为Innodb类型，不适合这个方式 .

参考文章：- 【mysql 分表+分页查询】

moTzxx

关注

5
点赞
踩
20

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

ThinkPHP5 按年度水平分表实例

☞ 实现步骤

①. 设计全局 id 生成器

②. 数据分表存储

☞ 核心代码实现，仅做参考

♢ 根据起始时间戳，返回操作的 数据表数组

♢ 插入新记录前，生成唯一ID

♢ 根据ID或时间戳，获取表名

♢ 根据订单ID，获取到单一一条订单数据

♢ 获取union联表所得表名

♢ 获取订单数量

♢ 分页数据 读取订单数据

☞ 总结

☞ 附录