跳至主要內容

ClickHouse Java实战：OLAP场景下的写入优化与查询加速

老张2026/4/30大约 9 分钟

ClickHouse Java实战：OLAP场景下的写入优化与查询加速

适读人群：Java后端工程师、数据工程师 | 阅读时长：约18分钟 | 技术栈：ClickHouse 23.x、ClickHouse Java Client、Spring Boot

开篇故事

两年前，我们的数据报表系统用的是MySQL。随着数据量涨到3亿行，一条"按月统计销售额"的SQL要跑40秒，产品天天催优化，DBA天天加索引，但治标不治本。

后来引入ClickHouse，同样的查询0.3秒，提升了130倍。但引入ClickHouse的过程并不顺利，我们犯了很多菜鸟错误：频繁单条插入、忽视了MergeTree的分区设计、不了解列式存储的查询特点。

踩了一圈坑，把ClickHouse调优到一个比较满意的状态，花了快两个月。今天把这套经验完整地写下来，让大家少走弯路。

一、核心问题：为什么MySQL在OLAP场景下不行

1.1 行存储 vs 列存储

当你只需要查询少数几列时，列存储只读取需要的列，跳过了大量无关数据。加上每列数据类型相同，压缩率极高（相同类型的数据序列压缩效果比混合类型好得多）。

1.2 ClickHouse的向量化执行

ClickHouse使用SIMD指令，对列数据进行向量化计算。一条CPU指令可以同时处理16个int32值（256位SIMD），加速效果显著。

二、原理深度解析

2.1 MergeTree：ClickHouse的存储引擎

MergeTree的写入模式：数据先写入内存，积累到一定量后写入磁盘形成Part，后台线程定期合并小Part为大Part。这个设计决定了ClickHouse不适合频繁单条写入，批量写入才是正确姿势。

2.2 索引原理：稀疏索引

ClickHouse的稀疏索引和MySQL的B+Tree索引思路完全不同，它不能点查单行，但对范围查询效率极高。

三、完整代码实现

3.1 建表设计（最重要的环节）

-- 错误的建表方式（常见菜鸟错误）
CREATE TABLE orders_wrong (
    id UInt64,
    user_id UInt64,
    product_id UInt64,
    amount Decimal(18, 2),
    status String,
    created_at DateTime,
    region String
) ENGINE = MergeTree()
ORDER BY id;  -- 按id排序对OLAP查询没有价值

-- 正确的建表方式
CREATE TABLE orders (
    -- 时间字段用于分区，放在前面
    created_date Date,                           -- 分区键（只存日期）
    created_at DateTime,                          -- 完整时间
    
    -- 常用过滤条件放在排序键前面
    region LowCardinality(String),               -- 低基数字段用LowCardinality
    user_id UInt64,
    order_id UInt64,
    
    -- 度量字段
    amount Decimal(18, 2),
    quantity UInt32,
    
    -- 状态、类型等低基数枚举
    status Enum8('pending' = 1, 'paid' = 2, 'shipped' = 3, 'completed' = 4, 'cancelled' = 5),
    
    -- 可为空的字段用Nullable或-1表示
    coupon_id UInt64 DEFAULT 0,                  -- 0表示无优惠券
    
    -- 不常查询的字段
    extra_info String DEFAULT ''
) 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_date)              -- 按月分区
ORDER BY (region, user_id, created_at)           -- 排序键：从低基数到高基数
PRIMARY KEY (region, user_id)                    -- 主键可以是排序键的前缀
TTL created_date + INTERVAL 2 YEAR DELETE        -- 2年后自动删除
SETTINGS 
    index_granularity = 8192,
    merge_with_ttl_timeout = 86400;

建表关键原则：

  1. 分区键选时间字段，通常按月或按天
  2. 排序键选最常用的过滤字段，从低基数到高基数排列
  3. 低基数字段（枚举、标签）用LowCardinality(String)，压缩效果更好
  4. 能用整数不用字符串，能用Date不用String

3.2 Java客户端配置

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.6</version>
    <classifier>all</classifier>
    <exclusions>
        <exclusion>
            <groupId>*</groupId>
            <artifactId>*</artifactId>
        </exclusion>
    </exclusions>
</dependency>
@Configuration
public class ClickHouseConfig {
    
    @Bean
    public ClickHouseDataSource clickHouseDataSource(ClickHouseProperties props) {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setHost(props.getHost());
        properties.setPort(props.getPort());
        properties.setDatabase(props.getDatabase());
        properties.setUser(props.getUser());
        properties.setPassword(props.getPassword());
        
        // 重要配置
        properties.setConnectionTimeout(30000);
        properties.setSocketTimeout(300000); // 大查询可能需要很长时间
        properties.setMaxThreads(8);          // 客户端线程数
        properties.setCompress(true);         // 传输压缩
        
        return new ClickHouseDataSource(
            "jdbc:clickhouse://" + props.getHost() + ":" + props.getPort() + "/" + props.getDatabase(),
            properties
        );
    }
}

