clickhouse的稀疏索引

clickhouse官网
稀疏索引
在使用primary key的时候指定的key必须是在order by多个字段中排在前面
CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime)
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;

1. <br>-- 查看索引的行数marks
2. SELECT
3.     part_type,
4.     path,
5.     formatReadableQuantity(rows) AS rows,
6.     formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes,
7.     formatReadableSize(data_compressed_bytes) AS data_compressed_bytes,
8.     formatReadableSize(primary_key_bytes_in_memory) AS primary_key_bytes_in_memory,
9.     marks,
10.     formatReadableSize(bytes_on_disk) AS bytes_on_disk
11. FROM system.parts
12. WHERE (table = 'hits_UserID_URL') AND (active = 1)
13. FORMAT Vertical;
15. part_type:                   Wide
16. path:                        ./store/d9f/d9f36a1a-d2e6-46d4-8fb5-ffe9ad0d5aed/all_1_9_2/
17. rows:                        8.87 million
18. data_uncompressed_bytes:     733.28 MiB
19. data_compressed_bytes:       206.94 MiB
20. primary_key_bytes_in_memory: 96.93 KiB
21. marks:                       1083
22. bytes_on_disk:               207.07 MiB
25. 1 rows in set. Elapsed: 0.003 sec.

复制代码

　　
1、在介绍索引之前先介绍下clickhouse存储数据的方式
　　在clickhouse中数据按照行列是存储在bin文件下的，也就是每一个列有一个文件夹。
　　在下图中显示每隔8192行或者是10M生成一个granule（颗粒），我更喜欢叫做块，块内有序。

 
2、稀疏索引工作原理
每一个块的第一条就会存储在索引中，这个索引存储在.idx文件中，然后被读入内存

 

1. SELECT URL, count(URL) AS Count
2. FROM hits_UserID_URL
3. WHERE UserID = 749927693
4. GROUP BY URL
5. ORDER BY Count DESC
6. LIMIT 10;<br><br>结果：

复制代码

┌─URL────────────────────────────┬─Count─┐
│ http://auto.ru/chatay-barana.. │   170 │
│ http://auto.ru/chatay-id=371...│    52 │
│ http://public_search           │    45 │
│ http://kovrik-medvedevushku-...│    36 │
│ http://forumal                 │    33 │
│ http://korablitz.ru/L_1OFFER...│    14 │
│ http://auto.ru/chatay-id=371...│    14 │
│ http://auto.ru/chatay-john-D...│    13 │
│ http://auto.ru/chatay-john-D...│    10 │
│ http://wot/html?page/23600_m...│     9 │
└────────────────────────────────┴───────┘
10 rows in set. Elapsed: 0.005 sec.
Processed 8.19 thousand rows,
740.18 KB (1.53 million rows/s., 138.59 MB/s.)
ClickHouse客户端的输出现在显示，只有8190行流到ClickHouse，而不是进行完整的表扫描。执行计划：
┌─explain───────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                               │
│   Limit (preliminary LIMIT (without OFFSET))                                          │
│     Sorting (Sorting for ORDER BY)                                                    │
│       Expression (Before ORDER BY)                                                    │
│         Aggregating                                                                   │
│           Expression (Before GROUP BY)                                                │
│             Filter (WHERE)                                                            │
│               SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│                 ReadFromMergeTree                                                     │
│                 Indexes:                                                              │
│                   PrimaryKey                                                          │
│                     Keys:                                                             │
│                       UserID                                                          │
│                     Condition: (UserID in [749927693, 749927693])                     │
│                     Parts: 1/1                                                        │
│                     Granules: 1/1083                                                  │
└───────────────────────────────────────────────────────────────────────────────────────┘
16 rows in set. Elapsed: 0.003 sec.
稀疏索引第一步是先通过二分查找快速寻找对应的块，第二步把这个块的数据解压，并寻找匹配的行数据
3、mrk文件为idx和bin架起来桥梁
　　mrk文件记录了索引，以及它对应的块的真实物理地址（block_offset），和解压后的位置（granule_offset)

　　索引通过block_offset锁定块，然后解压并加载到内存中，再通过granule_offset寻找数据的位置
  

 4、在使用多个索引的时候第二个键列作为筛选条件呢
      第一个键列是直接使用快速二分查找，那直接用第二个键列呢

1. SELECT UserID, count(UserID) AS Count
2. FROM hits_UserID_URL
3. WHERE URL = 'http://public_search'
4. GROUP BY UserID
5. ORDER BY Count DESC
6. LIMIT 10;
9. ┌─────UserID─┬─Count─┐
10. │ 2459550954 │  3741 │
11. │ 1084649151 │  2484 │
12. │  723361875 │   729 │
13. │ 3087145896 │   695 │
14. │ 2754931092 │   672 │
15. │ 1509037307 │   582 │
16. │ 3085460200 │   573 │
17. │ 2454360090 │   556 │
18. │ 3884990840 │   539 │
19. │  765730816 │   536 │
20. └────────────┴───────┘
22. 10 rows in set. Elapsed: 0.086 sec.
23. Processed 8.81 million rows,
24. 799.69 MB (102.11 million rows/s., 9.27 GB/s.)<br>

复制代码

 

1. -- trace_logging <br>...Executor): Key condition: (column 1 in ['http://public_search',
2.                                            'http://public_search'])
3. ...Executor): Used generic exclusion search over index for part all_1_9_2
4.               with 1537 steps
5. ...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
6.               1076/1083 marks by primary key, 1076 marks to read from 5 ranges
7. ...Executor): Reading approx. 8814592 rows with 10 streams

复制代码

　　从结果可以看到url是第二键列，用它作为条件过滤，在数据量887万行中读取了881万行，几乎是全表扫描。为什么呢？
　　在 trace_logging中我们可以看到总共1083个块，但是1076个块中被认为可能有 url=http://public_search',但是实际上只有39个有
　　好像我们可以得出在符合主键 (UserID, URL)中对UserID确实有作用，但是对于URL就等同失效了
但是是真的是这样吗？其实这个和第一个键的占比很关键。
举个例子寻找URL=W3的　
　　数据是先按照UserID排序后再按照URL排序，在UserID值比较少的时候，一个userID有很多条mark数据，因此就可以筛掉很多块。例如下图
　 因为userID一样，那么就可以直接比较URL，在mark0是W1而且mark1是W2,那么就可以直接认为mark0不可能含有W3，mark0就过掉了，mark2的URL比W3大，那么mark2以上的也不可能包含w3，那么只需要把mark1对应的数据进行寻找。
   那么这个索引建立还是很有作用的

 但是如果UserID很多，那就很等同失效
例如下图：
U1、U2、U3也可能有W3，这样子的话只能全表扫描了

 总结：
在多个索引时，使用第一个作为筛选条件是最好的，但是如果使用第二个的话，建议是在前置键列的基数较低（er）时最有效（也就是种类很少的时候，例如UserID很少）

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！