mysql 支持sqlload mysql支持olap吗

转载

mob64ca13fe62db 2023-12-15 20:09:58

文章标签 mysql 支持sqlload mysql 大数据 MySQL 数据库 文章分类 MySQL 数据库

引言

MySQL集群主从间数据同步机制十分完善。令人惊喜的是，ClickHouse作为近年来炙手可热的大数据分析引擎也可以挂载为MySQL的从库，作为MySQL的 "协处理器" 面向OLAP场景提供高效数据分析能力。早先的方案比较直截了当，通过第三方插件将所有MySQL上执行的操作进行转化，然后在ClickHouse端逐一回放达到数据同步。终于在2020年下半年，Yandex 公司在 ClickHouse 社区发布了MaterializeMySQL引擎，支持从MySQL全量及增量实时数据同步。MaterializeMySQL引擎目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。

基础概念

MySQL & ClickHouse
MySQL一般特指完整的MySQL RDBMS，是开源的关系型数据库管理系统，目前属于Oracle公司。MySQL凭借不断完善的功能以及活跃的开源社区，吸引了越来越多的企业和个人用户。
ClickHouse是由Yandex公司开源的面向OLAP场景的分布式列式数据库。ClickHouse具有实时查询，完整的DBMS及高效数据压缩，支持批量更新及高可用。此外，ClickHouse还较好地兼容SQL语法并拥有开箱即用等诸多优点。
Row Store & Column Store
MySQL存储采用的是Row Store，表中数据按照 Row 为逻辑存储单元在存储介质中连续存储。这种存储方式适合随机的增删改查操作，对于按行查询较为友好。但如果选择查询的目标只涉及一行中少数几个属性，Row 存储方式也不得不将所有行全部遍历再筛选出目标属性，当表属性较多时查询效率通常较低。尽管索引以及缓存等优化方案在 OLTP 场景中能够提升一定的效率，但在面对海量数据背景的 OLAP 场景就显得有些力不从心了。
ClickHouse 则采用的是 Column Store，表中数据按照Column为逻辑存储单元在存储介质中连续存储。这种存储方式适合采用 SIMD (Single Instruction Multiple Data) 并发处理数据，尤其在表属性较多时查询效率明显提升。列存方式中物理相邻的数据类型通常相同，因此天然适合数据压缩从而达到极致的数据压缩比。

使用方法

部署Master-MySQL 开启BinLog功能：ROW模式开启GTID模式：解决位点同步时MySQL主从切换问题（BinLog reset导致位点失效）

# my.cnf关键配置
 gtid_mode=ON
 enforce_gtid_consistency=1
 binlog_format=ROW

部署Slave-ClickHouse

mysql8数据库配置

sudo mkdir -p /home/mysql8dir
 sudo mkdir -p /home/mysql8dir/data
 sudo mkdir -p /home/mysql8dir/conf
 sudo mkdir -p /home/mysql8dir/logs
 
 user@:/home/mysql8dir/conf$ cat docker.cnf 
 [mysqld]
 skip-host-cache
 skip-name-resolve
 
 user@:/home/mysql8dir$ cat conf/mysql.cnf 
 [mysql]
 
 [mysqld]
 default_authentication_plugin = mysql_native_password
 
 # 开启GTID模式：解决位点同步时MySQL主从切换问题
 server_id=98
 gtid_mode=on
 enforce_gtid_consistency=on
 
 # 开启BinLog功能：ROW模式
 binlog_format=row
 log_bin=mysql-bin
 
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 lower_case_table_names=1

sudo docker pull mysql:8.0.18
 sudo docker run --name mysql8018 -v /home/mysql8dir/data:/var/lib/mysql:rw -v /home/mysql8dir/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=xxxxxx --restart always -p 3306:3306 --privileged=true -d mysql:8.0.18

数据库dump和restore

docker exec mysql8018 sh -c 'exec mysqldump --all-databases -u root -p"$MYSQL_ROOT_PASSWORD"' > /home/dump.sql
 
 docker exec -i mysql8018 sh -c 'exec mysql -u root -p"$MYSQL_ROOT_PASSWORD"' < /home/dump.sql

