ClickHouse性能优化

一时失言乱红尘 2024-03-23 16:06 46阅读 0赞

#### 目录 ####

*  1 Explain查看执行计划优化sql
 *   *  1.1 基本语法
     *  1.2 案例实操
     *   *  1.2.1 查看 PLAIN
         *  1.2.2 AST 语法树
         *  1.2.3 SYNTAX 语法优化
         *  1.2.4 查看 PIPELINE
 *  2 ClickHouse建表优化
 *   *  2.1 数据类型
     *   *  2.1.1 时间字段的类型
         *  2.1.2 空值存储类型
     *  2.2 分区和索引
     *  2.3 表参数
     *  2.4 写入和删除优化
     *  2.5 常见配置
     *   *  2.5.1 CPU 资源
         *  2.5.2 内存资源
         *  2.5.3 存储
 *  3 ClickHouse语法优化规则
 *   *  3.1 准备测试用表
     *  3.2 COUNT 优化
     *  3.3 消除子查询重复字段
     *  3.4 谓词下推
     *  3.5 聚合计算外推
     *  3.6 聚合函数消除
     *  3.7 删除重复的 order by key
     *  3.8 删除重复的 limit by key
     *  3.9 删除重复的 USING Key
     *  3.10 标量替换
     *  3.11 三元运算优化
 *  4 ClickHouse查询优化
 *   *  4.1 单表查询
     *   *  4.1.1 Prewhere 替代 where
         *  4.1.2 数据采样
         *  4.1.3 列裁剪与分区裁剪
         *  4.1.4 orderby 结合 where、limit
         *  4.1.5 避免构建虚拟列
         *  4.1.6 uniqCombined 替代 distinct
         *  4.1.7 使用物化视图
         *  4.1.8 其他注意事项
     *  4.2 多表关联
     *   *  4.2.1 准备表和数据
         *  4.2.2 用 IN 代替 JOIN
         *  4.2.3 大小表 JOIN
         *  4.2.4 注意谓词下推（版本差异）
         *  4.2.5 分布式表使用 GLOBAL
         *  4.2.6 使用字典表
         *  4.2.7 多表查询小结

--------------------

## 1 Explain查看执行计划优化sql ##

![在这里插入图片描述][6a2bdbe2b48f43b4901ac36c322feaee.png]

在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到，并且只能真正执行 sql，在执行日志里面查看。在 20.6 版本引入了原生的执行计划的语法。在 20.6.3 版本成为正式版本的功能。  
本文档基于目前较新稳定版 21.7.3.14。  
目前官网最新的在线测试链接：https://play.clickhouse.com/play?user=play

### 1.1 基本语法 ###

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]

>  *  PLAN：用于查看执行计划，默认值。
>     
>      *  header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
>      *  description 打印计划中各个步骤的描述，默认开启，默认值 1；
>      *  actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0。
>  *  AST ：用于查看语法树;
>  *  SYNTAX：用于优化语法;
>  *  PIPELINE：用于查看 PIPELINE 计划。
>     
>      *  header 打印计划中各个步骤的 head 说明，默认关闭;
>      *  graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看；
>      *  actions 如果开启了 graph，紧凑打印打，默认开启。

注：PLAN 和 PIPELINE 还可以进行额外的显示设置，如上参数所示。

### 1.2 案例实操 ###

#### 1.2.1 查看 PLAIN ####

1.  简单查询

explain plan select arrayJoin([1,2,3,null,null]);

1.  复杂 SQL 的执行计划

explain select database,table,count(1) cnt from system.parts where database in ('datasets','system') group by database,table order by database,cnt desc limit 2 by database;

![在这里插入图片描述][a79f3c611a794c88a24c91435419346c.png]

1.  打开全部的参数的执行计划

EXPLAIN header=1, actions=1,description=1 SELECT number from system.numbers limit 10;

![在这里插入图片描述][7699d482131f4cd3906f55e3de7495cd.png]

#### 1.2.2 AST 语法树 ####

EXPLAIN AST SELECT number from system.numbers limit 10;

![img][]

#### 1.2.3 SYNTAX 语法优化 ####

1.  先做一次查询

SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'oldlu') FROM system.numbers limit 10;

1.  查看语法优化

EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'oldlu') FROM  system.numbers limit 10;

1.  开启三元运算符优化

SET optimize_if_chain_to_multiif = 1;

1.  再次查看语法优化

EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' :  'oldlu') FROM numbers(10);

1.  返回优化后的语句

SELECT multiIf(number = 1, \\'hello\\', number = 2, \\'world\\', \\'xyz\\')
       FROM numbers(10)

#### 1.2.4 查看 PIPELINE ####

EXPLAIN PIPELINE SELECT sum(number) FROM system.numbers_mt GROUP BY number % 20;

![在这里插入图片描述][ade8cd005f6c4bdb8e80b81162f6ec40.png]

//打开其他参数
    EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM system.numbers_mt GROUP BY number%20;

## 2 ClickHouse建表优化 ##

### 2.1 数据类型 ###

#### 2.1.1 时间字段的类型 ####

`建表时能用数值型或日期时间型表示的字段就不要用字符串`，全 String 类型在以 Hive为中心的数仓建设中常见，但 [ClickHouse][] 环境不应受此影响。  
虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 **DateTime 不需要经过函数转换处理，执行效率高、可读性好**。

create table t_type2(
    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);

#### 2.1.2 空值存储类型 ####

官方已经指出 [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;

查看存储的文件：（没有权限就用 root 用户）

![在这里插入图片描述][3944738b3e14429ea3ec7ffa0240e2e3.png]

官网说明：https://clickhouse.com/docs/zh/sql-reference/data-types/nullable/

### 2.2 分区和索引 ###

分区粒度根据业务特点决定，不宜过粗或过细。`一般选择按天分区`，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳。  
  必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，`一般在查询条件中经常被用来充当筛选条件的属性被纳入进来,且查询越频繁的字段越要靠前`；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳。  
  比如官方案例的 hits\_v1 表：

……
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY (CounterID, EventDate, intHash32(UserID))
    ……

visits\_v1 表：

……
    PARTITION BY toYYYYMM(StartDate)
    ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
    ……
    1234

### 2.3 表参数 ###

Index\_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整。  
  如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。（参考基础文档 4.4.5 数据 TTL）

### 2.4 写入和删除优化 ###

（1）尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力  
  （2）不要一次写入太多分区，或数据写入太快，数据写入太快会导致 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 默认为 true  
  在服务器内存充裕的情况下增加内存配额，一般通过 max\_memory\_usage 来实现  
  在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max\_bytes\_before\_external\_group\_by、max\_bytes\_before\_external\_sort 参数来实现。

### 2.5 常见配置 ###

配置项主要在 config.xml 或 users.xml 中， 基本上都在 users.xml 里

*  config.xml 的配置项  
    https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/
 *  users.xml 的配置项  
    https://clickhouse.tech/docs/en/operations/settings/settings/

#### 2.5.1 CPU 资源 ####

<table> 
 <thead> 
  <tr> 
   <th>配置</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>background_pool_size</td> 
   <td>后台线程池的大小，merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，默认值 16，<code>允许的前提下建议改成 cpu 个数的 2 倍（线程数）</code>。</td> 
  </tr> 
  <tr> 
   <td>background_schedule_pool_size</td> 
   <td>执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。默认 128，<code>建议改成 cpu 个数的 2 倍（线程数）</code>。</td> 
  </tr> 
  <tr> 
   <td>background_distributed_schedule_pool_size</td> 
   <td>设置为分布式发送执行后台任务的线程数，默认 16，<code>建议改成 cpu个数的 2 倍（线程数）</code>。</td> 
  </tr> 
  <tr> 
   <td>max_concurrent_queries</td> 
   <td>最大并发处理的请求数(包含 select,insert 等)，默认值 100，<code>推荐 150(不够再加)~300</code>。</td> 
  </tr> 
  <tr> 
   <td>max_threads</td> 
   <td>设置单个查询所能使用的最大 cpu 个数，<code>默认是 cpu 核数</code></td> 
  </tr> 
 </tbody> 
</table>

#### 2.5.2 内存资源 ####

<table> 
 <thead> 
  <tr> 
   <th>配置</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>max_memory_usage</td> 
   <td>此参数在 users.xml 中,表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给 OS，<code>比如 128G 内存的机器，设置为 100GB</code>。</td> 
  </tr> 
  <tr> 
   <td>max_bytes_before_external_group_by</td> 
   <td><code>一般按照 max_memory_usage 的一半设置内存</code>，当 group 使用内存超过阈值后会刷新到磁盘进行。因为 clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议 50GB。</td> 
  </tr> 
  <tr> 
   <td>max_bytes_before_external_sort</td> 
   <td>当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。</td> 
  </tr> 
  <tr> 
   <td>max_table_size_to_drop</td> 
   <td>此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除 50GB 以上的分区表会失败。<code>建议修改为 0</code>，这样不管多大的分区表都可以删除。</td> 
  </tr> 
 </tbody> 
</table>

#### 2.5.3 存储 ####

ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景 [SSD][] 会比普通机械硬盘快 2-3 倍。

## 3 ClickHouse语法优化规则 ##

ClickHouse的 SQL 优化规则是基于 RBO(Rule Based Optimization)，下面是一些优化规则

### 3.1 准备测试用表 ###

1.  上传官方的数据集  
    将 visits\_v1.tar 和 hits\_v1.tar 上传到虚拟机，解压到 clickhouse 数据路径下

// 解压到 clickhouse 数据路径
       sudo tar -xvf hits_v1.tar -C /var/lib/clickhouse
       sudo tar -xvf visits_v1.tar -C /var/lib/clickhouse
       
       //修改所属用户
       sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets
       sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets

1.  重启 clickhouse-server

sudo clickhouse restart

1.  执行查询

clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"
       clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"

> 注意：官方的 tar 包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便。  
> hits\_v1 表有 130 多个字段，880 多万条数据  
> visits\_v1 表有 180 多个字段，160 多万条数据

### 3.2 COUNT 优化 ###

在调用 count 函数时，如果使用的是 count() 或者 count(\*)，且没有 where 条件，则会直接使用 system.tables 的 total\_rows，例如:

EXPLAIN SELECT count()FROM datasets.hits_v1;
    
    Union
      Expression (Projection)
        Expression (Before ORDER BY and SELECT)
          MergingAggregated
            ReadNothing (Optimized trivial count)

注意 Optimized trivial count ，这是对 count 的优化, `只要括号中不写具体的字段就会进行优化`。  
如果 count 具体的列字段，则不会使用此项优化：

EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;
    
    Union
      Expression (Projection)
        Expression (Before ORDER BY and SELECT)
          Aggregating
            Expression (Before GROUP BY)
              ReadFromStorage (Read from MergeTree)

### 3.3 消除子查询重复字段 ###

*  下面语句子查询中有两个重复的 id 字段，会被去重:

EXPLAIN SYNTAX SELECT 
      		a.UserID,
      		b.VisitID,
      		a.URL,
      		b.UserID
      	FROM
      		hits_v1 AS a 
      	LEFT JOIN ( 
      		SELECT 
      			UserID, 
      			UserID as HaHa, 
      			VisitID 
      		FROM visits_v1) AS b 
      		USING (UserID)
      	limit 3;

*  返回优化语句：

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.4 谓词下推 ###

当 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';
    
    //返回优化语句
    SELECT UserID
    FROM hits_v1
    WHERE UserID = \'8585742290196126178\'
    GROUP BY UserID

子查询也支持谓词下推：

EXPLAIN SYNTAX
    SELECT *
    FROM 
    (
    	SELECT UserID
    	FROM visits_v1
    )
    WHERE UserID = '8585742290196126178'
    
    //返回优化后的语句
    SELECT UserID
    FROM 
    (
    	SELECT UserID
    	FROM visits_v1
    	WHERE UserID = \'8585742290196126178\'
    )
    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'
    	
    //返回优化后的语句
    SELECT UserID
    FROM 
    	(
    		SELECT UserID
    		FROM 
    		(
    			SELECT UserID
    			FROM visits_v1
    			WHERE UserID = \'8585742290196126178\'
    		)
    		WHERE UserID = \'8585742290196126178\'
    		
    		UNION ALL
    		
    		SELECT UserID
    		FROM 
    		(
    			SELECT UserID
    			FROM visits_v1
    			WHERE UserID = \'8585742290196126178\'
    		)
    		WHERE UserID = \'8585742290196126178\'
    	)
    WHERE UserID = \'8585742290196126178\'

### 3.5 聚合计算外推 ###

聚合函数内的计算，会外推，例如：

EXPLAIN SYNTAX
    SELECT sum(UserID * 2)
    FROM visits_v1
    
    //返回优化后的语句
    SELECT sum(UserID) * 2
    FROM visits_v1

### 3.6 聚合函数消除 ###

如果对聚合键，也就是 group by key 使用 min、max、any 聚合函数，则将函数消除，  
例如：

EXPLAIN SYNTAX
    SELECT
    	sum(UserID * 2),
    	max(VisitID),
    	max(UserID)
    FROM visits_v1
    GROUP BY UserID
    
    //返回优化后的语句
    SELECT
    	sum(UserID) * 2,
    	max(VisitID),
    	UserID
    FROM visits_v1
    GROUP BY UserID

### 3.7 删除重复的 order by key ###

例如下面的语句，重复的聚合键 id 字段会被去重:

EXPLAIN SYNTAX
    	SELECT *
    	FROM visits_v1
    	ORDER BY
    		UserID ASC,
    		UserID ASC,
    		VisitID ASC,
    		VisitID ASC
    	
    //返回优化后的语句：
    select ……
    FROM visits_v1
    ORDER BY 
    	UserID ASC,
    	VisitID ASC

### 3.8 删除重复的 limit by key ###

例如下面的语句，重复声明的 name 字段会被去重：

EXPLAIN SYNTAX
    	SELECT *
    	FROM visits_v1
    	LIMIT 3 BY
    		VisitID,
    		VisitID
    	LIMIT 10
    	
    //返回优化后的语句：
    select ……
    FROM visits_v1
    LIMIT 3 BY VisitID
    LIMIT 10

### 3.9 删除重复的 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)
    
    //返回优化后的语句：
    SELECT 
    	UserID,
    	UserID,
    	VisitID,
    	URL,
    	b.UserID
    FROM hits_v1 AS a
    ALL LEFT JOIN visits_v1 AS b USING (UserID)

### 3.10 标量替换 ###

如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的 total\_disk\_usage 字段：

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;
    
    //返回优化后的语句：
    WITH CAST(0, \'UInt64\') 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

### 3.11 三元运算优化 ###

如果开启了 optimize\_if\_chain\_to\_multiif 参数，三元运算符会被替换成 multiIf 函数，  
例如：

EXPLAIN SYNTAX 
    	SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'oldlu') 
    	FROM numbers(10) 
    	settings optimize_if_chain_to_multiif = 1;
    
    //返回优化后的语句：
    SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'oldlu\')
    FROM numbers(10)
    SETTINGS optimize_if_chain_to_multiif = 1

## 4 ClickHouse查询优化 ##

### 4.1 单表查询 ###

#### 4.1.1 Prewhere 替代 where ####

Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere `只支持*MergeTree 族(合并树)`系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。  
  当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。

> where：先将所有数据所有行取出，然后用 where 后的条件进行匹配  
> Prewhere：先将进行 Prewhere 后的所有字段的列取出，然后将查询语句中使用到的字段，进行补全，然后再使用 Prewhere 的条件进行匹配，从而少读取了一些用不到的字段

在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

#关闭 where 自动转 prewhere(默认情况下， where 条件会自动优化成 prewhere)
    set optimize_move_to_prewhere=0; 
    # 使用 where
    select WatchID, 
    JavaEnable, 
    Title, 
    ……
    from datasets.hits_v1 where UserID='3198390223272470366';
    # 使用 prewhere 关键字
    select WatchID, 
    JavaEnable, 
    Title,
    ……
    from datasets.hits_v1 prewhere UserID='3198390223272470366';

默认情况，我们肯定不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：

*  使用常量表达式
 *  使用默认值为 alias 类型的字段
 *  包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
 *  select 查询的列字段和 where 的谓词相同
 *  使用了主键字段

#### 4.1.2 数据采样 ####

通过采样运算可极大提升数据分析的性能—— `SAMPLE N`，其中 0≥N≥1， 0即为 0%，1即为 100%，以此类推

SELECT Title,count(*) AS PageViews 
    FROM hits_v1
    SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数
    WHERE CounterID =57
    GROUP BY Title
    ORDER BY PageViews DESC LIMIT 1000

采样修饰符`只有在 MergeTree engine 表中才有效`，且在创建表时需要指定采样策略。

#### 4.1.3 列裁剪与分区裁剪 ####

数据量太大时应避免使用 select \* 操作，查询的性能会与查询的字段大小和数量成线性表换，`字段越少，消耗的 io 资源越少，性能就会越高`。

反例：
    select * from datasets.hits_v1;
    正例：
    select WatchID, 
    JavaEnable, 
    Title, 
    ……
    from datasets.hits_v1;

`分区裁剪就是只读取需要的分区，在过滤条件中指定`。

select WatchID,
    JavaEnable, 
    Title, 
    ……
    from datasets.hits_v1
    where EventDate='2014-03-23';

#### 4.1.4 orderby 结合 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

#### 4.1.5 避免构建虚拟列 ####

如非必须，`尽量不要在结果集上构建虚拟列`，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

反例：
    SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
    
    正例：拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
    SELECT Income,Age FROM datasets.hits_v1;

#### 4.1.6 uniqCombined 替代 distinct ####

性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 `2%左右的数据误差`，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact精确去重。  
  不建议在千万级不同数据上执行 distinct 去重查询，改为`近似去重 uniqCombined`

反例：
    select count(distinct rand()) from hits_v1;
    
    正例：
    SELECT uniqCombined(rand()) from datasets.hits_v1

#### 4.1.7 使用物化视图 ####

*  SQL 的视图：只是把复杂的查询逻辑记录下来的，但是并没有保存对应的数据
 *  物化视图：不仅把查询逻辑记录下来，还记录下来数据

#### 4.1.8 其他注意事项 ####

1.  查询熔断  
    为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。
2.  关闭虚拟内存  
    物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。
3.  配置 join\_use\_nulls  
    为每一个账户添加 join\_use\_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。
4.  批量写入时先排序  
    批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对新导入的数据进行合并，从而影响查询性能。
5.  关注 CPU  
    cpu 一般在 50%左右会出现查询波动，达到 70%会出现大范围的查询超时，cpu 是最关键的指标，要非常关注。

### 4.2 多表关联 ###

#### 4.2.1 准备表和数据 ####

#创建小表
    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;

#### 4.2.2 用 IN 代替 JOIN ####

*  ClickHouse 处理 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 table hits_v2
    select a.* from hits_v1 a left join visits_v1 b on a. CounterID=b.CounterID;

#### 4.2.3 大小表 JOIN ####

`多表 join 时要满足小表在右的原则`，右表关联时被加载到内存中与左表进行比较，ClickHouse中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

1.  小表在右

insert into table hits_v2
    select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b.CounterID;

1.  大表在右

insert into table hits_v2
    select a.* from visits_v2 b left join hits_v1 a on a. CounterID=b.CounterID;

#### 4.2.4 注意谓词下推（版本差异） ####

ClickHouse 在 `join 查询时不会主动发起谓词下推的操作`，需要每个[子查询][Link 1]提前完成过滤操作，也就是需要在 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;

#### 4.2.5 分布式表使用 GLOBAL ####

`两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字`，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N是该分布式表的分片数量），这就是查询放大，会带来很大开销。

#### 4.2.6 使用字典表 ####

将一些需要关联分析的业务创建成字典表进行 join 操作，前提是字典表不宜太大，因为字典表会常驻内存

#### 4.2.7 多表查询小结 ####

*  Join 原理：先把右表加载到内存，再去一一匹配左表
 *  非必要不使用 Join
 *  若不得不使用到 Join，优化方式：
    
     *  将小表放右边
     *  能过滤的先过滤，特别是针对右表
     *  特殊场景可以考虑使用字典表

[6a2bdbe2b48f43b4901ac36c322feaee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/446d15fbf4074aba9c9a042655624fe1.png
[a79f3c611a794c88a24c91435419346c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/cfc9f52ee37e4f91ace9ff626ef292f9.png
[7699d482131f4cd3906f55e3de7495cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/84f87f32d11a49a18dbe68307cb263d3.png
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/55e4a2dabf334297883ea23a8eea3519.png
[ade8cd005f6c4bdb8e80b81162f6ec40.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/c0d7f9ec49d5497687d3f48ff89320b9.png
[ClickHouse]: https://so.csdn.net/so/search?q=ClickHouse&spm=1001.2101.3001.7020
[Nullable]: https://so.csdn.net/so/search?q=Nullable&spm=1001.2101.3001.7020
[3944738b3e14429ea3ec7ffa0240e2e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/13468dcad3df4ffea8b100c7ffe29448.png
[SSD]: https://so.csdn.net/so/search?q=SSD&spm=1001.2101.3001.7020
[Link 1]: https://so.csdn.net/so/search?q=%E5%AD%90%E6%9F%A5%E8%AF%A2&spm=1001.2101.3001.7020