3.3 批量写入：核心优化

@Repository
public class OrderClickHouseRepository {
    
    @Autowired
    private ClickHouseDataSource dataSource;
    
    // 批量写入缓冲区
    private final BlockingQueue<OrderEvent> buffer = new LinkedBlockingQueue<>(10000);
    private final ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
    
    @PostConstruct
    public void startBatchWriter() {
        // 定时批量刷写
        scheduler.scheduleWithFixedDelay(this::flushBuffer, 1, 1, TimeUnit.SECONDS);
    }
    
    public void addEvent(OrderEvent event) {
        if (!buffer.offer(event)) {
            log.warn("写入缓冲区满，直接刷写");
            flushBuffer();
            buffer.offer(event);
        }
    }
    
    private void flushBuffer() {
        List<OrderEvent> batch = new ArrayList<>();
        buffer.drainTo(batch, 5000); // 每批最多5000条
        
        if (batch.isEmpty()) return;
        
        long startTime = System.currentTimeMillis();
        try {
            batchInsert(batch);
            log.info("批量写入{}条数据，耗时{}ms", batch.size(), 
                System.currentTimeMillis() - startTime);
        } catch (Exception e) {
            log.error("批量写入失败，数据量: {}", batch.size(), e);
            // 失败处理：可以重试、写入备用存储、或者记录到文件
        }
    }
    
    /**
     * 使用JDBC批量插入
     * 关键：一次INSERT多行，不是多次INSERT一行
     */
    private void batchInsert(List<OrderEvent> events) throws SQLException {
        String sql = "INSERT INTO orders (created_date, created_at, region, user_id, " +
                     "order_id, amount, quantity, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            for (OrderEvent event : events) {
                stmt.setDate(1, Date.valueOf(event.getCreatedAt().toLocalDate()));
                stmt.setTimestamp(2, Timestamp.valueOf(event.getCreatedAt()));
                stmt.setString(3, event.getRegion());
                stmt.setLong(4, event.getUserId());
                stmt.setLong(5, event.getOrderId());
                stmt.setBigDecimal(6, event.getAmount());
                stmt.setInt(7, event.getQuantity());
                stmt.setString(8, event.getStatus());
                stmt.addBatch();
            }
            
            stmt.executeBatch();
        }
    }
    
    /**
     * 高性能写入：使用ClickHouse的HTTP接口直传TSV格式
     * 比JDBC快3-5倍
     */
    public void batchInsertFast(List<OrderEvent> events) {
        StringBuilder sb = new StringBuilder();
        for (OrderEvent event : events) {
            sb.append(event.getCreatedAt().toLocalDate()).append('\t')
              .append(event.getCreatedAt()).append('\t')
              .append(event.getRegion()).append('\t')
              .append(event.getUserId()).append('\t')
              .append(event.getOrderId()).append('\t')
              .append(event.getAmount()).append('\t')
              .append(event.getQuantity()).append('\t')
              .append(event.getStatus()).append('\n');
        }
        
        // 直接POST到ClickHouse HTTP接口
        // INSERT INTO orders FORMAT TabSeparated
        WebClient.create("http://clickhouse:8123")
            .post()
            .uri(uriBuilder -> uriBuilder
                .queryParam("query", "INSERT INTO orders FORMAT TabSeparated")
                .queryParam("user", "default")
                .build())
            .bodyValue(sb.toString())
            .retrieve()
            .bodyToMono(String.class)
            .block();
    }
}

3.4 查询优化

@Repository
public class OrderAnalyticsRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 分析查询：充分利用分区裁剪
     */
    public List<RegionSalesDTO> getSalesByRegion(LocalDate startDate, LocalDate endDate) {
        // 好的写法：明确使用分区键过滤，让CH做分区裁剪
        String sql = """
            SELECT 
                region,
                sum(amount) as total_amount,
                count() as order_count,
                uniq(user_id) as unique_users
            FROM orders
            WHERE created_date >= ? AND created_date <= ?  -- 分区键，触发分区裁剪
            GROUP BY region
            ORDER BY total_amount DESC
            """;
        
        return jdbcTemplate.query(sql, 
            (rs, rowNum) -> new RegionSalesDTO(
                rs.getString("region"),
                rs.getBigDecimal("total_amount"),
                rs.getLong("order_count"),
                rs.getLong("unique_users")
            ),
            Date.valueOf(startDate), Date.valueOf(endDate));
    }
    
    /**
     * 带物化视图的预聚合查询
     */
    public List<DailyStatsDTO> getDailyStats(LocalDate startDate, LocalDate endDate) {
        // 查询物化视图（预先聚合好的数据），速度极快
        String sql = """
            SELECT 
                stats_date,
                region,
                total_amount,
                order_count
            FROM orders_daily_mv  -- 物化视图
            WHERE stats_date >= ? AND stats_date <= ?
            ORDER BY stats_date, region
            """;
        
        return jdbcTemplate.query(sql,
            (rs, rowNum) -> new DailyStatsDTO(
                rs.getDate("stats_date").toLocalDate(),
                rs.getString("region"),
                rs.getBigDecimal("total_amount"),
                rs.getLong("order_count")
            ),
            Date.valueOf(startDate), Date.valueOf(endDate));
    }
}

