为什么ClickHouse中ORDER BY在表创建后不能更改？

因为数据在磁盘上按该顺序物理排序。更改它需要重写所有列，在TB级表上需要数小时。

ClickHouse中的稀疏索引是什么？

它是一种稀疏索引，每8192行（颗粒）存储一条记录。它不指示行的确切位置，只指示颗粒的起始位置，从而加速分析查询。

如何正确选择ORDER BY中的列顺序？

先放低基数列（如sport_id），再放高基数列（如created_at）。将等值条件的列放在范围条件的列之前。

如何检查索引是否有效使用？

使用EXPLAIN indexes=1——查看读取的颗粒数与总颗粒数的比率。此外，system.query_log显示read_rows / result_rows。

能否在不重建表的情况下更改ORDER BY？

不能。唯一的方法是创建一个具有所需ORDER BY的新表，迁移数据，重命名表，然后删除旧表。

ClickHouse中的ORDER BY和PRIMARY KEY：索引选择

ClickHouse中的ORDER BY与PRIMARY KEY：如何正确设置索引

1. 为什么ORDER BY是你在表中指定的最重要内容

在传统数据库（PostgreSQL、MySQL）中，有两个概念：聚簇索引（主键，物理上决定数据在磁盘上的顺序）和二级索引（独立的B树）。你可以随时添加或删除索引，而无需重建表。

在ClickHouse中则不同。这里，数据在磁盘上只有一种物理顺序——即你在ORDER BY中指定的顺序。而且，不重建表就无法更改。完全不行。就像浇筑混凝土后才发现钢筋放错了位置，你必须全部打碎重来。

为什么这么严格？ 因为ClickHouse以列式格式存储数据，并进行了高度压缩。要改变行顺序，你必须从头重写所有列。没有人愿意花几小时或几天来重新组织一个TB级别的表。

因此，选择ORDER BY是一个战略性决策。你必须预测哪些查询最频繁，并设计键使它们执行得飞快。一个错误将代价高昂。

现实类比： 想象你是一名图书管理员，需要将所有书籍按特定顺序摆放在书架上。你可以选择一种顺序，比如按类型，然后在类型内按作者姓氏排序。这样，如果你按这些条件搜索，就能快速找到书籍。但如果你认为按出版日期排序更方便，你就得把书全部重新摆放。耗时数小时。

2. PRIMARY KEY ⊆ ORDER BY —— 一条罕见的规则

在ClickHouse中，你有两个参数：

ORDER BY —— 定义行的物理顺序（必选）。
PRIMARY KEY —— 定义索引（可选）。

有一条硬性规则：PRIMARY KEY中列出的列必须是ORDER BY中的前几列。换句话说，PRIMARY KEY是ORDER BY的前缀。

-- ✅ 正确：PRIMARY KEY是ORDER BY的前两列
CREATE TABLE bets
(
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount)    -- 完整顺序
PRIMARY KEY (user_id, created_at);         -- 前缀：前两列

-- ❌ 错误：PRIMARY KEY不是前缀
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id);   -- 顺序不同——ClickHouse会报错

-- ⚠️ 你可以完全省略PRIMARY KEY
-- 此时它会自动等于ORDER BY
CREATE TABLE bets
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- PRIMARY KEY = (user_id, created_at)

那么，如果PRIMARY KEY只是前缀，为什么还需要它？ 原因如下：ClickHouse索引（稀疏索引）仅建立在PRIMARY KEY的列上。如果你指定的PRIMARY KEY比ORDER BY短，可以节省索引内存，但行顺序仍然是完整的（按所有ORDER BY列）。当影响物理顺序的列不需要出现在索引中时，这很有用。

示例： 在ORDER BY (user_id, created_at, amount)中——行首先按user_id排序，然后按created_at，最后按amount。但你不需要按amount搜索，所以PRIMARY KEY (user_id, created_at)更短，索引更小，而物理布局有助于压缩（相同的amount存储在一起）。

3. 稀疏索引：每8192行一个条目（颗粒）

ClickHouse中的索引称为稀疏索引。它不像PostgreSQL的B树那样存储指向每一行的指针。相反，它每8192行存储一个条目（这个组称为颗粒）。

内部结构：

颗粒（第1–8192行）	颗粒第一行的PRIMARY KEY值
颗粒1	user_id=100, created_at=2025-01-01 00:00:01
颗粒2	user_id=100, created_at=2025-01-01 10:15:23
颗粒3	user_id=200, created_at=2025-01-01 00:00:05
...	...

ClickHouse如何搜索数据：

