需求

  • 对平均 1200w 数据量的数据表进行优化
  • 数据表中有 2016年，2017 年，2018 年，2019 年数据
  • 只查询最近半年的数据
  • 后台增加历史数据查询功能
  • 尽量减少代码改动

数据表

  • 积分日志表 tb_user_points_log
  • 虚拟充值表 tb_order_recharge
  • 虚拟充值执行表 tb_order_recharge_do

注意：

先备份数据，在备份的数据表的基础上进行分表，不直接操作原始表!

步骤

千万级别表加索引要多长时间 千万级数据表设计_千万级别表加索引要多长时间

将源数据表备份一份，依次将对应年份的数据归档，每成功归档一次，就将备份数据表中对应数据删除（目的减少查询数据量），最后根据备份表最小 ID，删除源数据表 小于 ID 的所有数据。

该步骤可以直接通过 SQL 执行，也可通过脚本执行。

脚本执行

删除源数据表数据操作，建议通过手动执行 SQL完成，其他操作通过脚本执行

以积分日志表 tb_user_points_log 为例

方式一、手动执行SQL

  1. 备份 tb_user_points_log 得到 tb_user_points_copy
2016年数据归档
  1. 将数据表 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;
  1. 对比数量
SELECT COUNT(id) FROM tb_user_points_log_2016;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1483200000;
  1. 一致则删除 tb_user_points_copy 的 2016 年数据
DELETE FROM tb_user_points_log_copy WHERE add_time < 1483200000;
2017年数据归档
  1. 将数据表 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;
  1. 对比数量
SELECT COUNT(id) FROM tb_user_points_log_2017;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1514736000;
  1. 一致则删除 tb_user_points_copy 的 2017 年数据
DELETE FROM tb_user_points_log_copy WHERE add_time < 1514736000;
2018年数据归档
  1. 将数据表 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;
  1. 对比数量
SELECT COUNT(id) FROM tb_user_points_log_2018;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1546272000;
  1. 一致则删除 tb_user_points_copy 的 2018 年数据
DELETE FROM tb_user_points_copy WHERE add_time < 1546272000;
2019年数据归档
  1. 现在是 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;
  1. 对比数量
SELECT COUNT(id) FROM tb_user_points_log_2019;
SELECT COUNT(id) FROM tb_user_points_log_copy WHERE add_time < 1556640000;
  1. 一致则删除 tb_user_points_copy 的 2019 年 5 月之前的数据
DELETE FROM tb_user_points_log_copy WHERE add_time < 1556640000;
删除原始数据
  1. 根据最小 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);
  1. 删除临时表
DELETE FROM tb_user_points_log_copy;
  1. 数据表分表完成！
  2. 增量归档
    每日凌晨，执行脚本将最近半年之前的数据归档

方式二、脚本执行

千万级别表加索引要多长时间 千万级数据表设计_数据_02

<?php
/**
 * Description: 将6个月前数据归档
 */

namespace wladmin\cmd;


use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;

class DataArchiving extends Command
{
    protected function configure()
    {
        $this->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};

增量归档脚本

<?php
/**
 * Description: 将6个月前数据归档
 */

namespace wladmin\cmd;


use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;

class DataArchiving extends Command
{
    protected function configure()
    {
        $this->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}");
        }
    }
}

历史数据查询

在数据访问层中根据需要查询时间 动态修改数据表名即可

这里使用的是 thinkphp Query 类中的 setTable() 和 getTable()

if (isset($params['history']) && !empty($params['history'])) {
            $this->model()->setTable($this->model()->getTable().'_'.$params['history']);
        }

遇到的问题

  • 开发中，曾尝试使用事务控制，数据量太多会导致提交过慢，因此使用逻辑控制
  • DB 一次性执行100多w删除操作后，发现程序不继续向下执行，未找到原因，因此将数据分批进行处理，但是分批可能存在问题，因为主键可能不是连续的，如果间隔不大的话，影响不大。