An index design for large data scales
In traditional relational database management systems, the primary index would contain one entry per table row. For our dataset, this would result in the primary index - usually aB(+) treeData structure - with 8.87 million entries. This index allows specific lines to be found quickly, resulting in high efficiency for search queries and point updates. Finding an entry in a B(+) tree data structure has an average time complexity ofO(log2n). For a table with 8.87 million rows, that means it takes 23 steps to find any index entry. This capability comes at a cost: additional disk and memory overhead and higher insertion costs for adding new rows to the table and entries to the index (and sometimes rebalancing the B-tree).
Given the challenges associated with B-Tree indexes, the table engines in ClickHouse take a different approach. the click houseMergeTree-Engine-Familywas developed and optimized to handle large amounts of data. These tables are designed to receive millions of row inserts per second and to store large amounts of data (100 petabytes). Data is quickly written to a tablepart by part, applying rules to merge the pieces into the background. In ClickHouse, each part has its own primary index. When parts are merged, the primary indexes of the merged part are also merged. At the very large scale that ClickHouse was designed for, it is of paramount importance to be very disk and storage efficient. Therefore, instead of indexing each line, a part's primary index has one index entry (known as a "tag") per group of lines (called "grains") - this technique is calledsparse index.
Sparse indexing is possible because ClickHouse stores the rows of a chunk on disk, ordered by the primary key column(s). Rather than finding individual rows directly (like a B-tree-based index), the sparse primary index allows you to quickly identify (through a binary search on index entries) groups of rows that might match the query. The found groups of potentially matching lines (beads) are passed in parallel to the ClickHouse engine to find the matches. This index design allows the primary index to be small (it can and should fit entirely in main memory) while still significantly reducing query execution times: particularly for range queries, which are typical in data analytics use cases. .
The following details how ClickHouse creates and uses its sparse primary index. Later in this article, we'll discuss some best practices for selecting, removing, and sorting the table columns used to create the index (primary key columns).
A table with a primary key
Create a table with a composite primary key with UserID and URL key columns:
TO CREATE TISCHhits_UserID_URL
(
`ID Benutzer`UInt32,
`URL`line,
`event time` appointment time
)
MOTOR =MergeTree
PRIMARY I LIKE (ID Benutzer,URL)
COMMAND VON (ID Benutzer,URL,event time)
index_granularity SETTINGS= 8192,index_granularity_bytes= 0;
DDL statement details
To simplify discussions later in this guide and make graphs and results reproducible, the DDL statement
The primary key in the DDL statement above causes the primary index to be created based on the two specified key columns.
Then paste the data:
INSERTION EMhits_UserID_URLTO CHOOSE
intHash32(ID Benutzer) SEID Benutzer,
URL,
event time
OUTSIDE OFURL('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz', 'TSV', „WatchID UInt64, JavaEnable UInt8, Title String, GoodEvent Int16, EventTime DateTime, EventDate Date, CounterID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RegionID UInt32, UserID UInt64, CounterClass Int8, OS UInt8, UserAgent UInt8, URL String, Referer String, URLDomain String, RefererDomain String, Refresh UInt8, IsRobot UInt8, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), ResolutionWidth UInt16, ResolutionHeight UInt16, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, FlashMinor2 String, NetMajor UInt8, NetMinor UInt8, UserAgentMajor UInt16, UserAgentMinor FixedString(2), CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, MobilePhone UInt8, MobilePhoneModel String, Params String, IPNetworkID UInt32, TraficSourceID Int8, SearchEngine UInt8, MobilePhone UInt8, MobilePhoneModel String, Params String, IPNetworkID UInt32, TraficSourceID Int8, SearchEngineID SearchPUInt8 , AdvEngineID UInt8, IsArtifical UInt8, WindowClientWidth UInt16, WindowClientHeight UInt16, ClientTimeZone Int16, ClientEven T Tempo DateTime, SilverlightVersion1 UINT8, Silverlightsion2 UINT8, SilverlighTversion3 UINT32, SilverlighTversion4 UINT16, String PagCharSet, Codeversion uint32, Islink Uint8, Isdownload UInt8, IsNotBounce, UINT8, FUNIQID UINT8, HIDEIRA, UINTHOL2, ISNOTBOUND8, FUNIQID UINT6, HIDENHO , HitColor FixedString(1), UTCEventTime DateTime, Age UInt8, Sex UInt8, Income UInt8, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), RemoteIP UInt32, RemoteIP6 FixedString(16), WindowName Int32, OpenerName Int32, HistoryLength Int16, BrowserLanguage FixedString(2), BrowserCountry FixedString(2), SocialNetwork String, SocialAction String, HTTPError UInt16, SendTiming Int32, DNSTiming Int32, ConnectTiming Int32, ResponseStartTiming Int32, ResponseEndTiming Int32, FetchTiming Int32, RedirectTiming Int32, DOMInteractiveTiming Int32, DOMContentLoadedTiming Int32, DOMContentLoadedTiming Int32, DOMContentLoadedTiming DOMContentLoadedTiming Int32, DOMComplet LoadEventStartTiming Int 32, LoadEventEndTiming Int32, NST oDOMContentLoadedTiming Int32, FirstPaintTiming Int32, RedirectCount Int8, SocialSourceNetworkID UInt8, SocialSourcePage String, ParamPrice Int64, ParamOrderID String, ParamCurrency FixedString(3), ParamCurrencyID UInt16, GoalsReached Array(UInt32), OpenstatStringAdminService , U OpenstatSourceID , UTMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, RefererHash UInt64, URLHash UInt64, CLID UInt32, YCLID UInt64, ShareService String, ShareURL String, ShareTitle String, ParsedParams Nested(Key1 String, Key2 String, String Key3, String Key4, String Key5, ValueDouble Float64), IslandID FixedString(16), RequestNum UInt32, RequestTry UInt8')
OSURL!= '';
The answer looks like this:
0 lines in the sentence. Elapsed: 149.432 sec. 8.87 million lines processed, 18.40 GB (59.38 thousand lines/sec., 123.16 MB/sec.)
And optimize the table:
OPTIMIZE TISCHhits_UserID_URL FINAL;
We can use the following query to get metadata about our table:
TO CHOOSE
type of part,
Absent,
formatLesbareMenge(lines) SE lines,
formatReadableSize(data_uncompressed_bytes) SEdata_uncompressed_bytes,
formatReadableSize(data_compressed_bytes) SEdata_compressed_bytes,
formatReadableSize(primary_key_bytes_in_memory) SEprimary_key_bytes_in_memory,
marked,
formatReadableSize(bytes_on_disk) SEbytes_on_disk
OUTSIDE OFSystem.For you
OS (Tisch = 'hits_UserID_URL') E (active= 1)
VERTICAL FORMAT;
The answer is:
part_type: Largo
Pfad: ./store/d9f/d9f36a1a-d2e6-46d4-8fb5-ffe9ad0d5aed/all_1_9_2/
Lines: 8.87 million
data_uncompressed_bytes: 733,28 MiB
data_compressed_bytes: 206,94 MiB
primary_key_bytes_in_memory: 96,93 KiB
Grades: 1083
bytes_on_disk: 207,07 MiB
1 line in the set. Elapsed: 0.003 sec
The ClickHouse client output shows:
- Table data is stored inWide formatin a specific directory on disk, which means that there is one data file (and one marker file) per table column in that directory.
- The table has 8.87 million rows.
- The uncompressed data size of all rows combined is 733.28 MB.
- The compressed size of all lines together on disk is 206.94 MB.
- The table has a primary index of 1083 entries (referred to as "tags") and the index size is 96.93 KB.
- In total, the table markup and data files and the main index file together occupy 207.07 MB on disk.
Data is stored on disk sorted by primary key column(s)
Our table we created above has
- a connectionprimary key (Benutzer ID, URL)E
- a connectionsort key (User ID, URL, event time).
observation
If we only specify the sort key, the primary key is implicitly set equal to the sort key.
(Video) Introducing ClickHouse -- The Fastest Data Warehouse You've Never Heard Of (Robert Hodges, Altinity)To be memory efficient, we explicitly specify a primary key that contains only the columns our queries are being filtered on. The primary index based on the primary key is fully loaded into main memory.
To have consistency in the guide charts and maximize the compression ratio, we define a separate sort key that includes all columns of our table (if in a column similar data is placed next to each other, for example, by sort, then the data is better compressed).
The primary key must be a prefix of the sort key if both are specified.
Inserted rows are stored on disk in lexicographical (ascending) order by primary key columns (and the additional sort key EventTime column).
observation
ClickHouse allows you to insert multiple rows with identical primary key column values. In this case (see line 1 and line 2 in the diagram below), the final order is determined by the specified sort key and therefore the value ofevent timeTo divide.
ClickHouse is acolumn-oriented database management system. As shown in the diagram below
- For representation on disk, there is a single data file (*.bin) per table column, storing all the values of that column in apillformat and
- The 8.87 million rows are stored on disk in lexicographically ascending order by primary key columns (and additional sort key columns), i.e. in this case
- first throughID Benutzer,
- then throughURL,
- and last untilevent time:
UserID.bin, URL.bin, and EventTime.bin are the data files on disk that contain the values of theID Benutzer,URL, Eevent timecolumns are saved.
observation
As the primary key defines the lexicographical order of the rows on disk, a table can only have one primary key.
We number the lines starting with 0 to align with ClickHouse's internal line numbering scheme, which is also used to log messages.
Data is organized into granules for parallel data processing
For data processing purposes, column values in a table are logically divided into granules. A granule is the smallest indivisible data set passed to ClickHouse for data processing. This means that ClickHouse is always reading instead of reading individual lines (continuously). and in parallel) a whole group (beads) of lines.
observation
Column values are not physically stored in granules: granules are just a logical organization of column values for query processing.
The following diagram shows how the (column values of) 8.87 million rows of our table are organized into 1083 granules as a result of the table DDL statement containing the configurationindex_granularity(default set to 8192).
The first 8192 rows (based on physical order on disk) (their column values) logically belong in granule 0, the next 8192 rows (their column values) belong in granule 1, and so on.
observation
The last granule (granule 1082) "contains" less than 8192 lines.
We mentioned at the beginning of this guide in the "DDL Statement Details" that we disable themadaptive index granularity(to simplify discussions in this guide, as well as to make graphs and results reproducible).
Therefore, all granules (except the last one) in our example table are the same size.
For tables with adaptive index granularity (index granularity is adaptive fromStandard) the size of some granules may be less than 8192 rows depending on the row data size.
We mark some column values of our primary key columns (ID Benutzer,URL) in orange. These column values highlighted in orange are the primary key column values of each first row of each granule. As we'll see below, these column values highlighted in orange are the table's primary index entries.
We number the Granules starting with 0 to match ClickHouse's internal numbering scheme, which is also used to log messages.
Primary index has one entry per granule
The primary index is created based on the granules shown in the diagram above. This index is an uncompressed flat array file (primary.idx) containing so-called numeric index markers starting at 0.
The diagram below shows that the index stores the primary key column values (the values highlighted in orange in the diagram above) for each first row of each granule. In other words, the primary index stores the primary key column values of every 8192nd row in the table (based on the physical row order defined by the primary key columns). For example
- the first index entry ("marker 0" in the diagram below) stores the key column values of the first row of granule 0 in the diagram above,
- the second index entry ("Marker 1" in the diagram below) stores the key column values of the first row of Granule 1 of the diagram above, and so on.
In total, the index has 1083 entries for our table with 8.87 million rows and 1083 granules:
observation
For tables withadaptive index granularity, there is also an additional "last" marker stored in the primary index that records the values of the primary key columns of the last row of the table, but since we have disabled the adaptive index granularity (to simplify the discussions in this guide also around graphs and results to make it reproducible), the index of our example table does not contain this end marker.
The primary index file is fully loaded into main memory. If the file is larger than the available free space, ClickHouse will throw an error.
Checking the contents of the primary index
In a self-managed ClickHouse cluster, we can use thesefile table functionto examine the contents of the primary index of our sample table. To do this, we must first copy the primary index file to theuser_files_patha running cluster node: Returns/Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4on the test machine. and on Linux you can check if it changed:$ grep userfilepath /etc/clickhouse-server/config.xml On the test machine is the way/Users/tomwriter/Clickhouse/User Files/ Now we can inspect the contents of the primary index via SQL: Returns Returns Returns This exactly matches our primary index content graph for our sample table:
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
LIMIT 0, 2;
4073710, http://mk.ru&pos=3_0
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
LIMIT 1082, 1;
Primary key entries are called index markers because each index entry marks the beginning of a specific range of data. Specifically for the example table:
UserID index tags:
the savedID BenutzerValues in the primary index are sorted in ascending order.
“Marker 1” in the above diagram therefore indicates that theID BenutzerThe values of all table rows in Sprinkle 1 and all subsequent Sprinkles are guaranteed to be greater than or equal to 4,073,710.As we will see later, this global order allows ClickHouseUse a binary search algorithmabove the first key column index markers when a query filters on the first primary key column.
URL index tags:
The very similar cardinality of the primary key columnsID BenutzerEURLmeans that the index markers for all key columns after the first column usually only show one range of data, as long as the ancestor key column value remains the same for all rows in the table, at least within the current granule.
For example, because the UserID values of Marker 0 and Marker 1 are different in the above diagram, ClickHouse cannot assume that all URL values of all table rows in Granule 0 are greater than or equal'http://showtopics.html%3...'. However, if the UserID values of Mark 0 and Mark 1 in the above graph were the same (meaning that the UserID value remains the same for all table rows within granule 0), ClickHouse could assume that all url values of all table rows in granules 0 are greater than or equal'http://showtopics.html%3...'.We'll discuss the impact of this on query execution performance in more detail later.
The primary index is used to select granules
Now we can run our queries with primary index support.
The top 10 most frequently clicked URLs for UserID 749927693 are calculated below.
TO CHOOSEURL, tell(URL) SETell
OUTSIDE OFhits_UserID_URL
OSID Benutzer= 749927693
GROUP VONURL
COMMAND VONTellDESCR
BORDER 10;
The answer is:
┌─URL────────────────────────────┬─Anzahl─┐
│ http://auto.ru/chatay-barana.. │ 170 │
│ http://auto.ru/chatay-id=371...│ 52 │
│ http://public_search │ 45 │
│ http://kovrik-medvedevushku-...│ 36 │
│ http://forum │ 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 a set. Elapsed: 0.005 sec
8.19 thousand lines processed,
740.18 KB (1.53 million rows/sec., 138.59 MB/sec.)
The output to the ClickHouse client now shows that instead of a full table scan, only 8,1900 rows were passed to ClickHouse.
Setracking recordis enabled, the ClickHouse server log file shows that ClickHouse was running abinary searchon the 1083 UserID index markers to identify granules that may contain rows with a UserID column value of749927693. This requires 19 steps with an average time complexity ofO(log2n):
...Executor): Key condition: (column 0 in [749927693, 749927693])
...Executor: Performing binary search in index area for part all_1_9_2 (1083 markers)
...Executor): Found (LEFT) Threshold Counter: 176
...Executor): Found (RIGHT) Limit Marker: 177
...Executor): Continuous range found in 19 steps
...Executor): 1/1 part selected per partition key, 1 part per primary key,
1/1083 marks per primary key, 1 mark to read from 1 range
...reading ...approx. 8192 rows out of 1441792
We can see from the above trace log that one of the 1083 markers present satisfies the query.
Trace-Protokolldetails
Marker 176 has been identified ('found left boundary tag' is inclusive, 'found right boundary tag' is exclusive) and so all 8192 rows of granule 176 (starting at row 1,441,792 - we'll see that later in this guide) are then passed to ClickHouse to find the actual rows with a UserID column value of749927693.
We can also reproduce this using theEXPLAIN clausein our example query:
EXPLAINindexes= 1
TO CHOOSEURL, tell(URL) SETell
OUTSIDE OFhits_UserID_URL
OSID Benutzer= 749927693
GROUP VONURL
COMMAND VONTellDESCR
BORDER 10;
The answer looks like this:
┌─forschlänen awarded - Rotkenner - Rotfuhr ────────────────────────────────┐
│ expression (projection) │
│ Limit (preliminary LIMIT (without OFFSET)) │
│ Sorting (Sort for ORDER BY) │
│ Expression (before ORDER BY) │
│ Add │
│ expression (before GROUP BY) │
│ Filter (WO) │
│ SettingQuotaAndLimits (set limits and quotas after reading memory) │
│ ReadFromMergeTree │
│ Indexes: │
│ primary key │
│ Chaves: │
│ Benutzer ID │
│ Condition: (UserID at [749927693, 749927693]) │
│ For you: 1/1 │
│ Granules: 1/1083 │
" ──────────────────────────────────────┘
16 lines in the set. Elapsed: 0.003 sec
The client output shows that one of the 1083 granules was chosen because it may contain rows with a UserID column value of 749927693.
Diploma
When a query filters on a column that is part of a composite key and is the first key column, ClickHouse runs the binary search algorithm on the key column's index markers.
As discussed above, ClickHouse uses its sparse primary index for quick selection (via binary search) of grains that could potentially contain rows that match a query.
this is thatfirst stage (selection of the granulate)of executing the ClickHouse query.
I amsecond stage (read data), ClickHouse finds the selected granules to pass all its rows to the ClickHouse engine to find the rows that actually match the query.
We will discuss this second step in detail in the following section.
Marker files are used to locate granules
The following diagram illustrates part of the primary index file for our table.
As discussed above, through a binary search of the 1083 UserID tags in the index, tag 176 was identified. Its corresponding granule 176 may therefore contain rows with a UserID column value of 749,927,693.
Granule selection details
The diagram above shows that Tag 176 is the first index entry where the minimum UserID value of the associated Header 176 is less than 749,927,693 and the minimum UserID value of Header 177 for the next Tag (Tag 177) is greater than this value is value. Therefore, only the 176 granule corresponding to the 176 tag can contain rows with a UserID column value of 749,927,693.
To confirm (or not) that some rows in cluster 176 contain a UserID column value of 749,927,693, all 8192 rows belonging to that cluster need to be passed to ClickHouse.
To do this, ClickHouse needs to know the physical location of Granule 176.
At ClickHouse, the physical locations of all the pellets on our table are stored in marker files. Similar to data files, there is one placeholder file per table column.
The following diagram shows the three placeholder files UserID.mrk, URL.mrk, and EventTime.mrk that store the physical locations of the granules for the table's UserID, URL, and EventTime columns.
We discussed that the primary index is a simple, uncompressed array file (primary.idx) containing index markers numbered starting from 0.
Likewise, a marker file is also a flat, uncompressed matrix file (*.mrk) containing markers numbered from 0 onwards.
Once ClickHouse has identified and selected the index marker for a granule that may contain potentially matching rows for a query, a positional array search can be performed on the marker files to obtain the physical positions of the granule.
Each tag file entry for a given column stores two positions in terms of offsets:
The first offset ('block_offset' in the diagram above) locates theBlockI ampillColumn data file containing the compressed version of the selected granule. This compacted block potentially contains some compacted grains. The localized compressed file block is decompressed when read into main memory.
The second offset ('granule_offset' in the diagram above) of the marker file gives the position of the granule within the uncompressed block data.
All 8192 rows belonging to the found uncompressed granules are then passed to ClickHouse for further processing.
observation
For tables withWide formatand withoutadaptive index granularity, usa ClickHouse.mrkTag the files as seen above that contain entries with two 8-byte addresses per entry. These entries are physical locations of grains that are all the same size.
The index granularity is adaptable fromStandard, but for our example table, we turned off adaptive index granularity (to simplify discussions in this guide and make graphs and results reproducible). Our worksheet uses wide format because the data size is larger thanmin_bytes_for_wide_part(this is 10 MB by default for self-managed clusters).
For wide format tables with adaptive index granularity, use ClickHouse.mrk2Mark files that contain similar entries.mrkTag files, but with an additional third value per entry: the number of string lines to which the current entry is linked.
For tables withcompact format, usa ClickHouse.mrk3tag files.
why tag files
Why doesn't the primary index directly contain the physical positions of the granules corresponding to the index marks?
Because at this very large scale that ClickHouse was designed for, it's important to be very efficient in terms of disk and storage.
The primary index file must fit in main memory.
For our sample query, ClickHouse used the primary index and selected a single granule that can contain rows that match our query. Just for that granule, ClickHouse needs the physical locations to pass the appropriate lines for further processing.
Also, this offset information is only needed for the UserID and URL columns.
Offset information is not required for columns that are not used in the query, for example event time.
For our example query, ClickHouse only needs the two physical location offsets for Granule 176 in the UserID data file (UserID.bin) and the two physical location offsets for Granule 176 in the URL data file (URL.bin).
The indirection provided by the bookmark files avoids storing entries for the physical locations of all 1083 granules for all three columns directly in the primary index: thus avoiding having unnecessary (potentially unused) data in main memory.
The following diagram and text illustrate how ClickHouse finds Granule 176 in the UserID.bin data file for our example query.
We discussed earlier in this guide that ClickHouse selected primary index marker 176, and therefore granule 176, as possible matching rows for our query.
ClickHouse now uses the selected marker number (176) from the index to a position array lookup in the marker file UserID.mrk to get the two offsets to find bead 176.
As shown, the first offset locates the compressed file block within the UserID.bin data file, which in turn contains the compressed version of Granule 176.
Once the located block of files is decompressed in main memory, the second offset of the marker file can be used to locate the granule 176 within the uncompressed data.
ClickHouse needs to find Granule 176 from the UserID.bin data file and URL.bin data file (and pass in all their values) to run our example query (Top 10 clicked URLs for Internet user with UserID 749,927,693 ).
The diagram above shows how ClickHouse finds the granules for the UserID.bin data file.
In parallel, ClickHouse does the same for Granule 176 for the URL.bin data file. The two respective granules are aligned and passed to the ClickHouse engine for further processing, i.e. H. to aggregate and count the URL values by group for all rows with UserID 749,927,693 before finally returning the top 10 URL groups in descending count order.