CockroachDB 分布式 SQL¶

CockroachDB 是一款"打不死"的分布式 SQL 数据库（名字来自蟑螂的生存能力），兼容 PostgreSQL 协议，自动分片和故障恢复，适合需要高可用和跨区域部署的大规模数据平台。

核心知识点¶

知识点	说明
数据库类型	分布式 SQL 数据库（NewSQL）
开发团队	Cockroach Labs
许可证	BSL（Business Source License）
PostgreSQL 兼容	兼容 PostgreSQL wire protocol
核心优势	自动分片、强一致性、多活部署、故障自愈
一致性模型	可序列化隔离（Serializable Isolation）
适用场景	金融交易、多区域部署、高可用关键应用

安装配置¶

方法一：二进制安装¶

# 下载 CockroachDB
curl https://binaries.cockroachdb.com/cockroach-latest.linux-amd64.tgz | tar -xz
sudo cp cockroach-*/cockroach /usr/local/bin/  # 安装到系统路径

# 启动单节点（开发/测试）
cockroach start-single-node \
  --insecure \                         # 不启用 TLS（开发环境）
  --listen-addr=localhost:26257 \      # 监听地址
  --http-addr=localhost:8080 \         # Web UI 地址
  --store=cockroach-data               # 数据存储目录

# Web UI: http://localhost:8080

方法二：Docker¶

docker run -d \
  --name cockroach \
  -p 26257:26257 \                    # SQL 端口
  -p 8080:8080 \                      # Web UI 端口
  cockroachdb/cockroach start-single-node --insecure

# 连接
cockroach sql --insecure --host=localhost:26257
# 或者用 psql（兼容 PostgreSQL）
psql -h localhost -p 26257 -U root

基本使用¶

因为兼容 PostgreSQL，SQL 语法基本相同¶

-- 创建数据库和表
CREATE DATABASE bioinfo;              -- 创建数据库
SET DATABASE = bioinfo;               -- 使用数据库

CREATE TABLE samples (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- UUID 主键（分布式友好）
    sample_id STRING UNIQUE,                        -- 样本 ID
    diagnosis STRING NOT NULL,                      -- 诊断
    bmi DECIMAL(5,2),                              -- BMI
    metadata JSONB,                                -- JSON 元数据
    created_at TIMESTAMPTZ DEFAULT now()           -- 时间戳
);

-- 增删改查
INSERT INTO samples (sample_id, diagnosis, bmi, metadata)
VALUES ('T2D_001', 'T2D', 28.5, '{"platform": "NovaSeq"}');

SELECT sample_id, diagnosis, bmi FROM samples WHERE diagnosis = 'T2D';

-- JSONB 查询（和 PostgreSQL 一样）
SELECT sample_id, metadata->>'platform' AS platform FROM samples;

CockroachDB 特有功能¶

-- 查看数据分布（哪些节点存了哪些数据）
SHOW RANGES FROM TABLE samples;       -- 查看表的 Range 分布

-- 多区域配置（跨地域部署）
ALTER DATABASE bioinfo SET PRIMARY REGION = "us-east1";
ALTER DATABASE bioinfo ADD REGION "us-west1";
ALTER DATABASE bioinfo ADD REGION "eu-west1";

-- 表级别的数据本地化
ALTER TABLE samples SET LOCALITY REGIONAL BY ROW;  -- 数据就近存储

高级用法¶

1. 事务处理¶

-- CockroachDB 默认使用可序列化隔离级别（最高安全级别）
BEGIN;
  INSERT INTO samples (sample_id, diagnosis, bmi)
  VALUES ('T2D_010', 'T2D', 27.5);

  UPDATE samples SET bmi = 28.0 WHERE sample_id = 'T2D_001';
COMMIT;

-- 如果事务冲突，CockroachDB 会自动重试

2. Python 操作¶

import psycopg2  # 用 PostgreSQL 驱动连接 CockroachDB

conn = psycopg2.connect(
    host="localhost",
    port=26257,
    user="root",
    database="bioinfo",
    sslmode="disable"  # 开发环境
)
cursor = conn.cursor()

cursor.execute("SELECT sample_id, bmi FROM samples WHERE diagnosis = %s", ("T2D",))
for row in cursor.fetchall():
    print(f"样本: {row[0]}, BMI: {row[1]}")

conn.close()

常见报错与解决¶

报错信息	原因	解决方法
`connection refused :26257`	服务未启动	启动 CockroachDB
`TransactionRetryError`	事务冲突	重试事务（CockroachDB 常见）
PostgreSQL 语法不支持	部分 PG 扩展不兼容	查看兼容性文档
性能低于预期	单节点瓶颈	添加更多节点组成集群

速查表¶

-- ===== CockroachDB 速查表 =====

-- 安装启动
-- cockroach start-single-node --insecure

-- 连接
-- cockroach sql --insecure
-- psql -h localhost -p 26257 -U root

-- 基本操作（PostgreSQL 语法）
CREATE DATABASE db;
CREATE TABLE t (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING);
INSERT INTO t (name) VALUES ('test');
SELECT * FROM t;

-- 分布式特有
SHOW RANGES FROM TABLE t;             -- 数据分布
SHOW CLUSTER SETTING all;             -- 集群配置

-- CockroachDB vs TiDB vs PostgreSQL
-- CockroachDB: PG 兼容, 可序列化隔离, 多活部署
-- TiDB: MySQL 兼容, HTAP, TiFlash 列存
-- PostgreSQL: 单机最强, 扩展丰富, 生态完善

-- Python: pip install psycopg2-binary（直接用 PG 驱动）