埋点分析系统 — 数据库索引优化文档

📅 2026-07-30 📂 obsidian ⏱ 约 39 分钟
📑 目录
  1. 目录
  2. 1. 当前状态诊断
  3. 2. 核心问题分析
  4. 3. 优化方案详解
  5. 4. 实施步骤与 SQL
  6. 5. 效果验证方法
  7. 6. 风险与回滚
  8. 附录：实施路线图

埋点分析系统 — 数据库索引优化文档

文档版本：v1.0.0

检查日期：2026-07-30

目标表：clklog.log_analysis

目录

  1. 当前状态诊断
  2. 核心问题分析
  3. 优化方案详解
  4. 实施步骤与 SQL
  5. 效果验证方法
  6. 风险与回滚

1. 当前状态诊断

1.1 表基本信息

指标值
总行数2,911,810 (约 291 万)
总大小165.45 MB (0.16 GB)
活跃分区数340
活跃部件数509
索引粒度 (index_granularity)8,192

1.2 表引擎与键结构

ENGINE = MergeTree
PARTITION BY stat_date          -- 按日期分区 ✓ 合理
ORDER BY distinct_id             -- 仅按用户ID排序 ✗ 不合理
SETTINGS index_granularity = 8192

1.3 数据跳过索引

当前状态：无任何数据跳过索引

所有查询均无法利用跳数索引，必须扫描分区内的全部数据。

1.4 异常分区（严重）

当前存在大量未来日期的异常分区：

分区日期范围分区数说明
2026-08 ~ 2026-12多个未来数月的分区（当前为 2026-07）
2027-07-0612027 年
2030-06-xx32030 年
2032-01/0832032 年
2036-01-0122036 年
2050-11-0812050 年
2052-04/0622052 年

影响：

  • 这些异常分区虽然数据量不大（每分区仅几行），但增加了分区元数据开销
  • 查询时需要遍历更多分区目录
  • 反映出上游数据写入时 stat_date 字段未做校验

1.5 关键列基数分析

列名非空行数去重数基数比建议索引类型
project_name2,911,81010.00%set (极低基数)
typeContext2,911,81030.00%set (极低基数)
event2,911,718160.00%set (极低基数)
os1,471,124470.00%set (极低基数)
screen_name24,195810.33%set (低基数)
province2,911,7421080.00%set (极低基数)
city2,911,7425040.02%set (低基数)
distinct_id2,911,81029,0081.00%bloom_filter (中基数)

说明：由于当前 project_name 仅有 1 个值（基数为 0.00%），短期收益有限，但系统设计支持多项目，长期仍建议建立索引。

1.6 近期查询模式分析（来自 system.query_log）

查询类型扫描行数耗时涉及列
count(distinct distinct_id) 累计用户2,870,198227msdistinctid, statdate
count(distinct distinct_id) 日活/新增40,190 ~ 48,5948~9msdistinctid, statdate
页面路径分析40,19615~18msscreenname, statdate, distinctid, eventsession_id
事件统计48,59411msevent, stat_date
网络类型/设备统计40,19010msnetworktype, statdate

关键发现：累计用户查询（无 stat_date 过滤）扫描了 287 万行，是当前最慢的查询。

2. 核心问题分析

问题 1：排序键设计不合理（高优先级）

现状：ORDER BY (distinct_id)

问题：

  • 绝大多数查询都包含 statdate 范围过滤和/或 event / screenname / project_name 过滤
  • 排序键中无这些高频过滤列，导致 ClickHouse 无法利用主键索引（primary index）进行数据跳过
  • 即使有时间分区，每个分区内仍需扫描全部数据

举例：一个 WHERE stat_date = '2026-07-30' AND event = '$AppClick' 查询：

  • 利用分区键跳过了非 2026-07-30 的数据 ✓
  • 但该分区内有 4 万行数据，由于排序键是 distinct_id，ClickHouse 无法快速定位 event = '$AppClick' 的行，必须全部扫描 ✗

问题 2：无任何数据跳过索引（高优先级）

现状：未创建任何跳数索引

问题：

  • 即使查询带有非常具体的等值条件（如 event = '$AppClick'、province = '上海'），也无法跳过不匹配的 granule
  • 每个查询必须读取每个涉及分区的所有列数据

问题 3：异常分区数据（中优先级）

现状：存在 2026-08 至 2052 年的异常分区

问题：

  • 虽然这些分区数据量很小，但增加了查询时的分区元数据遍历开销
  • 反映出上游数据质量问题，应从源头修复

问题 4：累计用户查询扫描全表（中优先级）

现状：count(distinct distinct_id) 无日期范围限制时扫描 287 万行，耗时 227ms

