深入优化大数据黑马ClickHouse

最新推荐文章于 2024-04-13 02:01:31 发布

杨大大-qwer

最新推荐文章于 2024-04-13 02:01:31 发布

阅读量1.1k

点赞数

分类专栏：大数据 clickhouse 列式数据库文章标签：数据库

本文链接：https://blog.csdn.net/weixin_37727274/article/details/106922171

版权

大数据同时被 3 个专栏收录

5 篇文章 0 订阅

订阅专栏

clickhouse

4 篇文章 0 订阅

订阅专栏

列式数据库

2 篇文章 0 订阅

订阅专栏

1. 查询优化

JOIN操作时一定要把数据量小的表放在左表，ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着左表中的每一条记录到右表中查找该记录是否存在，所以左表必须是小表
示例：
原始join查询语句，耗时7.171秒：在这里插入图片描述
第一次优化左表数据，减少查询的列，不使用*：

第二次优化左表数据：

就是这样不断减少右表的数据，将查询时间缩短到213毫秒，

2. 写入优化

批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致ClickHouse无法及时对新导入的数据进行合并，从而影响查询性能。
示例：
1. 首先在建表时就要对表结构进行设计，主要是设置分区键，排序方式，以及TTL，尽可能只保留有用数据
CREATE TABLE Txiangmu.abc (
id String,
create_time_stamp UInt64 DEFAULT CAST(toUnixTimestamp(now()), #默认时间戳
create_date Date DEFAULT toDate(now()) #默认当前时间，年月日
ENGINE = MergeTree()
PARTITION BY create_date # 以年月日作为分区键
ORDER BY (create_time_stamp, create_date) # 根据时间戳，日期，id进行排序
TTL create_date + toIntervalMonth(1) # 此行数据保存1个月，到期自动删除
SETTINGS index_granularity = 8192;
2. 将数据按照指定字段分组
Map<String, List> commentList = list.stream().collect(Collectors.groupingBy(CommentQuery::getCreateDate));
3. 再遍历集合，将数据批量插入数据库中，提高插入速度

3. 其他优化

尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作，因为ClickHouse底层会不断的做异步的数据合并，会影响查询性能，这个在做实时数据写入的时候要尽量避开
CPU一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，所以要实时监控CPU的变化，而监控这方面恰恰是CK的劣势，需要借助第三方工具，比如zabbix，或者自己开发
关闭虚拟内存，物理内存和虚拟内存的数据交换，会导致查询变慢；
尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数。有些时候，先GROUP BY再JOIN比先JOIN再GROUP BY查询时间更短
ClickHouse的分布式表性能性价比不如物理表高，建表分区字段值不宜过多
查询数据时，能不用星号就千万不用，数据量无法确定大小的时候，尽量采用分页的方式获取

ClickHouse不支持事务，不存在隔离级别。ClickHouse的定位是分析性数据库，而不是严格的关系型数据库。
IO方面，MySQL是行存储，ClickHouse是列存储，后者在count()这类操作天然有优势，同时，在IO方面，MySQL需要大量随机IO，ClickHouse基本是顺序IO。
有人可能觉得上面的数据导入的时候，数据肯定缓存在内存里了，这个的确，但是ClickHouse基本上是顺序IO。对IO基本没有太高要求，当然，磁盘越快，上层处理越快，但是99%的情况是，CPU先跑满了

遇到的问题

1. 执行结果超时，原因是执行命令，长时间未得到响应
在这里插入图片描述
解决方案：mysql数据库是设置连接超时时间来解决这类问题，而clickhouse的设置与mysql不一样，它是在url连接的最后面增加socket_timeout配置，如下：

2. 多数据源配置的问题
实际开发过程中，一般都是关系型数据库存储业务数据，列式数据库存储日志或者体积大的数据，涉及到数据源选择切换的问题，一般可以通过注入JDBC连接对象，在编写操作代码的时候，手动指定或者切换数据源，那么有没有自动切换的功能呢？
解决方案：
1. 在application.properties中配置多种数据源
在这里插入图片描述
2. 加载多数据源

package com.surfilter.os.config;

import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.pool.DruidDataSourceFactory;

/**
 * springboot集成mybatis的基本入口 1）创建数据源(如果采用的是默认的tomcat-jdbc数据源，则不需要)
 * 2）创建SqlSessionFactory 3）配置事务管理器，除非需要使用事务，否则不用配置
 * @author yangwei
 */
@Configuration 
@MapperScan("com.surfilter.os.dao")
public class DatabaseMyBatisConfig {

    @Autowired
    private Environment env;

    /**
     * 创建数据源(数据源的名称：方法名可以取为XXXDataSource(),XXX为数据库名称,该名称也就是数据源的名称)
     */
    @Bean(name = "ckDbDataSource")
    public DataSource ckDbDataSource() throws Exception {
    	Properties props =  getProperties();
    	props.put("url", env.getProperty("clickhouse.datasource.url"));
        props.put("username", env.getProperty("clickhouse.datasource.username"));
        props.put("password", env.getProperty("clickhouse.datasource.password"));
        props.put("driverClassName", env.getProperty("clickhouse.datasource.driverClassName"));
        
        props.put(DruidDataSourceFactory.PROP_INITIALSIZE, "5");
        props.put(DruidDataSourceFactory.PROP_MAXACTIVE, "200");
        props.put(DruidDataSourceFactory.PROP_MINIDLE, "50");
        props.put(DruidDataSourceFactory.PROP_MAXWAIT, "600000");
        props.put(DruidDataSourceFactory.PROP_VALIDATIONQUERY, "select 1");
        props.put(DruidDataSourceFactory.PROP_TESTONBORROW, "true");
        props.put(DruidDataSourceFactory.PROP_TESTONRETURN, "true");
        props.put(DruidDataSourceFactory.PROP_TESTWHILEIDLE, "true");
        
        props.put(DruidDataSourceFactory.PROP_TIMEBETWEENEVICTIONRUNSMILLIS, "150000");
        props.put(DruidDataSourceFactory.PROP_MINEVICTABLEIDLETIMEMILLIS, "600000");
        props.put(DruidDataSourceFactory.PROP_REMOVEABANDONED, "true");
        props.put(DruidDataSourceFactory.PROP_REMOVEABANDONEDTIMEOUT, "3600");
        props.put(DruidDataSourceFactory.PROP_LOGABANDONED, "true");
        
        return DruidDataSourceFactory.createDataSource(props);
    }

    @Bean(name = "mysqlDbDataSource")
    public DataSource mysqlDbDataSource() throws Exception {
    	Properties props =  getProperties();
    	props.put("url", env.getProperty("spring.datasource.url"));
        props.put("username", env.getProperty("spring.datasource.username"));
        props.put("password", env.getProperty("spring.datasource.password"));
        props.put("driverClassName", env.getProperty("spring.datasource.driverClassName"));
        props.put("validationQuery", env.getProperty("spring.datasource.validationQuery"));
        props.put("validationQueryTimeout", env.getProperty("spring.datasource.validationQueryTimeout"));
        
        props.put(DruidDataSourceFactory.PROP_INITIALSIZE, "5");
        props.put(DruidDataSourceFactory.PROP_MAXACTIVE, "200");
        props.put(DruidDataSourceFactory.PROP_MINIDLE, "50");
        props.put(DruidDataSourceFactory.PROP_MAXWAIT, "60000");
        props.put(DruidDataSourceFactory.PROP_TESTONBORROW, "true");
        props.put(DruidDataSourceFactory.PROP_TESTONRETURN, "true");
        props.put(DruidDataSourceFactory.PROP_TESTWHILEIDLE, "true");
        
        props.put(DruidDataSourceFactory.PROP_TIMEBETWEENEVICTIONRUNSMILLIS, "15000");
        props.put(DruidDataSourceFactory.PROP_MINEVICTABLEIDLETIMEMILLIS, "60000");
        props.put(DruidDataSourceFactory.PROP_REMOVEABANDONED, "true");
        props.put(DruidDataSourceFactory.PROP_REMOVEABANDONEDTIMEOUT, "3600");
        props.put(DruidDataSourceFactory.PROP_LOGABANDONED, "true");
        return DruidDataSourceFactory.createDataSource(props);
    }

    private Properties getProperties() {
    	Properties props = new Properties();
        props.put("type", env.getProperty("spring.datasource.type"));
        props.put("minIdle", env.getProperty("spring.datasource.minIdle"));
        props.put("maxActive", env.getProperty("spring.datasource.maxActive"));
        props.put("initialSize", env.getProperty("spring.datasource.initialSize"));
        props.put("timeBetweenEvictionRunsMillis", env.getProperty("spring.datasource.timeBetweenEvictionRunsMillis"));
        props.put("minEvictableIdleTimeMillis", env.getProperty("spring.datasource.minEvictableIdleTimeMillis"));
        props.put("testWhileIdle", env.getProperty("spring.datasource.testWhileIdle"));
        props.put("testOnBorrow", env.getProperty("spring.datasource.testOnBorrow"));
        props.put("testOnReturn", env.getProperty("spring.datasource.testOnReturn"));
        props.put("maxWait", env.getProperty("spring.datasource.maxWait"));
        props.put("poolPreparedStatements", env.getProperty("spring.datasource.poolPreparedStatements"));
        props.put("maxPoolPreparedStatementPerConnectionSize", env.getProperty("spring.datasource.maxPoolPreparedStatementPerConnectionSize"));
        props.put("maxPoolSize", env.getProperty("spring.datasource.maxPoolSize"));
        props.put("minPoolSize", env.getProperty("spring.datasource.minPoolSize"));
        props.put("borrowConnectionTimeout", env.getProperty("spring.datasource.borrowConnectionTimeout"));
        return props;
    }
    
    /**
     * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错
     * @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例）
     */
    @Bean
    @Primary
    public DatabaseDynamicDataSource dataSource(@Qualifier("ckDbDataSource") DataSource ckDbDataSource,
            @Qualifier("mysqlDbDataSource") DataSource mysqlDbDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DatabaseType.clickhouse, ckDbDataSource);
        targetDataSources.put(DatabaseType.mysql, mysqlDbDataSource);

        DatabaseDynamicDataSource dataSource = new DatabaseDynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(mysqlDbDataSource);// 默认的datasource设置为ckDbDataSource

        return dataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("ckDbDataSource") DataSource ckDbDataSource,
                                               @Qualifier("mysqlDbDataSource") DataSource mysqlDbDataSource) throws Exception{
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(this.dataSource(ckDbDataSource, mysqlDbDataSource));
        fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));
        fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapper-locations")));
        return fb.getObject();
    }

    /**
     * 配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactionManager(DatabaseDynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }

}

package com.surfilter.os.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DatabaseDynamicDataSource extends AbstractRoutingDataSource {
    protected Object determineCurrentLookupKey() {
        return DatabaseContextHolder.getDatabaseType();
    }

}

package com.surfilter.os.config;


/**
 * 作用：
 * 1、保存一个线程安全的DatabaseType容器
 */
