MergeTree 合并原理与数据生命周期

CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
SETTINGS index_granularity = 8192;

INSERT INTO events VALUES
    ('2024-06-01 10:00:00', 1, 'view', 1.5),
    ('2024-06-01 10:00:05', 2, 'click', 2.0);

-- 同一 INSERT 重发一次，第二次会被去重拒绝
INSERT INTO events VALUES ('2024-06-01 10:00:00', 1, 'view', 1.5);

-- 在 Replicated 引擎上，跨副本通过 block_id 去重，窗口由
-- replicated_deduplication_window 控制（默认 1000 个块）

-- 异步插入：server 端攒 1 秒或 10000 行再落盘
INSERT INTO events
SETTINGS async_insert = 1, async_insert_max_data_size = 10000000
SELECT * FROM source_events;

SELECT name, partition, part_type, rows, bytes_on_disk, level, active
FROM system.parts
WHERE table = 'events' AND active = 1;

-- 强制小表也使用 Wide 形态，便于观察列文件
CREATE TABLE events_wide (...)
ENGINE = MergeTree()
ORDER BY event_time
SETTINGS min_bytes_for_wide_part = 0, min_rows_for_wide_part = 0;

-- 命中主键前缀：只读相关 granule
SELECT count() FROM events WHERE event_time >= '2024-06-01' AND event_time < '2024-06-02';

-- 非主键列过滤：退化为扫描
SELECT count() FROM events WHERE event_type = 'view';

CREATE TABLE events_idx (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    value Float64,
    INDEX idx_event_type event_type TYPE minmax GRANULARITY 4,
    INDEX idx_user_id user_id TYPE minmax GRANULARITY 4
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

-- 验证索引是否命中：输出中 SelectedParts / SelectedGranules 下降即生效
EXPLAIN indexes = 1
SELECT count() FROM events_idx WHERE event_type = 'view';

-- 查看当前正在执行的合并任务
SELECT database, table, elapsed, progress,
       total_parts, parts_merged, result_part_path
FROM system.merges;

SELECT event_time, table, part_name, result_part_name, merged_from,
       merge_reason, rows, bytes_read
FROM system.part_log
WHERE event_type = 'MergeParts'
ORDER BY event_time DESC
LIMIT 10;

-- 等待所有分区的合并完成（生产谨慎使用，会阻塞写入）
OPTIMIZE TABLE events FINAL;

-- 只优化指定分区
OPTIMIZE TABLE events PARTITION 202406 FINAL;

-- 限制单次合并的规模，避免大 Part 合并长时间占满 IO
SET merge_max_block_size = 16384;

CREATE TABLE events_ttl (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY event_time
TTL event_time + INTERVAL 90 DAY;

-- 查看 TTL 配置是否被正确解析
SELECT name, ttl FROM system.tables WHERE name = 'events_ttl';

-- config.xml 中定义存储策略：
-- <storage_configuration>
--   <disks><disk_s3><type>s3</type>...</disk_s3></disks>
--   <policies><hot_cold>
--     <volumes><hot><disk>default</disk></hot>
--     <cold><disk>disk_s3</disk></cold>
--   </volumes></hot_cold></policies>

CREATE TABLE events_tier (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
ORDER BY event_time
TTL event_time + INTERVAL 30 DAY TO VOLUME 'cold'
SETTINGS storage_policy = 'hot_cold';

-- 删除整月分区（秒级完成，远快于 DELETE）
ALTER TABLE events DROP PARTITION '202406';

-- 把分区分离到 detached 目录（用于备份或排障）
ALTER TABLE events DETACH PARTITION '202405';
ALTER TABLE events ATTACH PARTITION '202405';

-- 返回 Ok 或错误信息
CHECK TABLE events;

-- 只检查一个分区
CHECK TABLE events PARTITION 202406;

-- 把疑似损坏的 Part 移入 detached 目录，避免影响查询
ALTER TABLE events DETACH PART '202406_1_10_2';

-- 直接从 detached 中丢弃（先确认有备份或副本可恢复）
ALTER TABLE events DROP DETACHED PART '202406_1_10_2';

SELECT database, table, replica_name, is_leader,
       parts_count, queue_size, inserts_in_queue, merges_in_queue
FROM system.replicas
WHERE table = 'events';

-- 更新指定行的字段
ALTER TABLE events UPDATE value = value * 1.1
WHERE user_id = 42;

-- 删除指定行
ALTER TABLE events DELETE WHERE event_type = 'spam';

SELECT database, table, mutation_id, command,
       create_time, parts_to_do, parts_done, is_done, latest_fail_reason
FROM system.mutations
WHERE table = 'events';

-- 走轻量删除（默认 lightweight_delete 开启）
ALTER TABLE events DELETE WHERE user_id = 0;

-- 查询时自动过滤被删除的行；最终合并时才真正物理清理
SELECT count() FROM events WHERE user_id = 0;

SELECT partition, count() AS parts, sum(rows) AS total_rows
FROM system.parts
WHERE table = 'events' AND active = 1
GROUP BY partition
ORDER BY partition;

-- 生产常用合并与写入调优
SET max_partitions_per_insert_block = 100;      -- 单次 INSERT 分区数上限
SET max_insert_block_size = 1048576;            -- 单块行数上限
SET parts_to_throw_insert = 300;                -- 超过则拒绝新写入
SET parts_to_delay_insert = 150;                -- 超过则减慢写入
SET background_pool_size = 8;                   -- 合并线程数（重启生效）
SET index_granularity = 8192;                   -- 建表时定义，勿轻易调大