熱數(shù)據(jù)查詢冷數(shù)據(jù)查詢應(yīng)用層查詢路由中間件MySQL熱庫(kù) orders_hotTiDB/ClickHouse冷庫(kù) orders_archiveSSD存儲(chǔ)池HDD存儲(chǔ)池

# 動(dòng)態(tài)計(jì)算冷熱數(shù)據(jù)邊界（示例）
import datetime

def is_hot_data(order_date):
    hot_threshold = datetime.datetime.now() - datetime.timedelta(days=90)
    return order_date >= hot_threshold

冷數(shù)據(jù)庫(kù)熱數(shù)據(jù)庫(kù)遷移調(diào)度器應(yīng)用
 冷數(shù)據(jù)庫(kù)
 熱數(shù)據(jù)庫(kù)
 遷移調(diào)度器
 應(yīng)用觸發(fā)遷移任務(wù)(每日02:00)
 SELECT id FROM ordersWHERE order_date < '2023-01-01'
 返回待遷移ID列表
 批量插入冷數(shù)據(jù)(分頁(yè))
 刪除已遷移數(shù)據(jù)(分批提交)
 創(chuàng)建壓縮索引

-- ClickHouse 跳數(shù)索引優(yōu)化
ALTER TABLE orders_archive 
ADD INDEX status_idx order_status TYPE minmax GRANULARITY 4

-- TiDB 聚簇索引
CREATE CLUSTERED INDEX idx_order_date ON orders_archive(order_date)

-- TiDB 彈性并行查詢
SET tidb_executor_concurrency = 16;
SELECT 
  user_id,
  SUM(amount) AS total_spent
FROM orders_archive
WHERE order_date BETWEEN '2020-01-01' AND '2022-12-31'
GROUP BY user_id
HAVING total_spent > 10000;

// 查詢路由中間件示例（偽代碼）
public ResultSet executeQuery(String sql) {
    QueryCondition condition = parseSQL(sql);
    
    if (condition.isHotData()) {
        return hotDB.execute(sql);
    } else if (condition.isAggregateQuery()) {
        return coldDB.execute(sql); 
    } else {
        // 并行查詢合并
        Future<Result> hotFuture = executor.submit(() -> hotDB.query(condition));
        Future<Result> coldFuture = executor.submit(() -> coldDB.query(condition));
        
        return mergeResults(hotFuture.get(), coldFuture.get());
    }
}

-- MySQL 在線DDL工具pt-online-schema-change
pt-online-schema-change \
  --alter "PARTITION BY RANGE (TO_DAYS(order_date)) (
    PARTITION p_2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_archive VALUES LESS THAN MAXVALUE
  )" \
  D=order_db,t=orders \
  --execute

# 基于Python的歸檔任務(wù)
import mysql.connector
from clickhouse_driver import Client

def archive_data():
    # 連接源庫(kù)和目標(biāo)庫(kù)
    src_conn = mysql.connector.connect(user='db_user', database='hot_db')
    ch_client = Client(host='clickhouse-server')
    
    # 分頁(yè)遷移數(shù)據(jù)
    page_size = 5000
    last_id = 0
    
    while True:
        src_cursor = src_conn.cursor(dictionary=True)
        src_cursor.execute(f"""
            SELECT * FROM orders 
            WHERE order_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
            AND id > {last_id}
            ORDER BY id LIMIT {page_size}
        """)
        
        rows = src_cursor.fetchall()
        if not rows:
            break
            
        # 批量插入ClickHouse
        ch_client.execute(
            "INSERT INTO orders_archive VALUES",
            [[row['id'], row['amount'], ...] for row in rows]
        )
        
        # 記錄斷點(diǎn)
        last_id = rows[-1]['id']
        
        # 從熱庫(kù)刪除（帶事務(wù)）
        delete_ids = [row['id'] for row in rows]
        src_cursor.execute(f"DELETE FROM orders WHERE id IN ({','.join(map(str, delete_ids))})")
        src_conn.commit()

-- ClickHouse 壓縮策略
ALTER TABLE orders_archive MODIFY SETTING 
  storage_policy = 'cold_policy',
  compression = CODEC(ZSTD(3), LZ4HC)

# 全表掃描（1億行）
MySQL: 12.8 min 
ClickHouse: 8.2 sec   # 94x faster

# 聚合查詢（按用戶統(tǒng)計(jì)）
MySQL: 4.2 min
TiDB: 11.5 sec        # 22x faster

-- 基于訪問頻次的自動(dòng)降冷
CREATE RULE auto_archive AS 
  ON SELECT TO orders_hot 
  WHEN last_access < NOW() - INTERVAL 180 DAY 
  DO MOVE TO orders_archive;

靜態(tài)資源熱點(diǎn)歷史訂單SQL預(yù)聚合CDN邊緣節(jié)點(diǎn)Redis查詢服務(wù)熱數(shù)據(jù)庫(kù)ClickHouse