huahuibao/console/controllers/SaltController.php

<?php

namespace console\controllers;

use bizGhs\order\models\Order as GhsOrder;
use bizHd\purchase\models\Purchase;
use common\components\stringUtil;
use Yii;
use yii\console\Controller;
use yii\console\ExitCode;

/**
 * 补充历史订单 salt 字段
 *
 * 大表安全说明:
 * - 不做 COUNT(*)，避免全表统计
 * - 默认 empty 模式只查 salt='' 的记录，补完后即停止，不会扫全表
 * - 查询均为普通 SELECT（MVCC 快照读），不会锁表；UPDATE 仅锁单行
 * - 大表建议先加索引: ALTER TABLE xhGhsOrder ADD INDEX idx_salt_id (salt, id);
 *
 * 用法:
 *   php yii salt/fill
 *   php yii salt/fill --dryRun=1
 *   php yii salt/fill --batchSize=1000 --sleepMs=200
 *   php yii salt/fill --mode=pk --scanSize=1000
 *   php yii salt/fill-ghs-order
 *   php yii salt/fill-cg
 *   php yii salt/fill-clear
 *   php yii salt/fill-settle
 */
class SaltController extends Controller
{
    /** @var int 每批处理条数 */
    public $batchSize = 1000;

    /** @var int pk 模式下每次主键游标扫描行数 */
    public $scanSize = 1000;

    /**
     * 扫描模式:
     * - empty: 只查 salt=''，适合待补数据量较少（默认）
     * - pk: 按主键分段扫描，适合 salt 列无索引且 empty 模式执行计划不佳时
     */
    public $mode = 'empty';

    /** @var int 每批之间的休眠毫秒数，降低白天执行时的数据库压力 */
    public $sleepMs = 100;

    /** @var int 最多处理条数，0 表示全部 */
    public $limit = 0;

    /** @var int 是否仅预览，1=是 */
    public $dryRun = 0;

    public function options($actionID)
    {
        return array_merge(parent::options($actionID), [
            'batchSize',
            'scanSize',
            'mode',
            'sleepMs',
            'limit',
            'dryRun',
        ]);
    }

    /**
     * 补充 xhGhsOrder、xhCg 历史数据的 salt 字段
     */
    public function actionFill()
    {
        return $this->runFill([
            'xhGhsOrder' => GhsOrder::tableName(),
            'xhCg' => Purchase::tableName(),
        ]);
    }

    /**
     * 仅补充 xhGhsOrder
     */
    public function actionFillGhsOrder()
    {
        return $this->runFill([
            'xhGhsOrder' => GhsOrder::tableName(),
        ]);
    }

    /**
     * 仅补充 xhCg
     */
    public function actionFillCg()
    {
        return $this->runFill([
            'xhCg' => Purchase::tableName(),
        ]);
    }

    /**
     * 补充 xhClear 结账单历史数据的 salt 字段
     * 职责：利用主键游标进行分页查询，分批重新生成并设置 salt，确保线上大表安全
     */
    public function actionFillClear()
    {
        $db = Yii::$app->db;
        $this->stdout("开始分页重新设置 xhClear 表的 salt 字段...\n");

        $processed = 0;
        $lastId = null;
        $batchSize = $this->batchSize > 0 ? $this->batchSize : 1000;

        while (true) {
            // 如果设置了 limit 且已达到上限，则退出
            if ($this->limit > 0 && $processed >= $this->limit) {
                break;
            }

            // 计算当前批次需要查询的数量
            $currentBatchSize = $batchSize;
            if ($this->limit > 0) {
                $currentBatchSize = min($batchSize, $this->limit - $processed);
            }

            // 使用主键游标分页查询 ID（利用主键索引，性能极高，避免大表全表扫描）
            if ($lastId === null) {
                $ids = $db->createCommand("SELECT id FROM xhClear ORDER BY id DESC LIMIT :limit")
                    ->bindValue(':limit', $currentBatchSize)
                    ->queryColumn();
            } else {
                $ids = $db->createCommand("SELECT id FROM xhClear WHERE id < :lastId ORDER BY id DESC LIMIT :limit")
                    ->bindValues([':lastId' => $lastId, ':limit' => $currentBatchSize])
                    ->queryColumn();
            }

            if (empty($ids)) {
                break;
            }

            // 循环更新当前批次的记录
            foreach ($ids as $id) {
                if ($this->dryRun) {
                    $this->stdout("[dry-run] xhClear id={$id}\n");
                    $processed++;
                    continue;
                }

                // 重新生成 10 位随机 salt 并更新
                $salt = stringUtil::charsShuffleLowerCase(10);
                $db->createCommand("UPDATE xhClear SET salt = :salt WHERE id = :id")
                    ->bindValues([':salt' => $salt, ':id' => $id])
                    ->execute();
                $processed++;

                // 每次更新后休眠，降低数据库写入压力
                if ($this->sleepMs > 0) {
                    usleep($this->sleepMs * 1000);
                }
            }

            // 更新游标为当前批次中最小的 ID
            $lastId = min($ids);
            $this->stdout("xhClear 已处理 {$processed} 条记录，当前 ID 游标: {$lastId}\n");
        }

        $this->stdout("xhClear salt 全部重新设置完成，共处理 {$processed} 条记录\n");
        return ExitCode::OK;
    }

