MySQL TPC-H 基準測試工具完整手冊：編譯、建模、導數、執行與自動化腳本

TPC-H 是事務處理性能委員會 (TPC) 制定的決策支援基準，模擬商業分析場景，包含 8 張核心表、23 條複雜 OLAP 查詢、並發資料修改，聚焦測試資料庫引擎、平台、I/O 子系統在大數據量下的查詢能力。本文提供從源碼編譯到自動化執行的完整實戰手冊。

一、環境準備與源碼編譯

# 1. 下載源碼 (TPC 官網需註冊)
wget https://www.tpc.org/tpch/spec/tpch_2_18_0.zip
unzip tpch_2_18_0.zip
cd tpch_2_18_0/dbgen

# 2. 複製並修改 Makefile
cp makefile.suite makefile
vim makefile
# 關鍵變數修改：
CC = gcc
DATABASE = MYSQL
MACHINE = LINUX
WORKLOAD = TPCH
# 若無 MYSQL 選項，需手動添加編譯旗標 -DMYSQL

tpcd.h 關鍵巨集定義 (MySQL 適配)

#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "LIMIT %d;\n"
#define SET_DBASE "USE %s;\n"
#define STR_CMP strcasecmp
#endif

編譯驗證

make clean && make
# 產出兩個核心工具：
# dbgen  - 數據生成器
# qgen   - SQL 生成器

二、測試數據生成 (dbgen)

# 縮放因子 (Scale Factor) SF=1 約 1 GB 原始數據
# 生產基準建議 SF ≥ 100 (100 GB)，測試環境可用 SF=10 (10 GB)
./dbgen -s 10 -v -f
# -s 10      : Scale Factor 10
# -v         : 詳細進度輸出
# -f         : 強制覆蓋既有檔案
# 輸出 8 個 .tbl 檔案：customer, lineitem, nation, orders, part, partsupp, region, supplier

關鍵表結構與數據量參考 (SF=10)

三、MySQL 專用 DDL 腳本修正 (dss.ddl / dss.ri)

官方腳本針對 Informix/DB2/Oracle，需大幅修正適配 MySQL：

dss.ddl 核心修正點

-- 1. 添加資料庫管理
DROP DATABASE IF EXISTS tpch;
CREATE DATABASE tpch CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tpch;

-- 2. 類型映射調整
-- INTEGER → INT / BIGINT
-- CHAR(n) → VARCHAR(n) 或 CHAR(n) (視固定長度)
-- DATE → DATE
-- DECIMAL(15,2) → DECIMAL(15,2)
-- 移除非標準子句：NOT NULL DEFAULT '0' 等調整為 MySQL 語法

-- 3. 主鍵定義調整
-- PRIMARY KEY (col1, col2) 標準語法

-- 4. 分區表可選 (大表建議)
-- ALTER TABLE lineitem PARTITION BY RANGE (l_shipdate) ...

dss.ri 索引與外鍵修正

-- 1. 移除 CONNECT TO TPCD; → USE tpch;
-- 2. 註釋符 -- 後加空格 (MySQL 要求)
-- 3. 外鍵語法標準化：引用欄位必須顯式指定
-- 原：REFERENCES TPCD.REGION;
-- 新：REFERENCES TPCD.REGION(R_REGIONKEY);

-- 4. 複合外鍵引用完整欄位組合
-- REFERENCES TPCD.PARTSUPP(PS_PARTKEY, PS_SUPPKEY);

-- 5. 表名小寫化 (Linux 大小寫敏感)
ALTER TABLE CUSTOMER RENAME TO customer;
-- ... 8 張表全部小寫

四、數據導入 (LOAD DATA INFILE)

# 關閉 Binlog 避免超大事務超出 max_binlog_cache_size
mysql -u root -p -e "SET GLOBAL sql_log_bin=0;"

# 批次導入 (按依賴順序：碼表→主表→事實表)
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/region.tbl' INTO TABLE region FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/part.tbl' INTO TABLE part FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"
mysql -u root -p tpch -e "LOAD DATA INFILE '/path/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"

# 重啟 Binlog
mysql -u root -p -e "SET GLOBAL sql_log_bin=1;"

大表導入優化參數

[mysqld]
bulk_insert_buffer_size = 1G
innodb_buffer_pool_size = 70%_RAM
innodb_log_file_size = 4G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = 0
# 導入完成後恢復生產配置

五、查詢 SQL 生成與修正 (qgen)

# 生成 23 條查詢
mkdir queries
for i in {1..23}; do
  ./qgen $i | sed 's/\r//' > queries/tpch_${i}.sql
done

# 批次修正常見語法問題
cd queries
for f in *.sql; do
  # 1. LIMIT 子句修正
  sed -i 's/;\nLIMIT/LIMIT/g' $f
  sed -i 's/LIMIT -1/LIMIT 1/g' $f
  
  # 2. INTERVAL 後的數字移除 (MySQL 不支援 precision)
  sed -i "s/interval '[0-9]*' day ([0-9]*)/interval '\1' day/g" $f
  
  # 3. 特定查詢別名修正 (Q13, Q17 等)
  # 手動檢查並修正 COUNT(*) → COUNT(*) AS c_count 等
done

六、自動化基準測試執行腳本

#!/bin/bash
# tpch_benchmark.sh
# 用法: ./tpch_benchmark.sh [SF] [ITERATIONS] [QUERY_SUBSET]
# 例: ./tpch_benchmark.sh 10 3 "1 3 6 14 19"

SF=${1:-10}
ITER=${2:-3}
QUERIES=${3:-$(seq -f "%02g" 1 23)}

LOG="tpch_sf${SF}_$(date +%Y%m%d_%H%M%S).log"
exec > >(tee -a "$LOG") 2>&1

echo "=== TPC-H Benchmark SF=$SF Iterations=$ITER ==="
echo "Start: $(date)"

# 預熱
echo "Warming up buffer pool..."
mysql -u root -p tpch < queries/tpch_01.sql > /dev/null

for iter in $(seq 1 $ITER); do
  echo "=== Iteration $iter ==="
  for q in $QUERIES; do
    qfile="queries/tpch_${q}.sql"
    echo "[$(date)] Query $q starting..."
    
    # 重啟 MySQL 確保冷緩衝池 (可選，視測試目的)
    # systemctl restart mysql
    # sleep 10
    
    # 執行並計時
    start=$(date +%s.%N)
    mysql -u root -p tpch < "$qfile" > /dev/null
    end=$(date +%s.%N)
    
    runtime=$(echo "$end - $start" | bc)
    echo "Query $q completed in ${runtime}s"
    echo "QUERY,$SF,$iter,$q,$runtime" >> tpch_results.csv
  done
done

echo "=== Benchmark Complete ==="
echo "Results saved to tpch_results.csv"
echo "End: $(date)"

七、結果分析關鍵指標

八、常見問題排查

從源碼編譯到自動化跑分，TPC-H 完整鏈路打通，讓 MySQL OLAP 能力有了可度量、可對比、可追蹤的標準尺規。