Mysql57数据库配置

user:/home/mysqldir$ cat conf/docker.cnf
 [mysqld]
 skip-host-cache
 skip-name-resolve
 
 user:/home/mysqldir$ cat conf/mysql.cnf
 [mysql]
 
 
 [mysqld]
 # 开启GTID模式：解决位点同步时MySQL主从切换问题
 server-id=1
 gtid_mode=ON
 enforce_gtid_consistency=1
 # 开启BinLog功能：ROW模式
 log_bin=mysql_bin
 binlog_format=row
 
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 lower_case_table_names=1

数据库dump和restore

# 导出gasv4数据库到本地dump文件
 sudo docker exec mysql5730 sh -c 'exec mysqldump --databases gasv4 -u root -p"$MYSQL_ROOT_PASSWORD"' > /home/dump.sql
 
 # 导入本地gasv4数据库dump文件到数据库
 sudo docker exec -i mysql5730 sh -c 'exec mysql -u root -p"$MYSQL_ROOT_PASSWORD"' < /home/dump.sql

clickhouse数据库配置

sudo mkdir -p /home/clickhouse2156dir
 sudo mkdir -p /home/clickhouse2156dir/data
 sudo mkdir -p /home/clickhouse2156dir/cfg

<?xml version="1.0"?>
 <yandex>
     <!-- Profiles of settings. -->
     <profiles>
         <!-- Default settings. -->
         <default>
             <!-- Maximum memory usage for processing single query, in bytes. -->
             <max_memory_usage>10000000000</max_memory_usage>
 
         <allow_experimental_database_materialize_mysql>1</allow_experimental_database_materialize_mysql>
         
             <load_balancing>random</load_balancing>
         </default>
 
         <!-- Profile that allows only read queries. -->
         <readonly>
             <readonly>1</readonly>
         </readonly>
     </profiles>
 
     <!-- Users and ACL. -->
     <users>
         <!-- If user name was not specified, 'default' user is used. -->
         <default>
         <password>xxxxxx</password>
 
             <networks>
                 <ip>::/0</ip>
             </networks>
 
             <!-- Settings profile for user. -->
             <profile>default</profile>
 
             <!-- Quota for user. -->
             <quota>default</quota>
 
             <!-- User can create other users and grant rights to them. -->
             <!-- <access_management>1</access_management> -->
         </default>
     </users>
 
     <!-- Quotas. -->
     <quotas>
         <!-- Name of quota. -->
         <default>
             <!-- Limits for time interval. You could specify many intervals with different limits. -->
             <interval>
                 <!-- Length of interval. -->
                 <duration>3600</duration>
 
                 <!-- No limits. Just calculate resource usage for time interval. -->
                 <queries>0</queries>
                 <errors>0</errors>
                 <result_rows>0</result_rows>
                 <read_rows>0</read_rows>
                 <execution_time>0</execution_time>
             </interval>
         </default>
     </quotas>
 </yandex>

sudo docker pull yandex/clickhouse-server:21.5.6
 
 $ sudo docker run -d --name clickhouse2156 --restart always -p 8123:8123 -p 9000:9000 --ulimit nofile=262144:262144 -v /home/clickhouse2156dir/data:/var/lib/clickhouse  -v /home/clickhouse2156dir/cfg/users.xml:/etc/clickhouse-server/users.xml yandex/clickhouse-server:21.5.6 
 # 占用端口情况：
 # 8123 --- HTTP
 # 9010 --- TCP
 # 9004 --- mysql
 # 9009 --- 集群内部数据交换备份

clickhouse_slave 创建语法

# 语法
 CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
 ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]

引擎参数说明

参数	说明
host:port	MySQL数据库的URL和端口号。
database	MySQL数据库名称。
user	MySQL数据库账号。
password	MySQL数据库账号的密码。

默认新增字段

使用MaterializeMySQL数据库引擎，在ClickHouse集群上新建ReplacingMergeTree引擎的表，会默认在表中增加两个隐藏字段：

字段	说明
_version	事务计数器，记录数据版本信息。UInt64类型。
_sign	删除标记，标记该行是否删除。TypeInt8类型。可选值：1：该行未删除；-1：该行已删除。

