文章目录
- ClickHouse从入门到精通（三）
- ClickHouse 高级篇
- Explain 查看执行计划
- 1. 基本语法
- 2. 使用
- 建表优化
- 1. 数据类型
- 时间字段类型
- 空值存储类型
- 2. 分区和索引
- 3. 表参数
- 4. 写入和删除优化
- 5. 常见配置
- ClickHouse 语法优化规则
- 0. 准备测试表
- 1. count 优化
- 2. 消除子查询重复字段
- 3. 谓词下推
- 4. 聚合计算外推
- 5. 聚合函数消除
- 6. 删除重复的 order by key
- 7. 删除重复的 limit by key
- 8. 删除重复的 using key
- 9. 标量替换
- 10. 三元运算优化
- 查询优化
- 1. 单表查询
- prewhere替代where
- 数据采样
- 列裁剪与分区裁剪
- order by 结合 where、limit
- 避免构建虚拟列
- uniqCombined 替代 distinct
- 使用物化视图
- 其它注意事项
- 2. 多表关联
- 用 IN 代替 JOIN
- 大小表 JOIN
- 注意谓词下推(版本差异)
- 分布式表使用 global
- 使用字典表
- 提前过滤
ClickHouse从入门到精通（三）
ClickHouse 高级篇
Explain 查看执行计划
- 在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到，并且只能真正执行 sql，在执行日志里面查看。在 20.6 版本引入了原生的执行计划的语法。在 20.6.3 版本成为正式版本的功能。
- Demo 使用：https://play.clickhouse.com/play?user=play
- 本文档基于当前最新版本 22.3.3.44
1. 基本语法
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE | TABLE OVERRIDE] [setting = value, ...]
[
SELECT ... |
tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
]
[FORMAT ...]- 举个🌰
2. 使用
- AST：用于查看语法树。支持所有类型的查询，不仅仅是
select
EXPLAIN AST SELECT 1;
┌─explain───────────────────────────┐
│ SelectWithUnionQuery (children 1) │
│ ExpressionList (children 1) │
│ SelectQuery (children 1) │
│ ExpressionList (children 1) │
│ Literal UInt64_1 │
└───────────────────────────────────┘
EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();
┌─explain────────────────────────────┐
│ AlterQuery t1 (children 2) │
│ ExpressionList (children 1) │
│ AlterCommand DELETE (children 1) │
│ Function equals (children 1) │
│ ExpressionList (children 2) │
│ Identifier date │
│ Function today (children 1) │
│ ExpressionList │
│ Identifier t1 │
└────────────────────────────────────┘- SYNTAX：用于优化语法。
EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;
┌─explain────────────────────────────┐
│ SELECT │
│ `--a.number` AS `a.number`, │
│ `--b.number` AS `b.number`, │
│ number AS `c.number` │
│ FROM │
│ ( │
│ SELECT │
│ number AS `--a.number`, │
│ b.number AS `--b.number` │
│ FROM system.numbers AS a │
│ CROSS JOIN system.numbers AS b │
│ ) AS `--.s` │
│ CROSS JOIN system.numbers AS c │
└────────────────────────────────────┘- PLAN：用于查看执行计划，默认值。
- header：打印计划中各个步骤的 header 说明，默认值 0 关闭。
- description：打印计划中各个步骤的描述，默认值 1 开启。
- indexes：显示已使用的索引、已过滤部分的数量以及应用的每个索引的已过滤颗粒的数量。默认值 0 关闭。支持合并树表。
- actions：打印计划中各个步骤的详细信息，默认值 0 关闭。
- json：以JSON格式将查询计划步骤打印为一行，默认值 0 关闭。建议使用 TSVRaw 格式，以避免不必要的转义。
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
┌─explain───────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromStorage (SystemNumbers) │
└───────────────────────────────────────────────────────────────────────────────┘
- PIPELINE：用于查看 PIPELINE 计划
- header：打印计划中各个步骤的 header 说明，默认值 0 关闭。
- graph：用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合 graphviz 查看。
- compact：如果开启了 graph，紧凑打印打，默认开启。
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;
┌─explain───────────────────────┐
│ (Expression) │
│ ExpressionTransform │
│ (Aggregating) │
│ AggregatingTransform │
│ (Expression) │
│ ExpressionTransform │
│ (SettingQuotaAndLimits) │
│ (ReadFromStorage) │
│ Limit │
│ Numbers 0 → 1 │
└───────────────────────────────┘
- ESTIMATE：显示处理查询时要从表中读取的估计行数、标记数和分区数。使用MergeTree族中的表。
-- 创建表
CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;
-- 插入数据
INSERT INTO ttt SELECT number FROM numbers(128);
-- 优化表
OPTIMIZE TABLE ttt;
EXPLAIN ESTIMATE SELECT * FROM ttt;
┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
│ test │ ttt │ 1 │ 128 │ 8 │
└──────────┴───────┴───────┴──────┴───────┘- TABLE OVERRIDE：显示通过表函数访问的表结构上的表重写的结果。进行一些验证，如果重写会导致某种失败，则会引发异常。
-- 在远程 MySQL 创建表
CREATE TABLE test.tbl (
id INT PRIMARY KEY,
created DATETIME DEFAULT now()
);
EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'test', 'tbl2', 'root', '123456')
PARTITION BY toYYYYMM(assumeNotNull(created));
┌─explain─────────────────────────────────────────────────┐
│ PARTITION BY uses columns: `created` Nullable(DateTime) │
└─────────────────────────────────────────────────────────┘建表优化
1. 数据类型
时间字段类型
- 建表时能用数值型或日期时间型表示的字段就不要用字符串，全 String 类型在以 Hive 为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。
- 虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。
create table t_type (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Int32
) engine = ReplacingMergeTree(create_time)
partition by toYYYYMMDD(toDate(create_time)) -- 需要转换一次，否则报错
primary key (id)
order by (id, sku_id);
空值存储类型
- 官方已经指出 Nullable 类型几乎总是会拖累性能，因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1 表示没有商品ID）。
-- 创建表
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog;
-- 插入数据
INSERT INTO t_null VALUES (1, NULL), (2, 3);
-- 查询
SELECT x + y FROM t_null;
- 官网说明：https://clickhouse.com/docs/zh/sql-reference/data-types/nullable/
2. 分区和索引
- 分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳。
- 必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by
- 可以是单一维度，也可以是组合维度的索引；
- 通常需要满足高级列在前、查询频率大的在前原则；
- 基数特别大的不适合做索引列，如主键ID、用户表的 userid 字段；
- 通常筛选后的数据满足在百万以内为最佳。
-- 官方案例 hits_v1 表
...
PARTTION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
...
-- 官方案例 visits_v1 表
...
PARTTION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
...3. 表参数
- Index_granularity
- 如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。
4. 写入和删除优化
- 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力。
- 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）。
写入过快报错，报错信息
\1. Code: 252, e.displayText() = DB::Exception: Too many parts(304). Merges are processing significantly slower than inserts
\2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query) exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888 bytes), maximum: 9.31 GiB
- ”Too many parts“处理：
- 使用 WAL 预写日志(in_memory_parts_enable_wal 默认开启)，提高写入性能；
- ”Memory limit“处理：
- 在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现；
- 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现。
5. 常见配置
- 配置项主要在 config.xml 或 users.xml 中， 基本上都在 users.xml 里
- config.xml 配置项：https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/
- user.xml 配置项：https://clickhouse.com/docs/en/operations/settings/settings/
- CPU 资源：
配置 | 描述 |
background_pool_size | 后台线程池的大小，merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，默认值 16，建议改成 cpu 个数的 2 倍(线程数)。 |
background_schedule_pool_size | 执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。默认 128，建议改成 cpu 个数的 2 倍(线程数)。 |
background_distributed_schedule_pool_size | 设置为分布式发送执行后台任务的线程数，默认 16，建议改成 cpu 个数的 2 倍(线程数)。 |
max_concurrent_queries | 最大并发处理的请求数(包含 select,insert 等)，默认值 100，推荐 150(不够再加)~300。 |
max_threads | 设置单个查询所能使用的最大 cpu 个数，默认是 cpu 核数。 |
- 内存资源：
配置 | 描述 |
max_memory_usage | 此参数在 users.xml 中，表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给 OS，比如 128G 内存的机器，设置为 100GB。 |
max_bytes_before_external_group_by | 一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行。因为 clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议 50GB。 |
max_bytes_before_external_sort | 当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。 |
max_table_size_to_drop | 此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除 50GB 以上的分区表会失败。建议修改为 0，这样不管多大的分区表都可以删除。 |
- 存储：ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。
ClickHouse 语法优化规则
- ClickHouse 的 SQL 优化规则是基于 RBO(Rule Based Optimization)，下面是一些优化规则
0. 准备测试表
- 下载官方测试数据集到 node01，并解压到 clickhouse 数据目录下
# 下载
wget https://datasets.clickhouse.com/hits/partitions/hits_v1.tar
wget https://datasets.clickhouse.com/visits/partitions/visits_v1.tar
# 解压缩
tar -xvf hits_v1.tar -C /var/lib/clickhouse
tar -xvf visits_v1.tar -C /var/lib/clickhouse
# 修改所属用户
chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets
chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets- 重启 clickhouse-server，并执行查询：
clickhouse-client --password 123456 --query "select count(*), (select count(*) from system.columns where database = 'datasets' and table = 'hits_v1') from datasets.hits_v1"
clickhouse-client --password 123456 --query "select count(*), (select count(*) from system.columns where database = 'datasets' and table = 'visits_v1') from datasets.visits_v1"官方的 tar 包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便。
- hits_v1 表有 133 个字段，880 多万条数据；visits_v1 表有 181 个字段，160 多万条数据。
1. count 优化
- 在调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows，如果 count 具体的列字段，则不会使用此项优化。
2. 消除子查询重复字段
- 下面语句子查询中有两个重复的 id 字段，会被去重
explain syntax select a.UserID, b.VisitID, a.URL, b.UserID from hits_v1 as a left join (select UserID, UserID as uId, VisitID from visits_v1) as b using (UserID) limit 3;
-- 返回优化语句
┌─explain───────────────┐
│ SELECT │
│ UserID, │
│ VisitID, │
│ URL, │
│ b.UserID │
│ FROM hits_v1 AS a │
│ ALL LEFT JOIN │
│ ( │
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM visits_v1 │
│ ) AS b USING (UserID) │
│ LIMIT 3 │
└───────────────────────┘3. 谓词下推
- 当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。例如下面的查询，HAVING name 变成了 WHERE name，在 group by 之前过滤。
explain syntax select UserID from hits_v1 group by UserID having UserID = '8585742290196126178';
- 子查询也支持谓词下推：
explain syntax select * from (select UserID from visits_v1) where UserID = '8585742290196126178';
- 再来一个复杂的例子：
explain syntax select * from (
select * from (select UserID from visits_v1)
union all
select * from (select UserID from visits_v1)
) where UserID = '8585742290196126178';
4. 聚合计算外推
- 聚合函数内的计算，会外推
explain syntax select sum(UserID * 2) from visits_v1;
-- 优化后的语句
┌─explain────────────────┐
│ SELECT sum(UserID) * 2 │
│ FROM visits_v1 │
└────────────────────────┘5. 聚合函数消除
- 如果对聚合键，也就是 group by key 使用 min、max、any 聚合函数，则将函数消除。
explain syntax select sum(UserID * 2), max(VisitID), max(UserID) from visits_v1 group by UserID;
-- 优化后的语句
┌─explain──────────────┐
│ SELECT │
│ sum(UserID) * 2, │
│ max(VisitID), │
│ UserID │
│ FROM visits_v1 │
│ GROUP BY UserID │
└──────────────────────┘6. 删除重复的 order by key
- 重复的聚合键 id 字段会被去重
explain syntax select * from visits_v1 order by UserID asc, UserID asc, VisitID asc, VisitID asc;
-- 优化后的语句
┌─explain───────────────────────────────────┐
│ SELECT │
│ ...... │
│ FROM visits_v1 │
│ ORDER BY │
│ UserID ASC, │
│ VisitID ASC │
└───────────────────────────────────────────┘7. 删除重复的 limit by key
- 重复声明的 name 字段会被去重
explain syntax select * from visits_v1 limit 3 by VisitID, VisitID limit 10;
-- 优化后的语句
┌─explain───────────────────────────────────┐
│ SELECT │
│ ...... │
│ FROM visits_v1 │
│ LIMIT 3 BY VisitID │
│ LIMIT 10 │
└───────────────────────────────────────────┘8. 删除重复的 using key
- 重复的关联键 id 字段会被去重
explain syntax select a.UserID, a.UserID, b.VisitID, a.URL, b.UserID from hits_v1 as a left join visits_v1 as b using(UserID, UserID);
-- 优化后的语句
┌─explain─────────────────────────────────────┐
│ SELECT │
│ UserID, │
│ UserID, │
│ VisitID, │
│ URL, │
│ b.UserID │
│ FROM hits_v1 AS a │
│ ALL LEFT JOIN visits_v1 AS b USING (UserID) │
└─────────────────────────────────────────────┘9. 标量替换
- 如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的 total_disk_usage 字段：
-- 统计各个表使用disk的情况
explain syntax with (select sum(bytes) from system.parts where active) as total_disk_usage
select (sum(bytes) / total_disk_usage) * 100 as table_disk_usage, table from system.parts group by table order by table_disk_usage desc limit 10;
-- 优化后的语句
┌─explain─────────────────────────────────────────────────────────────────────────┐
│ WITH identity(_CAST(0, 'Nullable(UInt64)')) AS total_disk_usage │
│ SELECT │
│ (sum(bytes_on_disk AS bytes) / total_disk_usage) * 100 AS table_disk_usage, │
│ table │
│ FROM system.parts │
│ GROUP BY table │
│ ORDER BY table_disk_usage DESC │
│ LIMIT 10 │
└─────────────────────────────────────────────────────────────────────────────────┘10. 三元运算优化
- 如果开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数，例如：
explain syntax select number = 1 ? 'hello' : (number = 2 ? 'world' : 'hehe') from numbers(10) settings optimize_if_chain_to_multiif = 1;
-- 优化后的语句
┌─explain──────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'hehe') │
│ FROM numbers(10) │
│ SETTINGS optimize_if_chain_to_multiif = 1 │
└──────────────────────────────────────────────────────────────────┘查询优化
1. 单表查询
prewhere替代where
- prewhere 和 where 语句的作用相同，都是用来过滤数据。不同之处在于 prewhere 只支持 *MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。
- 当查询列明显多于筛选列时使用 prewhere 可十倍提升查询性能，prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。
- 在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。
- 默认情况下， where 条件会自动优化成 prewhere。
-- 为了演示对比，先关闭 where 自动转 prewhere
set optimize_move_to_prewhere = 0;
-- 使用 where
select WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID,
CounterClass,
OS,
UserAgent,
URL,
Referer,
URLDomain,
RefererDomain,
Refresh,
IsRobot,
RefererCategories,
URLCategories,
URLRegions,
RefererRegions,
ResolutionWidth,
ResolutionHeight,
ResolutionDepth,
FlashMajor,
FlashMinor,
FlashMinor2
from datasets.hits_v1 where UserID='3198390223272470366';
-- 使用 prewhere 关键字
select WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID,
CounterClass,
OS,
UserAgent,
URL,
Referer,
URLDomain,
RefererDomain,
Refresh,
IsRobot,
RefererCategories,
URLCategories,
URLRegions,
RefererRegions,
ResolutionWidth,
ResolutionHeight,
ResolutionDepth,
FlashMajor,
FlashMinor,
FlashMinor2
from datasets.hits_v1 prewhere UserID='3198390223272470366';
- 默认情况，我们肯定不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：
- 使用常量表达式；
- 使用默认值为 alias 类型的字段；
- 包含了 arrayJoin、globalIn、globalNotIn 或 indexHint 的查询；
- select 查询的列字段和 where 的谓词相同；
select UserID from datasets.hits_v1 where UserID = '3198390223272470366';- 使用了主键字段。
数据采样
- 有时候一些统计不要求完全的精准度，通过采样运算可极大提升数据分析的性能
select Title, count(*) as PageViews from hits_v1
sample 0.1 -- 代表采样 10% 的数据，也可以是具体的条数
where CounterID = 57
group by Title
order by PageViews desc limit 1000;
select Title, count(*) as PageViews from hits_v1
where CounterID = 57
group by Title
order by PageViews desc limit 1000;
- 采样修饰符只有在 MergeTree 引擎表中才有效，且在创建表时需要指定采样策略。
列裁剪与分区裁剪
- 数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性变换，字段越少，消耗的 io 资源越少，性能就会越高。
-- 反例
select * from datasets.hits_v1;
-- 正例
select WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID
from datasets.hits_v1;- 分区裁剪就是只读取需要的分区，在过滤条件中指定。
select WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID
from datasets.hits_v1
where EventDate='2014-03-23';order by 结合 where、limit
- 千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用。
-- 正例
select UserID, Age from hits_v1 where CounterID = 57 order by Age desc limit 1000;
-- 反例
select UserID, Age from hits_v1 order by Age desc;
避免构建虚拟列
- 如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。
-- 反例
select Income, Age, Income/Age as IncRate from datasets.hits_v1;
-- 正例: 查出 Income, Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
select Income, Age from datasets.hits_v1;
uniqCombined 替代 distinct
- 性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2% 左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact 精确去重。
- 不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined。
-- 反例
select count(distinct rand()) from hits_v1;
explain syntax select count(distinct rand()) from hits_v1;
┌─explain──────────────────┐
│ SELECT uniqExact(rand()) │
│ FROM hits_v1 │
└──────────────────────────┘
-- 正例
select uniqCombined(rand()) from datasets.hits_v1
使用物化视图
- 后面再详细介绍。
其它注意事项
- 查询熔断：为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。
- 关闭虚拟内存：物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。
- 配置 join_use_nulls：为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。
- 批量写入时先排序：批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对新导入的数据进行合并，从而影响查询性能。
- 关注 CPU：cpu 一般在 50%左右会出现查询波动，达到 70%会出现大范围的查询超时，cpu 是最关键的指标，要非常关注。
2. 多表关联
- 准备表和数据
-- 创建小表
CREATE TABLE visits_v2 ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;
-- 创建 join 结果表：避免控制台疯狂打印数据
CREATE TABLE hits_v2 ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0;用 IN 代替 JOIN
- 当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN。
insert into hits_v2 select a.* from hits_v1 a where a.CounterID in (select CounterID from visits_v1);
-- 反例：使用join
insert into hits_v2 select a.* from hits_v1 a left join visits_v1 b on a.CounterID = b.CounterID;大小表 JOIN
- 多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。
-- 小表在右
insert into table hits_v2 select a.* from hits_v1 a left join visits_v2 b on a.CounterID = b.CounterID;
-- 大表在右
insert into table hits_v2 select a.* from visits_v2 b left join hits_v1 a on a.CounterID = b.CounterID;注意谓词下推(版本差异)
- ClickHouse 在 join 查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能的差异）
explain syntax select a.* from hits_v1 a left join visits_v2 b on a.CounterID = b.CounterID having a.EventDate = '2014-03-17';
explain syntax select a.* from hits_v1 a left join visits_v2 b on a.CounterID = b.CounterID having b.StartDate = '2014-03-17';
insert into hits_v2 select a.* from hits_v1 a left join visits_v2 b on a.CounterID=b.CounterID where a.EventDate = '2014-03-17';
insert into hits_v2 select a.* from (select * from hits_v1 where EventDate = '2014-03-17') a left join visits_v2 b on a.CounterID = b.CounterID;分布式表使用 global
- 两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N是该分布式表的分片数量），这就是查询放大，会带来很大开销。
使用字典表
- 将一些需要关联分析的业务创建成字典表进行 join 操作，前提是字典表不宜太大，因为字典表会常驻内存。
提前过滤
- 通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
