分表+分区

一长期在线的签到类营销活动，用户每天只能参与一次签到，签到成功之后根据连续签到天数来获取对应的奖励。每日参与签跃用户约 100 万。用户可以查看最近1个月签到记录和奖励领取记录。
连续签到天数跟奖励对应关系见下表:
连续签到天数  奖励
3           A
5           B
10          C
20          D
30          E
连续签到:一旦某一天没有签到，连续签到次数从0开始重新计数。
请根据上述信息设计出合理高效的数据库和表来满足项目需要。

1. 表结构设计

一、基本概念

1. 分表：将一个大表拆分为多个小表（如 user_checkin_0、user_checkin_1），按 user_id 取模决定数据存到哪张表。

2. 分区：将一个表中的数据按时间（如每月）分成多个物理存储块，但逻辑上仍是一张表，分区是数据库层的功能。

二、表结构设计

-- 用户签到记录表（按用户ID分表）
CREATE TABLE user_checkin_%s (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    checkin_date DATE NOT NULL COMMENT '签到日期（格式：YYYY-MM-DD）',
    created_at INT  NOT NULL DEFAULT 0 COMMENT '创建时间',
    PRIMARY KEY (id, checkin_date),        -- 主键包含 checkin_date
    UNIQUE KEY udx_user_date (user_id, checkin_date),
    KEY idx_date (checkin_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (TO_DAYS(checkin_date)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    ...
);

 

2、hyperf框架设置

一、数据库配置 config/autoload/databases.php

return [
    'default' => [
        'driver' => Hyperf\DB\DB::class,
        'sharding' => [
            'enable' => true,
            'rules' => [
                'user_checkin' => [         // 逻辑表名
                    'shard_key' => 'user_id', 
                    'algorithm' => 'mod',    // 取模算法
                    'num' => 3,              // 分3张表（示例）
                    'format' => 'user_checkin_%s', // 物理表名格式
                ],
            ],
        ],
    ],
];

 

二、 模型类 app/Model/UserCheckin.php

namespace App\Model;

use Hyperf\DbConnection\Model\Model;

class UserCheckin extends Model
{
    // 逻辑表名（对应配置中的 user_checkin）
    protected $table = 'user_checkin';

    // 动态选择分表
    public function getTable()
    {
        $userId = $this->user_id ?? 0;
        $suffix = $userId % 3;  // 分3张表，实际用1024
        return "user_checkin_{$suffix}";
    }
}　　

 

3、手动或定时任务创建分区

每个月一号的时候定时任务去操作 自动分区

一、代码定时任务创建分区

 编辑命令 app/Command/PartitionCommand.php

namespace App\Command;

use Hyperf\Command\Command as HyperfCommand;
use Hyperf\DbConnection\Db;
use Psr\Container\ContainerInterface;

class PartitionCommand extends HyperfCommand
{
    protected $name = 'partition:maintain';

    public function handle()
    {
        // 下个月1号的日期（如 2023-04-01）
        $nextMonth = date('Y-m-01', strtotime('+1 month'));
        $partitionName = 'p' . date('Ym', strtotime($nextMonth));

        // 为每个分表添加分区
        for ($i = 0; $i < 3; $i++) {  // 示例3张分表，实际用1024
            $table = "user_checkin_{$i}";
            $sql = "ALTER TABLE {$table} ADD PARTITION (
                PARTITION {$partitionName} VALUES LESS THAN (TO_DAYS('{$nextMonth}'))
            )";
            Db::statement($sql);
            $this->info("为表 {$table} 添加分区 {$partitionName}");
        }
    }
}

注册命令 config/autoload/commands.php

 

return [
    App\Command\PartitionCommand::class,
];

设置定时任务（每月1号执行）

# 编辑 Crontab
crontab -e

# 添加任务（每月1号 00:05 执行）
5 0 1 * * cd /path/to/project && php bin/hyperf.php partition:maintain

4、查询sql

一、纯 SQL 查询语句

-- 假设用户ID为 12345，分表为 user_checkin_313（12345 % 1024 = 313）
-- 查询 2023-10 月的签到记录
SELECT *
FROM user_checkin_313
WHERE 
  user_id = 12345
  AND checkin_date BETWEEN '2023-10-01' AND '2023-10-31';

二、Hyperf 框架动态查询实现

namespace App\Model;

use Hyperf\DbConnection\Model\Model;

class UserCheckin extends Model
{
    protected $table = 'user_checkin'; // 逻辑表名

    // 动态设置分表名
    public function getTable()
    {
        $userId = $this->user_id ?? 0;
        $suffix = $userId % 1024; // 分表数量 1024
        return "user_checkin_{$suffix}";
    }
}

查询最近一个月签到记录

// 获取当前月份的第一天和最后一天
$startDate = date('Y-m-01'); // 本月第一天（如 2023-10-01）
$endDate = date('Y-m-t');     // 本月最后一天（如 2023-10-31）

// 执行查询（假设用户ID为 12345）
$userId = 12345;
$records = UserCheckin::query()
    ->where('user_id', $userId)
    ->whereBetween('checkin_date', [$startDate, $endDate])
    ->get();

// 输出结果
foreach ($records as $record) {
    echo $record->checkin_date . ' : ' . $record->points . PHP_EOL;
}

三、执行流程解析

1. 分表路由：
   Hyperf 根据 user_id 计算分表后缀（如 user_checkin_313），自动拼接物理表名。

2. 分区剪裁：
   MySQL 根据 checkin_date BETWEEN '2023-10-01' AND '2023-10-31' 自动定位到 p202310 分区，跳过其他分区。

3. 查询优化：

   • 索引建议：在 user_id 和 checkin_date 上创建复合索引，加速查询：

      

     ALTER TABLE user_checkin_313 
     ADD INDEX idx_user_date (user_id, checkin_date);

四、验证查询效率

1. 查看执行计划

// 在 Hyperf 中获取 SQL 执行计划
$explain = UserCheckin::query()
    ->where('user_id', 12345)
    ->whereBetween('checkin_date', [$startDate, $endDate])
    ->explain()
    ->get();

// 输出结果
dd($explain);

预期结果：

• partitions 列显示 p202310（仅扫描目标分区）。

• type 列为 range，表示高效的范围查询。

2. 日志验证

检查 Hyperf 的 SQL 日志，确认实际查询的表名和条件：

[DEBUG] SELECT * FROM `user_checkin_313` 
WHERE `user_id` = 12345 
AND `checkin_date` BETWEEN '2023-10-01' AND '2023-10-31'