支持的类型对应

MySQL	ClickHouse
TINY	Int8
SHORT	Int16
INT24	Int32
LONG	UInt32
LONG	UInt64
FLOAT	Float32
DOUBLE	Float64
DECIMAL，NEWDECIMAL	Decimal
DATE，NEWDATE	Date
DATETIME，TIMESTAMP	DateTime
DATETIME2，TIMESTAMP2	DateTime64
STRING	String
VARCHAR，VAR_STRING	String
BLOB	String
BIT	UInt64
SET	UInt64
ENUM	Enum16
JSON	String
YEAR	String
TIME	String
GEOMETRY	String

其他的MySQL数据类型将全部都转换为字符串，同时以上的所有类型均支持可为空。

使用细则

DDL查询

MySQL DDL查询被转换成相应的ClickHouse DDL查询（ALTER, CREATE, DROP, RENAME）。如果ClickHouse不能解析某些DDL查询，该查询将被忽略。

数据复制

MaterializeMySQL不支持直接插入、删除和更新查询，而是将DDL语句进行相应转换：

MySQL INSERT查询被转换为INSERT with _sign=1。
MySQL DELETE查询被转换为INSERT with _sign=-1。
MySQL UPDATE查询被转换成INSERT with _sign=-1和INSERT with _sign=-1。

SELECT查询

如果在SELECT查询中没有指定version，则使用FINAL修饰符，返回version的最大值对应的数据，即最新版本的数据。
如果在SELECT查询中没有指定sign，则默认使用WHERE _sign=1，即返回未删除状态（sign=1)的数据。

索引转换

ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组。
ClickHouse只有一个物理顺序，由ORDER BY子句决定。如果需要创建新的物理顺序，请使用物化视图。

说明
带有_sign=-1的行不会从表中物理删除。
MaterializeMySQL引擎不支持级联UPDATE/DELETE查询。
复制很容易被破坏。
禁止对数据库和表进行手动操作。
MaterializeMySQL受optimize_on_insert设置的影响，当MySQL服务器中的一个表发生变化时，数据被合并到MaterializeMySQL数据库中相应的表中。

工作原理

BinLog Event MySQL中BinLog Event主要包含以下几类：

1. MYSQL_QUERY_EVENT　　　　-- DDL
 2. MYSQL_WRITE_ROWS_EVENT　-- insert
 3. MYSQL_UPDATE_ROWS_EVENT -- update
 4. MYSQL_DELETE_ROWS_EVENT -- delete

事务提交后，MySQL 将执行过的 SQL 处理 BinLog Event，并持久化到 BinLog 文件 ClickHouse通过消费BinLog达到数据同步，过程中主要考虑３个方面问题： 1、DDL兼容：由于ClickHouse和MySQL的数据类型定义有区别，DDL语句需要做相应转换 2、Delete/Update 支持：引入_version字段，控制版本信息 3、Query 过滤：引入_sign字段，标记数据有效性

DDL操作对比一下MySQL的DDL语句以及在ClickHouse端执行的DDL语句：

