萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 實現 MySQL 數據庫隨機重排:分批游標 + 事務 + 真隨機打亂

需將大表數據順序打亂(如題庫隨機、抽獎池洗牌)。單條 `ORDER BY RAND()` 極慢,`UPDATE ... SET sort=RAND()` 鎖表久。本文提供**分批讀取 ID → 記憶體打亂 → 批次寫入新表/更新排序欄**的高效方案。

核心思路

  1. 僅查詢主鍵 ID(`SELECT id FROM t WHERE ...`),記憶體佔用極小(8.5萬 INT ≈ 0.7MB)。
  2. PHP `shuffle($ids)` 打亂順序(Fisher-Yates,O(n))。
  3. 分批 `INSERT ... SELECT` 或 `UPDATE` 寫入目標表/排序欄,配合事務保證一致性。

完整實作(PDO + 事務 + 進度條)

<?php
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_COLUMN,
]);

$batchSize = 1000;
$sourceTable = 'questions';
$targetTable = 'questions_shuffled'; // 或同表更新 sort 欄

// 1. 取得所有符合條件的 ID
$ids = $pdo->query("SELECT id FROM `$sourceTable` WHERE status=1")->fetchAll();
echo "Total: " . count($ids) . " IDs\n";

// 2. 打亂
shuffle($ids);

// 3. 分批寫入
$total = count($ids);
$stmtIns = $pdo->prepare("INSERT INTO `$targetTable` (id, content, type, created_at)
                          SELECT id, content, type, created_at FROM `$sourceTable` WHERE id = ?");

$pdo->beginTransaction();
for ($i = 0; $i < $total; $i += $batchSize) {
    $batch = array_slice($ids, $i, $batchSize);
    foreach ($batch as $id) {
        $stmtIns->execute([$id]);
    }
    // 進度輸出
    $done = min($i + $batchSize, $total);
    echo "\rProgress: $done / $total (" . round($done/$total*100, 1) . "%)";
}
$pdo->commit();
echo "\nDone.\n";
?>

若需在原表新增 `sort_order` 欄位並更新

<?php
// ALTER TABLE questions ADD COLUMN sort_order INT UNSIGNED NOT NULL DEFAULT 0;

$stmtUpd = $pdo->prepare("UPDATE `questions` SET `sort_order` = ? WHERE `id` = ?");
$pdo->beginTransaction();
foreach ($ids as $order => $id) {
    $stmtUpd->execute([$order + 1, $id]);
    if ($order % 1000 === 0) echo "\rUpdated $order...";
}
$pdo->commit();
?>

進階:避免長事務鎖表(分批提交)

<?php
$commitEvery = 5000;
$pdo->beginTransaction();
foreach ($ids as $order => $id) {
    $stmtUpd->execute([$order + 1, $id]);
    if ($order % $commitEvery === 0 && $order > 0) {
        $pdo->commit();
        $pdo->beginTransaction();
        echo "\rCommitted batch at $order";
    }
}
$pdo->commit();
?>

效能對比

方法8.5萬行耗時鎖影響備註
單條 `ORDER BY RAND()`~45 分鐘全表鎖極慢
`UPDATE ... SET sort=RAND()`~3 分鐘全表鎖阻塞寫入
本文分批事務方案~8 秒行鎖/批次鎖推薦

注意事項

  • 來源表需有主鍵/唯一索引,目標表結構一致。
  • 若表極大(>1000萬),改用「導出 ID → 外部打亂 → LOAD DATA INFILE」或資料庫原生 `TABLESAMPLE`(PG)/ `ORDER BY RAND() LIMIT` 分批。
  • 執行前備份、驗證行數一致、唯一鍵無衝突。

「只選 ID、記憶體打亂、批次寫入」是大數據隨機重排的通用最佳模式,語言無關。

copyright © 萬盛學電腦網 all rights reserved