问题：

  • 当前数据量小（291 万行），影响不大
  • 随着数据增长到数亿行，该查询会成为严重瓶颈

3. 优化方案详解

3.1 方案一：添加数据跳过索引（优先实施，风险最低）

为高频过滤列添加数据跳过索引（Data Skipping Index）。

索引类型选择原则

索引类型适用场景原理
minmax数值/日期列，范围查询存储每个 granule 的最小/最大值，查询时跳过不满足范围的 granule
set(max_rows)低基数字符串列，等值查询存储每个 granule 出现的所有取值，查询时如果值不在 set 中则跳过
bloom_filter中/高基数字符串列，等值查询布隆过滤器，可能有误报但空间效率高
ngrambfv1 / tokenbfv1文本搜索，LIKE 查询N-gram / Token 布隆过滤器

推荐索引列表（按优先级）

#索引名列名类型粒度理由
1idx_eventeventset(0)116 种取值，几乎所有事件分析页都用
2idxscreennamescreen_nameset(0)1页面路径分析高频过滤，81 种取值
3idx_provinceprovinceset(0)1地域分析，108 种取值
4idx_typetypeContextset(0)1仅 3 种取值，成本极低
5idx_ososset(0)1设备分析，47 种取值
6idx_citycityset(0)1504 种取值，仍适合 set
7idxdistinctiddistinct_idbloom_filter129,008 种取值，布隆过滤高效

注：set(0) 表示不限制 set 大小（ClickHouse 会自动管理）。如果担心内存，可改为 set(100) 等具体值。

预期收益

  • 带有 event 等值条件的查询：预计可跳过 60-90% 的 granule
  • 带有 screen_name 等值条件的查询：预计可跳过 70-95% 的 granule
  • 累计用户查询（配合 bloom_filter）：预计可跳过部分 granule

3.2 方案二：优化排序键（评估后实施，成本较高）

当前：ORDER BY (distinct_id)

建议：改为 ORDER BY (projectname, statdate, event, distinct_id)

设计理由

  1. project_name — 最高优先级的等值过滤（当前虽仅有 1 个项目，但系统设计支持多项目）
  2. stat_date — 第二高频条件（几乎所有查询都有日期范围），虽然已有分区键，但在排序键中可进一步优化
  3. event — 第三高频条件，事件分析页核心过滤列
  4. distinct_id — 保留在末尾，支持用户级查询

收益

  • 所有带 projectname + statdate + event 条件的查询将获得数量级性能提升
  • 主键索引（primary index）将与实际查询模式匹配

代价

  • 需要重建整个表（数据迁移），约 165 MB 数据预计耗时数分钟
  • 期间可能影响写入（需视 ClickHouse 版本而定，新版本支持在线修改但仍有开销）

3.3 方案三：清理异常分区（快速实施）

删除所有 2030 年及以后的异常分区，以及 2026 年 8 月及以后的未来分区。

3.4 方案四：累计用户查询优化（中长期）

对于无日期限制的 count(distinct distinct_id) 查询，可考虑：

  1. 使用物化视图：预计算每日的 distinct_id 集合，累计查询时合并
  2. 使用聚合表：如果只需要总数而非去重明细，可改用 AggregatingMergeTree
  3. 增加时间窗口限制：业务上考虑是否真的需要"全历史累计"，还是"近 90 天活跃"即可

4. 实施步骤与 SQL

阶段一：添加数据跳过索引（30-60 分钟，低风险）

该操作是在线的，不阻塞读写，但会消耗部分 CPU 和 IO。建议在业务低峰期执行。

-- ================================================
-- 步骤 1：添加 7 个数据跳过索引
-- ================================================

-- 1. event (16 种取值，最高频过滤)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_event event TYPE set(0) GRANULARITY 1;

-- 2. screen_name (81 种取值，页面路径分析)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_screen_name screen_name TYPE set(0) GRANULARITY 1;

-- 3. province (108 种取值，地域分析)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_province province TYPE set(0) GRANULARITY 1;

-- 4. typeContext (3 种取值，成本极低)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_type typeContext TYPE set(0) GRANULARITY 1;

-- 5. os (47 种取值，设备分析)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_os os TYPE set(0) GRANULARITY 1;

-- 6. city (504 种取值，城市级分析)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_city city TYPE set(0) GRANULARITY 1;

-- 7. distinct_id (29,008 种取值，用户查询)
ALTER TABLE clklog.log_analysis
ADD INDEX idx_distinct_id distinct_id TYPE bloom_filter GRANULARITY 1;


-- ================================================
-- 步骤 2：物化索引（让已有数据也能使用索引）
-- ================================================
-- 注意：MATERIALIZE 是最耗时的步骤，需在低峰期执行
-- 可以逐条执行，观察每条的耗时

ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_event;
ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_screen_name;
ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_province;
ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_type;
ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_os;
ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_city;
ALTER TABLE clklog.log_analysis MATERIALIZE INDEX idx_distinct_id;


