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;
-- 查看索引的行数marks SELECT part_type, path, formatReadableQuantity(rows) AS rows, formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes, formatReadableSize(data_compressed_bytes) AS data_compressed_bytes, formatReadableSize(primary_key_bytes_in_memory) AS primary_key_bytes_in_memory, marks, formatReadableSize(bytes_on_disk) AS bytes_on_disk FROM system.parts WHERE (table = 'hits_UserID_URL') AND (active = 1) FORMAT Vertical; part_type: Wide path: ./store/d9f/d9f36a1a-d2e6-46d4-8fb5-ffe9ad0d5aed/all_1_9_2/ rows: 8.87 million data_uncompressed_bytes: 733.28 MiB data_compressed_bytes: 206.94 MiB primary_key_bytes_in_memory: 96.93 KiB marks: 1083 bytes_on_disk: 207.07 MiB 1 rows in set. Elapsed: 0.003 sec.
1、在介绍索引之前先介绍下clickhouse存储数据的方式
在clickhouse中数据按照行列是存储在bin文件下的,也就是每一个列有一个文件夹。
在下图中显示每隔8192行或者是10M生成一个granule(颗粒),我更喜欢叫做块,块内有序。
2、稀疏索引工作原理
每一个块的第一条就会存储在索引中,这个索引存储在.idx文件中,然后被读入内存
SELECT URL, count(URL) AS Count FROM hits_UserID_URL WHERE UserID = 749927693 GROUP BY URL ORDER BY Count DESC LIMIT 10;
结果:
┌─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、在使用多个索引的时候第二个键列作为筛选条件呢
第一个键列是直接使用快速二分查找,那直接用第二个键列呢
SELECT UserID, count(UserID) AS Count FROM hits_UserID_URL WHERE URL = 'http://public_search' GROUP BY UserID ORDER BY Count DESC LIMIT 10; ┌─────UserID─┬─Count─┐ │ 2459550954 │ 3741 │ │ 1084649151 │ 2484 │ │ 723361875 │ 729 │ │ 3087145896 │ 695 │ │ 2754931092 │ 672 │ │ 1509037307 │ 582 │ │ 3085460200 │ 573 │ │ 2454360090 │ 556 │ │ 3884990840 │ 539 │ │ 765730816 │ 536 │ └────────────┴───────┘ 10 rows in set. Elapsed: 0.086 sec. Processed 8.81 million rows, 799.69 MB (102.11 million rows/s., 9.27 GB/s.)
-- trace_logging
...Executor): Key condition: (column 1 in ['http://public_search', 'http://public_search']) ...Executor): Used generic exclusion search over index for part all_1_9_2 with 1537 steps ...Executor): Selected 1/1 parts by partition key, 1 parts by primary key, 1076/1083 marks by primary key, 1076 marks to read from 5 ranges ...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,这样子的话只能全表扫描了
总结:文章来源:https://www.toymoban.com/news/detail-736828.html
在多个索引时,使用第一个作为筛选条件是最好的,但是如果使用第二个的话,建议是在前置键列的基数较低(er)时最有效(也就是种类很少的时候,例如UserID很少)文章来源地址https://www.toymoban.com/news/detail-736828.html
到了这里,关于clickhouse的稀疏索引的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!