📚 课程目录第3章表操作与DDL

3、表操作与DDL：创建库与表、修改表结构、分区与分片概念、表级TTL与列级TTL、临时表与视图

这一章，我们正式进入 ClickHouse 的「动手环节」。建库建表、改结构、玩分区、搞 TTL，这些都是日常用得最频繁的操作。我个人习惯是先想清楚数据怎么进来、怎么出去，再动 SQL。不然表建好了，后面改起来挺折腾的。

3.1 创建库与表

ClickHouse 的建库语法和 MySQL 很像，但引擎这块差别很大。默认用 Atomic 库引擎，支持原子性的 DDL 操作。

CREATE DATABASE IF NOT EXISTS my_analytics
ENGINE = Atomic;

建表时，引擎选择是关键。我刚开始用 ClickHouse 时，图省事全用了 TinyLog，结果并发一上来就崩。后来才明白，MergeTree 家族才是生产环境的主角。

CREATE TABLE my_analytics.events
(
    event_date  Date,
    event_type  String,
    user_id     UInt64,
    cost        Float32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type);

小提示：ORDER BY 决定了每个分区内的数据排序，也直接影响查询性能。选错排序键，查询可能慢 10 倍。

3.2 修改表结构

ClickHouse 支持在线修改表结构，但有些操作是异步的。比如加列很快，删列却要等后台合并。

-- 添加列
ALTER TABLE my_analytics.events
ADD COLUMN ip_address String AFTER user_id;

-- 修改列类型
ALTER TABLE my_analytics.events
MODIFY COLUMN cost Decimal(16,2);

-- 删除列
ALTER TABLE my_analytics.events
DROP COLUMN ip_address;

注意：修改列类型时，只能从低精度向高精度转换。我曾经试图把 String 改成 Int，结果直接报错。ClickHouse 不会帮你做隐式类型转换。

3.3 分区与分片概念

这两个词容易搞混。我简单解释一下：

分区：把一张表的数据按某个字段（通常是时间）切分成多个物理目录。查询时只扫描相关分区，速度飞快。
分片：把数据分布到多台机器上，每台机器只存一部分数据。这是水平扩展的手段。

分区是表内部的，分片是集群层面的。你想想看，如果一张表有 10 亿行，不分区的话，每次查询都要扫全表，谁也扛不住。

-- 按月份分区
CREATE TABLE my_analytics.events_partitioned
(
    event_date  Date,
    event_type  String,
    user_id     UInt64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type);

  核心要点：分区粒度不宜过细。我见过有人按天分区，结果一天的数据只有几百行，分区数上万，合并线程直接打满。建议按月或按周。

分片则依赖 Distributed 表引擎。它本身不存数据，只是把查询路由到各个分片。

CREATE TABLE my_analytics.events_distributed
(
    event_date  Date,
    event_type  String,
    user_id     UInt64
)
ENGINE = Distributed('my_cluster', 'my_analytics', 'events', rand());

这里 rand() 是分片键，决定数据写到哪个分片。选分片键要尽量均匀，避免数据倾斜。

3.4 表级 TTL 与列级 TTL

TTL（Time To Live）是 ClickHouse 的自动过期清理机制。说白了，就是告诉系统：「数据老了就删掉，别占地方。」

表级 TTL：整行数据过期后自动删除。

CREATE TABLE my_analytics.events_ttl
(
    event_date  Date,
    event_type  String,
    user_id     UInt64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type)
TTL event_date + INTERVAL 3 MONTH;

列级 TTL：只让某一列过期，其他列保留。这个功能很实用，比如日志表里有个大字段（如 request_body），只想保留 7 天，其他字段保留 1 年。

CREATE TABLE my_analytics.events_column_ttl
(
    event_date    Date,
    event_type    String,
    request_body  String TTL event_date + INTERVAL 7 DAY,
    user_id       UInt64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type)
TTL event_date + INTERVAL 1 YEAR;

避坑指南：我曾经在生产环境设了列级 TTL，但忘了设表级 TTL。结果列数据删了，行数据还在，查询时出现大量空字段。后来我养成了习惯：列级 TTL 和表级 TTL 一起配。

3.5 临时表与视图

临时表：会话级别的表，断开连接就自动删除。适合存中间结果。

CREATE TEMPORARY TABLE temp_user_stats
(
    user_id  UInt64,
    total    Float32
);

INSERT INTO temp_user_stats
SELECT user_id, sum(cost)
FROM my_analytics.events
GROUP BY user_id;

视图：分普通视图和物化视图。普通视图就是个保存的 SQL 查询，不存数据。物化视图则是把查询结果物理存储下来，数据插入原表时自动更新。

-- 普通视图
CREATE VIEW my_analytics.view_high_cost AS
SELECT *
FROM my_analytics.events
WHERE cost > 100;

-- 物化视图
CREATE MATERIALIZED VIEW my_analytics.mv_daily_stats
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type)
AS SELECT
    event_date,
    event_type,
    count() AS cnt,
    sum(cost) AS total_cost
FROM my_analytics.events
GROUP BY event_date, event_type;

注意：物化视图是「写时转化」，不是「读时加速」。数据插入原表时，会同步写入物化视图。如果原表数据量巨大，物化视图的写入压力也不小。我建议先在小表上测试，确认查询收益大于写入开销，再上线。

3.6 本章知识体系

下面这张图帮你理清本章的核心脉络：

3.7 本章小结

这一章我们走通了 ClickHouse 表操作的全流程。从建库建表开始，到改结构、玩分区、配 TTL，再到临时表和视图。嗯，这里要注意：分区和 TTL 是 ClickHouse 性能优化的两个核心杠杆，用好了能省下大量存储和计算资源。

我个人建议，每建一张表之前，先问自己三个问题：

数据按什么字段分区？时间字段通常是最佳选择。
TTL 设多久？过期数据是删还是降冷？
需不需要物化视图？预聚合能省多少查询时间？

想清楚再动手，后面运维会轻松很多。