-- ================================================
-- 步骤 3：验证索引已创建
-- ================================================
SELECT
    name,
    type,
    expr,
    granularity,
    status
FROM system.data_skipping_indices
WHERE database = 'clklog'
  AND table = 'log_analysis';

阶段二：清理异常分区（5 分钟，低风险）

-- ================================================
-- 先查看所有异常分区
-- ================================================
SELECT
    partition,
    sum(rows) as total_rows,
    round(sum(bytes_on_disk) / 1024, 2) as size_kb
FROM system.parts
WHERE database = 'clklog'
  AND table = 'log_analysis'
  AND active
  AND (partition >= '2026-08-01' OR partition < '2020-01-01')
GROUP BY partition
ORDER BY partition;


-- ================================================
-- 删除 2030 年及以后的分区（建议先执行）
-- ================================================
ALTER TABLE clklog.log_analysis DROP PARTITION '2030-06-18';
ALTER TABLE clklog.log_analysis DROP PARTITION '2030-06-28';
ALTER TABLE clklog.log_analysis DROP PARTITION '2030-06-29';
ALTER TABLE clklog.log_analysis DROP PARTITION '2032-01-01';
ALTER TABLE clklog.log_analysis DROP PARTITION '2032-01-02';
ALTER TABLE clklog.log_analysis DROP PARTITION '2032-08-02';
ALTER TABLE clklog.log_analysis DROP PARTITION '2036-01-01';
ALTER TABLE clklog.log_analysis DROP PARTITION '2050-11-08';
ALTER TABLE clklog.log_analysis DROP PARTITION '2052-04-29';
ALTER TABLE clklog.log_analysis DROP PARTITION '2052-06-05';


-- ================================================
-- 删除 2026 年 8 月及以后的未来分区（可选，执行前确认数据来源）
-- ================================================
-- 注意：如果这些分区是合法的测试数据或未来写入预留，请不要删除
-- 建议先确认上游数据写入逻辑后再决定

-- 查看有哪些未来分区
SELECT partition, sum(rows) as cnt
FROM system.parts
WHERE database = 'clklog' AND table = 'log_analysis' AND active
  AND partition >= '2026-08-01' AND partition < '2030-01-01'
GROUP BY partition ORDER BY partition;

-- 如需删除，示例：
-- ALTER TABLE clklog.log_analysis DROP PARTITION '2026-08-01';
-- ... 依次执行

阶段三：评估并实施排序键改造（低峰期，2-4 小时）

重要：此操作需要重建表，请在充分评估后再执行。当前表仅 165MB，实际耗时可能很短。

方案 A：通过 MATERIALIZE TTL 或其他在线方式（如果 ClickHouse 版本支持）

-- 检查 ClickHouse 版本
SELECT version();

-- 21.x 及以上版本支持 ALTER TABLE ... MODIFY ORDER BY
ALTER TABLE clklog.log_analysis
MODIFY ORDER BY (project_name, stat_date, event, distinct_id);

方案 B：传统方式（新建表 → 迁移数据 → 切换表名）

-- ================================================
-- 步骤 1：创建新表（使用优化后的排序键）
-- ================================================
CREATE TABLE clklog.log_analysis_new
(
    -- 此处省略所有列定义，与原表一致
    -- ... 参考 SHOW CREATE TABLE clklog.log_analysis 的输出
)
ENGINE = MergeTree
PARTITION BY stat_date
ORDER BY (project_name, stat_date, event, distinct_id)
SETTINGS index_granularity = 8192;


-- ================================================
-- 步骤 2：拷贝数据
-- ================================================
INSERT INTO clklog.log_analysis_new
SELECT * FROM clklog.log_analysis;


-- ================================================
-- 步骤 3：验证数据一致性
-- ================================================
SELECT count() FROM clklog.log_analysis;
SELECT count() FROM clklog.log_analysis_new;


-- ================================================
-- 步骤 4：切换表名（需要短暂停机）
-- ================================================
-- 暂停写入后执行：
RENAME TABLE clklog.log_analysis TO clklog.log_analysis_old;
RENAME TABLE clklog.log_analysis_new TO clklog.log_analysis;

-- 恢复写入，验证正常后：
-- DROP TABLE clklog.log_analysis_old;

5. 效果验证方法

5.1 验证索引是否生效

-- 使用 EXPLAIN indexes = 1 查看索引使用情况
EXPLAIN indexes = 1
SELECT count(*)
FROM clklog.log_analysis
WHERE stat_date = '2026-07-30'
  AND event = '$AppClick';

预期输出中应包含：

