clickhouse的索引结构和查询优化

- 日理万妓 2022-03-28 06:19 924阅读 0赞

clickhouse存在很多引擎，下面的所有内容基于[MergeTree][]引擎

首先看下官网的主键相关内容：

索引效用实例-以MergeTree  为例  
MergeTree  系列的引擎，数据是由多组部分文件组成的，一般来说，每个月（译者注：CK目前最小分区单元是月）会有几个部分文件（这里的部分就是块）。  
每一个部分的数据，是按照主键进行字典序排列。例如，如果你有一个主键是（CounterID，Date），数据行会首先按照CounterID排序，如果CounterID相同，按照日期排序。  
主键的数据结构，看起来像是标记文件组成的矩阵，这个标记文件就是每间隔index\_granularity（索引粒度）行的主键值。  
MergeTree引擎中，的默认index\_granularity设置的英文8192。  
主键是（CounterID，Date）的存储示意图如下：

![20181219171346716][]

首先按照CounterID排序，如果CounterID相同，按照日期排序

*  主键是有序数据的稀疏索引。我们用图的方式看一部分的数据（原则上，图中应该保持标记的平均长度，但是用ASCI码的方式不太方便）。
 *  标记文件，就像一把尺子一样。主键对于范围查询的过滤效率非常高。对于查询操作，CK会读取一组可能包含目标数据的标记文件。
 *  例如，如果你的查询条件是CounterID IN（'a'，'h'），服务器将会读取标记文件为\[0,3\]和\[6,8\]之间对应的数据文件。
 *  如果你的查询条件是CounterID IN（'a'，'h'）并且指定了Date = 3，服务器将会读取标记文件为\[1,3\]和\[7,8\}之间对应的数据文件。
 *  有时，主键的过滤效果并不是很好，比如，只有第二列出现在查询条件中：
 *  如果查询条件只是Date = 3，服务器讲读取\[1,10）之间对应的数据文件。
 *  在上述例子中，标记文件除了0，其他90％的数据都需要扫描，虽然索引过滤效果不好，但是，仍然是可以跳过一些数据的。
 *  另一方面，如果每个CounterID对应多条数据，索引将会跳过更多的日期数据。（???）
 *  综合来讲，使用索引，总是会比全表扫描要高效一些的。

关于主键还有以下几点需要说明

*  稀疏索引会读取很多不必要的数据：读取主键的每一个部分，会多读取index\_granularity \* 2的数据。这对于稀疏索引来说很正常，也没有必要减少index\_granularity的值.ClickHouse的设计，致力于高效的处理海量数据，这就是为什么一些多余的读取并不会有损性能。index\_granularity=8192对于大多数场景都是比较好的选择。
 *  主键并不是唯一的，可以插入主键相同的数据行。
 *  主键的构成，同样可以存在函数表达式。如，（CounterID，EventDate，intHash32（UserID））

上述例子中，通过使用哈希函数，把特定的用户名对应的CounterID和EVENTDATE做了聚合，顺便，这种聚合方式，可以在样本这个功能中利用到。稀疏索引适用于海量数据表，并且，稀疏索引文件本身，放到内存是没有问题的

### ck的sql慢的优化方向 ###

1.  分区，原则是尽量把经常一起用到的数据放到相同区（也可以根据where条件来分区），如果一个区太大再放到多个区，
2.  主键（索引，即排序）order by字段选择： 就是把where 里面肯定有的字段加到里面，where 中一定有的字段放到第一位，注意字段的区分度适中即可 区分度太大太小都不好，因为ck的索引时稀疏索引，采用的是按照固定的粒度抽样作为实际的索引值，不是mysql的二叉树，所以不建议使用区分度特别高的字段。

两种主键，第一种ORDER BY (industry, l1\_name, l2\_name, l3\_name, job\_city, job\_area, row\_id)，第二种不包含row\_id字段，即ORDER BY (industry, l1\_name, l2\_name, l3\_name, job\_city, job\_area)，其中row\_id 是唯一的，在where条件中使用row\_id来查询时，你会发现第二种会性能更好，即将row\_id从主键中移除，查询效果更好

ck的索引优化  
1索引结构是稀疏索引 不要拿mysql的二叉树来类比

建索引的正确方式  
开始字段不应该是区分度很高的字段，如果是唯一的，那么索引效果非常差，也不能找区分度特别差的，应该找区分度中等，这就涉及到你的SETTINGS的值，如果比较大，可以找区分度稍差的列，如果比较小，找区分度稍大的列作为索引

boss\_info表大概是1500万条数据，20个G数据量

CREATE TABLE bi.boss\_info ( row\_id String,  user\_id Int32,  user\_name String,  gender String,  title String,  is\_hr String,  certification String,  user\_status String,  user\_extra\_status String,  completion String,  lure\_content String,  email String,  brand\_id Int32,  company\_name String,  com\_id Int32,  company\_full\_name String,  website String,  address String,  brand\_completion String,  brand\_certify String,  industry String,  scale String,  stage String,  add\_time String,  com\_description String,  com\_date8 String,  active\_time String,  unactive\_days Int32,  job\_title String,  l1\_name String,  l2\_name String,  l3\_name String,  city String,  low\_salary Int32,  high\_salary Int32,  degree String,  exp\_description String,  work\_years String,  job\_address String,  job\_province String,  job\_city String,  job\_area String,  job\_status String,  job\_num Int32,  online\_props\_buy String,  all\_item\_num Int32,  all\_income String,  online\_vip\_buy String,  online\_vip\_time String,  online\_super\_vip\_buy String,  online\_super\_vip\_time String,  offline\_props\_distribute String,  offline\_props\_time String,  offline\_vip\_distribute String,  offline\_vip\_time String,  pay\_now String,  data\_dt Date) ENGINE = MergeTree() PARTITION BY data\_dt ORDER BY (industry, l1\_name, l2\_name, l3\_name, job\_city, job\_area, row\_id) SETTINGS index\_granularity = 8192

CREATE TABLE bi.boss\_info2 ( row\_id String,  user\_id Int32,  user\_name String,  gender String,  title String,  is\_hr String,  certification String,  user\_status String,  user\_extra\_status String,  completion String,  lure\_content String,  email String,  brand\_id Int32,  company\_name String,  com\_id Int32,  company\_full\_name String,  website String,  address String,  brand\_completion String,  brand\_certify String,  industry String,  scale String,  stage String,  add\_time String,  com\_description String,  com\_date8 String,  active\_time String,  unactive\_days Int32,  job\_title String,  l1\_name String,  l2\_name String,  l3\_name String,  city String,  low\_salary Int32,  high\_salary Int32,  degree String,  exp\_description String,  work\_years String,  job\_address String,  job\_province String,  job\_city String,  job\_area String,  job\_status String,  job\_num Int32,  online\_props\_buy String,  all\_item\_num Int32,  all\_income String,  online\_vip\_buy String,  online\_vip\_time String,  online\_super\_vip\_buy String,  online\_super\_vip\_time String,  offline\_props\_distribute String,  offline\_props\_time String,  offline\_vip\_distribute String,  offline\_vip\_time String,  pay\_now String,  data\_dt Date) ENGINE = MergeTree() PARTITION BY data\_dt ORDER BY (industry, l1\_name, l2\_name, l3\_name, job\_city, job\_area) SETTINGS index\_granularity = 16384

建表语句区别：boss\_info和boss\_info2的区别是去掉了索引中的row\_id

现象：  
sql1：select row\_id from boss\_info order by row\_id desc limit 3;----耗时较大  
结果中第一条是：999997-1  
3 rows in set. Elapsed: 0.342 sec. Processed 14.62 million rows, 279.07 MB (42.71 million rows/s., 815.10 MB/s.)   
sql2：select row\_id from boss\_info2 order by row\_id desc limit 3;  
3 rows in set. Elapsed: 0.061 sec. Processed 14.62 million rows, 279.07 MB (240.21 million rows/s., 4.58 GB/s.)   
sql3：select  \* from boss\_info where row\_id ='999998-1';----耗时较大,时间不太稳定，再次测平均是4-6s  
1 rows in set. Elapsed: 20.228 sec. Processed 13.16 million rows, 24.10 GB (650.83 thousand rows/s., 1.19 GB/s.)   
sql4：select  \* from boss\_info2 where row\_id ='999997-1';  
1 rows in set. Elapsed: 2.195 sec. Processed 14.62 million rows, 279.08 MB (6.66 million rows/s., 127.16 MB/s.)

sql5：select row\_id from boss\_info order by row\_id asc limit 3;  
结果中第一条是：1000010-1  
3 rows in set. Elapsed: 0.058 sec. Processed 14.62 million rows, 279.07 MB (251.10 million rows/s., 4.79 GB/s.)   
sql6：select row\_id from boss\_info2 order by row\_id asc limit 3;  
3 rows in set. Elapsed: 0.061 sec. Processed 14.62 million rows, 279.07 MB (240.11 million rows/s., 4.58 GB/s.)   
sql7：select  \* from boss\_info where row\_id ='1000010-1';  
1 rows in set. Elapsed: 4.003 sec. Processed 13.16 million rows, 24.10 GB (3.29 million rows/s., 6.02 GB/s.)   
sql8：select  \* from boss\_info2 where row\_id ='1000010-1';  
1 rows in set. Elapsed: 2.711 sec. Processed 14.62 million rows, 279.07 MB (5.39 million rows/s., 102.95 MB/s.)

sql9：select count(\*) from boss\_info;  
 结果：14622978   
 上面所有的sql都是进行了全表扫描.都是扫描了1500万的数据

sql10：select  \* from boss\_info where industry='咨询' and row\_id ='999997-1';  
1 rows in set. Elapsed: 0.172 sec. Processed 147.64 thousand rows, 24.10 65B (859.30 thousand rows/s., 1.54 GB/s.)   
这句sql没有进行全表扫描，仅仅扫描了15万左右  
sql11：select \* from boss\_info where l1\_name='技术' and row\_id ='999997-1';  
1 rows in set. Elapsed: 1.728 sec. Processed 4.76 million rows, 8.46 GB (2.75 million rows/s., 4.89 GB/s.)   
sql12：select  \* from boss\_info where l3\_name='C++' and row\_id ='999997-1';  
1 rows in set. Elapsed: 3.073 sec. Processed 10.06 million rows, 18.03 GB (3.27 million rows/s., 5.87 GB/s.)

select industry, l1\_name, l2\_name, l3\_name, job\_city, job\_area, row\_id from boss\_info order by  row\_id desc limit 3 ;  
3 rows in set. Elapsed: 0.264 sec. Processed 14.62 million rows, 1.91 GB (55.45 million rows/s., 7.24 GB/s.)   
select \* from boss\_info order by  row\_id desc limit 3 ;  
3 rows in set. Elapsed: 3.299 sec. Processed 14.62 million rows, 25.51 GB (4.43 million rows/s., 7.73 GB/s.)   
select  \* from boss\_info where row\_id ='999988-9';  
先说ck的索引结构：  
典型的稀疏索引，即ck中数据的存储会按照order by的字段顺序存储，同时会根据你设置的setting（即索引粒度）来抽样数据，数据内容就是order by字段对应的真实值；

问题：（说明下面答案都是笔者猜测的，没有十足把握确认正确）  
1为什么sql10索引生效，sql1和sql3中的row\_id索引都没有生效，甚至拉慢了查询效率  
索引结构问题，ck是稀疏索引，sql10中industry刚好是索引的第一部分，所以索引生效直接定位范围区间；但是sql1和sql3中row\_id是索引的最后一部分，定位到的返回就会是全表范围，所以真正取值时要进行全表扫描。  
拉慢查询效率原因：  
首先会扫描所有的索引来定位取值范围，但是定位到的取值范围就是全表，所以此步完全是多做的  
然后会进行全表扫描

2sql3和sql4同是全表扫描，为什么sql3扫描数据量24GB，而sql4只有279M，导致sql3慢了10倍  
数据量近百倍差距--->row\_id在索引中是一定加载了所有字段，不在索引中仅仅扫描了row\_id字段

row\_id是索引一部分的时候 因为用row\_id定位的区间是全部（稀疏和在索引末尾部分导致），所以全部字段加载到内存，再找符合row\_id条件的记录？没有索引的时候就先用row\_id匹配，仅仅读取row\_id列，然仅仅加载row\_id所在的一个粒度区间所有内容

3sql1和sql2相比，为何sql1用了索引反而慢了5倍，为何sql1比sql2的处理速度要快  
可以参考问题一，先走了索引，但实际白走了

[MergeTree]: https://clickhouse.yandex/reference_en.html#MergeTree
[20181219171346716]: /images/20220328/9ff1a67cf59e4ea9a05179740a405993.png