CockroachDB分散式SQL實戰：2026年從MySQL線上遷移到NewSQL的完整方案
编程语言
MySQL分庫分表已經讓你精疲力竭了？
分片鍵選錯導致熱點、跨分片JOIN效能災難、分散式事務靠訊息佇列勉強拼湊、擴容需要停機遷移資料——當你的MySQL叢集規模達到TB級，分庫分表的運維成本已經遠超業務價值。2026年，NewSQL資料庫給出了更優雅的答案：CockroachDB，一個原生分散式、強一致性、相容PostgreSQL協定的SQL資料庫。
本文將帶你完成從MySQL到CockroachDB的線上零停機遷移，從架構原理到實操步驟，從分散式事務到多區域部署。
CockroachDB架構核心
| 概念 | 說明 | 與MySQL對比 |
|---|---|---|
| Range | 資料按範圍分片的基本單位（預設64MB） | 類似MySQL的Partition，但自動管理 |
| Replica | 每個Range預設3副本，用Raft共識 | MySQL MGR的類似機制，但更成熟 |
| Lease Holder | Range的讀寫領導者，處理本地讀 | 類似MySQL的主節點，但粒度更細 |
| DistSQL | 分散式SQL引擎，自動並行化查詢 | MySQL需要手動分片+中介軟體 |
| HLC | 混合邏輯時鐘，提供全域性時序 | MySQL依賴GTID，跨分片無全域性時序 |
分散式事務ACID保證
CockroachDB使用Parallel Commits協定實現分散式事務，無需2PC的協調者單點：
事務流程：
1. 寫入意圖（Write Intent）到所有涉及Range
2. 並行傳送PREPARE和COMMIT到所有Range
3. 任一節點當機時，其他節點透過Write Intent恢復
4. 事務延遲 = 1次網路往返（而非2PC的2次）
問題分析：MySQL遷移到CockroachDB的挑戰
- SQL相容性：CockroachDB相容PostgreSQL協定，MySQL語法需要改寫
- 自增ID：MySQL的AUTO_INCREMENT在分散式下不連續，需改用UUID或SEQUENCE
- 外鍵級聯：CockroachDB支援外鍵但不推薦級聯更新/刪除（效能問題）
- 儲存引擎差異：InnoDB的聚簇索引 vs CockroachDB的LSM-tree
- 字元集：MySQL的utf8mb4對應CockroachDB的UTF8（預設）
分步實操：MySQL線上遷移
Step 1：安裝CockroachDB
# Linux
curl -L https://binaries.cockroachdb.com/cockroach-v24.3.0.linux-amd64.tgz | tar -xz
sudo cp cockroach-v24.3.0.linux-amd64/cockroach /usr/local/bin/
# Docker
docker pull cockroachdb/cockroach:v24.3.0
# 啟動單節點（開發測試）
cockroach start-single-node --insecure --listen-addr=localhost:26257 --store=path=/data/cockroach
Step 2：啟動生產級叢集
# 節點1
cockroach start \
--insecure \
--store=path=/data/cockroach1 \
--listen-addr=192.168.1.10:26257 \
--http-addr=192.168.1.10:8080 \
--join=192.168.1.10:26257,192.168.1.11:26257,192.168.1.12:26257 \
--background
# 初始化叢集（僅首次）
cockroach init --insecure --host=192.168.1.10:26257
Step 3：使用MOLT遷移工具
# 安裝MOLT
curl -L https://binaries.cockroachdb.com/molt/v1.0.0/molt_linux_amd64.tar.gz | tar -xz
sudo cp molt /usr/local/bin/
# 評估遷移可行性
molt assess \
--source-type=mysql \
--source="root:password@tcp(mysql-host:3306)/mydb" \
--target="postgresql://root@cockroach-host:26257/mydb?sslmode=disable"
# 匯出Schema
molt dump-schema \
--source-type=mysql \
--source="root:password@tcp(mysql-host:3306)/mydb" \
--output-dir=./schema
# 轉換Schema（MySQL→CockroachDB）
molt convert-schema \
--input-dir=./schema \
--output-dir=./converted-schema
# 應用Schema到CockroachDB
cockroach sql --insecure --host=cockroach-host < ./converted-schema/schema.sql
# 線上資料遷移（CDC方式，零停機）
molt migrate-data \
--source-type=mysql \
--source="root:password@tcp(mysql-host:3306)/mydb" \
--target="postgresql://root@cockroach-host:26257/mydb?sslmode=disable" \
--mode=cdc \
--batch-size=10000 \
--concurrency=8
Step 4：SQL語法改寫
-- MySQL: AUTO_INCREMENT → CockroachDB: SERIAL
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
-- MySQL: LIMIT offset, count → CockroachDB: LIMIT count OFFSET offset
SELECT * FROM orders LIMIT 20 OFFSET 10;
-- MySQL: GROUP_CONCAT → CockroachDB: STRING_AGG
SELECT dept, STRING_AGG(name, ',') FROM employees GROUP BY dept;
-- MySQL: ON DUPLICATE KEY UPDATE → CockroachDB: UPSERT
UPSERT INTO users (id, name) VALUES (1, 'Alice');
完整程式碼：應用層遷移範例
package main
import (
"context"
"database/sql"
"fmt"
"log"
"time"
_ "github.com/lib/pq"
)
type Order struct {
ID int
UserID int
Amount float64
Status string
CreatedAt time.Time
}
type OrderRepo struct {
db *sql.DB
}
func NewOrderRepo(connStr string) (*OrderRepo, error) {
db, err := sql.Open("postgres", connStr)
if err != nil {
return nil, fmt.Errorf("open db: %w", err)
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
return &OrderRepo{db: db}, nil
}
func (r *OrderRepo) CreateOrder(ctx context.Context, order *Order) error {
query := `
INSERT INTO orders (user_id, amount, status, created_at)
VALUES ($1, $2, $3, $4)
RETURNING id`
return r.db.QueryRowContext(ctx, query,
order.UserID, order.Amount, order.Status, order.CreatedAt,
).Scan(&order.ID)
}
func (r *OrderRepo) TransferOrder(ctx context.Context, fromUserID, toUserID int, amount float64) error {
tx, err := r.db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
defer tx.Rollback()
deductQuery := `UPDATE accounts SET balance = balance - $1 WHERE user_id = $2 AND balance >= $1`
result, err := tx.ExecContext(ctx, deductQuery, amount, fromUserID)
if err != nil {
return fmt.Errorf("deduct: %w", err)
}
if n, _ := result.RowsAffected(); n == 0 {
return fmt.Errorf("insufficient balance")
}
creditQuery := `UPDATE accounts SET balance = balance + $1 WHERE user_id = $2`
if _, err := tx.ExecContext(ctx, creditQuery, amount, toUserID); err != nil {
return fmt.Errorf("credit: %w", err)
}
logQuery := `INSERT INTO transfer_logs (from_user, to_user, amount, created_at) VALUES ($1, $2, $3, NOW())`
if _, err := tx.ExecContext(ctx, logQuery, fromUserID, toUserID, amount); err != nil {
return fmt.Errorf("log: %w", err)
}
return tx.Commit()
}
func main() {
repo, err := NewOrderRepo("postgresql://root@localhost:26257/mydb?sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer repo.db.Close()
ctx := context.Background()
err = repo.TransferOrder(ctx, 1, 2, 100.50)
if err != nil {
log.Fatal("Transfer failed:", err)
}
fmt.Println("Transfer succeeded")
}
多區域部署配置
ALTER RANGE default CONFIGURE ZONE USING num_replicas = 5, constraints = '{"+us-east-1":2, "+eu-west-1":2, "+ap-southeast-1":1}';
ALTER DATABASE mydb CONFIGURE ZONE USING num_replicas = 5, constraints = '{"+us-east-1":2, "+eu-west-1":2, "+ap-southeast-1":1}';
ALTER TABLE users CONFIGURE ZONE USING num_replicas = 5, lease_preferences = '[[+us-east-1]]';
ALTER PARTITION us_east OF TABLE orders CONFIGURE ZONE USING constraints = '{"+us-east-1":2, "+eu-west-1":1}';
ALTER PARTITION eu_west OF TABLE orders CONFIGURE ZONE USING constraints = '{"+eu-west-1":2, "+us-east-1":1}';
避坑指南
坑1：AUTO_INCREMENT改為SERIAL後ID不連續
-- ❌ 期望：1, 2, 3, 4...
-- ✅ 實際：SERIAL底層是unique_rowid()，產生大數
-- 解決：如需連續ID，用SEQUENCE
CREATE SEQUENCE order_seq;
CREATE TABLE orders (id BIGINT DEFAULT nextval('order_seq') PRIMARY KEY, ...);
坑2：外鍵級聯更新效能災難
-- ❌ 在分散式下級聯更新會觸發跨Range事務
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON UPDATE CASCADE
);
-- ✅ 不使用級聯，應用層處理
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE RESTRICT
);
坑3：大事務超時
-- ❌ 單事務更新10萬行，超過5分鐘事務超時
BEGIN;
UPDATE large_table SET status = 'processed' WHERE created_at < '2026-01-01';
COMMIT;
-- ✅ 分批更新
BEGIN;
UPDATE large_table SET status = 'processed' WHERE created_at < '2026-01-01' LIMIT 1000;
COMMIT;
坑4：SELECT * FOR UPDATE導致Range鎖爭用
-- ❌ 鎖住整個Range
SELECT * FROM orders WHERE user_id = 1 FOR UPDATE;
-- ✅ 使用AS OF SYSTEM TIME讀取歷史快照
SELECT * FROM orders AS OF SYSTEM TIME '-5s' WHERE user_id = 1;
坑5：忽略Zone Configuration導致讀延遲高
-- ❌ 預設3副本隨機分佈，跨區域讀延遲200ms+
-- ✅ 配置lease_preferences讓讀請求就近
ALTER TABLE users CONFIGURE ZONE USING lease_preferences = '[[+us-east-1]]';
報錯排查
| 序號 | 報錯訊息 | 原因 | 解決方法 |
|---|---|---|---|
| 1 | TransactionRetryWithProtoRefreshError |
事務衝突，需重試 | 使用CRDB的事務重試包裝器，或降低並發度 |
| 2 | transaction deadline exceeded |
事務執行超過5分鐘超時 | 拆分大事務為小批次 |
| 3 | duplicate key value violates unique constraint |
SERIAL產生的ID衝突 | 使用UUID或顯式SEQUENCE |
| 4 | foreign key violation |
外鍵約束檢查失敗 | 確保引用資料存在，檢查插入順序 |
| 5 | value type tuple doesn't match type of column |
MySQL隱式型別轉換不支援 | 顯式CAST型別轉換 |
| 6 | unimplemented: this syntax is not supported |
MySQL特有語法不支援 | 查閱相容性文件，改寫為PG語法 |
| 7 | memory budget exceeded |
查詢中間結果超過記憶體限制 | 新增LIMIT、最佳化JOIN順序、增大記憶體設定 |
| 8 | node is draining |
節點正在下線 | 等待節點完成drain或連線其他節點 |
| 9 | replica not available |
副本數不足（節點當機） | 確保存活節點>=quorum，等待副本恢復 |
| 10 | incompatible schema change |
線上Schema Change衝突 | 等待前一個Schema Change完成 |
進階最佳化
1. 讀取歷史資料避免鎖爭用
SELECT * FROM orders AS OF SYSTEM TIME INTERVAL '-5s'
WHERE status = 'pending';
2. 批量插入最佳化
IMPORT INTO orders (id, user_id, amount, status)
CSV DATA (
'nfs://data/orders_part1.csv',
'nfs://data/orders_part2.csv'
) WITH skip = 1;
3. 自動重試事務包裝
func RetryTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
for i := 0; i < 5; i++ {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
err = fn(tx)
if err == nil {
return tx.Commit()
}
tx.Rollback()
if !isRetryableError(err) {
return err
}
time.Sleep(time.Duration(100*(i+1)) * time.Millisecond)
}
return fmt.Errorf("max retries exceeded")
}
對比分析
| 維度 | MySQL分庫分表 | CockroachDB | TiDB | Spanner |
|---|---|---|---|---|
| 分散式事務 | 需中介軟體 | 原生ACID | 原生ACID | 原生ACID |
| SQL相容 | MySQL | PostgreSQL | MySQL | PostgreSQL |
| 線上擴容 | 需停機遷移 | 自動Rebalance | 自動Rebalance | 自動 |
| 多區域 | 需自建 | 原生支援 | 有限支援 | 原生支援 |
| 一致性 | 最終一致 | 強一致(Serializable) | RC/SI | 強一致 |
| 運維複雜度 | 高 | 中 | 中 | 低(託管) |
| 成本 | 低 | 中 | 中 | 高 |
| 開源 | ✅ | ✅(BSL) | ✅ | ❌ |
總結：從MySQL遷移到CockroachDB不是簡單的「換資料庫」，而是從「分庫分表+中介軟體」到「原生分散式SQL」的架構升級。關鍵路徑：用MOLT工具線上遷移→改寫不相容SQL→配置多區域Zone→分批處理大事務→配置事務重試。CockroachDB的強一致性和自動Rebalance讓運維成本降低80%，但前提是理解其分散式事務機制和SQL相容邊界。
線上工具推薦
- JSON格式化：/zh-TW/json/format
- Base64編解碼：/zh-TW/encode/base64
- cURL轉程式碼：/zh-TW/dev/curl-to-code
- SQL格式化：/zh-TW/dev/sql-format
本站提供瀏覽器本地工具，免註冊即可試用 →
#CockroachDB#分布式SQL#NewSQL#分布式事务#多区域#在线迁移#高可用#一致性