3.5 物化视图：预聚合加速查询

-- 创建物化视图：自动维护每日统计数据
CREATE MATERIALIZED VIEW orders_daily_mv
ENGINE = SummingMergeTree()
ORDER BY (stats_date, region)
AS SELECT
    toDate(created_at) as stats_date,
    region,
    sum(amount) as total_amount,
    count() as order_count,
    uniq(user_id) as unique_users
FROM orders
GROUP BY stats_date, region;

-- 查询物化视图时，需要用sum()聚合（SummingMergeTree的特性）
SELECT 
    stats_date,
    region,
    sum(total_amount) as total_amount,  -- 需要再次sum
    sum(order_count) as order_count
FROM orders_daily_mv
WHERE stats_date >= '2024-01-01' AND stats_date <= '2024-12-31'
GROUP BY stats_date, region
ORDER BY stats_date;

四、工程实践与最佳实践

4.1 写入优化总结

4.2 查询优化总结

  • 必须使用分区键过滤，否则全表扫描
  • 排序键的前缀原则：ORDER BY (a, b)，过滤时a在前效果最好
  • 避免使用SELECT *，明确指定需要的列
  • 大量count(distinct)用uniq()（近似计数，比精确count快100倍）
  • 复杂分析用物化视图预聚合

五、踩坑实录

坑一：频繁单条写入导致Part爆炸

最常见的错误：把ClickHouse当MySQL用，每条业务数据写入时同步插入ClickHouse。

结果是Parts数量爆炸，ClickHouse不断合并，性能极差，还会报"too many parts"错误。

-- 观察Parts数量
SELECT table, partition, count() as part_count
FROM system.parts
WHERE database = 'mydb' AND active
GROUP BY table, partition
ORDER BY part_count DESC;

-- 如果part_count > 300，说明写入太频繁了

解决方案：必须批量写入，单批至少1000条，每秒最多写入次数不超过10次。

坑二：UPDATE和DELETE的坑

ClickHouse的UPDATE/DELETE不是真正的实时更新，而是异步的mutation操作，会触发大量数据重写，性能极差。

正确做法：用ReplacingMergeTree，插入新版本记录，查询时用FINAL关键字获取最新版本：

CREATE TABLE user_profile (
    user_id UInt64,
    name String,
    email String,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)  -- 按updated_at保留最新版本
ORDER BY user_id;

-- 更新：直接插入新记录
INSERT INTO user_profile VALUES (1, 'newname', 'newemail@x.com', now());

-- 查询最新版本
SELECT * FROM user_profile FINAL WHERE user_id = 1;

坑三：Nullable字段性能问题

ClickHouse的Nullable字段比非Nullable字段慢很多，因为需要额外维护一个null位图。

对于绝大多数场景，用DEFAULT值替代Nullable：

-- 避免
amount Nullable(Decimal(18,2))

-- 推荐：用0表示空
amount Decimal(18,2) DEFAULT 0

坑四：查询时忘记使用分区键

-- 慢查询！region不是分区键，全表扫描
SELECT sum(amount) FROM orders WHERE region = 'beijing';

-- 快查询！加上分区键过滤
SELECT sum(amount) FROM orders 
WHERE created_date >= '2024-01-01' -- 分区键
  AND region = 'beijing';

六、总结与个人判断

ClickHouse是我见过的OLAP数据库里，在性价比维度上最突出的一个。部署简单（单机模式），性能强悍，SQL兼容性好。对于中等规模（日增百万到千万行）的报表系统，ClickHouse是目前最佳选择之一。

但它不是银弹。几个明确不适用的场景：

  • 需要频繁UPDATE/DELETE的场景（用MySQL/PostgreSQL）
  • 事务性业务数据（用传统RDBMS）
  • 点查单行性能要求（用Redis或HBase）
  • 超大规模（日增百亿行以上，需要评估Doris/Snowflake）

掌握ClickHouse的关键是转变思维：它是为批量分析而生的，不是CRUD工具。只要抓住"批量写入+合理分区+物化视图"三个核心，大多数OLAP场景都能驾驭。

编辑此页
最近更新:
贡献者: xiaozhangge