(一).项目分析，确定sharding column： 🔗
首先对功能进行分析，现有一张用户场景皮肤道具表属于单表设计。同一个用户可以拥有N张皮肤。每个用户现阶段有一百多种皮肤，数据已达到千万级别，而且皮肤数量在后期还会随着场景数量增加而不断增加。
表中有两个字段可以作为分表的字段（用户id和应用场景id），场景是根据对应的应用场景id加载的所以可以根据场景id进行分库分表。但是每次加载某个场景时功能需求需要一次性取出整个场景所有的皮肤id，分表后会将皮肤分配到N张表中，不光增加项目复杂度还要对MySQL发送多条sql请求操作数据库。所以不适合根据应用场景id作为sharding column。
根据uid进行分表的话，同一个用户的所有数据都将会被分配到同一张表中，这样在加载场景时直接通过sharding column就可以访问分库分表后的单表，而不需要跨库跨表操作，性能跟项目复杂度最优，所以选择uid作为sharding column进行分表。
注：场景皮肤道具表结构 🔗
CREATE TABLE `user_skin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(10) unsigned NOT NULL COMMENT '帐号UID',
`sceneId` int(11) NOT NULL DEFAULT '0' COMMENT '应用场景id',
`skinId` int(11) unsigned NOT NULL COMMENT '皮肤id',
`tm` datetime DEFAULT NULL COMMENT '获得的时间',
PRIMARY KEY (`id`),
UNIQUE KEY `a_t` (`uid`,`skinId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='道具皮肤表';
(二).确定分库分表方案： 🔗
在选取了合适的sharding column后，需要选取确定具体的分库分表方案了，考虑游戏项目对接口延时比较敏感，故而选取了主动迁移为主，被动迁移为辅相结合的方案。
- 主动迁移挂起一个单独的程序来主动遍历需要分库分表的表，然后将查询到的数据迁移到分库分表后的新表表中。
- 被动迁移在相关的接口业务代码中放入勾子函数，当调用对应接口时，遍历需要分库分表的表，然后迁移数据。
(三).方案具体实施： 🔗
3.1主动迁移： 🔗
- 单独的程序首先主动遍历用户表，查询出
用户uid。 - 程序获取Redis排它锁，判断被动迁移程序是否在运行迁移数据。
- 成功获取Redis排它锁以后查询
迁移记录表判断数据是否已经迁移完成。 - 根据
用户uid查询出对应的用户道具数据。 - 将
用户uid取模选择数据迁移的新表。 - 判断需要迁移的新表是否已经存在，不存在则创建。
- 开始迁移用户数据。
- 用户数据迁移成功后再
迁移记录表中写入迁移记录信息。
主动迁移伪代码： 🔗
//主动迁移数据
public function driveData()
{
$limit = 0;
$pageSize = 100;
while (true) {
$userList = $this->db->query("SELECT uid FROM user_info LIMIT ?,?", $limit, $pageSize);
foreach ($userList as $uid) {
$tmpLock = 'setuSkin:' . $uid . '.lock';
if (Redis::set($tmpLock, 1, "nx", "ex", 10)) {
if (Redis::get('isOkMove' . $uid)) {
Redis::del($tmpLock);//解锁
dd('has move');//已经转移了
}
$moId = $this->idMod($uid);
if ($moId === false) {//不合法的取模
Redis::del($tmpLock);//解锁
dd('mod fail');
}
$tbName = $this->tbName($moId);//获得表名
$this->isTable($tbName);//判断MySQL中表是否存在，不存在则创建新表
$res = $this->moveUserSkinData($tbName, $uid);//开始进行数据转移
Redis::del($tmpLock);//解锁
dd([$res, $tbName]);
}
}
$limit = $limit + $pageSize;
}
}
3.2被动迁移： 🔗
- 首先程序获取Redis排它锁，判断被动迁移程序是否在运行迁移数据。
- 成功获取Redis排它锁以后查询
迁移记录表判断数据是否已经迁移完成。 - 根据
用户uid查询出对应的用户道具数据。 - 将
用户uid取模选择数据迁移的新表。 - 判断需要迁移的新表是否已经存在，不存在则创建。
- 开始迁移用户数据。
- 用户数据迁移成功后再
迁移记录表中写入迁移记录信息。
被动迁移伪代码： 🔗
private static $tbNamePrefix = 'user_skin_';//表前缀
//非热点数据，被动转移
public function Hook($uid)
{
$tmpLock = 'setuSkin:' . $uid . '.lock';
if (Redis::set($tmpLock, 1, "nx", "ex", 10)) {
if (Redis::get('isOkMove' . $uid)) {
Redis::del($tmpLock);//解锁
dd('has move');//已经转移了
}
$moId = $this->idMod($uid);
if ($moId === false) {//不合法的取模
Redis::del($tmpLock);//解锁
dd('mod fail');
}
$tbName = $this->tbName($moId);//获得表名
$this->isTable($tbName);//判断MySQL中表是否存在，不存在则创建新表
$res = $this->moveUserSkinData($tbName, $uid);//开始进行数据转移
Redis::del($tmpLock);//解锁
dd([$res, $tbName]);
}
}
注：被动迁移与被动迁移公用方法 🔗
/**
* 开始转移数据
*/
private function moveUserSkinData($tbName, $uid)
{
$loop = 0;
while ($loop < 3) {
$loop++;
$userSkinList = DB::table('user_skin')
->where(['uid' => $uid])
->get();
if (!$userSkinList) {
return false;
}
$items = [];
foreach ($userSkinList as $userSkinListInfo) {
foreach ($userSkinListInfo as $vield => $value) {
$info[$vield] = $value;
}
array_push($items, $info);
}
if (empty($items)) {
return false;
}
$insertRes = DB::table($tbName)->insert($items);
if ($insertRes) {
return $this->recordMoveLog($uid, 1);
}
return false;
}
}
private function recordMoveLog($uid, $fun = 1)
{
$result = Redis::pipeline(function ($pipe) use ($uid) {
$key = 'isOkMove' . $uid;//完成转移记录
$pipe->set($key, $uid);
$pipe->expire($key, 86400);
});
if (is_array($result) && !empty($result['0'])) {
$data = [
'uid' => $uid,
'fun' => $fun,
'tm' => date('Y-m-d H:i:s'),
];
return DB::table('user_skin_move_log')
->insert($data);
}
return false;
}
//判断MySQL中表是否存在，不存在则创建新表
private function isTable($tableName)
{
if (Redis::expire($tableName, 86400))//判断表是否存在
return true;
if (!Schema::hasTable($tableName)) {//不存在创建表
DB::statement($this->createTb($tableName));
}
$result = Redis::pipeline(function ($pipe) use ($tableName) {
$pipe->set($tableName, $tableName);
$pipe->expire($tableName, 86400);
});
return is_array($result) && !empty($result['0']);
}
//创建表
private function createTb($tableName)
{
return "CREATE TABLE IF NOT EXISTS `" . $tableName . "` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(10) unsigned NOT NULL COMMENT '帐号UID',
`sceneId` int(11) NOT NULL DEFAULT '0' COMMENT '应用场景id',
`skinId` int(11) unsigned NOT NULL COMMENT '皮肤id',
`tm` datetime DEFAULT NULL COMMENT '获得的时间',
PRIMARY KEY (`id`),
UNIQUE KEY `a_t` (`uid`,`skinId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='道具皮肤表';";
}
//用户数据转移记录表
private function moveLogTb()
{
return "CREATE TABLE `user_skin_move_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(10) unsigned NOT NULL COMMENT '帐号UID',
`fun` int(10) unsigned NOT NULL COMMENT '转移的功能',
`tm` datetime DEFAULT NULL COMMENT '获得的时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户数据转移记录表';";
}
/**
* @desc 创建表名
* @param $moId 模值
* @return string
*/
private function tbName($moId)
{
return self::$tbNamePrefix . $moId;
}
/**
* @desc 数字取模
* @param $account
* @param int $tail
* @param int $mod
* @return float
*/
private function idMod($account, $tail = 4, $mod = 2)
{
if (!is_numeric($account)) {
return false;//不合法用户id
}
return fmod($account, $mod);//取模计算数值
}
/**
* @desc 字符串取模
* @param $account
* @param int $tail
* @param int $mod
* @return float
*/
private function strMod($account, $tail = 4, $mod = 2)
{
$crc32 = sprintf("%u", crc32($account));//使用%u解决32位下出现负数的问题
return fmod($crc32, $mod);//取模计算数值
}