需將大表數據順序打亂(如題庫隨機、抽獎池洗牌)。單條 `ORDER BY RAND()` 極慢,`UPDATE ... SET sort=RAND()` 鎖表久。本文提供**分批讀取 ID → 記憶體打亂 → 批次寫入新表/更新排序欄**的高效方案。
<?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";
?>
<?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 秒 | 行鎖/批次鎖 | 推薦 |
「只選 ID、記憶體打亂、批次寫入」是大數據隨機重排的通用最佳模式,語言無關。