查询优化器与执行引擎深入

-- 这条查询在行式数据库中逐行执行：
-- 对每一行：读取 x -> 做乘法 -> 过滤 -> 累加
-- 在 ClickHouse 中则是：
SELECT sum(x * 2) FROM numbers(10_000_000);

-- 用一个简单聚合观察向量化效果
SELECT count(), sum(number) FROM numbers(1_000_000_000);

CREATE TABLE events_lc (
    event_time DateTime,
    event_type LowCardinality(String),
    user_id UInt64
) ENGINE = MergeTree()
ORDER BY event_time;

-- 查看当前单 Block 的行数上限
SELECT name, value FROM system.settings WHERE name = 'max_block_size';
-- 默认 65409 行/块

-- 1. 逻辑执行计划（默认）
EXPLAIN SELECT count() FROM events WHERE event_type = 'view';

-- 2. 语法树重写（优化器改写后的 SQL）
EXPLAIN SYNTAX SELECT count() FROM events WHERE event_type = 'view';

-- 3. 物理执行管道（线程 + 算子）
EXPLAIN PIPELINE SELECT count() FROM events WHERE event_type = 'view';

-- 4. 估算行数
EXPLAIN ESTIMATE SELECT count() FROM events WHERE event_type = 'view';

EXPLAIN PIPELINE graph = 1
SELECT event_type, count() FROM events GROUP BY event_type;

EXPLAIN indexes = 1
SELECT count() FROM events
WHERE event_time >= '2024-06-01' AND event_time < '2024-06-02';
-- SelectedParts: N, SelectedGranules: M, SelectedMarks: K

-- 下面两个查询，ClickHouse 会尽量把过滤下推到读 Part 阶段
EXPLAIN PIPELINE SELECT count() FROM events WHERE user_id = 42;
EXPLAIN indexes = 1 SELECT count() FROM events
WHERE event_time BETWEEN '2024-06-01' AND '2024-06-30';

-- 开启/关闭具体优化，用于排查性能差异
SET optimize_aggregation_in_order = 1;
SET optimize_read_in_order = 1;
SET optimize_trivial_count_query = 1;

-- 对比开启前后的计划与耗时
EXPLAIN PIPELINE SELECT count() FROM events;

-- 查看当前线程数
SELECT name, value FROM system.settings WHERE name = 'max_threads';

-- 会话级覆盖
SET max_threads = 16;

-- 单表 30 个 Part，max_threads = 16 时，16 个线程各读一部分 Part
EXPLAIN PIPELINE
SELECT sum(value) FROM events WHERE event_time >= '2024-06-01';

SET parallel_replicas_count = 3;   -- 3 个副本并行
SELECT count() FROM events WHERE event_time >= today() - 1;

-- 单查询内存上限（默认 10GB）
SET max_memory_usage = 20_000_000_000;

-- 允许 GROUP BY / ORDER BY 溢写磁盘
SET max_bytes_before_external_group_by = 10_000_000_000;
SET max_bytes_before_external_sort = 10_000_000_000;

-- 正在运行的查询内存
SELECT query_id, query, memory_usage, elapsed
FROM system.processes
WHERE query NOT ILIKE '%system.processes%';

-- 历史查询的峰值内存
SELECT query, memory_usage, read_rows, query_duration_ms
FROM system.query_log
ORDER BY event_time DESC
LIMIT 10;

-- 反例：全量排序到内存
SELECT * FROM events ORDER BY value DESC;

-- 正例：只取 Top 100，配合 limit 优化
SELECT * FROM events ORDER BY value DESC LIMIT 100;

SELECT
    query_id,
    query_start_time,
    query_duration_ms,
    read_rows,
    read_bytes,
    result_rows,
    memory_usage,
    threads,
    exception_code,
    ProfileEvents['SelectedBytes'] AS selected_bytes
FROM system.query_log
WHERE query_start_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

SELECT
    ProfileEvents['RealTimeMicroseconds'] / 1000000 AS real_s,
    ProfileEvents['UserTimeMicroseconds'] / 1000000 AS cpu_s,
    ProfileEvents['SelectedRows'] AS sel_rows,
    ProfileEvents['SelectedBytes'] AS sel_bytes,
    ProfileEvents['ContextLock'] AS ctx_lock
FROM system.query_log
WHERE query_id = '你的查询ID';

-- 打开 trace_log 采样后，可定位耗时算子
SELECT arrayJoin(trace), count() FROM system.trace_log
WHERE query_id = '你的查询ID'
GROUP BY 1 ORDER BY 2 DESC LIMIT 10;

# 1. 找到慢查询 ID
clickhouse-client --query "SELECT query_id, query FROM system.query_log
WHERE query_duration_ms > 1000 ORDER BY query_start_time DESC LIMIT 5"

-- 2. 看执行计划与索引命中
EXPLAIN indexes = 1 SELECT ...;      -- 确认裁剪
EXPLAIN PIPELINE SELECT ...;         -- 确认并行
-- 3. 看指标
SELECT read_rows, read_bytes, memory_usage, threads
FROM system.query_log WHERE query_id = '...';
-- 4. 对照 profile 决定优化方向