MaterializedMySQL是一個用于從MySQL數據庫中獲取數據的表引擎。它的工作原理是:

• MaterializedMySQL會定期從MySQL拉取數據,同步到云數據庫ClickHouse本地。
• 數據同步后,會存儲在本地云數據庫ClickHouse表中,表引擎設置為合適的引擎(如MergeTree)。
• 查詢時,云數據庫ClickHouse不會訪問MySQL,而是直接從本地已同步的表中讀取數據。
• 同步的時間間隔可以通過 mysql_materialized_tables_ttl參數設置,默認10分鐘同步一次。

MaterializedMySQL的主要優點是:

• 減少了跨網絡訪問MySQL的開銷,查詢性能更好。
• 可以對同步的數據進行云數據庫ClickHouse的查詢優化,實現更快的分析。
• 支持增量同步,只同步新增或修改的數據,而不是整表同步。
• 支持只同步指定的列,以減少不需要的數據量。
• 本地表引擎可自定義,使存儲方式更靈活。

創建數據庫

語法

創建使用MaterializedMySQL引擎的表的語法:

CREATE TABLE [IF NOT EXISTS] table_name 
ENGINE = MaterializedMySQL(`mysql_source_host`, `mysql_source_database`, `mysql_source_table`, `clickhouse_sink_database`, `clickhouse_sink_table`, `clickhouse_engine`)

• mysql_source_host:MySQL的源主機地址
• mysql_source_database:MySQL源數據庫名
• mysql_source_table:MySQL源表名
• clickhouse_sink_database:云數據庫ClickHouse目標數據庫名
• clickhouse_sink_table:云數據庫ClickHouse目標表名
• clickhouse_engine:云數據庫ClickHouse目標表的引擎

示例

-- 從MySQL sync_test庫的data表同步到ClickHouse的sync數據庫sync_data表

CREATE TABLE sync.sync_data
ENGINE = MaterializedMySQL('localhost', 'sync_test', 'data', 'sync', 'sync_data', 'MergeTree() order by id');

這個表會定期從MySQL localhost上的sync_test庫的data表同步數據到云數據庫ClickHouse的sync庫的sync_data表,目標表的引擎是MergeTree。

MaterializedMySQL提供了從MySQL增量同步數據的便捷途徑,可以讓云數據庫ClickHouse對MySQL數據進行更優化的存儲和查詢。

MySQL服務器端配置

為了 MaterializedMySQL的正確工作，有一些必須設置的 MySQL端配置設置:

• default_authentication_plugin = mysql_native_password，因為 MaterializedMySQL 只能授權使用該方法。
• gtid_mode = on，因為基于GTID的日志記錄是提供正確的 MaterializedMySQL復制的強制要求。

                
說明
                
當打開 gtid_mode 時需要指定 enforce_gtid_consistency = on。
              

支持的數據類型

MySQL	云數據庫ClickHouse
TINY	Int8
SHORT	Int16
INT24	Int32
LONG	UInt32
LONGLONG	UInt64
FLOAT	Float32
DOUBLE	Float64
DECIMAL, NEWDECIMAL	Decimal
DATE, NEWDATE	Date
DATETIME, TIMESTAMP	DateTime
DATETIME2, TIMESTAMP2	DateTime64
YEAR	UInt16
TIME	Int64
ENUM	Enum
STRING	String
VARCHAR, VAR_STRING	String
BLOB	String
GEOMETRY	String
BINARY	FixedString
BIT	UInt64
SET	UInt64

MySQL中的Time 類型，會被云數據庫ClickHouse轉換成微秒來存儲。如果MySQL表包含此類類型的列，云數據庫ClickHouse拋出異常"Unhandled data type"并停止復制。

規范和推薦用法

兼容性限制

除了數據類型的限制之外，還有一些限制與 MySQL數據庫相比有所不同，這應該在復制之前解決:

• MySQL 中的每個表都應該包含 PRIMARY KEY。
• 對于表的復制，那些包含 ENUM 字段值超出范圍的行(在 ENUM 簽名中指定)將不起作用。