    /**
     * 补充 xhSettle 结算单历史数据的 salt 字段
     * 职责：利用主键游标进行分页查询，分批重新生成并设置 salt，确保线上大表安全
     */
    public function actionFillSettle()
    {
        $db = Yii::$app->db;
        $this->stdout("开始分页重新设置 xhSettle 表的 salt 字段...\n");

        $processed = 0;
        $lastId = null;
        $batchSize = $this->batchSize > 0 ? $this->batchSize : 1000;

        while (true) {
            // 如果设置了 limit 且已达到上限，则退出
            if ($this->limit > 0 && $processed >= $this->limit) {
                break;
            }

            // 计算当前批次需要查询的数量
            $currentBatchSize = $batchSize;
            if ($this->limit > 0) {
                $currentBatchSize = min($batchSize, $this->limit - $processed);
            }

            // 使用主键游标分页查询 ID（利用主键索引，性能极高，避免大表全表扫描）
            if ($lastId === null) {
                $ids = $db->createCommand("SELECT id FROM xhSettle ORDER BY id DESC LIMIT :limit")
                    ->bindValue(':limit', $currentBatchSize)
                    ->queryColumn();
            } else {
                $ids = $db->createCommand("SELECT id FROM xhSettle WHERE id < :lastId ORDER BY id DESC LIMIT :limit")
                    ->bindValues([':lastId' => $lastId, ':limit' => $currentBatchSize])
                    ->queryColumn();
            }

            if (empty($ids)) {
                break;
            }

            // 循环更新当前批次的记录
            foreach ($ids as $id) {
                if ($this->dryRun) {
                    $this->stdout("[dry-run] xhSettle id={$id}\n");
                    $processed++;
                    continue;
                }

                // 重新生成 10 位随机 salt 并更新
                $salt = stringUtil::charsShuffleLowerCase(10);
                $db->createCommand("UPDATE xhSettle SET salt = :salt WHERE id = :id")
                    ->bindValues([':salt' => $salt, ':id' => $id])
                    ->execute();
                $processed++;

                // 每次更新后休眠，降低数据库写入压力
                if ($this->sleepMs > 0) {
                    usleep($this->sleepMs * 1000);
                }
            }

            // 更新游标为当前批次中最小的 ID
            $lastId = min($ids);
            $this->stdout("xhSettle 已处理 {$processed} 条记录，当前 ID 游标: {$lastId}\n");
        }

        $this->stdout("xhSettle salt 全部重新设置完成，共处理 {$processed} 条记录\n");
        return ExitCode::OK;
    }

