NewSQL in Practice: Overview, HTAP Architecture, and Real-World Migration Cases

NewSQL is a class of modern distributed relational databases that combine the convenience of SQL with the scalability of NoSQL. This article presents practical applications, HTAP architecture, and best practices.

1. NewSQL Overview

1.1 Core Capabilities

NewSQL 核心能力
┌─────────────────────────────────────────────────────────────┐
│                                                         │
│      ┌───────────────────┐                            │
│      │      NewSQL       │                            │
│      │                   │                            │
│      │  ┌─────────────┐  │                            │
│      │  │   完整 SQL   │  │  ┌─────────────┐            │
│      │  │   强一致    │  │  │ Traditional │            │
│      │  │   水平扩展  │  │  │   SQL      │            │
│      │  │   高吞吐    │  │  │  - ACID    │            │
│      │  └─────────────┘  │  │  - SQL     │            │
│      │                   │  │  - 垂直扩展│            │
│      │                   │  └─────────────┘            │
│      │                   │  ┌─────────────┐            │
│      │                   │  │    NoSQL    │            │
│      │                   │  │  - 水平扩展 │            │
│      │                   │  │  - 最终一致 │            │
│      │                   │  │  - KV 为主 │            │
│      └───────────────────┘  └─────────────┘            │
│                                                         │
└─────────────────────────────────────────────────────────────┘

1.2 Product Comparison

TiDB : storage TiKV (RocksDB), MySQL compatible, suited for internet scenarios.

CockroachDB : self‑developed storage, PostgreSQL compatible, validated in finance.

SingleStore : self‑developed storage, MySQL compatible, strong HTAP performance.

YugaByteDB : RocksDB storage, PostgreSQL/CQL compatible, supports multiple protocols.

ScyllaDB : Seastar storage, CQL compatible, Cassandra compatible.

2. HTAP Architecture

2.1 HTAP Overview

HTAP (Hybrid Transactional/Analytical Processing) combines OLTP and OLAP in a single system.

HTAP 架构
┌─────────────────────────────────────────────────────────────┐
│                                                         │
│               应用层                                    │
│   ┌─────────────────┬─────────────────┐                │
│   │   OLTP 写入   │   OLAP 查询   │                │
│   └────────┬───────────────┬───────────────┘                │
│            │               │                               │
│   ┌────────▼────────┐  ┌──────▼──────┐                    │
│   │   Row Store     │  │ Column Store │                    │
│   │   (事务)        │  │   (分析)    │                    │
│   │   TiKV          │  │   TiFlash   │                    │
│   └─────────────────┘  └─────────────┘                    │
│            │               │                               │
│            └──────────┬───────┘                               │
│                       ▼                                   │
│               数据自动同步                                 │
│                                                         │
└─────────────────────────────────────────────────────────────┘

2.2 TiDB HTAP Configuration

-- 查看集群配置
SHOW CONFIG;

-- 添加 TiFlash 副本
ALTER TABLE orders SET TIFLASH REPLICA 2;

-- 查看副本状态
SHOW TIFLASH REPLICA orders;

-- 强制 OLAP 查询走 TiFlash
EXPLAIN SELECT DATE(created_at) as date,
       COUNT(*) as order_count,
       SUM(total_amount) as total
FROM orders
GROUP BY DATE(created_at);

-- 指定使用 TiFlash
SELECT /*+ READ_FROM_TIFLASH() */ DATE(created_at) as date,
       COUNT(*) as order_count
FROM orders
GROUP BY DATE(created_at);

2.3 SingleStore Architecture

-- SingleStore 分区表
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    SHARD KEY (user_id),
    KEY (created_at)
);

-- 引用表 (广播复制)
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 聚合计算 (自动并行)
SELECT u.name, SUM(o.total_amount) as total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL 30 DAY
GROUP BY u.id, u.name
ORDER BY total DESC;

3. Storage‑Compute Separation

3.1 Separation Architecture

