安装ClickHouse并和SpringBoot、Mybatisplus整合，实现千万级数据查询优化

躺平小废物
2,497 阅读3分钟

安装ClickHouse并和SpringBoot、Mybatisplus整合，实现千万级数据查询优化

引言

线上产生数据越来越多，MySQL数据库查询越来越慢，常规优化如分库分表、添加索引、使用缓存、优化代码查询逻辑已经无法实现优化（真实情况是功能能做出来就不错了，优化代码不存在的，什么？分库分表？改表结构？历史数据怎么割接处理？能不能尽量不改原有的逻辑加些什么中间件实现效果？），带着这些需求上网查了资料，最终决定使用ClickHouse来优化查询速度，具体设计是：MySQL还是作为业务库持久化存储数据，DML操作对MySQL，DQL操作使用ClickHouse，并且使用ClickHouse自带的数据库引擎MaterializedMySQL实现MySQL到ClickHouse的数据同步。

本文安装部署一个单机ClickHouse，和SpringBoot、Mybatisplus集成，测试查询千万级数据效果并和直接查询MySQL数据库对比，以此来体会ClickHouse的强悍。

一、安装ClickHouse

官网安装教程链接：clickhouse.com/docs/zh/get…
我是Centos，安装过程如下：
1.安装
yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-server clickhouse-client
2.启动
/etc/init.d/clickhouse-server start
# 查看是否启动成功
ps -ef|grep clickhouse
# 命令行登录（默认没有密码）
clickhouse-client
3.修改密码并配置远程连接
# 修改密码（注：只读文件，退出时命令： wq!）
vim /etc/clickhouse-server/users.xml
# 配置远程访问（注：只读文件，退出时命令： wq!）
vim /etc/clickhouse-server/config.xml

clipboard.png

clipboard11.png

4.使用密码重新连接
/etc/init.d/clickhouse-server stop
/etc/init.d/clickhouse-server start
clickhouse-client --password 123456

image-20230310145934696.png

5.可以访问ClickHouse自带的UI界面
http://部署ClickHouse服务器IP:8123/play

image-20230310150158504.png

二、开启MySQL的binlog并且配置ClickHouse的MaterializedMySQL引擎实现MySQL到ClickHouse的数据同步

1.修改MySQL的配置文件my.cnf开启binlog

image-20230310150608161.png

重启MySQL使用命令查看是否正确开启
# 登录MySQL
docker exec -it 08a438ea4dff bash
mysql  -u root -p数据库密码
show variables like '%log_bin%';

image-20230310150931773.png

2.ClickHouse使用MaterializedMySQL引擎
CREATE DATABASE mysql_db ENGINE = MaterializedMySQL('MySQL数据库服务器ip:MySQL数据库服务器端口', '要同步的MySQL数据库库名', 'MySQL数据库用户名', 'MySQL数据库密码');

image-20230310150931774.png

如果出现这个报错，先执行以下命令，再重新在ClickHouse创建数据库
set allow_experimental_database_materialized_mysql=1;
可以看到数据库已经从MySQL同步过来了

image-20230310152011275.png

image-20230310152052146.png

三、SpringBoot+MyBatisplus+ClickHouse、MySQL整合对比查询千万级数据速度

1.新建一个SpringBoot项目
pom配置
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.2.0</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.13</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.2</version>
</dependency>
<dependency>
    <groupId>com.alibaba.fastjson2</groupId>
    <artifactId>fastjson2</artifactId>
    <version>2.0.23</version>
</dependency>
yml配置
server:
  port: 8888
​
spring:
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://MySQL的ip:MySQL的端口/clickhouse_test?characterEncoding=UTF-8&serverTimezone=GMT%2B8&allowMultiQueries=true
          username: MySQL的用户名
          password: MySQL的密码
        slave:
          driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
          url: jdbc:clickhouse://ClickHouse的ip:8123/mysql_db
          username: default
          password: ClickHouse的密码
​
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
​
logging:
  level:
    com.zc.hadoop_hdfs_test.mapper: DEBUG
2.使用存储过程在MySQL数据库生成大量测试数据
# 创建测试表
CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5649324 DEFAULT CHARSET=utf8mb4
​
# 创建生成测试数据的存储过程
DELIMITER $$
​
USE `clickhouse_test`$$
​
DROP PROCEDURE IF EXISTS `proc_batch_insert`$$
​
CREATE DEFINER=`root`@`%` PROCEDURE `proc_batch_insert`()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=187635267;
SET ageVal=100;
SET i=1;
WHILE i <= 20000000 DO
        INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END$$
​
DELIMITER ;
​
​
# 数量自定义，我现在定的两千万
# 执行存储过程
call proc_batch_insert();
3.编写测试类测试ClickHouse和MySQL查询速度对比
Mybatisplus已经集成了ClickHouse，直接使用继承的BaseMapper来操作数据库测试
MySQL的Mapper的代码如下：

image-20230310153852052.png

ClickHouse的Mapper的代码如下：

image-20230310153921291.png

来一个简单的查询表的数据记录数看下MySQL和ClickHouse的查询速度对比

image-20230310154205066.png

可以看到，500万条数据的情况下，速度相差3倍左右(数据量不一致不是同步出问题了，因为后台有上面提到的生成两千万测试数据的存储过程还没执行完，一直在产生新的数据)，现在只想说句：ClickHouse牛逼！
最后，来个两千万数据量的like语句查询速度对比

image-20230313084822080.png

avatar
文章
阅读
粉丝