DDL Queries

MySQL DDL 語句會被轉換成對應的云數據庫ClickHouse DDL 語句，比如： (ALTER, CREATE, DROP, RENAME). 如果云數據庫ClickHouse 無法解析某些語句DDL 操作，則會跳過。

數據復制

MaterializedMySQL不支持直接的 INSERT， DELETE 和 UPDATE 查詢。然而，它們在數據復制方面得到了支持:

• MySQL INSERT查詢被轉換為 _sign=1的INSERT查詢。
• MySQL DELETE查詢被轉換為 INSERT，并且 _sign=-1。
• 如果主鍵被修改了，MySQL的 UPDATE 查詢將被轉換為 INSERT 帶 _sign=1 和INSERT 帶有_sign=-1;如果主鍵沒有被修改，則轉換為 INSERT和 _sign=1。

MaterializedMySQL 數據表查詢

SELECT 查詢從 MaterializedMySQL表有一些細節:

• 如果在SELECT查詢中沒有指定 _version，則FINAL修飾符被使用，所以只有帶有 MAX(_version)的行會返回每個主鍵值。
• 如果在SELECT查詢中沒有指定 _sign，則默認使用 WHERE _sign=1。所以被刪除的行不是 包含在結果集中。
• 結果包括列注釋，以防MySQL數據庫表中存在這些列注釋。

索引轉換

在云數據庫ClickHouse表中，MySQL的 PRIMARY KEY 和 INDEX 子句被轉換為 ORDER BY 元組。

云數據庫ClickHouse只有一個物理排序，由 order by 條件決定。要創建一個新的物理排序，請使用materialized views。

                
說明
                
_sign=-1?的行不會被物理地從表中刪除。
級聯?UPDATE/DELETE?查詢不支持?MaterializedMySQL?引擎。
MaterializedMySQL?受?optimize_on_insert?設置的影響。當MySQL服務器中的一個表發生變化時，數據會合并到?MaterializedMySQL?數據庫中相應的表中。
              

使用示例

MySQL 查詢語句:

mysql>CREATE DATABASE db;
mysql>CREATE TABLE db.test (a INTPRIMARYKEY, b INT);
mysql>INSERT INTO db.test VALUES (1,11),(2,22);
mysql>DELETE FROM db.test WHERE a=1;
mysql>ALTER TABLE db.test ADDCOLUMN c VARCHAR(16);
mysql>UPDATE db.test SET c='Wow!', b=222;
mysql>SELECT * FROM test;

┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘

云數據庫ClickHouse中的數據庫，與MySQL服務器交換數據:

創建的數據庫和表:

CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306','db','user','***');
SHOW TABLES FROM mysql;

┌─name─┐
│ test │
└──────┘

數據插入之后：

SELECT * FROM mysql.test;

┌─a─┬──b─┐
│ 1 │ 11 │
│ 2 │ 22 │
└───┴────┘

刪除數據后，添加列并更新:

SELECT * FROM mysql.test;

┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘

完整同步示例

CREATE TABLE mysql_sync.full_table 
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'products_mv', 'MergeTree() order by id');  

這會完整同步MySQL server，其ip地址為 A.A.A.A 上的sales庫中products表到云數據庫ClickHouse中的sync庫的products_mv表。

增量同步示例

CREATE TABLE mysql_sync.updated_data
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'product_updates', 'MergeTree() order by id', 1);

該表僅同步products表中新增或修改過的數據。第6個參數 1表示啟用增量同步。

只同步部分列示例

CREATE TABLE mysql_sync.product_names
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'product_names', 'MergeTree() order by id', 0, ['name', 'description']);

這里只同步products表的name和description兩列。第7個參數定義同步的列。

設置同步周期示例

CREATE TABLE mysql_sync.orders
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'store', 'orders', 'sync', 'orders_mv', 'MergeTree() order by id', 0, [], 3600);

這里通過第8個參數將同步周期設置為3600秒,即每小時同步一次。