你有一个查询 WHERE user_id = 100 AND created_at >= '2025-01-01'。
ClickHouse查看稀疏索引，找到相关颗粒。
它发现user_id=100出现在颗粒1、2，可能还有3及之后。
但它不知道目标行在颗粒内的确切位置——因为索引只指向颗粒的开始。
因此，ClickHouse读取所有可能包含所需行的颗粒（有时会多读——这称为索引过滤）。

类比： 稀疏索引就像一本书的目录，每章有100页。目录显示：“第3章从第201页开始。”如果你需要第210页上的某个短语，你仍然必须阅读第201–300页的全部内容，因为不知道确切位置。在PostgreSQL中，B树索引会直接给出第210页。

为什么在ClickHouse中这很快？ 因为：

ClickHouse有选择地读取列——如果WHERE需要user_id，SELECT需要amount，它只读取这两列。
颗粒内的数据是压缩的，一次读取8192行非常高效（最小体积约64 KB，颗粒大小可通过index_granularity配置）。
对于分析型查询（读取数百万行），这种粒度是合适的。

4. 基数规则：低基数在前，高基数在后

基数是列中唯一值的数量。例如：

sport_id（运动类型：足球、冰球、网球）——基数为20（低）
market_id（投注市场：结果、总分、让球）——基数为1000（中）
created_at（精确到秒的时间）——基数为数十亿（高）

ClickHouse的黄金法则： 在ORDER BY中，低基数的列应放在高基数列之前。

为什么？ 因为稀疏索引在剪枝颗粒时会更有效。

错误键： ORDER BY (created_at, sport_id)

数据首先按时间排序。相邻行的sport_id会跳跃：足球、冰球、网球，然后又足球……
查询 WHERE sport_id = 1 迫使ClickHouse读取所有颗粒，因为sport_id=1分散在整个表中。

正确键： ORDER BY (sport_id, created_at)

首先，所有足球行（sport_id=1）按时间排序。然后所有冰球行（sport_id=2）——紧凑。
查询 WHERE sport_id = 1 在索引层面剪枝掉所有与足球无关的颗粒。ClickHouse只读取sport_id=1的颗粒。

类比： 想象整理一副扑克牌。如果先按花色（低基数——4种）排序，再按点数（高基数——13种），所有黑桃会在一起。如果反过来——先按点数，那么所有花色的A会分散在整副牌中。找到所有黑桃就困难了。

5. 博彩示例：如何选择正确的ORDER BY

我们来比较博彩公司投注表的两个选项。

选项A（错误）：ORDER BY (created_at, sport_id)

CREATE TABLE bets_bad
(
    sport_id    UInt8,       -- 1 = 足球, 2 = 冰球, 3 = 网球
    market_id   UInt32,      -- 投注市场ID
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);

典型查询性能：

-- 查询：过去一小时内所有足球投注
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN将显示：读取几乎所有颗粒，因为sport_id=1分散在时间线上

索引(created_at, sport_id)帮助不大，因为sport_id是第二列。ClickHouse可以使用前缀created_at，但随后必须在颗粒级别过滤sport_id，读取额外数据。

选项B（正确）：ORDER BY (sport_id, market_id, created_at)

