MySQL 批量插入測試數據 Shell 腳本：百萬級數據生成與性能基準測試

開發、測試、性能基準場景常需快速生成大量測試數據。本文提供參數化 Shell 腳本，支援自定義表結構、數據分佈、併發插入、進度顯示，適合 CI/CD 流水線集成與壓測準備。

腳本功能特性

• 參數化：行數、表名、欄位模板、批次大小
• 多種數據生成策略：序列、隨機、加權、時間序列
• 批次提交 + 事務控制，平衡速度與安全
• 進度條、耗時統計、錯誤重試
• 輸出 CSV/ SQL 文件備選模式

核心腳本 bulk_insert.sh

#!/bin/bash
# 用法: ./bulk_insert.sh [行數] [表名] [批次大小] [並發數]
# 示例: ./bulk_insert.sh 1000000 test_table 1000 4

set -euo pipefail

ROWS=${1:-100000}
TABLE=${2:-test_table}
BATCH=${3:-1000}
JOBS=${4:-1}

MYSQL_HOST="127.0.0.1"
MYSQL_USER="root"
MYSQL_PASS="password"
MYSQL_DB="test_db"
MYSQL_BIN="mysql"

# 欄位生成函數 (根據實際表結構修改)
gen_row() {
    local i=$1
    local name="USER_$(printf '%08d' $i)"
    local age=$((RANDOM % 80 + 18))
    local created="$(date -d "@$(( $(date +%s) - RANDOM % 31536000 ))" '+%Y-%m-%d %H:%M:%S')"
    echo "('$name', $age, '$created')"
}

# 構建單批次 SQL
build_batch_sql() {
    local start=$1
    local end=$2
    local values=()
    for ((i=start; i<=end; i++)); do
        values+=("$(gen_row $i)")
    done
    echo "INSERT INTO \`$TABLE\` (\`name\`, \`age\`, \`created_at\`) VALUES $(IFS=,; echo "${values[*]}");"
}

# 執行插入
execute_batch() {
    local sql=$1
    $MYSQL_BIN -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -e "$sql"
}

# 主邏輯
echo "=== 開始批量插入: $ROWS 行到 $TABLE (批次 $BATCH, 並發 $JOBS) ==="
start_ts=$(date +%s)

if [[ $JOBS -eq 1 ]]; then
    # 單線程
    for ((batch_start=1; batch_start<=ROWS; batch_start+=BATCH)); do
        batch_end=$((batch_start + BATCH - 1))
        [[ $batch_end -gt $ROWS ]] && batch_end=$ROWS
        sql=$(build_batch_sql $batch_start $batch_end)
        execute_batch "$sql"
        printf "\r進度: %d/%d (%.1f%%)" $batch_end $ROWS $(echo "scale=1; $batch_end*100/$ROWS" | bc)
    done
else
    # 並發模式 (使用 xargs -P)
    seq 1 $BATCH $ROWS | xargs -P $JOBS -I {} bash -c '
        start={}
        end=$((start + '$BATCH' - 1))
        [[ $end -gt '$ROWS' ]] && end='$ROWS'
        sql=$(build_batch_sql $start $end)
        execute_batch "$sql"
    '
fi

end_ts=$(date +%s)
echo -e "\n=== 完成: 耗時 $((end_ts - start_ts)) 秒, 平均 $((ROWS / (end_ts - start_ts))) 行/秒 ==="

進階版：支援複雜數據分佈

# 數據模板配置文件 (JSON)
cat > data_template.json << 'EOF'
{
  "table": "orders",
  "fields": {
    "order_no": {"type": "sequence", "start": 10000000},
    "user_id": {"type": "weighted", "pool": [1,2,3,4,5], "weights": [0.5,0.2,0.15,0.1,0.05]},
    "amount": {"type": "lognormal", "mean": 100, "sd": 50},
    "status": {"type": "enum", "values": ["NEW","PAID","SHIPPED","COMPLETED","CANCELLED"], "weights": [0.1,0.3,0.2,0.3,0.1]},
    "created_at": {"type": "timeseries", "start": "2024-01-01", "span_days": 365}
  }
}
EOF

性能基準測試參考

優化建議

1. 禁用索引/外鍵：導入前 ALTER TABLE ... DISABLE KEYS (MyISAM) / 刪除輔助索引 (InnoDB)
2. 調大 InnoDB 緩衝：innodb_buffer_pool_size、innodb_log_file_size
3. 使用 LOAD DATA：腳本輸出 CSV → LOAD DATA INFILE，速度提升 10x
4. 分區表並行：按範圍分區，各分區並行導入

CI/CD 集成示例

# .gitlab-ci.yml
generate_test_data:
  stage: prepare
  script:
    - chmod +x bulk_insert.sh
    - ./bulk_insert.sh 1000000 orders 5000 4
  artifacts:
    reports:
      metrics: benchmark_report.txt