千万数据量数据表分表实践
据表中有 2016年，2017 年，2018 年，2019 年数据
只查询最近半年的数据
后台增加历史数据查询功能
尽量减少代码改动
积分日志表 tb_user_points_log
虚拟充值表 tb_order_recharge
虚拟充值执行表 tb_order_recharge_do
注意：
先备份数据，在备份的数据表的基础上进行分表，不直接操作原始表!
image-20191115145313258
将源数据表备份一份，依次将对应年份的数据归档，每成功归档一次，就将备份数据表中对应数据删除（目的减少查询数据量），最后根据备份表最小 ID，删除源数据表 小于 ID 的所有数据。
该步骤可以直接通过 SQL 执行，也可通过脚本执行。
脚本执行
删除源数据表数据操作，建议通过手动执行 SQL完成，其他操作通过脚本执行
以积分日志表 tb_user_points_log 为例
备份 tb_user_points_log 得到 tb_user_points_copy
2016年数据归档
将数据表 tb_user_points_copy 2016 年的数据归档存入 2016 年数据表 tb_user_points_log_2016
CREATE TABLE tb_user_points_log_2016 LIKE tb_user_points_log_copy;
INSERT INTO tb_user_points_log_2016 SELECT * FROM tb_user_points_log_copy WHERE add_time < 1483200000;
对比数量
SELECT COUNT(id) FROM tb_user_points_log_2016;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1483200000;
一致则删除 tb_user_points_copy 的 2016 年数据
DELETE FROM tb_user_points_log_copy WHERE add_time < 1483200000;
2017年数据归档
将数据表 tb_user_points_copy 2017 年的数据归档存入 2017 年数据表 tb_user_points_log_2017
CREATE TABLE tb_user_points_log_2017 LIKE tb_user_points_log_copy;
INSERT INTO tb_user_points_log_2017 SELECT * FROM tb_user_points_log_copy WHERE add_time < 1514736000;
对比数量
SELECT COUNT(id) FROM tb_user_points_log_2017;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1514736000;
一致则删除 tb_user_points_copy 的 2017 年数据
DELETE FROM tb_user_points_log_copy WHERE add_time < 1514736000;
2018年数据归档
将数据表 tb_user_points_copy 2018 年的数据归档存入 2018 年数据表 tb_user_points_log_2018
CREATE TABLE tb_user_points_log_2018 LIKE tb_user_points_log_copy;
INSERT INTO tb_user_points_log_2018 SELECT * FROM tb_user_points_log_copy WHERE add_time < 1546272000;
对比数量
SELECT COUNT(id) FROM tb_user_points_log_2018;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1546272000;
一致则删除 tb_user_points_copy 的 2018 年数据
DELETE FROM tb_user_points_copy WHERE add_time < 1546272000;
2019年数据归档
现在是 11 月，将 5 月之前的数据归档
CREATE TABLE tb_user_points_log_2019 LIKE tb_user_points_log_copy;
INSERT INTO tb_user_points_log_2019 SELECT * FROM tb_user_points_log_copy WHERE add_time < 1556640000;
对比数量
SELECT COUNT(id) FROM tb_user_points_log_2019;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1556640000;
一致则删除 tb_user_points_copy 的 2019 年 5 月之前的数据
DELETE FROM tb_user_points_log_copy WHERE add_time < 1556640000;
删除原始数据
根据最小 tb_user_points_copy 的最小 ID，删除原始表 小于 ID 的所有数据
DELETE FROM tb_user_points_log WHERE id < (SELECT id FROM tb_user_points_log_copy ORDER BY id asc LIMIT 1);
删除临时表
DELETE FROM tb_user_points_log_copy;
数据表分表完成！
增量归档
每日凌晨，执行脚本将最近半年之前的数据归档
image-20191119153515166
setName('DataArchiving')->setDescription('将6个月前数据归档');
}
/**
* 将6个月前数据归档
* php think DataArchiving
* @param Input $input
* @param Output $output
*
* @return int|void|null
*/
protected function execute(Input $input, Output $output)
{
try {
$this->archiveData('tb_user_points_log', 'id', 'add_time');
$this->archiveData('tb_order_recharge', 'or_id', 'create_time');
$this->archiveData('tb_order_recharge_do', 'ord_id', 'create_time');
echo '归档完成';
} catch (\Exception $e) {
mylog($e->getMessage(),'归档发生错误:'.PHP_EOL);
}
}
/**
* 归档数据表
* @param string $sourceTable 源数据表名
* @param string $primaryKey 主键名
* @param string $timeKey 时间键名
*
* @author Dong.cx 2019-11-18 18:05
* @version V4.0.1
*/
private function archiveData($sourceTable, $primaryKey, $timeKey)
{
try {
date_default_timezone_set('PRC');
// 1.复制源数据表
$copyTable = $sourceTable . '_copy';
$isExist = $this->tableExist($copyTable, $sourceTable);
if (!$isExist) {
echo "开始复制源数据表{$copyTable}" . PHP_EOL;
$archivingTimeLine = time();
$sql = "INSERT IGNORE INTO {$copyTable} SELECT * FROM {$sourceTable} WHERE {$timeKey} < {$archivingTimeLine}";
Db::execute($sql);
echo "复制源数据表{$copyTable}完成" . PHP_EOL;
}
echo "{$copyTable} 开始归档" . PHP_EOL;
// 归档
$this->archive(2016, $sourceTable, $primaryKey, $timeKey);
$this->archive(2017, $sourceTable, $primaryKey, $timeKey);
$this->archive(2018, $sourceTable, $primaryKey, $timeKey);
$this->archive(2019, $sourceTable, $primaryKey, $timeKey);
echo "{$copyTable} 归档完成";
} catch (\Exception $e) {
echo '归档发生错误:' . $e->getMessage() .PHP_EOL;
}
}
/**
* 归档操作
* @param int $year 年份
* @param string $sourceTable 源数据表名
* @param string $primaryKey 主键名
* @param string $timeKey 时间键名
*
* @return bool
* @throws \Exception
* @author Dong.cx 2019-11-18 18:12
* @version V4.0.1
*/
private function archive($year, $sourceTable, $primaryKey, $timeKey)
{
try {
$copyTable = $sourceTable . '_copy';
echo "{$copyTable} 开始归档{$year}年数据--->" . PHP_EOL;
if ($year == date('Y')) {
// 注意现在是 11月份,可以简单这样写,如果是小于6月,则要相应修改
$archivingTimeLine = strtotime('-6 month', strtotime('today'));
} else {
$archivingTimeLine = mktime(0,0,0,1,1,$year+1);
}
$sql = "SELECT COUNT({$primaryKey}) as num FROM {$copyTable} WHERE {$timeKey} < {$archivingTimeLine}";
$res = Db::query($sql);
if (!$res || !$res[0]['num']) {
echo "{$copyTable} {$year}年数据归档完成,未查询到需要归档的数据" . PHP_EOL;
return true;
}
// 需归档数量
$targetNum = $res[0]['num'];
// 归档表名
$tableArchivingName = $sourceTable . '_' . $year;
$this->tableExist($tableArchivingName, $sourceTable);
// 分批归档
$this->archivingBatch($tableArchivingName, $copyTable, $primaryKey,$timeKey, $archivingTimeLine, $year, $targetNum);
return true;
} catch (\Exception $e) {
throw $e;
}
}
/**
* 分批归档
* @param string $tableArchivingName 归档表名称
* @param string $copyTable 复制表名
* @param string $primaryKey 主键名
* @param string $timeKey 时间键
* @param int $archivingTimeLine 归档时间线
* @param string $year 归档年
* @param int $targetNum 需归档的数据量
*
* @throws \Exception
* @author Dong.cx 2019-11-19 13:10
* @version V4.0.1
*/
private function archivingBatch($tableArchivingName, $copyTable, $primaryKey,$timeKey, $archivingTimeLine, $year, $targetNum)
{
// 归档表起始ID
$res = Db::query("SELECT {$primaryKey} FROM {$tableArchivingName} ORDER BY {$primaryKey} DESC LIMIT 1");
$startID = $res ? $res[0][$primaryKey] : 0;
$totalDelNum = 0;
$batchNum = 10000;
$taskNum = ceil($targetNum/$batchNum);
$minID = Db::query("SELECT {$primaryKey} FROM {$copyTable} ORDER BY {$primaryKey} ASC LIMIT 1");
if (!$minID) throw new \Exception('$minID为空!');
$minID = $minID[0][$primaryKey];
$maxID = Db::query("SELECT {$primaryKey} FROM {$copyTable} WHERE {$timeKey} < {$archivingTimeLine} ORDER BY {$primaryKey} DESC LIMIT 1");
if (!$maxID) throw new \Exception('$max 为空!');
$maxID = $maxID ? $maxID[0][$primaryKey] : 0;
for ($i = 1; $i <= $taskNum; $i++) {
if ($i == $taskNum) {
// 归档
$sql = "INSERT IGNORE INTO {$tableArchivingName} SELECT * FROM {$copyTable} WHERE {$primaryKey} <= {$maxID} AND {$timeKey} < {$archivingTimeLine}";
Db::execute($sql);
// 删除
$sql = "DELETE FROM {$copyTable} WHERE {$primaryKey} <= {$maxID} AND {$timeKey} < {$archivingTimeLine}";
$totalDelNum += Db::execute($sql);
} else {
$end = $minID + $i * $batchNum;
// 归档
$sql = "INSERT IGNORE INTO {$tableArchivingName} SELECT * FROM {$copyTable} WHERE {$primaryKey} <= {$end} AND {$timeKey} < {$archivingTimeLine}";
Db::execute($sql);
// 删除
$sql = "DELETE FROM {$copyTable} WHERE {$primaryKey} <= {$end} AND {$timeKey} < {$archivingTimeLine}";
$totalDelNum += Db::execute($sql);
}
}
// 成功归档数据量
$num = Db::query("SELECT COUNT({$primaryKey}) as num FROM {$tableArchivingName} WHERE {$primaryKey} > {$startID}")[0]['num'];
if ($targetNum != $num) throw new \Exception("归档数据不一致,过期数据量{$targetNum},归档量{$num},删除量{$totalDelNum}");
if ($num != $totalDelNum) throw new \Exception("删除数据不一致,归档量{$num},删除量{$totalDelNum}");
echo "{$copyTable} {$year}年数据归档完成,过期数据量{$targetNum},归档量{$num},删除量{$totalDelNum}" . PHP_EOL;
// 删除源数据表数据
//echo "开始删除源数据表 {$sourceTable}已归档数据" . PHP_EOL；
//$num = Db::execute("DELETE FROM {$sourceTable} WHERE {$primaryKey} < (SELECT id FROM {$copyTable} ORDER BY {$primaryKey} asc LIMIT 1)");
//echo "源数据表 {$sourceTable}已归档数据删除完成,删除数据量{$num}" . PHP_EOL；
//echo "开始删除临时表 {$copyTable}" . PHP_EOL；
// 删除临时表
//Db::execute("DELETE FROM {$copyTable}");
//echo "临时表{$copyTable}删除完成" . PHP_EOL;
}
最后由于是要删除源数据表，属于敏感操作，(脚本最后注释部分) 建议再复查一次数据归档正确性，确认无误后，手动执行 SQL操作。
DELETE FROM {$sourceTable} WHERE {$primaryKey} < (SELECT {$primaryKey} FROM {$copyTable} ORDER BY {$primaryKey} asc LIMIT 1;
DELETE FROM {$copyTable};
setName('DataArchiving')->setDescription('将6个月前数据归档');
}
/**
* 将6个月前数据归档
* php think DataArchiving
* @param Input $input
* @param Output $output
*
* @return int|void|null
*/
protected function execute(Input $input, Output $output)
{
try {
$this->archiveDataEveryDay('tb_user_points_log', 'id', 'add_time');
$this->archiveDataEveryDay('tb_order_recharge', 'or_id', 'create_time');
$this->archiveDataEveryDay('tb_order_recharge_do', 'ord_id', 'create_time');
echo '归档完成';
} catch (\Exception $e) {
mylog($e->getMessage(),'归档发生错误:'.PHP_EOL);
}
}
/**
* 归档数据
* @param string $sourceTable 源数据表名
* @param string $primaryKey 源数据表主键名
* @param string $timeKey 时间控制键名
*
* @return bool
* @throws \Exception
* @author Dong.cx 2019-11-15 18:36
* @version V4.0.1
*/
private function archiveDataEveryDay($sourceTable, $primaryKey, $timeKey)
{
try {
//mylog("{$sourceTable} 开始归档".PHP_EOL);
// 归档时间线
$archivingTimeLine = strtotime('-6 month', strtotime('today'));
// 归档表的年份
$year = date('Y', $archivingTimeLine);
// 归档表名
$tableArchivingName = $sourceTable . '_' . $year;
// 需要归档的数据量
$sql = "SELECT COUNT({$primaryKey}) as num FROM {$sourceTable} WHERE {$timeKey} < {$archivingTimeLine}";
$res = Db::query($sql);
// 没有需要归档的,直接返回
if (!$res) {
mylog("{$sourceTable} 归档完成,未查询到需要归档的数据");
return true;
}
$count = $res[0]['num'];
// 检测数据表是否存在,不存在则创建
$this->tableExist($tableArchivingName, $sourceTable);
$sql = "INSERT IGNORE INTO {$tableArchivingName} SELECT * FROM {$sourceTable} WHERE {$timeKey} < {$archivingTimeLine}";
// 1.开始归档
// 归档表起始ID
$res = Db::query("SELECT {$primaryKey} FROM {$tableArchivingName} ORDER BY {$primaryKey} DESC LIMIT 1");
$startID = $res ? $res[0][$primaryKey] : 0;
Db::execute($sql);
// 成功归档数据量
$num = Db::query("SELECT COUNT({$primaryKey}) as num FROM {$tableArchivingName} WHERE {$primaryKey} > {$startID}")[0]['num'];
if ($count != $num) throw new \Exception("归档数据不一致,过期数据量{$count},归档量{$num}");
$lastID = Db::query("SELECT {$primaryKey} FROM {$tableArchivingName} ORDER BY {$primaryKey} DESC LIMIT 1")[0][$primaryKey];
// 2.删除源数据
$sql = "DELETE FROM {$sourceTable} WHERE {$primaryKey} <= {$lastID} AND {$timeKey} < {$archivingTimeLine}";
$delNum = Db::execute($sql);
if ($delNum != $count) throw new \Exception("删除数据不一致,过期数据量{$count},删除量{$delNum}");
//mylog("{$sourceTable} 归档完成,过期数据量{$count},归档量{$count},删除量{$delNum}" . PHP_EOL);
return true;
} catch (\Exception $e) {
Db::rollback();
throw $e;
}
}
/**
* 检测数据表是否存在,不存在则创建
* @param $table
* @param $likeTable
*/
private function tableExist($table, $likeTable)
{
$sql = "SHOW TABLES LIKE '{$table}'";
$isExist = Db::query($sql);
if (!$isExist) {
Db::execute("CREATE TABLE {$table} LIKE {$likeTable}");