-
ThinkPHP5 按年度水平分表实例
-
背景
近期网站运营人员、产品反馈：查询数据耗时太长，网站交互上出现明显加载缓慢的情况
分析问题发现：随着流量的增长，部分数据表已达到 800万、1000万 尤其因为数据表之间会有众多的联表操作 如此一来，造成查询效率变低，影响使用- 1
- 2
- 3
CPU瓶颈：
单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表-
方案
针对前面的问题的出现，
因此，需要对一些数据表做水平拆分处理
常用的方案，按照一定策略（hash、range等）,根据我们的业务需求，选用了按年度分表
水平分表结果：
每个表的结构都一样； 每个表的数据都不一样，没有交集； 所有表的并集是全量数据；- 1
- 2
- 3
即：根据要添加记录的创建时间，写入对应年度的数据表中，比如 时间戳
1662372169对应的订单记录，会在表task_order_2022中-
框架
ThinkPHP5、mysql 5.7
☞ 实现步骤在此，鄙人根据自己的操作经验，以常见的订单表为对象，整理一番，欢迎指摘
- 梳理一下实现思路：
1. 确认好分表策略，是按ID取模 还是按年度 或者按记录数 ... 2. 设计 全局 id生成器 (用以控制 订单ID的生成，以及反向确定数据所在具体表) 3. 历史数据转储 （根据分表规则，提取历史数据到具体的表） 4. 如果插入新记录，先确认生成了订单ID,再根据当前时间戳，对应写入数据表中 5. 如果已知了某条订单记录ID,可以根据当时的时间戳，找到对应的表，然后再获取具体订单信息 6. 如果修改某条指定的订单记录，先根据ID或时间戳，找到对应的表，再执行update语句 7. ......- 1
- 2
- 3
- 4
- 5
- 6
- 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表';- 1
- 2
- 3
- 4
- 5
- 6
- 7
[备注]此表用于记录订单表的 ID,以及创建时间；
在插入订单记录前，订单 ID 都由该表产生，同时也会根据 （订单id+创建时间）反向找到真实存储的订单表,如 task_order_2021- 一般都是在项目运行一段时间后，
拥有了大量的历史数据后，才会考虑进行水平分表的处理
所以，先将历史数据的主键和创建时间提取出来，插入到新表
insert into _task_order_id(id,create_time) select id,createtime from mz_task_order;- 1
②. 数据分表存储-
首先对历史数据做一下处理
可以根据订单记录的创建时间，先执行筛选语句，将对应的记录转储到新建的对应年度订单表比如提取 2021年度的订单数据，存到
_task_order_2021中
执行 sql 语句如下：
create table _task_order_2021 select * from task_order where createtime BETWEEN 1609430400 and 1640966400;- 1
[提示]：另一种情况， 也是我遇到的场景：为了避免历史数据的转储时，造成丢失，和减少对原始数据完整性的验证 可以考虑选取一个时间节点， 只对时间节点后面的数据做分表处理，原始数据依然保留在原表 如此一来，最大的好处：减少了对原始数据的检测（当然，可能也不存在问题，毕竟为了减少数据变动） 缺点：在查询数据时，都要考虑下时间节点前后的规则，避免查询异常，其次可能原表数据有点多，历史数据查询时效率稍慢些！- 1
- 2
- 3
- 4
- 5
- 6
☞ 核心代码实现，仅做参考根据我的具体场景，提取几个核心处理方法，可供参考；如果使用，注意对方法的提取和数据表名称、字段信息的替换
♢ 根据起始时间戳，返回操作的 数据表数组- 简单描述：根据起止时间戳，确认一下能操作的 数据表数组（可能有多个）
[注意]：得到数据表后，一般要检验一下是否存在，所以参考后面补充的方法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; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
♢ 插入新记录前，生成唯一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]; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
♢ 根据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; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
♢ 根据订单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; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
♢ 获取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; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
♢ 获取订单数量- 根据搜索条件，获取订单数量，注意要遍历需要统计的数据表，最后叠加得到结果
/** * 获取订单数量 * @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; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
♢ 分页数据 读取订单数据-
一般都是用于后台管理
分表后都会影响分页查询
为了减少联表造成的查询耗时，建议不要跨年查询同时为了提高查询效率，建议去掉 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
☞ 总结- 分库分表，首先得知道瓶颈在哪里，然后才能合理地拆分（分库还是分表？水平还是垂直？分几个？）。且不可为了分库分表而拆分。
1、选key很重要，既要考虑到拆分均匀，也要考虑到非 partition key的查询。 2、只要能满足需求，拆分规则越简单越好。- 1
- 2
- 跨片的联表的问题
目前没有进行分库，可以不进行考虑。好的设计和切分是可以减少甚至杜绝此种情况的发生的。现在都崇尚单表操作，简单的做法就是分两次查询进行，第一次查询出关联关系，第二次就根据关联关系进行请求到关联数据，在应用中再进行组合。
☞ 附录▷ 文章参考
经验值高的文章
▶ 冷数据、热数据的考量- 热数据：2个星期内的订单数据，查询实时性较高;
冷数据：归档订单数据，查询频率不高;
根据实际业务场景，用户基本不会操作或查询2个星期以上的数据，如果这部分数据存储在DB中，那么成本会非常高，而且也不方便维护。另外，如果有特殊情况需要访问归档数据，可以走离线数据查看。
对于这2类数据，规划如下：
热数据：使用MySQL进行存储，分库分表； 冷数据：ES 或 TiDB或Hive存储；- 1
- 2
▶ 跨片的数据统计问题可以参考前面的代码 ——
【获取订单数量】，就是对每个分片的数据统计后再做叠加处理在产品设计上，应尽量避免此种的需求，在每种统计的范畴下，都限制为半年一个维度。
当然，会有无法避免需要统计年度的数据，就是跨半年的数据统计。可以在各个切片上得到结果，在服务端再进行合并处理。
和单表处理的不同是，这种处理是可以并行执行的，所以基本上都会比单一的大表会快很多，
但是如果结果集很大，会给服务端造成比较大的内存消耗。▶ 跨片的排序分页这个问题比较大，所有的分库分表都会有这个问题。
- 第一页的数据比较好解决。查询所有分片的第一页数据，再通过服务端进行处理即可
一般在这种情况下，产品设计只做排序前面几页的展示，因为排序后，后面页数数据并没有太多的意义，绝大多数人不会翻到排序 10 页以后的数据去看
推荐参考文章 数据库分表方案(分表分区分库策略)
- 也可以参考我的处理方法，不允许一次跨两年以上的查询
[提示]参考到网上经验， 如果目标待拆分表不要求事务，可设定引擎类型： "ENGINE=MyISAM" 创建一个全表，关联所有的分表，起到一个中间辅助功能，方便分页查询 因为我们的业务需求，要求订单表为Innodb类型，不适合这个方式 .- 1
- 2
- 3
- 4
参考文章：-
【mysql 分表+分页查询】 -
-
相关阅读:
docker安装hadoop集群遇到的一些问题
G1回收器的适用场景
《Redis实战》笔记
共码未来 | 更全面的开源机器学习生态
深度学习中的函数（一）
【 React 】在React 项目是如何捕获错误的？
线上教育知识付费小程序源码系统 界面支持DIY装修 带完整的安装代码包以及搭建部署教程
雷神学习---视音频数据处理入门：RGB、YUV像素数据处理
Day12--获取楼层的数据并渲染楼层的标题
大佬带飞，代码分享不会用？玩转Git，跟上大佬节奏！
- 原文地址：https://blog.csdn.net/u011415782/article/details/126422213
