ClickHouse 审计日志查询优化先拆查询职责再处理读放大这篇文章做了脱敏处理。业务名、表名、机器规格和真实线上规模都不展开只保留问题形态、排查路径和改造思路。为了让结果更容易复现我另外补了一个本地 ClickHouse 测试实验用脚本构造审计日志数据再对优化前后的查询做对比。所以这篇文章里有两条线项目背景来自真实问题但业务信息和规模做了脱敏 实验数据来自本地 demo主要用来验证查询路径变化后的 read_rows / read_bytes 变化场景很常见后台有一个审计日志查询页面日志已经进了 ClickHouse。刚开始大家对它的预期比较简单既然已经用列存了查询应该不会太差。但数据量和查询条件一起上来以后页面体验开始变得不稳定。最开始暴露出来的现象大概是这样查最近一天还可以 查最近一周开始等 查一个月基本像卡住 一加操作人、资源 ID、关键字接口就容易超时 概览卡片有时比列表还慢 导出任务还能跑但在线列表体验很差后来回头看真正的问题并不在某一条 SQL而是我们把几类访问模式完全不同的需求都压在了同一条在线查询链路上。明细列表、总数统计、概览聚合、关键字搜索、详情字段读取看起来都叫“审计日志查询”但它们对 ClickHouse 的使用方式其实不一样。先看一眼基线性能优化最怕一上来就说“很慢”。慢到什么程度、慢在哪里、慢在什么条件下如果不先量化后面很容易变成凭感觉改 SQL。本地 demo 里我用 500 万行数据、10 轮 benchmark 做了一次对比。这个规模不能代表生产环境但足够观察几个典型查询的读放大情况。运行方式是cd blog\audit-log-query-optimization\demo\clickhouse-audit-demo# 先验证流程和 SQL 是否能跑通.\scripts\run-demo.ps1-Rows 100000-Repeat 1# 再用更大的数据量做模拟压测.\scripts\run-demo.ps1-Rows 5000000-Repeat 10最终07_summary.sql会把每类查询的多轮结果汇总出来。这次 500 万行 demo 里优化前几个典型查询大概是这样查询P95max_read_rowsmax_read_bytes列表深分页old_list_offset37 ms1,217,852526.33 MiB实时总数old_count7 ms1,217,85218.58 MiB概览扫明细old_overview_from_detail11 ms1,217,85230.20 MiBJSON 关键字old_keyword_json_scan19 ms955,708315.87 MiB本地单机 ClickHouse 跑得很快所以这里不要盯着毫秒数本身看。更值得看的其实是read_rows和read_bytes同样是查一个租户最近 7 天的数据旧查询经常读到百万行级别。列表查询还会把detail_json一起读出来所以read_bytes很高。原来的表和查询长什么样原始表结构做过简化大概是这样CREATETABLEaudit_log(id UInt64,tenant_id UInt64,operator_id UInt64,actionLowCardinality(String),resource_type LowCardinality(String),resource_id String,client_ip String,result UInt8,summary String,detail_json String,created_at DateTime64(3))ENGINEMergeTreePARTITIONBYtoYYYYMM(created_at)ORDERBY(created_at,tenant_id,id);这个ORDER BY第一眼看没什么问题。日志天然和时间相关把created_at放前面好像很顺。但真实查询不是“全局按时间查日志”而是“某个租户在某段时间内查日志”。这个差别很关键。旧表优先按时间组织数据租户数据会散在时间范围里而页面几乎每次都会带tenant_id。原来的列表 SQL 也比较典型SELECTid,tenant_id,operator_id,action,resource_type,resource_id,client_ip,result,summary,detail_json,created_atFROMaudit_logWHEREtenant_id{tenant_id:UInt64}ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}AND({operator_id:UInt64}0ORoperator_id{operator_id:UInt64})AND({action:String}ORaction{action:String})AND({resource_id:String}ORresource_id{resource_id:String})AND({keyword:String}ORpositionCaseInsensitive(summary,{keyword:String})0ORpositionCaseInsensitive(JSONExtractString(detail_json,targetName),{keyword:String})0)ORDERBYcreated_atDESCLIMIT{offset:UInt64},{page_size:UInt64};为了显示总条数页面还会再跑一条SELECTcount()FROMaudit_logWHEREtenant_id{tenant_id:UInt64}ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}AND...这套写法在数据量小的时候还能扛住。数据上来以后几个问题会叠在一起ORDER BY (created_at, tenant_id, id)和租户内查询不匹配列表查询直接读取detail_json每行都带着一大块详情LIMIT offset, size翻到后面会扫描并丢弃大量结果count()和列表一起压在线链路概览卡片每次扫明细表聚合高频 JSON 字段在查询时动态解析关键字搜索和普通列表混在一起。ClickHouse 很擅长列式扫描和聚合但它不是魔法。排序键、分区、字段模型和查询入口如果没有按访问模式设计照样会读很多不该读的数据。先确认问题是不是在 ClickHouse 查询最开始没有直接重建表。日志查询慢表象可能来自很多地方比如前端表格渲染、接口响应体太大、权限补全、网络传输、ClickHouse 后台 merge甚至是导出任务和在线查询抢资源。所以先把一次请求拆开看参数解析 权限范围计算 count 查询 概览统计查询 列表查询 详情字段反序列化 操作人和资源信息补全 JSON 返回慢请求里ClickHouse 查询时间占比最高。进一步看system.query_log关注的不是哪条 SQL “看起来最长”而是读了多少数据。当时用的定位 SQL 类似这样SELECTquery_duration_ms,read_rows,formatReadableSize(read_bytes)ASread_bytes,formatReadableSize(memory_usage)ASmemory_usage,ProfileEvents[SelectedParts]ASselected_parts,ProfileEvents[SelectedMarks]ASselected_marks,normalizedQueryHash(query)ASquery_hash,left(query,160)ASsample_queryFROMsystem.query_logWHEREtypeQueryFinishANDevent_timenow()-INTERVAL30MINUTEANDqueryILIKE%audit_log%ORDERBYquery_duration_msDESCLIMIT20;这里有几个指标很有用read_rows高通常说明过滤条件没能把读取范围压下来read_bytes高经常和读取大字段有关SelectedParts、SelectedMarks偏大要回头看分区和主键裁剪memory_usage高通常要看聚合、排序或者大字段读取。做个实验把问题复现出来为了避免这篇文章只停留在经验描述我补了一个简化 demo。它不依赖真实业务系统只用 Docker 起一个单机 ClickHouse通过numbers()构造审计日志数据。demo 的目录结构是demo/clickhouse-audit-demo ├── docker-compose.yml ├── README.md ├── scripts │ ├── run-demo.ps1 │ └── run-demo.sh └── sql ├── 01_schema.sql ├── 02_load_old.sql ├── 03_benchmark_before.sql ├── 04_migrate.sql ├── 05_benchmark_after.sql ├── 06_validate.sql └── 07_summary.sql脚本会做几件事步骤脚本作用101_schema.sql建旧表、新表、概览聚合表、物化视图、benchmark 结果表202_load_old.sql用numbers()构造旧表数据303_benchmark_before.sql跑旧表列表、count、概览、JSON 关键字查询404_migrate.sql把旧表数据迁移到新表同时展开target_id、target_name505_benchmark_after.sql跑新表游标列表、hasMore、物化视图概览和目标字段查询606_validate.sql校验迁移后总行数、租户日维度行数、展开字段、概览结果707_summary.sql汇总多轮 benchmark输出avg_ms、p95_ms、max_ms每个 benchmark 查询都会打一个log_comment然后从system.query_log把duration_ms、read_rows、read_bytes、memory_usage取出来写入结果表。几个 case 的含义如下case_name含义对应优化点old_list_offset旧表 offset 深分页并读取detail_json排序键不匹配、深分页、列表背详情字段old_count旧表实时总数统计拆掉实时 countold_overview_from_detail概览每次扫明细表聚合概览统计走物化视图old_keyword_json_scan查询时解析 JSON 并做包含匹配JSON 高频字段写入时展开new_list_cursor新表游标分页只查摘要字段新排序键、游标分页、列表瘦身new_has_more用LIMIT pageSize 1判断是否还有下一页拆掉实时 countnew_overview_materialized_view从小时汇总表查概览物化视图预聚合new_target_field_query使用展开后的target_id查询高频 JSON 字段普通列化这套 demo 不模拟生产里的分片、副本、冷热盘、并发流量和后台任务。它的作用更单纯验证同样的数据在查询路径变化后读行数和读字节数是不是会下降。第一步先给在线查询划边界原来页面默认查最近 7 天用户也可以直接选很大的时间范围。对审计日志来说这个默认值有点危险因为列表页一打开就会触发查询。这一步先做入口约束默认时间范围从最近 7 天改成最近 24 小时普通列表查询最大允许 31 天超过 31 天的查询走异步导出不阻塞在线接口。这不是为了限制用户而是把两类需求分开在线排障查最近一段时间要求快 合规审计查较长时间要求全入口约束能减少一些无意义的大范围查询但它解决不了排序键不匹配、深分页、实时 count 和概览聚合。所以它只是把入口先收住真正的读放大还要继续处理。第二步按真实查询模式重建排序键ClickHouse 里很容易误判的一点是把ORDER BY当成普通排序字段。对 MergeTree 来说ORDER BY决定数据在磁盘上的组织方式也决定主键稀疏索引能不能少读数据。旧表的ORDER BY (created_at, tenant_id, id)更像是在服务全局时间查询但真实查询大多是租户内时间范围查询。新表改成CREATETABLEaudit_log_v2(id UInt64,tenant_id UInt64,event_dateDateDEFAULTtoDate(created_at),operator_id UInt64,actionLowCardinality(String),resource_type LowCardinality(String),resource_id String,client_ip String,result UInt8,summary String,detail_json String,target_id StringDEFAULT,target_name StringDEFAULT,created_at DateTime64(3),INDEXidx_resource_id resource_idTYPEbloom_filter(0.01)GRANULARITY4,INDEXidx_operator_id operator_idTYPEset(10000)GRANULARITY4,INDEXidx_actionactionTYPEset(256)GRANULARITY4,INDEXidx_target_id target_idTYPEbloom_filter(0.01)GRANULARITY4)ENGINEMergeTreePARTITIONBYtoYYYYMM(created_at)ORDERBY(tenant_id,event_date,created_at,id)SETTINGS index_granularity8192;tenant_id放在排序键最前面是因为查询基本都在租户范围内发生。event_date用来配合按天、按周、按月的时间范围过滤让主键裁剪更干净。这里没有把operator_id、resource_id、action都塞进排序键。排序键只能有一套字段塞得越多不一定越好反而可能破坏最常见的租户时间查询。低频筛选条件更适合配合跳数索引或者放到更合适的检索链路里。第三步把 offset 分页改成游标分页原来的分页是ORDERBYcreated_atDESCLIMIT10000,50;这种写法在后台系统里很常见但放到日志表里会越来越慢。ClickHouse 仍然需要找到前面 10000 条满足条件的记录再丢掉它们最后返回 50 条。改造后用(created_at, id)做游标SELECTid,tenant_id,operator_id,action,resource_type,resource_id,client_ip,result,summary,created_atFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDevent_datetoDate({start:DateTime64(3)})ANDevent_datetoDate({end:DateTime64(3)})ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}WHERE(created_at{cursor_time:DateTime64(3)}OR(created_at{cursor_time:DateTime64(3)}ANDid{cursor_id:UInt64}))ORDERBYcreated_atDESC,idDESCLIMIT50;第一页不传游标下一页带上上一页最后一条记录的created_at和id。这个方案不适合直接跳到第 200 页。但审计日志查询本来就不是商品列表真实使用里更多是不断向后翻或者缩小条件重新查。这里的收益不是某个语法技巧而是避免引擎做无意义扫描。第四步列表和详情分开审计日志详情里有一段detail_json里面放请求参数、变更前后字段、扩展上下文等信息。这个字段对详情页有用但列表页并不需要。原来列表接口直接把它查出来再交给前端决定展示什么。数据量小时这样省事数据量上来以后代价就很明显多读列压缩块解压成本更高响应体更大后端序列化更重前端渲染也会被拖慢。列表查询改成只查摘要字段SELECTid,tenant_id,operator_id,action,resource_type,resource_id,client_ip,result,summary,created_atFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDevent_datetoDate({start:DateTime64(3)})ANDevent_datetoDate({end:DateTime64(3)})ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}WHERE...ORDERBYcreated_atDESC,idDESCLIMIT50;详情页再按tenant_id、created_at和id单独查SELECTdetail_jsonFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDevent_datetoDate({created_at:DateTime64(3)})ANDcreated_at{created_at:DateTime64(3)}-INTERVAL1SECONDANDcreated_at{created_at:DateTime64(3)}INTERVAL1SECONDWHEREid{id:UInt64}LIMIT1;ClickHouse 是列存列表不读大字段收益很直接。更重要的是接口职责也清楚了列表负责定位详情负责展开。第五步把 count 从主链路拿掉很多后台列表默认都显示总条数共 123456 条但审计日志这种表带复杂条件实时count()的成本很高。尤其是时间范围大、条件不够精准时count 查询本身可能比列表还慢。这一步没有直接删除总数而是改了交互和接口策略首屏默认不计算精确总数列表接口只返回hasMore需要总数时单独触发统计相同条件的统计结果短时间缓存超过阈值的统计返回“超过 N 条”不继续硬数。后端从“必须 count 完才能返回列表”变成先查 pageSize 1 条 如果多出 1 条说明 hasMore true 返回前 pageSize 条这里最关键的不是技术而是承认一个事实不是所有列表都必须实时显示精确总数。对审计日志来说用户更关心能不能快速定位记录而不是每次打开页面都知道精确总量。第六步概览统计走物化视图列表稳下来以后还有一个容易被忽略的问题概览。页面顶部有几个卡片和趋势图今日操作次数 失败操作次数 高风险操作次数 按操作类型分布 按小时趋势 Top 操作人这些数字看起来只是几个卡片但原来的做法是每次打开页面都扫明细表聚合SELECTtoStartOfHour(created_at)AShour,action,result,count()AScnt,uniqExact(operator_id)ASoperator_cntFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}GROUPBYhour,action,resultORDERBYhour;这类查询和列表查询的慢法不一样。列表只返回 50 条概览则要把时间范围内的数据扫完再聚合。时间范围一大它会和列表查询抢资源。所以概览从明细查询里拆出来用物化视图预聚合到小时粒度CREATETABLEaudit_log_overview_hour(tenant_id UInt64,hourDateTime,actionLowCardinality(String),result UInt8,total_count AggregateFunction(count),operator_count AggregateFunction(uniq,UInt64))ENGINEAggregatingMergeTreePARTITIONBYtoYYYYMM(hour)ORDERBY(tenant_id,hour,action,result);物化视图负责把新写入的日志持续聚合进去CREATEMATERIALIZEDVIEWmv_audit_log_overview_hourTOaudit_log_overview_hourASSELECTtenant_id,toStartOfHour(created_at)AShour,action,result,countState()AStotal_count,uniqState(operator_id)ASoperator_countFROMaudit_log_v2GROUPBYtenant_id,hour,action,result;概览接口改成查小时汇总表SELECThour,action,result,countMerge(total_count)AScnt,uniqMerge(operator_count)ASoperator_cntFROMaudit_log_overview_hourWHEREtenant_id{tenant_id:UInt64}ANDhourtoStartOfHour({start:DateTime64(3)})ANDhourtoStartOfHour({end:DateTime64(3)})INTERVAL1HOURGROUPBYhour,action,resultORDERBYhour;这里没有追求所有统计都实时精确到秒。概览本来就是趋势和分布小时粒度已经够用。需要精确明细时用户会继续下钻到列表。需要注意的是ClickHouse 物化视图只处理创建以后写入的数据历史数据不会自动补进去。历史回灌要么通过插入audit_log_v2触发物化视图要么单独对audit_log_overview_hour做聚合回填。第七步把高频 JSON 字段展开最难处理的是关键字。原来的关键字搜索会查summary也会查detail_json里的字段而且是包含匹配。ClickHouse 可以扫但在线接口不能每次都靠扫大范围 JSON 兜底。这里把搜索分成三档精确字段查询资源 ID、操作人、操作类型继续走排序键和跳数索引高频 JSON 字段如targetId、targetName写入时展开成普通列大范围模糊搜索走异步任务或独立检索链路不混在在线列表里。字段展开可以这样做ALTERTABLEaudit_log_v2ADDCOLUMNtarget_id StringDEFAULT,ADDCOLUMNtarget_name StringDEFAULT,ADDINDEXidx_target_id target_idTYPEbloom_filter(0.01)GRANULARITY4;也可以用物化列从 JSON 中提取但这里更倾向写入时展开。审计日志写入本来就在业务侧构造事件把高频检索字段明确写出来后面排查也更直观。历史数据上的跳数索引不能指望发布后立刻生效需要放到低峰期按分区异步 materialize。第八步冷热数据分开处理审计日志有一个特点最近一段时间访问最多历史数据访问频率会明显下降但又不能随便删因为合规和追溯还需要。所以后面把存储策略调整成近 30 天热数据保留在在线查询明细表 30 天到 180 天归档分区主要服务导出和低频查询 超过保留周期进入冷存储或按合规策略清理查询接口根据时间范围做路由只查近 30 天直接查热数据跨月份查询拆成多个时间段查询再在应用层合并游标大范围查询不走同步列表进入导出任务。这一步不是第一天就做的因为它涉及数据迁移、回灌、TTL 策略和历史查询兼容。前面的排序键、分页、count 拆分先把火灭掉以后再做冷热分层会稳很多。看下结果本地实验的结果不要解读成生产性能承诺更适合看趋势。这次 5亿行、10 轮 benchmark 里比较有参考价值的是读行数和读字节数的变化这次大数据量、10 轮 benchmark 里比较有参考价值的是读行数和读字节数的变化查询优化前 P95优化后 P95主要变化列表查询713 ms110 msread_rows 从 17,345,901 降到 1,802,240read_bytes 从 7.16 GiB 降到 214.50 MiB总数判断46 ms69 ms实时count()改成LIMIT 51判断hasMoreread_rows 从 116,723,053 降到 1,835,008概览查询94 ms42 msread_rows 从 116,723,053 降到 8,192read_bytes 从 2.78 GiB 降到 132.43 KiBJSON 字段查询163 ms164 ms运行时解析 JSON 改成普通列查询read_bytes 从 2.61 GiB 降到 117.18 MiB这组结果里有一个点要单独说明本地单机环境下hasMore和target_id查询的 P95 不一定每次都比旧查询低。尤其是数据还在同一台机器、缓存状态不可控时毫秒级结果会有波动。但从资源消耗看几个优化方向是对得上的列表查询、概览查询和 JSON 字段查询的read_bytes都明显下降概览从扫明细变成读小时汇总后read_rows也从亿级降到了几千行级别。。这里确实涉及数据迁移这次优化不是简单改几条 SQL。ClickHouse 的 MergeTree 排序键不能像普通索引那样原地随便调整。要把ORDER BY (created_at, tenant_id, id)改成更适合查询的ORDER BY (tenant_id, event_date, created_at, id)本质上就是建新表、迁移数据、切换读路径。迁移按几个阶段做。第一步先建新表和物化视图不影响旧链路audit_log 旧明细表继续承接线上查询 audit_log_v2 新明细表使用新的排序键和展开字段 overview_hour 概览小时汇总表 mv_overview_hour 从 audit_log_v2 写入时自动聚合第二步新写入先双写一段时间。写路径和读路径最好分开灰度write_new_audit_log_table true read_new_audit_log_table_for_tenants [1001, 1002]第三步历史数据分批回灌。不要一次性全量INSERT SELECT而是按月份、租户或分区拆开INSERTINTOaudit_log_v2(...)SELECT...FROMaudit_logWHEREcreated_at2024-01-01 00:00:00ANDcreated_at2024-02-01 00:00:00ANDtenant_idIN(...);第四步做校验。demo 里的06_validate.sql包含几类检查总行数是否一致新旧表按租户、日期的行数是否一致target_id、target_name和原 JSON 字段是否匹配概览物化视图汇总结果和明细临时聚合是否一致。第五步灰度切读。先让少量租户或内部账号读新表写入仍然保持双写。确认列表、详情、概览、导出都没问题再逐步扩大范围。第六步保留回滚窗口。切读以后旧表不要马上删至少保留一个观察周期。如果新链路有问题配置开关可以把读路径切回旧表。这套迁移并不优雅但比较稳。真正麻烦的不是 SQL 怎么写而是迁移期间要同时保证新数据不能丢 历史数据能补齐 查询结果能回滚方案边界这套方案适合的是这类场景审计日志以追加写为主更新很少查询天然有租户、组织、项目等权限边界大多数查询集中在最近一段时间高频查询可以归纳成几类精确过滤概览维度相对稳定可以接受分钟级以内延迟用户能接受大范围查询走异步导出。它不适合这些场景所有人都要跨全量数据任意检索关键字搜索是最高频入口查询维度经常变化无法提前设计排序键和展开字段概览维度经常临时变化或者要求每个数字强实时精确数据写入后需要频繁更新或删除。需要说明的是这个方案并不能解决所有问题。大范围导出、复杂全文检索、跨归档数据统计仍然应该走单独链路而不是继续塞进在线列表。最后回头来看这次优化最重要的不是某个 ClickHouse 参数也不是某条 SQL 写法。更关键的是把审计日志查询拆成几类不同职责在线列表按租户和时间快速定位明细 详情查询按 id 延迟读取大字段 总数判断默认只返回 hasMore 概览统计用物化视图预聚合 高频 JSON 字段写入时展开 大范围检索和导出走异步链路性能问题有时不是“数据库不够快”而是不同访问模式被混在同一条主链路里。工程优化的目标也不是让所有请求都极快而是让不同请求有可预测的处理方式。
一次 ClickHouse 审计日志查询优化：从 P95 9.6s 到 420ms 左右
发布时间：2026/7/15 6:17:10
ClickHouse 审计日志查询优化先拆查询职责再处理读放大这篇文章做了脱敏处理。业务名、表名、机器规格和真实线上规模都不展开只保留问题形态、排查路径和改造思路。为了让结果更容易复现我另外补了一个本地 ClickHouse 测试实验用脚本构造审计日志数据再对优化前后的查询做对比。所以这篇文章里有两条线项目背景来自真实问题但业务信息和规模做了脱敏 实验数据来自本地 demo主要用来验证查询路径变化后的 read_rows / read_bytes 变化场景很常见后台有一个审计日志查询页面日志已经进了 ClickHouse。刚开始大家对它的预期比较简单既然已经用列存了查询应该不会太差。但数据量和查询条件一起上来以后页面体验开始变得不稳定。最开始暴露出来的现象大概是这样查最近一天还可以 查最近一周开始等 查一个月基本像卡住 一加操作人、资源 ID、关键字接口就容易超时 概览卡片有时比列表还慢 导出任务还能跑但在线列表体验很差后来回头看真正的问题并不在某一条 SQL而是我们把几类访问模式完全不同的需求都压在了同一条在线查询链路上。明细列表、总数统计、概览聚合、关键字搜索、详情字段读取看起来都叫“审计日志查询”但它们对 ClickHouse 的使用方式其实不一样。先看一眼基线性能优化最怕一上来就说“很慢”。慢到什么程度、慢在哪里、慢在什么条件下如果不先量化后面很容易变成凭感觉改 SQL。本地 demo 里我用 500 万行数据、10 轮 benchmark 做了一次对比。这个规模不能代表生产环境但足够观察几个典型查询的读放大情况。运行方式是cd blog\audit-log-query-optimization\demo\clickhouse-audit-demo# 先验证流程和 SQL 是否能跑通.\scripts\run-demo.ps1-Rows 100000-Repeat 1# 再用更大的数据量做模拟压测.\scripts\run-demo.ps1-Rows 5000000-Repeat 10最终07_summary.sql会把每类查询的多轮结果汇总出来。这次 500 万行 demo 里优化前几个典型查询大概是这样查询P95max_read_rowsmax_read_bytes列表深分页old_list_offset37 ms1,217,852526.33 MiB实时总数old_count7 ms1,217,85218.58 MiB概览扫明细old_overview_from_detail11 ms1,217,85230.20 MiBJSON 关键字old_keyword_json_scan19 ms955,708315.87 MiB本地单机 ClickHouse 跑得很快所以这里不要盯着毫秒数本身看。更值得看的其实是read_rows和read_bytes同样是查一个租户最近 7 天的数据旧查询经常读到百万行级别。列表查询还会把detail_json一起读出来所以read_bytes很高。原来的表和查询长什么样原始表结构做过简化大概是这样CREATETABLEaudit_log(id UInt64,tenant_id UInt64,operator_id UInt64,actionLowCardinality(String),resource_type LowCardinality(String),resource_id String,client_ip String,result UInt8,summary String,detail_json String,created_at DateTime64(3))ENGINEMergeTreePARTITIONBYtoYYYYMM(created_at)ORDERBY(created_at,tenant_id,id);这个ORDER BY第一眼看没什么问题。日志天然和时间相关把created_at放前面好像很顺。但真实查询不是“全局按时间查日志”而是“某个租户在某段时间内查日志”。这个差别很关键。旧表优先按时间组织数据租户数据会散在时间范围里而页面几乎每次都会带tenant_id。原来的列表 SQL 也比较典型SELECTid,tenant_id,operator_id,action,resource_type,resource_id,client_ip,result,summary,detail_json,created_atFROMaudit_logWHEREtenant_id{tenant_id:UInt64}ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}AND({operator_id:UInt64}0ORoperator_id{operator_id:UInt64})AND({action:String}ORaction{action:String})AND({resource_id:String}ORresource_id{resource_id:String})AND({keyword:String}ORpositionCaseInsensitive(summary,{keyword:String})0ORpositionCaseInsensitive(JSONExtractString(detail_json,targetName),{keyword:String})0)ORDERBYcreated_atDESCLIMIT{offset:UInt64},{page_size:UInt64};为了显示总条数页面还会再跑一条SELECTcount()FROMaudit_logWHEREtenant_id{tenant_id:UInt64}ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}AND...这套写法在数据量小的时候还能扛住。数据上来以后几个问题会叠在一起ORDER BY (created_at, tenant_id, id)和租户内查询不匹配列表查询直接读取detail_json每行都带着一大块详情LIMIT offset, size翻到后面会扫描并丢弃大量结果count()和列表一起压在线链路概览卡片每次扫明细表聚合高频 JSON 字段在查询时动态解析关键字搜索和普通列表混在一起。ClickHouse 很擅长列式扫描和聚合但它不是魔法。排序键、分区、字段模型和查询入口如果没有按访问模式设计照样会读很多不该读的数据。先确认问题是不是在 ClickHouse 查询最开始没有直接重建表。日志查询慢表象可能来自很多地方比如前端表格渲染、接口响应体太大、权限补全、网络传输、ClickHouse 后台 merge甚至是导出任务和在线查询抢资源。所以先把一次请求拆开看参数解析 权限范围计算 count 查询 概览统计查询 列表查询 详情字段反序列化 操作人和资源信息补全 JSON 返回慢请求里ClickHouse 查询时间占比最高。进一步看system.query_log关注的不是哪条 SQL “看起来最长”而是读了多少数据。当时用的定位 SQL 类似这样SELECTquery_duration_ms,read_rows,formatReadableSize(read_bytes)ASread_bytes,formatReadableSize(memory_usage)ASmemory_usage,ProfileEvents[SelectedParts]ASselected_parts,ProfileEvents[SelectedMarks]ASselected_marks,normalizedQueryHash(query)ASquery_hash,left(query,160)ASsample_queryFROMsystem.query_logWHEREtypeQueryFinishANDevent_timenow()-INTERVAL30MINUTEANDqueryILIKE%audit_log%ORDERBYquery_duration_msDESCLIMIT20;这里有几个指标很有用read_rows高通常说明过滤条件没能把读取范围压下来read_bytes高经常和读取大字段有关SelectedParts、SelectedMarks偏大要回头看分区和主键裁剪memory_usage高通常要看聚合、排序或者大字段读取。做个实验把问题复现出来为了避免这篇文章只停留在经验描述我补了一个简化 demo。它不依赖真实业务系统只用 Docker 起一个单机 ClickHouse通过numbers()构造审计日志数据。demo 的目录结构是demo/clickhouse-audit-demo ├── docker-compose.yml ├── README.md ├── scripts │ ├── run-demo.ps1 │ └── run-demo.sh └── sql ├── 01_schema.sql ├── 02_load_old.sql ├── 03_benchmark_before.sql ├── 04_migrate.sql ├── 05_benchmark_after.sql ├── 06_validate.sql └── 07_summary.sql脚本会做几件事步骤脚本作用101_schema.sql建旧表、新表、概览聚合表、物化视图、benchmark 结果表202_load_old.sql用numbers()构造旧表数据303_benchmark_before.sql跑旧表列表、count、概览、JSON 关键字查询404_migrate.sql把旧表数据迁移到新表同时展开target_id、target_name505_benchmark_after.sql跑新表游标列表、hasMore、物化视图概览和目标字段查询606_validate.sql校验迁移后总行数、租户日维度行数、展开字段、概览结果707_summary.sql汇总多轮 benchmark输出avg_ms、p95_ms、max_ms每个 benchmark 查询都会打一个log_comment然后从system.query_log把duration_ms、read_rows、read_bytes、memory_usage取出来写入结果表。几个 case 的含义如下case_name含义对应优化点old_list_offset旧表 offset 深分页并读取detail_json排序键不匹配、深分页、列表背详情字段old_count旧表实时总数统计拆掉实时 countold_overview_from_detail概览每次扫明细表聚合概览统计走物化视图old_keyword_json_scan查询时解析 JSON 并做包含匹配JSON 高频字段写入时展开new_list_cursor新表游标分页只查摘要字段新排序键、游标分页、列表瘦身new_has_more用LIMIT pageSize 1判断是否还有下一页拆掉实时 countnew_overview_materialized_view从小时汇总表查概览物化视图预聚合new_target_field_query使用展开后的target_id查询高频 JSON 字段普通列化这套 demo 不模拟生产里的分片、副本、冷热盘、并发流量和后台任务。它的作用更单纯验证同样的数据在查询路径变化后读行数和读字节数是不是会下降。第一步先给在线查询划边界原来页面默认查最近 7 天用户也可以直接选很大的时间范围。对审计日志来说这个默认值有点危险因为列表页一打开就会触发查询。这一步先做入口约束默认时间范围从最近 7 天改成最近 24 小时普通列表查询最大允许 31 天超过 31 天的查询走异步导出不阻塞在线接口。这不是为了限制用户而是把两类需求分开在线排障查最近一段时间要求快 合规审计查较长时间要求全入口约束能减少一些无意义的大范围查询但它解决不了排序键不匹配、深分页、实时 count 和概览聚合。所以它只是把入口先收住真正的读放大还要继续处理。第二步按真实查询模式重建排序键ClickHouse 里很容易误判的一点是把ORDER BY当成普通排序字段。对 MergeTree 来说ORDER BY决定数据在磁盘上的组织方式也决定主键稀疏索引能不能少读数据。旧表的ORDER BY (created_at, tenant_id, id)更像是在服务全局时间查询但真实查询大多是租户内时间范围查询。新表改成CREATETABLEaudit_log_v2(id UInt64,tenant_id UInt64,event_dateDateDEFAULTtoDate(created_at),operator_id UInt64,actionLowCardinality(String),resource_type LowCardinality(String),resource_id String,client_ip String,result UInt8,summary String,detail_json String,target_id StringDEFAULT,target_name StringDEFAULT,created_at DateTime64(3),INDEXidx_resource_id resource_idTYPEbloom_filter(0.01)GRANULARITY4,INDEXidx_operator_id operator_idTYPEset(10000)GRANULARITY4,INDEXidx_actionactionTYPEset(256)GRANULARITY4,INDEXidx_target_id target_idTYPEbloom_filter(0.01)GRANULARITY4)ENGINEMergeTreePARTITIONBYtoYYYYMM(created_at)ORDERBY(tenant_id,event_date,created_at,id)SETTINGS index_granularity8192;tenant_id放在排序键最前面是因为查询基本都在租户范围内发生。event_date用来配合按天、按周、按月的时间范围过滤让主键裁剪更干净。这里没有把operator_id、resource_id、action都塞进排序键。排序键只能有一套字段塞得越多不一定越好反而可能破坏最常见的租户时间查询。低频筛选条件更适合配合跳数索引或者放到更合适的检索链路里。第三步把 offset 分页改成游标分页原来的分页是ORDERBYcreated_atDESCLIMIT10000,50;这种写法在后台系统里很常见但放到日志表里会越来越慢。ClickHouse 仍然需要找到前面 10000 条满足条件的记录再丢掉它们最后返回 50 条。改造后用(created_at, id)做游标SELECTid,tenant_id,operator_id,action,resource_type,resource_id,client_ip,result,summary,created_atFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDevent_datetoDate({start:DateTime64(3)})ANDevent_datetoDate({end:DateTime64(3)})ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}WHERE(created_at{cursor_time:DateTime64(3)}OR(created_at{cursor_time:DateTime64(3)}ANDid{cursor_id:UInt64}))ORDERBYcreated_atDESC,idDESCLIMIT50;第一页不传游标下一页带上上一页最后一条记录的created_at和id。这个方案不适合直接跳到第 200 页。但审计日志查询本来就不是商品列表真实使用里更多是不断向后翻或者缩小条件重新查。这里的收益不是某个语法技巧而是避免引擎做无意义扫描。第四步列表和详情分开审计日志详情里有一段detail_json里面放请求参数、变更前后字段、扩展上下文等信息。这个字段对详情页有用但列表页并不需要。原来列表接口直接把它查出来再交给前端决定展示什么。数据量小时这样省事数据量上来以后代价就很明显多读列压缩块解压成本更高响应体更大后端序列化更重前端渲染也会被拖慢。列表查询改成只查摘要字段SELECTid,tenant_id,operator_id,action,resource_type,resource_id,client_ip,result,summary,created_atFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDevent_datetoDate({start:DateTime64(3)})ANDevent_datetoDate({end:DateTime64(3)})ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}WHERE...ORDERBYcreated_atDESC,idDESCLIMIT50;详情页再按tenant_id、created_at和id单独查SELECTdetail_jsonFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDevent_datetoDate({created_at:DateTime64(3)})ANDcreated_at{created_at:DateTime64(3)}-INTERVAL1SECONDANDcreated_at{created_at:DateTime64(3)}INTERVAL1SECONDWHEREid{id:UInt64}LIMIT1;ClickHouse 是列存列表不读大字段收益很直接。更重要的是接口职责也清楚了列表负责定位详情负责展开。第五步把 count 从主链路拿掉很多后台列表默认都显示总条数共 123456 条但审计日志这种表带复杂条件实时count()的成本很高。尤其是时间范围大、条件不够精准时count 查询本身可能比列表还慢。这一步没有直接删除总数而是改了交互和接口策略首屏默认不计算精确总数列表接口只返回hasMore需要总数时单独触发统计相同条件的统计结果短时间缓存超过阈值的统计返回“超过 N 条”不继续硬数。后端从“必须 count 完才能返回列表”变成先查 pageSize 1 条 如果多出 1 条说明 hasMore true 返回前 pageSize 条这里最关键的不是技术而是承认一个事实不是所有列表都必须实时显示精确总数。对审计日志来说用户更关心能不能快速定位记录而不是每次打开页面都知道精确总量。第六步概览统计走物化视图列表稳下来以后还有一个容易被忽略的问题概览。页面顶部有几个卡片和趋势图今日操作次数 失败操作次数 高风险操作次数 按操作类型分布 按小时趋势 Top 操作人这些数字看起来只是几个卡片但原来的做法是每次打开页面都扫明细表聚合SELECTtoStartOfHour(created_at)AShour,action,result,count()AScnt,uniqExact(operator_id)ASoperator_cntFROMaudit_log_v2 PREWHERE tenant_id{tenant_id:UInt64}ANDcreated_at{start:DateTime64(3)}ANDcreated_at{end:DateTime64(3)}GROUPBYhour,action,resultORDERBYhour;这类查询和列表查询的慢法不一样。列表只返回 50 条概览则要把时间范围内的数据扫完再聚合。时间范围一大它会和列表查询抢资源。所以概览从明细查询里拆出来用物化视图预聚合到小时粒度CREATETABLEaudit_log_overview_hour(tenant_id UInt64,hourDateTime,actionLowCardinality(String),result UInt8,total_count AggregateFunction(count),operator_count AggregateFunction(uniq,UInt64))ENGINEAggregatingMergeTreePARTITIONBYtoYYYYMM(hour)ORDERBY(tenant_id,hour,action,result);物化视图负责把新写入的日志持续聚合进去CREATEMATERIALIZEDVIEWmv_audit_log_overview_hourTOaudit_log_overview_hourASSELECTtenant_id,toStartOfHour(created_at)AShour,action,result,countState()AStotal_count,uniqState(operator_id)ASoperator_countFROMaudit_log_v2GROUPBYtenant_id,hour,action,result;概览接口改成查小时汇总表SELECThour,action,result,countMerge(total_count)AScnt,uniqMerge(operator_count)ASoperator_cntFROMaudit_log_overview_hourWHEREtenant_id{tenant_id:UInt64}ANDhourtoStartOfHour({start:DateTime64(3)})ANDhourtoStartOfHour({end:DateTime64(3)})INTERVAL1HOURGROUPBYhour,action,resultORDERBYhour;这里没有追求所有统计都实时精确到秒。概览本来就是趋势和分布小时粒度已经够用。需要精确明细时用户会继续下钻到列表。需要注意的是ClickHouse 物化视图只处理创建以后写入的数据历史数据不会自动补进去。历史回灌要么通过插入audit_log_v2触发物化视图要么单独对audit_log_overview_hour做聚合回填。第七步把高频 JSON 字段展开最难处理的是关键字。原来的关键字搜索会查summary也会查detail_json里的字段而且是包含匹配。ClickHouse 可以扫但在线接口不能每次都靠扫大范围 JSON 兜底。这里把搜索分成三档精确字段查询资源 ID、操作人、操作类型继续走排序键和跳数索引高频 JSON 字段如targetId、targetName写入时展开成普通列大范围模糊搜索走异步任务或独立检索链路不混在在线列表里。字段展开可以这样做ALTERTABLEaudit_log_v2ADDCOLUMNtarget_id StringDEFAULT,ADDCOLUMNtarget_name StringDEFAULT,ADDINDEXidx_target_id target_idTYPEbloom_filter(0.01)GRANULARITY4;也可以用物化列从 JSON 中提取但这里更倾向写入时展开。审计日志写入本来就在业务侧构造事件把高频检索字段明确写出来后面排查也更直观。历史数据上的跳数索引不能指望发布后立刻生效需要放到低峰期按分区异步 materialize。第八步冷热数据分开处理审计日志有一个特点最近一段时间访问最多历史数据访问频率会明显下降但又不能随便删因为合规和追溯还需要。所以后面把存储策略调整成近 30 天热数据保留在在线查询明细表 30 天到 180 天归档分区主要服务导出和低频查询 超过保留周期进入冷存储或按合规策略清理查询接口根据时间范围做路由只查近 30 天直接查热数据跨月份查询拆成多个时间段查询再在应用层合并游标大范围查询不走同步列表进入导出任务。这一步不是第一天就做的因为它涉及数据迁移、回灌、TTL 策略和历史查询兼容。前面的排序键、分页、count 拆分先把火灭掉以后再做冷热分层会稳很多。看下结果本地实验的结果不要解读成生产性能承诺更适合看趋势。这次 5亿行、10 轮 benchmark 里比较有参考价值的是读行数和读字节数的变化这次大数据量、10 轮 benchmark 里比较有参考价值的是读行数和读字节数的变化查询优化前 P95优化后 P95主要变化列表查询713 ms110 msread_rows 从 17,345,901 降到 1,802,240read_bytes 从 7.16 GiB 降到 214.50 MiB总数判断46 ms69 ms实时count()改成LIMIT 51判断hasMoreread_rows 从 116,723,053 降到 1,835,008概览查询94 ms42 msread_rows 从 116,723,053 降到 8,192read_bytes 从 2.78 GiB 降到 132.43 KiBJSON 字段查询163 ms164 ms运行时解析 JSON 改成普通列查询read_bytes 从 2.61 GiB 降到 117.18 MiB这组结果里有一个点要单独说明本地单机环境下hasMore和target_id查询的 P95 不一定每次都比旧查询低。尤其是数据还在同一台机器、缓存状态不可控时毫秒级结果会有波动。但从资源消耗看几个优化方向是对得上的列表查询、概览查询和 JSON 字段查询的read_bytes都明显下降概览从扫明细变成读小时汇总后read_rows也从亿级降到了几千行级别。。这里确实涉及数据迁移这次优化不是简单改几条 SQL。ClickHouse 的 MergeTree 排序键不能像普通索引那样原地随便调整。要把ORDER BY (created_at, tenant_id, id)改成更适合查询的ORDER BY (tenant_id, event_date, created_at, id)本质上就是建新表、迁移数据、切换读路径。迁移按几个阶段做。第一步先建新表和物化视图不影响旧链路audit_log 旧明细表继续承接线上查询 audit_log_v2 新明细表使用新的排序键和展开字段 overview_hour 概览小时汇总表 mv_overview_hour 从 audit_log_v2 写入时自动聚合第二步新写入先双写一段时间。写路径和读路径最好分开灰度write_new_audit_log_table true read_new_audit_log_table_for_tenants [1001, 1002]第三步历史数据分批回灌。不要一次性全量INSERT SELECT而是按月份、租户或分区拆开INSERTINTOaudit_log_v2(...)SELECT...FROMaudit_logWHEREcreated_at2024-01-01 00:00:00ANDcreated_at2024-02-01 00:00:00ANDtenant_idIN(...);第四步做校验。demo 里的06_validate.sql包含几类检查总行数是否一致新旧表按租户、日期的行数是否一致target_id、target_name和原 JSON 字段是否匹配概览物化视图汇总结果和明细临时聚合是否一致。第五步灰度切读。先让少量租户或内部账号读新表写入仍然保持双写。确认列表、详情、概览、导出都没问题再逐步扩大范围。第六步保留回滚窗口。切读以后旧表不要马上删至少保留一个观察周期。如果新链路有问题配置开关可以把读路径切回旧表。这套迁移并不优雅但比较稳。真正麻烦的不是 SQL 怎么写而是迁移期间要同时保证新数据不能丢 历史数据能补齐 查询结果能回滚方案边界这套方案适合的是这类场景审计日志以追加写为主更新很少查询天然有租户、组织、项目等权限边界大多数查询集中在最近一段时间高频查询可以归纳成几类精确过滤概览维度相对稳定可以接受分钟级以内延迟用户能接受大范围查询走异步导出。它不适合这些场景所有人都要跨全量数据任意检索关键字搜索是最高频入口查询维度经常变化无法提前设计排序键和展开字段概览维度经常临时变化或者要求每个数字强实时精确数据写入后需要频繁更新或删除。需要说明的是这个方案并不能解决所有问题。大范围导出、复杂全文检索、跨归档数据统计仍然应该走单独链路而不是继续塞进在线列表。最后回头来看这次优化最重要的不是某个 ClickHouse 参数也不是某条 SQL 写法。更关键的是把审计日志查询拆成几类不同职责在线列表按租户和时间快速定位明细 详情查询按 id 延迟读取大字段 总数判断默认只返回 hasMore 概览统计用物化视图预聚合 高频 JSON 字段写入时展开 大范围检索和导出走异步链路性能问题有时不是“数据库不够快”而是不同访问模式被混在同一条主链路里。工程优化的目标也不是让所有请求都极快而是让不同请求有可预测的处理方式。
![PhoneGap NFC Plugin与物联网结合：智能家居NFC控制方案终极指南 [特殊字符]](http://pic.xiahunao.cn/yaotu/PhoneGap NFC Plugin与物联网结合：智能家居NFC控制方案终极指南 [特殊字符])
