一文搞懂Clickhouse的MySQL引擎

ClickHouse是一个开源的分布式列式数据库管理系统，专为在线分析处理（OLAP）设计。它具备高性能、可扩展性强、支持SQL查询等特点，能够实时生成分析数据报告。ClickHouse使用列式存储和向量化执行等技术，提高了查询性能和数据压缩率，非常适合大数据分析和实时数据查询场景。这篇文章我就来分享一下Clickhouse中的MySQL引擎。

MySQL引擎的作用

ClickHouse的MySQL引擎具有将远程的MySQL服务器中的表映射到ClickHouse中，并允许用户对表进行insert和select查询，以便在ClickHouse与MySQL之间进行数据交换的作用。

常见的使用场景

1）数据迁移与同步：需要将MySQL中的数据迁移到ClickHouse进行高效分析时，MySQL表引擎可以作为桥梁，实现数据的无缝迁移。通过MySQL表引擎，可以在ClickHouse中创建外部表，这些表直接映射到MySQL中的表，无需手动复制数据。

2）实时数据同步：在某些场景下，需要保证MySQL和ClickHouse之间的数据实时同步，MySQL表引擎支持异步数据同步，可以在后台自动将MySQL中的数据更新到ClickHouse中，保证数据的实时性。

3）复杂查询优化：MySQL在处理复杂查询时，性能可能会受到较大影响。通过将查询转移到ClickHouse，并利用其强大的列式存储和查询优化能力，可以显著提高查询性能。

4）数据导出：MySQL表引擎允许直接在ClickHouse中查询MySQL中的数据，无需手动导出数据。

演示案例

环境准备

MySQL建表

CREATE TABLE `user_t` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `user_id` INT(11) NULL DEFAULT NULL,
 `user_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
  PRIMARY KEY (`id`) USING BTREE
 )
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB;

插入数据

insert into user_t values(1,1001,'zs');
insert into user_t values(2,1002,'ls');

Clickhouse建表

CREATE TABLE ck_user_t
(
    `id` Int32,
    `user_id` Int32,
    `user_name` String
)
ENGINE = MySQL('localhost:3306', 'mysql_to_ck', 'user_t', 'root', '12345')

其中ENGINE = MySQL表示使用的为MySQL引擎，括号里面的localhost:3306表示MySQL的连接地址，mysql_to_ck表示将要映射的MySQL库，user_t表示将要映射的MySQL表，root为MySQL的用户名，12345为MySQL用户名对应的密码。

通过MySQL引擎查询数据

在Clickhouse执行select * from ck_user_t，可以看到以下数据：

接下来我们在Clickhouse中插入数据，看看MySQL表有什么变化

在Clickhouse中执行

insert into ck_user_t values(3,1003,'zl');

然后在MySQL中执行select * from user_t，可以看到MySQL也是同步变化的

至此我们完成了MySQL表通过Clickhouse的MySQL引擎达到数据映射的效果，那么有小伙伴可能会问了，该映射中Clickhouse有没有将对应的MySQL数据存下来呢？

答案是没有，那么如何验证这个答案呢，有两种方式：

一是将对应MySQL表删除，此时如果再查询Clickhouse表就会报错DB::Exception: mysqlxx::BadQuery: Table 'xxx' doesn't exist while executing query: 'xxx'

二是在不删除MySQL表的情况下可以查询Clickhouse系统表中的system.tables表select name,total_rows from tables where name='ck_user_t'，就会发现这个表的数据行数是NULL。

最后要说的是，其实这是Clickhouse中MySQL引擎最简单的使用方式，其实该引擎的使用还有很多复杂的配置项，具体我们可以参阅官方文档：

https://clickhouse.com/docs/zh/engines/database-engines/mysql

PS 实验过程中的操作记录

1）Docker镜像导入导出

导出，其中mysql为容器名称

docker export mysql > mysql.tar

导入并运行

docker import mysql.tar

docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

如果导入后直接运行报错docker: Error response from daemon: No command specified.，那么原因可能是Docker镜像找不到执行命令，此时我们就需要通过在原宿主机上执行docker ps --no-trunc命令找到该镜像的镜像命令，然后在重新执行命令

docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql docker-entrypoint.sh mysqld

然后就可以进入镜像了

docker exec -it mysql /bin/bash

2）MySQL创建用户并授权

创建用户，用户名test，密码为test123

CREATE USER 'test'@'%' IDENTIFIED BY 'test123';

授权或者授权单个数据库

GRANT ALL PRIVILEGES ON project.* TO 'test'@'%';
GRANT ALL PRIVILEGES ON clickhouse.* TO 'test'@'%';

刷新权限

FLUSH PRIVILEGES

本次分享就到这里啦~~