mysql> show create table runoob_tbl\G;
 *************************** 1. row ***************************
 Table: runoob_tbl
 Create Table: CREATE TABLE `runoob_tbl` (
   `runoob_id` int unsigned NOT NULL AUTO_INCREMENT,
   `runoob_` varchar(100) NOT NULL,
   `runoob_author` varchar(40) NOT NULL,
   `submission_date` date DEFAULT NULL,
   PRIMARY KEY (`runoob_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)
 ---------------------------------------------------------------
 cat /metadata/slave_db/runoob_tbl.sql
 ATTACH TABLE _ UUID '14dbff59-930e-4aa8-9f20-ccfddaf78077'
 (
     `runoob_id` UInt32,
     `runoob_` String,
     `runoob_author` String,
     `submission_date` Nullable(Date),
     `_sign` Int8 MATERIALIZED 1,
     `_version` UInt64 MATERIALIZED 1
 )
 ENGINE = ReplacingMergeTree(_version)
 PARTITION BY intDiv(runoob_id, 4294967)
 ORDER BY tuple(runoob_id)
 SETTINGS index_granularity = 8192

可以看到： 1、在DDL转化时默认增加了2个隐藏字段：_sign(-1删除, 1写入) 和 _version(数据版本) 2、默认将表引擎设置为 ReplacingMergeTree，以 _version 作为 column version 3、原DDL主键字段 runoob_id 作为ClickHouse排序键和分区键此外还有许多DDL处理，比如增加列、索引等，相应代码在Parsers/MySQL 目录下。

Delete/Update操作
Update：

# Mysql端：
 UPDATE runoob_tbl set runoob_author='Mike' where runoob_id=2;
 
 mysql> select * from runoob_tbl;
 +-----------+----------------+---------------+-----------------+
 | runoob_id | runoob_title   | runoob_author | submission_date |
 +-----------+----------------+---------------+-----------------+
 |         1 | MySQL-learning | Bob           | 2021-01-06      |
 |         2 | MySQL-learning | Mike          | 2021-01-06      |
 +-----------+----------------+---------------+-----------------+
 2 rows in set (0.00 sec)
 
 ----------------------------------------------------------------
 # ClickHouse端：
 DESKTOP:) select *, _sign, _version from runoob_tbl order by runoob_id;
 
 SELECT
     *,
     _sign,
     _version
 FROM runoob_tbl
 ORDER BY runoob_id ASC
 
 Query id: c5f4db0a-eff6-4b49-a429-b55230c26301
 
 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
 │         1 │ MySQL-learning │ Bob           │      2021-01-06 │     1 │        2 │
 │         2 │ MySQL-learning │ Mike          │      2021-01-06 │     1 │        4 │
 │         2 │ MySQL-learning │ Tim           │      2021-01-06 │     1 │        3 │
 └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
 3 rows in set. Elapsed: 0.003 sec.

可以看到，ClickHouse数据也实时同步了更新操作。

Delete:

# Mysql端
 mysql> DELETE from runoob_tbl where runoob_id=2;
 
 mysql> select * from runoob_tbl;
 +-----------+----------------+---------------+-----------------+
 | runoob_id | runoob_title   | runoob_author | submission_date |
 +-----------+----------------+---------------+-----------------+
 |         1 | MySQL-learning | Bob           | 2021-01-06      |
 +-----------+----------------+---------------+-----------------+
 1 row in set (0.00 sec)
 
 ----------------------------------------------------------------
 # ClickHouse端
 DESKTOP:) select *, _sign, _version from runoob_tbl order by runoob_id;
 
 SELECT
     *,
     _sign,
     _version
 FROM runoob_tbl
 ORDER BY runoob_id ASC
 
 Query id: e9cb0574-fcd5-4336-afa3-05f0eb035d97
 
 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
 │         1 │ MySQL-learning │ Bob           │      2021-01-06 │     1 │        2 │
 └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
 │         2 │ MySQL-learning │ Mike          │      2021-01-06 │    -1 │        5 │
 └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
 │         2 │ MySQL-learning │ Mike          │      2021-01-06 │     1 │        4 │
 │         2 │ MySQL-learning │ Tim           │      2021-01-06 │     1 │        3 │
 └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
 4 rows in set. Elapsed: 0.002 sec.

可以看到，删除id为2的行只是额外插入了_sign == -1的一行记录，并没有真正删掉。

日志回放
MySQL 主从间数据同步时Slave节点将 BinLog Event 转换成相应的SQL语句，Slave 模拟 Master 写入。类似地，传统第三方插件沿用了MySQL主从模式的BinLog消费方案，即将 Event 解析后转换成 ClickHouse 兼容的 SQL 语句，然后在 ClickHouse 上执行（回放），但整个执行链路较长，通常性能损耗较大。不同的是，MaterializeMySQL 引擎提供的内部数据解析以及回写方案隐去了三方插件的复杂链路。回放时将 BinLog Event 转换成底层 Block 结构，然后直接写入底层存储引擎，接近于物理复制。此方案可以类比于将 BinLog Event 直接回放到 InnoDB 的 Page 中。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。