背景
近期网站运营人员、产品反馈：查询数据耗时太长，网站交互上出现明显加载缓慢的情况 分析问题发现：
代码语言：javascript复制随着流量的增长，部分数据表已达到 800万、1000万
尤其因为数据表之间会有众多的联表操作
如此一来，造成查询效率变低，影响使用
CPU瓶颈：
单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表
- 方案
针对前面的问题的出现，
因此，需要对一些数据表做水平拆分处理
常用的方案，按照一定策略（
hash、range等）,根据我们的业务需求，选用了按年度分表
水平分表结果：
代码语言：javascript复制每个表的结构都一样；
每个表的数据都不一样，没有交集；
所有表的并集是全量数据；即：根据要添加记录的创建时间，写入对应年度的数据表中，比如 时间戳
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，获取到单一一条订单数据
代码语言：javascript复制 /**
* 获取指定ID的 订单数据
* TODO 待优化
* @param int $order_id
* @param string $field_str
* @return array|PDOStatement|string|Model|null
* @throws thinkdbexceptionDataNotFoundException
* @throws thinkdbexceptionModelNotFoundException
* @throws thinkexceptionDbException
*/
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|thinkCollection|thinkmodelCollection
* @throws thinkexceptionDbException
*/
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|thinkCollection|thinkmodelCollection
* @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 thinkPaginator
* @throws thinkexceptionDbException
*/
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、只要能满足需求，拆分规则越简单越好。- 跨片的联表的问题 目前没有进行分库，可以不进行考虑。好的设计和切分是可以减少甚至杜绝此种情况的发生的。现在都崇尚单表操作，简单的做法就是分两次查询进行，第一次查询出关联关系，第二次就根据关联关系进行请求到关联数据，在应用中再进行组合。
☞ 附录
▷ 文章参考
- 【ThinkPHP5.1 文档 — 分表查询】
【thinkphp5 Union 联合联合查询 联合查询多张表 成一组数据】
经验值高的文章
- mysql-分库分表
- 知乎 - Mysql分库分表方案
- 概念参考-分库分表方案
- 【重点推荐文章 —— 分库分表方案】
▶ 冷数据、热数据的考量
- 热数据：2个星期内的订单数据，查询实时性较高; 冷数据：归档订单数据，查询频率不高;
根据实际业务场景，用户基本不会操作或查询2个星期以上的数据，如果这部分数据存储在DB中，那么成本会非常高，而且也不方便维护。另外，如果有特殊情况需要访问归档数据，可以走离线数据查看。
对于这2类数据，规划如下：
代码语言：javascript复制热数据：使用MySQL进行存储，分库分表；
冷数据：ES 或 TiDB或Hive存储；▶ 跨片的数据统计问题
可以参考前面的代码 ——
【获取订单数量】，就是对每个分片的数据统计后再做叠加处理
在产品设计上，应尽量避免此种的需求，在每种统计的范畴下，都限制为半年一个维度。 当然，会有无法避免需要统计年度的数据，就是跨半年的数据统计。
可以在各个切片上得到结果，在服务端再进行合并处理。 和单表处理的不同是，这种处理是可以并行执行的，所以基本上都会比单一的大表会快很多， 但是如果结果集很大，会给服务端造成比较大的内存消耗。
▶ 跨片的排序分页
这个问题比较大，所有的分库分表都会有这个问题。
- 第一页的数据比较好解决。查询所有分片的第一页数据，再通过服务端进行处理即可 一般在这种情况下，产品设计只做排序前面几页的展示，因为排序后，后面页数数据并没有太多的意义，绝大多数人不会翻到排序 10 页以后的数据去看
推荐参考文章 数据库分表方案(分表分区分库策略)
- 也可以参考我的处理方法，不允许一次跨两年以上的查询
[提示]
参考到网上经验，
如果目标待拆分表不要求事务，可设定引擎类型： "ENGINE=MyISAM"
创建一个全表，关联所有的分表，起到一个中间辅助功能，方便分页查询
因为我们的业务需求，要求订单表为Innodb类型，不适合这个方式 .参考文章：-
【mysql 分表 分页查询】
