ThinkPHP5 按年度水平分表实例

泥豆芽儿 MT

发布于 2022-09-28 13:14:00

7770

发布于 2022-09-28 13:14:00

背景

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

随着流量的增长，部分数据表已达到 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 分表+分页查询】

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-09-07，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

ThinkPHP5 按年度水平分表实例

ThinkPHP5 按年度水平分表实例

`☞ 实现步骤`

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

`②. 数据分表存储`

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

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

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

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

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

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

`♢ 获取订单数量`

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

`☞ 总结`

`☞ 附录`

▷ 文章参考

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

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

`▶ 跨片的排序分页`

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

ThinkPHP5 按年度水平分表实例

ThinkPHP5 按年度水平分表实例

☞ 实现步骤

①. 设计全局 id 生成器

②. 数据分表存储

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

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

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

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

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

♢ 获取union联表所得表名

♢ 获取订单数量

♢ 分页数据 读取订单数据

☞ 总结

☞ 附录

▷ 文章参考

▶ 冷数据、热数据的考量

▶ 跨片的数据统计问题

▶ 跨片的排序分页

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

`☞ 实现步骤`

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

`②. 数据分表存储`

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

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

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

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

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

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

`♢ 获取订单数量`

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

`☞ 总结`

`☞ 附录`

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

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

`▶ 跨片的排序分页`