    private function runFill(array $tables)
    {
        ini_set('memory_limit', '512M');
        set_time_limit(0);

        if ($this->batchSize < 1) {
            $this->stderr("batchSize 必须大于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }
        if ($this->scanSize < 1) {
            $this->stderr("scanSize 必须大于 0\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }
        if (!in_array($this->mode, ['empty', 'pk'], true)) {
            $this->stderr("mode 仅支持 empty 或 pk\n");
            return ExitCode::UNSPECIFIED_ERROR;
        }

        $exitCode = ExitCode::OK;
        foreach ($tables as $label => $tableName) {
            $code = $this->fillTable($label, $tableName);
            if ($code !== ExitCode::OK) {
                $exitCode = $code;
            }
        }

        return $exitCode;
    }

    private function fillTable($label, $tableName)
    {
        $db = Yii::$app->db;
        $db->createCommand('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED')->execute();

        $this->stdout("{$label}({$tableName}) 开始补充 salt，模式: {$this->mode}\n");
        if ($this->mode === 'empty') {
            $processed = $this->fillByEmptySalt($label, $tableName);
        } else {
            $processed = $this->fillByPrimaryKeyScan($label, $tableName);
        }

        $this->stdout("{$label} 完成，本次处理 {$processed} 条\n");
        return ExitCode::OK;
    }

    /**
     * 只查 salt='' 的记录，从最大 id 向小 id 分批处理
     */
    private function fillByEmptySalt($label, $tableName)
    {
        $db = Yii::$app->db;
        $processed = 0;
        $lastId = null;

        while (true) {
            if ($this->limit > 0 && $processed >= $this->limit) {
                break;
            }

            $batchLimit = $this->limit > 0 ? min($this->batchSize, $this->limit - $processed) : $this->batchSize;
            if ($lastId === null) {
                $rows = $db->createCommand(
                    "SELECT id FROM {$tableName} WHERE salt = '' ORDER BY id DESC LIMIT {$batchLimit}"
                )->queryColumn();
            } else {
                $rows = $db->createCommand(
                    "SELECT id FROM {$tableName} WHERE salt = '' AND id < :lastId ORDER BY id DESC LIMIT {$batchLimit}"
                )->bindValue(':lastId', $lastId)->queryColumn();
            }

            if (empty($rows)) {
                break;
            }

            $processed += $this->updateIds($label, $tableName, $rows);
            $lastId = (int)min($rows);

            $this->stdout("{$label} 已处理 {$processed} 条，当前 id >= {$lastId}\n");
            $this->sleepBetweenBatch();
        }

        return $processed;
    }

    /**
     * 按主键从最大 id 向小 id 分段扫描；适合 empty 模式执行计划不佳时使用
     */
    private function fillByPrimaryKeyScan($label, $tableName)
    {
        $db = Yii::$app->db;
        $processed = 0;
        $lastId = null;

        while (true) {
            if ($this->limit > 0 && $processed >= $this->limit) {
                break;
            }

            if ($lastId === null) {
                $ids = $db->createCommand(
                    "SELECT id FROM {$tableName} ORDER BY id DESC LIMIT {$this->scanSize}"
                )->queryColumn();
            } else {
                $ids = $db->createCommand(
                    "SELECT id FROM {$tableName} WHERE id < :lastId ORDER BY id DESC LIMIT {$this->scanSize}"
                )->bindValue(':lastId', $lastId)->queryColumn();
            }

            if (empty($ids)) {
                break;
            }

            $lastId = (int)min($ids);
            $minId = (int)min($ids);
            $maxId = (int)max($ids);

            $emptyIds = $db->createCommand(
                "SELECT id FROM {$tableName} WHERE id >= :minId AND id <= :maxId AND salt = '' ORDER BY id DESC"
            )->bindValues([
                ':minId' => $minId,
                ':maxId' => $maxId,
            ])->queryColumn();

            if (!empty($emptyIds)) {
                if ($this->limit > 0) {
                    $emptyIds = array_slice($emptyIds, 0, $this->limit - $processed);
                }
                $processed += $this->updateIds($label, $tableName, $emptyIds);
            }

            $this->stdout("{$label} 已处理 {$processed} 条，已扫描到 id >= {$lastId}\n");
            $this->sleepBetweenBatch();
        }

        return $processed;
    }

    private function updateIds($label, $tableName, array $ids)
    {
        $db = Yii::$app->db;
        $updated = 0;

        foreach ($ids as $id) {
            $id = (int)$id;
            if ($id <= 0) {
                continue;
            }

            if ($this->dryRun) {
                $this->stdout("[dry-run] {$label} id={$id}\n");
                $updated++;
                continue;
            }

            $salt = stringUtil::charsShuffleLowerCase(10);
            $affected = $db->createCommand(
                "UPDATE {$tableName} SET salt = :salt WHERE id = :id AND salt = ''"
            )->bindValues([
                ':salt' => $salt,
                ':id' => $id,
            ])->execute();

            if ($affected > 0) {
                $updated++;
            }
        }

        return $updated;
    }

    private function sleepBetweenBatch()
    {
        if ($this->sleepMs > 0) {
            usleep($this->sleepMs * 1000);
        }
    }
}