存储计算分离架构
┌─────────────────────────────────────────────────────────────┐
│                                                         │
│  ┌─────────────────────────────────────────────────────┐ │
│  │               计算层 (无状态)                     │ │
│  │  ┌─────────┐  ┌─────────┐  ┌─────────┐               │ │
│  │  │ Compute │  │ Compute │  │ Compute │   ...       │ │
│  │  │ Node 1 │  │ Node 2 │  │ Node 3 │               │ │
│  │  └────┬────┘  └────┬────┘  └────┬────┘               │ │
│  └───────┼────────────┼────────────┼─────────────────────┘ │
│          │            │            │                     │
│          └────────────┴────────────┘                     │
│                     ▼                                   │
│  ┌─────────────────────────────────────────────────────┐ │
│  │               分布式存储层                         │ │
│  │  ┌─────────┐  ┌─────────┐  ┌─────────┐          │ │
│  │  │ Storage │  │ Storage │  │ Storage │   ...    │ │
│  │  │ Tier 1 │  │ Tier 2 │  │ Tier 3 │          │ │
│  │  │ (SSD)   │  │ (NVMe) │  │ (HDD)   │          │ │
│  │  └─────────┘  └─────────┘  └─────────┘          │ │
│  └─────────────────────────────────────────────────────┘ │
│                                                         │
└─────────────────────────────────────────────────────────────┘

3.2 Elastic Scaling

# TiDB Serverless 自动扩缩
import os
connection = pymysql.connect(
    host=os.environ['HOST'],
    port=4000,
    user=os.environ['USER'],
    password=os.environ['PASSWORD'],
    database='test',
    ssl={'ca': '/etc/ssl/certs/ca-certificates.crt'}
)
cursor = connection.cursor()
cursor.execute("SELECT COUNT(*) FROM large_table")

3.3 Hot‑Cold Data Tiering

-- CockroachDB 分区
ALTER TABLE logs PARTITION BY RANGE (created_at) (
    PARTITION hot VALUES FROM (MINVALUE) TO ('2024-01-01'),
    PARTITION warm VALUES FROM ('2024-01-01') TO ('2024-04-01'),
    PARTITION cold VALUES FROM ('2024-04-01') TO ('2024-07-01'),
    PARTITION archive VALUES FROM ('2024-07-01') TO (MAXVALUE)
);

-- 设置不同存储层
ALTER PARTITION hot OF TABLE logs SET LOCATION 's3://hot-bucket';
ALTER PARTITION cold OF TABLE logs SET LOCATION 's3://cold-bucket';

4. Practical Cases

4.1 Migrating an Order System to TiDB

-- 原始 MySQL 表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(32) UNIQUE,
    total_amount DECIMAL(10,2),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- 迁移到 TiDB (DDL 兼容)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(32) UNIQUE,
    total_amount DECIMAL(10,2),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- 添加分析副本
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- OLTP 查询 (毫秒级)
SELECT * FROM orders WHERE id = 123456;

-- OLAP 查询 (自动路由到 TiFlash)
SELECT DATE(created_at) as date,
       status,
       COUNT(*) as count,
       SUM(total_amount) as amount
FROM orders
WHERE created_at > NOW() - INTERVAL 30 DAY
GROUP BY DATE(created_at), status;

4.2 Real‑Time Analytics Dashboard

from sqlalchemy import create_engine
import pandas as pd

# TiDB 连接
engine = create_engine('mysql+pymysql://user:pass@host:4000/database')

def get_realtime_stats():
    query = """
    SELECT
        DATE(created_at) as date,
        HOUR(created_at) as hour,
        COUNT(*) as orders,
        SUM(total_amount) as gmv
    FROM orders
    WHERE created_at > NOW() - INTERVAL 24 HOUR
    GROUP BY DATE(created_at), HOUR(created_at)
    ORDER BY date DESC, hour DESC
    """
    df = pd.read_sql(query, engine)
    return df.to_dict('records')