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=50 --sleepMs=200
 *   php yii salt/fill --mode=pk --scanSize=2000
 *   php yii salt/fill-ghs-order
 *   php yii salt/fill-cg
 */
class SaltController extends Controller
{
    /** @var int 每批处理条数 */
    public $batchSize = 5000;

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

    /**
     * 扫描模式:
     * - 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(),
        ]);
    }

    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='' 的记录，补完即停，不会扫描已有 salt 的上千万数据
     */
    private function fillByEmptySalt($label, $tableName)
    {
        $db = Yii::$app->db;
        $processed = 0;
        $lastId = 0;

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

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

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

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

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

        return $processed;
    }

    /**
     * 按主键游标分段扫描，查询只走主键索引；适合 empty 模式执行计划不佳时使用
     */
    private function fillByPrimaryKeyScan($label, $tableName)
    {
        $db = Yii::$app->db;
        $processed = 0;
        $lastId = 0;

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

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

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

            $lastId = (int)end($ids);
            $minId = (int)$ids[0];
            $maxId = $lastId;

            $emptyIds = $db->createCommand(
                "SELECT id FROM {$tableName} WHERE id >= :minId AND id <= :maxId AND salt = '' ORDER BY id ASC"
            )->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);
        }
    }
}