CREATE TABLE bets_good
(
    sport_id    UInt8,
    market_id   UInt32,
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (sport_id, market_id, created_at);

相同查询：

-- 查询：过去一小时内足球投注
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN将显示：只读取sport_id = 1的颗粒，数量显著减少

为什么更好： ClickHouse可以通过索引立即找到sport_id = 1的块，在这些块内，数据按market_id和created_at排序。时间过滤created_at >= ...在这些块内的颗粒级别应用。

6. 等值 vs 范围：哪个更高效

对于ORDER BY中的列，存在效率层次：

等值（=）——最高效。如果搜索精确值，ClickHouse可以跳过整个颗粒块。
不等值（>=, <=, BETWEEN）——效率较低，但如果它是键中的最后一列，则仍可使用。
LIKE或其他函数——通常不使用索引（除非转换为范围）。

规则： 在ORDER BY中，等值条件的列应放在范围条件的列之前。

示例：键(user_id, created_at)

-- ✅ 很好：user_id = 等值（第一列），created_at >= 范围（第二列）
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';

-- ❌ 不好：created_at 范围（第一列），user_id = 等值（第二列）
-- 索引只能按created_at剪枝，但user_id必须在颗粒内过滤
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;

为什么？ 因为数据按(user_id, created_at)物理排序。一个user_id的所有记录紧凑存储，并按时间排序。如果按时间范围搜索，很容易。但如果先按时间搜索，一个user_id的记录分散在整个表中——无法通过索引剪枝。

类比： 想象一本电话簿，先按姓氏排序，再按名字。找到“所有伊万诺夫”很容易（姓氏是第一列）。找到“所有1990年后出生的人”则需要阅读整本书。

7. UInt8+UInt32+DateTime复合键 vs 仅DateTime

有时会想：“为什么不用ORDER BY created_at——简单明了？”让我们用博彩示例分析。

仪表盘实际需要的查询：

特定用户上周的投注：WHERE user_id = 123 AND created_at >= today() - 7
按运动统计某天：WHERE sport_id = 1 AND created_at = yesterday()
按市场聚合某小时：WHERE market_id = 100 AND created_at >= now() - 1 hour

选项1：ORDER BY (created_at)

CREATE TABLE bets_simple
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY created_at;

问题：

按user_id查询会很慢——必须全表扫描。
按sport_id查询——同样。

选项2：ORDER BY (user_id, sport_id, market_id, created_at)

CREATE TABLE bets_composite
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY (user_id, sport_id, market_id, created_at);

现在：

查询 WHERE user_id = 123 AND created_at >= ... —— 极好（使用前缀user_id）。
查询 WHERE sport_id = 1 AND created_at = ... —— 差，因为sport_id不是第一列。ClickHouse无法通过索引按sport_id剪枝。

折衷方案： 选择最频繁的过滤模式，将其列放在ORDER BY的开头。如果最常按user_id搜索，就把user_id放第一。如果更常按brand_id，就放那个。

经验法则： ORDER BY应包含至少2–4列。单列很少是最优的。

8. 如何使用EXPLAIN检查键效率

ClickHouse提供了强大的工具来分析索引使用情况。

EXPLAIN indexes = 1

-- 启用索引使用信息输出
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';

结果将显示类似：

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (user_id = 123) AND (created_at >= '2025-06-01')
        Used keys: (user_id, created_at)
        Granules: 15 / 1280

数字含义： 15 / 1280 —— 表中1280个颗粒中，只读取了15个。极好结果。如果显示1200 / 1280，则索引几乎没帮助。

system.query_log

系统表query_log存储每次查询的统计信息。用于索引分析最有用的列：

-- 查找慢查询并查看它们读取了多少行
SELECT 
    query,
    read_rows,          -- 读取的行数
    result_rows,        -- 返回的行数
    read_rows / result_rows AS efficiency,  -- 越接近1越好
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish' 
  AND query LIKE '%bets%'
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC;

如何解读：

read_rows / result_rows ≈ 1..10 —— 索引工作良好
read_rows / result_rows > 1000 —— 你为一行读取了数千行——索引糟糕
read_rows 接近表总行数 —— 全表扫描

system.query_log中的columns_read

SELECT 
    query,
    read_rows,
    written_rows,
    result_rows,
    columns_read,      -- 读取的列列表
    columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;

如果在columns_read中看到SELECT或WHERE中没有的列，ClickHouse正在读取额外数据（可能由于糟糕的ORDER BY）。

9. 博彩行业模式

模式1：按特定玩家查询

如果最频繁的查询是“显示用户的投注历史”，键(user_id, created_at)是理想的。

CREATE TABLE bets_by_user
(
    user_id     UInt64,
    created_at  DateTime,
    sport_id    UInt8,
    amount      Decimal(18,2)
)
ORDER BY (user_id, created_at);   -- 一个用户的所有投注紧凑并按时间排序

查询 WHERE user_id = 123 AND created_at BETWEEN ... 将只读取该用户的颗粒，数量很少。

模式2：多品牌平台

你有多个品牌（casino_A, casino_B），查询几乎总是包含brand_id。那么：

CREATE TABLE bets_multi_brand
(
    brand_id    UInt8,        -- 低基数（5个品牌）
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ORDER BY (brand_id, created_at);

查询 WHERE brand_id = 1 AND created_at >= ... 在索引层面剪枝掉所有其他品牌的数据。

模式3：按运动统计的仪表盘

如果报表按sport_id（足球、冰球）分组并按时间过滤：

CREATE TABLE bets_by_sport
(
    sport_id    UInt8,
    created_at  DateTime,
    user_id     UInt64,
    amount      Decimal(18,2)
)
ORDER BY (sport_id, created_at);

没有通用的键。 你必须选择一个或两个最频繁的查询模式，并针对它们优化。其他查询会变慢——这是不可避免的权衡。

10. 创建表后更改ORDER BY —— 不可能

这是最令人沮丧但最重要的知识。你不能通过ALTER等命令更改现有表的ORDER BY或PRIMARY KEY。

-- ❌ 不存在这样的命令
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at);   -- 错误！

为什么？ 因为行的物理顺序已经确定。要更改，你需要重建表。

如果发现错误怎么办？

方法1：创建新表，迁移数据，重命名

-- 1. 创建具有正确ORDER BY的新表
CREATE TABLE bets_new
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- 新键

-- 2. 迁移数据（如果表很大，可以异步）
INSERT INTO bets_new SELECT * FROM bets;

-- 3. 交换表（原子操作）
RENAME TABLE bets TO bets_old, bets_new TO bets;

-- 4. 验证一切正常，然后删除旧表
DROP TABLE bets_old;

方法2：使用物化视图（如果你可以同时以两种顺序存储数据）

-- 保留旧表用于某些查询
-- 创建具有不同ORDER BY的物化视图用于其他查询
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets;   -- 数据将被复制

方法3：接受并忍受糟糕的键（有时增加资源比迁移TB级数据更划算）

建议： 在创建包含大量数据（数十亿行）的表之前，始终在样本上测试ORDER BY。创建一个包含1000万行的副本，运行EXPLAIN indexes=1，测试不同查询。这将为你节省数周的痛苦。

下一步

现在你明白了ClickHouse中的ORDER BY不仅仅是排序，而是一个战略性索引。接下来的主题：

如何配置index_granularity —— 将颗粒大小从8192改为其他值（几乎不需要）。
分区 vs ORDER BY —— 何时分区有帮助，何时索引起作用。
跳数索引（布隆过滤器索引） —— 针对不在ORDER BY中的列的二级索引。
通过system.query_log分析慢查询 —— 深度剖析。

总结： ClickHouse中理想ORDER BY的公式：低基数和等值条件的列在前；高基数和范围条件的列在后。不要试图覆盖所有情况——选择最频繁的查询，忽略其余。永远不要忘记EXPLAIN indexes=1——ClickHouse开发者的最佳朋友。

← 上一篇: ClickHouse 中的分区：如何在文件夹级别管理数据
→ 下一篇: ClickHouse中的TTL：自动数据生命周期管理

— Editorial Team

ClickHouse中的ORDER BY和PRIMARY KEY：索引选择

ClickHouse中的ORDER BY与PRIMARY KEY：如何正确设置索引

1. 为什么ORDER BY是你在表中指定的最重要内容

2. PRIMARY KEY ⊆ ORDER BY —— 一条罕见的规则

3. 稀疏索引：每8192行一个条目（颗粒）

4. 基数规则：低基数在前，高基数在后

5. 博彩示例：如何选择正确的ORDER BY

选项A（错误）：ORDER BY (created_at, sport_id)

选项B（正确）：ORDER BY (sport_id, market_id, created_at)

6. 等值 vs 范围：哪个更高效

7. UInt8+UInt32+DateTime复合键 vs 仅DateTime

8. 如何使用EXPLAIN检查键效率

EXPLAIN indexes = 1

system.query_log

system.query_log中的columns_read

9. 博彩行业模式

模式1：按特定玩家查询

模式2：多品牌平台

模式3：按运动统计的仪表盘

10. 创建表后更改ORDER BY —— 不可能

下一步

社区

继续阅读

最近浏览

标签

主题

常见问题

ClickHouse中的ORDER BY与PRIMARY KEY：如何正确设置索引

1. 为什么ORDER BY是你在表中指定的最重要内容

2. PRIMARY KEY ⊆ ORDER BY —— 一条罕见的规则

3. 稀疏索引：每8192行一个条目（颗粒）

4. 基数规则：低基数在前，高基数在后

5. 博彩示例：如何选择正确的ORDER BY

选项A（错误）：ORDER BY (created_at, sport_id)

选项B（正确）：ORDER BY (sport_id, market_id, created_at)

6. 等值 vs 范围：哪个更高效

7. UInt8+UInt32+DateTime复合键 vs 仅DateTime

8. 如何使用EXPLAIN检查键效率

EXPLAIN indexes = 1

system.query_log

system.query_log中的columns_read

9. 博彩行业模式

模式1：按特定玩家查询

模式2：多品牌平台

模式3：按运动统计的仪表盘

10. 创建表后更改ORDER BY —— 不可能

下一步

社区

继续阅读

ClickHouse中的分区：策略与操作

ClickHouse中的CollapsingMergeTree：无需UPDATE即可更新

ClickHouse中的SummingMergeTree和AggregatingMergeTree

ClickHouse中的ReplacingMergeTree：完整指南

Oracle在耶路撒冷开设了一个地下云计算区域——以色列首个主权云

Matic机器人吸尘器：放弃激光雷达和AI导航

Insurights：管理健康保险的AI平台融资2200万美元

KAI和IAI：游荡弹药与防空压制的新概念

新格伦蓝色起源爆炸：对NASA阿尔忒弥斯和亚马逊柯伊伯的影响

英特尔20亿美元收购Habana Labs：对抗英伟达的战略

最近浏览

标签

主题

常见问题

分享本文