ClickHouse index_granularity 詳解
ClickHouse index_granularity 詳解
什么是 index_granularity
index_granularity 是ClickHouse中一個重要的性能配置參數，它定義了索引的粒度（granularity），即每多少個數據行會創建一個索引標記（index mark）。
基本概念
1. 索引標記的工作原理
數據行: [1, 2, 3, ..., 8192] [8193, 8194, ..., 16384] [16385, 16386, ...]
索引標記: ↑標記1 ↑標記2 ↑標記3
- 每8192行數據創建一個索引標記
- 每個標記存儲該段數據的邊界信息（最小值、最大值等）
- 查詢時先通過索引標記快速定位到可能包含目標數據的段
2. 索引標記的創建依據
索引標記是根據ORDER BY子句中定義的列來創建的：
CREATE TABLE blocks (
block_number UInt64,
timestamp DateTime,
block_hash String
) ENGINE = MergeTree()
ORDER BY (block_number, timestamp) -- 索引標記基于這兩列創建
SETTINGS index_granularity = 8192;
索引標記存儲的信息：
- 最小值：該段數據中ORDER BY列的最小值
- 最大值：該段數據中ORDER BY列的最大值
- 行數：該段包含的行數
- 數據塊位置：指向實際數據塊的指針
3. 索引查找過程示例
-- 查詢區塊號范圍
SELECT * FROM blocks WHERE block_number BETWEEN 10000 AND 20000;
索引查找過程：
- 檢查索引標記1：max=8192 < 10000，跳過
- 檢查索引標記2：min=8193, max=16384，與查詢范圍重疊，需要讀取
- 檢查索引標記3：min=16385 > 20000，跳過
- 只讀取索引標記2對應的數據塊
ORDER BY 對索引的影響
1. 不同ORDER BY的影響
單列排序：
ORDER BY block_number
-- 索引標記基于block_number創建
-- 適合按區塊號查詢
多列排序：
ORDER BY (block_number, timestamp)
-- 索引標記基于(block_number, timestamp)組合創建
-- 適合復合查詢條件
你的表結構：
ORDER BY (start_block, end_block)
-- 索引標記基于(start_block, end_block)組合創建
-- 適合按區塊范圍查詢
2. 索引效率的關鍵因素
ORDER BY列的選擇：
-- 好的選擇：經常用于WHERE條件的列
ORDER BY (block_number, timestamp)
-- 不好的選擇：很少用于查詢的列
ORDER BY (random_column, unused_column)
列的順序：
-- 好的順序：選擇性高的列在前
ORDER BY (block_number, timestamp) -- block_number選擇性高
-- 不好的順序：選擇性低的列在前
ORDER BY (status, block_number) -- status選擇性低
查詢模式匹配（左側匹配原則）：
-- 表結構
ORDER BY (start_block, end_block, status)
-- 高效查詢（遵循左側匹配）
WHERE start_block = 12345 -- ? 使用第1列
WHERE start_block = 12345 AND end_block = 12350 -- ? 使用第1、2列
WHERE start_block = 12345 AND end_block = 12350 AND status = 'finished' -- ? 使用第1、2、3列
WHERE start_block BETWEEN 1000 AND 2000 -- ? 范圍查詢也遵循左側匹配
-- 低效查詢（違反左側匹配）
WHERE end_block = 12350 -- ? 跳過第1列，直接使用第2列
WHERE status = 'finished' -- ? 跳過第1、2列，直接使用第3列
WHERE start_block = 12345 AND status = 'finished' -- ? 使用第1、3列，跳過第2列
左側匹配原則詳解
1. 基本原理
ClickHouse的索引遵循左側匹配原則，類似于MySQL的復合索引。查詢時必須從左到右使用ORDER BY中的列，不能跳過中間的列。
2. 與MySQL的對比
-- MySQL復合索引：(start_block, end_block, status)
-- 遵循左側匹配原則
-- ClickHouse ORDER BY：(start_block, end_block, status)
-- 同樣遵循左側匹配原則
3. 索引標記的工作原理
-- 表結構：ORDER BY (start_block, end_block, status)
-- 索引標記存儲：
標記1: start_block_min=1, start_block_max=1000, end_block_min=1, end_block_max=1000, status_min='init', status_max='pending'
標記2: start_block_min=1001, start_block_max=2000, end_block_min=1001, end_block_max=2000, status_min='init', status_max='pending'
4. 查詢效率分析
-- ? 高效查詢：WHERE start_block = 1500
-- 可以快速定位到標記2，因為start_block在范圍內
-- ? 低效查詢：WHERE end_block = 1500
-- 無法有效使用索引，因為索引標記的end_block范圍重疊
-- 需要掃描多個標記或全表掃描
5. 實際應用示例
-- 你的block_tasks表
CREATE TABLE block_tasks (
start_block UInt64,
end_block UInt64,
status String
) ENGINE = MergeTree()
ORDER BY (start_block, end_block)
SETTINGS index_granularity = 8192;
-- 高效查詢模式
SELECT * FROM block_tasks WHERE start_block = 12345; -- ? 使用第1列
SELECT * FROM block_tasks WHERE start_block BETWEEN 1000 AND 2000; -- ? 范圍查詢第1列
SELECT * FROM block_tasks WHERE start_block = 12345 AND end_block = 12350; -- ? 使用第1、2列
-- 低效查詢模式
SELECT * FROM block_tasks WHERE end_block = 12350; -- ? 跳過第1列
SELECT * FROM block_tasks WHERE status = 'finished'; -- ? 跳過第1、2列
6. 最佳實踐
-- 設計ORDER BY時要考慮查詢模式
-- 將最常用于WHERE條件的列放在前面
ORDER BY (最常用的列, 次常用的列, 較少用的列)
-- 例如：如果經常按start_block查詢，偶爾按start_block+end_block查詢
ORDER BY (start_block, end_block) -- 好的設計
-- 如果經常按end_block查詢，偶爾按start_block查詢
ORDER BY (end_block, start_block) -- 更好的設計
默認值和推薦值
- 默認值：8192
- 推薦值：8192（ClickHouse官方推薦）
- 常見值：1024, 4096, 8192, 16384, 65536
不同值的影響
| index_granularity | 內存使用 | 查詢速度 | 寫入速度 | 適用場景 |
|---|---|---|---|---|
| 1024 (小) | 高 | 慢 | 快 | 頻繁更新，查詢較少 |
| 4096 (中小) | 較高 | 較慢 | 較快 | 平衡型應用 |
| 8192 (默認) | 中等 | 快 | 中等 | 通用場景，推薦 |
| 16384 (大) | 較低 | 中等 | 較慢 | 批量寫入，查詢頻繁 |
| 65536 (很大) | 低 | 中等 | 慢 | 大批量數據，查詢較少 |
為什么選擇 8192
1. 數學優勢
- 2的冪次方：8192 = 2^13，便于計算機處理
- 內存對齊：與系統頁面大小（8KB）匹配
- 緩存友好：適合CPU緩存行大小
2. 性能平衡
- 查詢性能：能夠快速定位到相關數據段
- 內存使用：不會創建過多索引標記
- 寫入性能：在批量插入時保持良好的性能
3. 經驗優化
- ClickHouse團隊經過大量測試得出的最佳值
- 適用于大多數應用場景
- 平衡了各種性能指標
實際應用示例
1. 區塊數據表
CREATE TABLE blocks (
block_number UInt64,
timestamp DateTime,
block_hash String
) ENGINE = MergeTree()
ORDER BY (block_number, timestamp)
SETTINGS index_granularity = 8192;
查詢示例：
-- 查詢特定區塊范圍
SELECT * FROM blocks WHERE block_number BETWEEN 1000000 AND 1001000;
性能分析：
- 100萬行數據，8192粒度，約122個索引標記
- 查詢時只需檢查122個標記，然后讀取對應的8192行數據段
- 相比全表掃描，性能提升顯著
2. 交易數據表
CREATE TABLE transactions (
tx_hash String,
block_number UInt64,
from_address String,
to_address String
) ENGINE = MergeTree()
ORDER BY (block_number, tx_hash)
SETTINGS index_granularity = 8192;
3. 日志數據表
CREATE TABLE logs (
block_number UInt64,
log_index UInt32,
address String,
data String
) ENGINE = MergeTree()
ORDER BY (block_number, log_index)
SETTINGS index_granularity = 8192;
如何選擇合適的值
1. 數據量考慮
-- 小表（<10萬行）：使用較小值
SETTINGS index_granularity = 1024;
-- 中等表（10萬-1000萬行）：使用默認值
SETTINGS index_granularity = 8192;
-- 大表（>1000萬行）：使用較大值
SETTINGS index_granularity = 16384;
2. 查詢模式考慮
-- 頻繁點查詢：使用較小值
SETTINGS index_granularity = 4096;
-- 范圍查詢為主：使用默認值
SETTINGS index_granularity = 8192;
-- 批量查詢：使用較大值
SETTINGS index_granularity = 16384;
3. 寫入模式考慮
-- 頻繁小批量寫入：使用較小值
SETTINGS index_granularity = 4096;
-- 批量寫入：使用默認值
SETTINGS index_granularity = 8192;
-- 大批量寫入：使用較大值
SETTINGS index_granularity = 32768;
性能測試示例
1. 查詢性能對比
-- 測試不同粒度的查詢性能
-- 數據量：1000萬行
-- 查詢：WHERE block_number BETWEEN 1000000 AND 1001000
-- index_granularity = 1024
-- 索引標記數：約9766個
-- 查詢時間：較慢
-- index_granularity = 8192
-- 索引標記數：約1221個
-- 查詢時間：快
-- index_granularity = 65536
-- 索引標記數：約153個
-- 查詢時間：中等
2. 內存使用對比
-- 索引標記內存使用估算
-- 每個標記約占用幾十字節
-- 1000萬行數據：
-- 1024粒度：約9766個標記，內存使用高
-- 8192粒度：約1221個標記，內存使用中等
-- 65536粒度：約153個標記，內存使用低
最佳實踐
1. 默認配置
-- 大多數情況下使用默認值
SETTINGS index_granularity = 8192;
2. 特殊場景調整
-- 高并發點查詢
SETTINGS index_granularity = 4096;
-- 大批量數據寫入
SETTINGS index_granularity = 16384;
-- 內存受限環境
SETTINGS index_granularity = 32768;
3. 監控和調優
-- 查看表設置
SELECT * FROM system.tables WHERE name = 'your_table';
-- 監控查詢性能
SELECT * FROM system.query_log
WHERE query LIKE '%your_table%'
ORDER BY event_time DESC;
常見問題
Q: 如何確定最佳值？
A: 通過性能測試，監控查詢時間和內存使用，選擇平衡點。
Q: 可以動態調整嗎？
A: 不可以，需要在創建表時設置，修改需要重建表。
Q: 不同表可以使用不同值嗎？
A: 可以，每個表可以獨立設置。
Q: 設置過小會有什么問題？
A: 內存使用增加，寫入性能下降。
Q: 設置過大會有什么問題？
A: 查詢時需要掃描更多數據，查詢性能下降。
總結
index_granularity = 8192 是ClickHouse的推薦配置，它在查詢性能、內存使用和寫入性能之間達到了很好的平衡。
關鍵要點
- 索引基于ORDER BY列：索引標記是根據ORDER BY子句中定義的列創建的
- 左側匹配原則：查詢時必須從左到右使用ORDER BY中的列，不能跳過中間的列
- 查詢匹配很重要：WHERE條件應該盡量使用ORDER BY列，以獲得最佳性能
- 列順序影響效率：選擇性高的列應該放在ORDER BY的前面
- 默認值適用大多數場景：8192是經過優化驗證的最佳選擇
Web3數據收集項目應用
- 區塊數據：使用默認值8192，適合范圍查詢
- 交易數據：使用默認值8192，平衡查詢和寫入
- 日志數據：使用默認值8192，適合批量查詢
- 任務管理：ORDER BY (start_block, end_block) 配合8192粒度，高效支持任務查詢
除非有特殊需求，否則建議使用默認值8192，這是經過優化驗證的最佳選擇。
浙公網安備 33010602011771號