Index `idx_event` has dropped 1/2 granules.

5.2 性能对比测试

-- 测试 1：事件过滤查询（加索引前后对比）
SELECT event, count(*)
FROM clklog.log_analysis
WHERE stat_date BETWEEN '2026-07-23' AND '2026-07-30'
  AND event = '$AppClick'
GROUP BY event;

-- 测试 2：页面路径查询
SELECT screen_name, count(*)
FROM clklog.log_analysis
WHERE stat_date BETWEEN '2026-07-23' AND '2026-07-30'
  AND screen_name = '首页'
GROUP BY screen_name;

-- 测试 3：地域查询
SELECT province, count(distinct distinct_id)
FROM clklog.log_analysis
WHERE stat_date BETWEEN '2026-07-23' AND '2026-07-30'
  AND province = '上海市'
GROUP BY province;

-- 测试 4：累计用户查询
SELECT count(distinct distinct_id)
FROM clklog.log_analysis;

记录每条查询的：

  • 执行时间（querydurationms）
  • 读取行数（read_rows）
  • 读取字节数（read_bytes）

5.3 观察生产慢查询

实施索引后持续观察 system.query_log：

SELECT
    query,
    query_duration_ms,
    read_rows,
    memory_usage / 1024 / 1024 as memory_mb
FROM system.query_log
WHERE type = 'QueryFinish'
  AND has(databases, 'clklog')
  AND query LIKE '%log_analysis%'
  AND query_duration_ms > 100
ORDER BY event_time DESC
LIMIT 30;

6. 风险与回滚

6.1 风险评估

操作风险等级说明
添加数据跳过索引低在线操作，不阻塞读写，仅增加部分 CPU/IO 开销
物化索引低-中消耗 IO，建议低峰期执行，可随时中断（中断后可重新 MATERIALIZE）
删除异常分区低快速操作，确认数据可删后执行
修改排序键中-高需要重建表，视数据量和 ClickHouse 版本可能需要短暂停机

6.2 回滚方案

索引回滚

-- 删除某个索引
ALTER TABLE clklog.log_analysis DROP INDEX idx_event;

-- 删除所有索引（如需整体回滚）
ALTER TABLE clklog.log_analysis DROP INDEX idx_event;
ALTER TABLE clklog.log_analysis DROP INDEX idx_screen_name;
ALTER TABLE clklog.log_analysis DROP INDEX idx_province;
ALTER TABLE clklog.log_analysis DROP INDEX idx_type;
ALTER TABLE clklog.log_analysis DROP INDEX idx_os;
ALTER TABLE clklog.log_analysis DROP INDEX idx_city;
ALTER TABLE clklog.log_analysis DROP INDEX idx_distinct_id;

排序键回滚

如果使用方案 B（新表迁移）：

  • 保留旧表 loganalysisold 至少 7 天
  • 如需回滚：RENAME TABLE clklog.loganalysis TO clklog.loganalysisbad; RENAME TABLE clklog.loganalysisold TO clklog.loganalysis;

分区回滚

DROP PARTITION 是不可逆操作。如果担心误删：

  • 先 DETACH PARTITION 而非 DROP PARTITION（detach 后数据在 detached 目录，可重新 ATTACH）
  • 或先备份该分区数据
-- 安全方式：先 detach（可恢复）
ALTER TABLE clklog.log_analysis DETACH PARTITION '2030-06-29';

-- 确认无误后再彻底删除（detached 目录下手动清理）
-- 或重新附加：
ALTER TABLE clklog.log_analysis ATTACH PARTITION '2030-06-29';

附录：实施路线图

阶段操作建议时间预计耗时负责人
阶段一添加 7 个数据跳过索引立即（低峰期）10 minDBA
阶段一物化索引立即（低峰期）20-50 minDBA
阶段一验证索引效果索引物化完成后5 min开发
阶段二清理 2030+ 异常分区1 周内（确认上游无问题）5 minDBA
阶段二观察 1-2 周查询性能索引上线后持续开发/DBA
阶段三评估排序键改造收益索引上线 2 周后-开发/DBA
阶段三如需改造，低峰期实施排序键优化下个维护窗口0.5-2 hDBA

文档结束

obsidian

📎 相关文章

📊 股票代码总索引

title股票代码总索引 created20260805 updated20260805 tags[股票代码, 索引, MOC] 📊 股票代码总索引 [!inf...

科技股下半年机会

date20260720 updated20260720 conversation_idf21364a807fd487da6923432916c6b30 tit...

社交数据 - 社交数据

社交数据 社交数据 共 36 张数据表 标签#数据字典 #13_社交数据 表列表 表名 中文名 字段数 说明 [[T_SOC]] 新三板代码表 7 [[T_SO...