public class DatabaseContextHolder {
    private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
    
    public static void setDatabaseType(DatabaseType type){
        contextHolder.set(type);
    }
    
    public static DatabaseType getDatabaseType(){
        return contextHolder.get();
    }
    
    public static void removeDatabaseType(){
        contextHolder.remove();
    }
}

package com.surfilter.os.config;

public enum DatabaseType {
	     clickhouse,mysql
}

package com.surfilter.os.config;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Aspect
@Component

/**
 * 切换数据源
 * @author yangwei
 *
 */
@Order(1)
public class DataSourceAop {

	// 切入点在service层的方法上，配置aop的切入点
	@Pointcut("execution( * com.surfilter.os.dao..*.*(..))")
	public void dataSourcePointCut() {
	}


	/**
	 * 选择切面，根据执行的包名，来区分使用哪个数据源，并切换
	 * @param joinPoint
	 */
	@Before("dataSourcePointCut()")
	public void before(JoinPoint joinPoint) {
		if(joinPoint.getSignature().toString().contains("clickhouse")){
          DatabaseContextHolder.setDatabaseType(DatabaseType.clickhouse);
      }else{
          DatabaseContextHolder.setDatabaseType(DatabaseType.mysql);
      }
	}

	// 执行完切面后，清空线程共享中的数据源名称
	@After("dataSourcePointCut()")
	public void after(JoinPoint joinPoint) {
		DatabaseContextHolder.removeDatabaseType();
	}

}

最后在springboot启动类上加上以下注解，排除自动配置数据源
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

以上就能根据包名来区分数据源选项

3. 内存暴涨，很快就吃完内存
clickhouse在进行order by时，如果查询的数据列数很多，那么它耗费的内存将会呈几何倍增长，原因是因为查询出的数据排序算法是在内存中进行的。遇到此类问题，确实是不好定位问题，我们也是通过查询clickhouse日志，分析日志内容，逐条验证，最后找到问题的。

ClickHouse并非无所不能，查询语句需要不断的调优，可能与查询条件有关，不同的查询条件表是左join还是右join也是很有讲究的。
好了，关于ClickHouse的分析就到这里啦，后续的问题和使用心得我也会持续更新。。。

杨大大-qwer

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
深入优化大数据黑马ClickHouse

1. 查询优化JOIN操作时一定要把数据量小的表放在左表，ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着左表中的每一条记录到右表中查找该记录是否存在，所以左表必须是小表示例：原始join查询语句，耗时7.171秒：第一次优化左表数据，减少查询的列，不使用*：第二次优化左表数据：就是这样不断减少右表的数据，将查询时间缩短到213毫秒，2. 写入优化批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要
复制链接

扫一扫