摘要：利用快速構(gòu)建系統(tǒng)。構(gòu)建系統(tǒng)和的安裝本文不再贅述，直接開始動手構(gòu)建系統(tǒng)。分別為和，用于讀寫組，用于只讀組。最后配置的監(jiān)控服務(wù)可選，非必須至此，一個全部基于開源應(yīng)用的簡易系統(tǒng)就構(gòu)建好了。

利用ProxySQL、MySQL、ClickHouse快速構(gòu)建HTAP系統(tǒng)。

1. 關(guān)于ClickHouse

企業(yè)里隨著數(shù)據(jù)量的增加，以及日趨復(fù)雜的分析性業(yè)務(wù)需求，主要適用于OLTP場景的MySQL壓力越來越大。多年前還能免費試用的infobright社區(qū)版也早就銷聲匿跡，infinidb被MariaDB收入囊中之后改頭換面變成ColumnStore，但最近幾年發(fā)展的平平淡淡，都不是理想的OLAP方案。

戰(zhàn)斗民族出品的ClickHouse這幾年則是風(fēng)頭正勁，國內(nèi)用戶也越來越多，幾大公有云上也提供相應(yīng)的產(chǎn)品服務(wù)，是目前市面上最快的OLAP數(shù)據(jù)庫，性能遠超Vertica、Sybase IQ等。

ClickHouse對MySQL的兼容性也很好，除了很多語法相同或接近外，甚至還可以利用MySQL客戶端（協(xié)議）連接到ClickHouse。

ClickHouse 可以掛載為 MySQL 的一個從庫 ，先全量再增量的實時同步 MySQL 數(shù)據(jù)，這個功能可以說是今年最亮眼、最剛需的功能，基于它我們可以輕松的打造一套企業(yè)級解決方案，讓 OLTP 和 OLAP 的融合從此不再頭疼。目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 語句，及大部分常用的 DDL 操作。只需要安裝最新版本的ClickHouse就可以體驗到這個新特性了，不過該特性還處于experimental階段，還在不斷完善中。

2. 關(guān)于ProxySQL

ProxySQL是一款強大的中間件為MySQL的架構(gòu)提供了有力的支持，支持傳統(tǒng)主從復(fù)制、半同步復(fù)制、MGR、PXC等多種MySQL架構(gòu)，還支持故障自動檢測和切換、連接池、讀寫分離、日志記錄、監(jiān)控、集群部署等多項實用功能。當然了，ProxySQL最大的缺點是性能損失較大，預(yù)計至少有20% ~ 30%的性能損失，因此如果是在高性能場景下可能不太合適。不過可以考慮通過分庫分表等方式降低單節(jié)點壓力，充分發(fā)揮ProxySQL的集群功能。

3. 構(gòu)建HTAP系統(tǒng)

ClickHouse和ProxySQL的安裝本文不再贅述，直接開始動手構(gòu)建HTAP系統(tǒng)。下面是整體架構(gòu)示意圖

3.1 將ClickHouse配置成為MySQL的從庫

登入ClickHouse后，執(zhí)行下面的命令啟用新特性：

clickhouse :) SET allow_experimental_database_materialize_mysql = 1;

在ClickHouse中，創(chuàng)建一個復(fù)制通道，即可構(gòu)建一個MySQL復(fù)制從庫，例如：

clickhouse :) CREATE DATABASE test ENGINE = MaterializeMySQL(172.24.10.10:3306, test, repl, repl);
clickhouse :) use test;
clickhouse :) show tables;
┌─name─────┐
│ sbtest1  │
│ sbtest10 │
│ sbtest11 │
...
32 rows in set. Elapsed: 0.006 sec.

首次創(chuàng)建復(fù)制通道后，ClickHouse會快速從MySQL主庫讀取所有數(shù)據(jù)并應(yīng)用，可以查看數(shù)據(jù)復(fù)制的進度：

[[email protected]]# cat metadata/sbtest/.metadata

Version: 2
Binlog File: binlog.001496
Executed GTID: 097ee9f2-2ded-11eb-9211-e4434ba52b50:1-952676723
Binlog Position: 789663343
Data Version: 2

提醒：在這里，我設(shè)置的是主從復(fù)制專用賬號。相對于普通的主從復(fù)制賬號，用于ClickHouse從庫的賬號至少還要加上只讀權(quán)限，例如：

[[email protected]]> show grants for repl;
+--------------------------------------------------------------------------+
| Grants for repl@%                                                        |
+--------------------------------------------------------------------------+
| GRANT RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%` |
| GRANT SELECT ON `test`.* TO `repl`@`%`                                   |
+--------------------------------------------------------------------------+

ClickHouse的MaterializeMySQL引擎從MySQL復(fù)制數(shù)據(jù)的速度非常之快，甚至比MySQL原生的從庫還要更快，大家可以親身感受體驗下。
接下來，再在ClickHouse中創(chuàng)建一個業(yè)務(wù)賬號，以及一個服務(wù)監(jiān)控賬號（用于ProxySQL對后端服務(wù)的監(jiān)控）。編輯ClickHouse的配置文件 users.xml，增加兩個用戶：

        
            app_user
            
                ::/0
            
            default
            default
        
        
            monitor
            
                ::/0
            
            default
            default
        

我只設(shè)置了簡單密碼，也沒有修改其權(quán)限和quota模式，僅演示用，線上生產(chǎn)環(huán)境請自行調(diào)整以合規(guī)。

3.2 配置ProxySQL

配置mysql_servers表，增加兩條記錄，并使配置生效：

proxysql> insert into mysql_servers(hostgroup_id, hostname, port) values(0, 172.24.10.10, 3306);
proxysql> insert into mysql_servers(hostgroup_id, hostname, port) values(1, 172.24.10.11, 9004);
proxysql> save mysql servers to disk; load mysql servers to run;

其中，172.24.10.10:3306 是MySQL主庫，172.24.10.11:9004是ClickHouse從庫，9004端口是ClickHouse里給MySQL客戶端連接專用的，可以用MySQL客戶端（協(xié)議）連接進來執(zhí)行各種操作。
hostgroup分別為0和1，0用于讀寫組，1用于只讀組。
配置mysql_users表，加入業(yè)務(wù)賬號和監(jiān)控賬號：

proxysql> select username,password,active from mysql_users;
+-----------+----------+--------+
| username  | password | active |
+-----------+----------+--------+
| app_user  | app_user | 1      |
| monitor   | monitor  | 1      |
+-----------+----------+--------+

proxysql> save mysql users to disk; load mysql users to runtime;

配置mysql_query_rules表，這個是關(guān)鍵，用于判斷將哪些SQL轉(zhuǎn)發(fā)到MySQL主庫，哪些轉(zhuǎn)發(fā)到ClickHouse：

proxysql> select rule_id, active, match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+-------------------------+-----------------------+
| rule_id | active | match_pattern           | destination_hostgroup |
+---------+--------+-------------------------+-----------------------+
| 1       | 1      | ^SELECT.*+CLICKHOUSE.* | 1                     |
+---------+--------+-------------------------+-----------------------+

proxysql>  save mysql query rules to disk; load mysql query rules to run;

上述規(guī)則的意思是，當SELECT語句中包含 "+CLICKHOUSE" 關(guān)鍵字時，就會自動轉(zhuǎn)發(fā)到 ClickHouse 后端去處理，其余的都發(fā)送到MySQL后端處理。例如下面這兩條SQL就會分別轉(zhuǎn)發(fā)到MySQL和ClickHouse后端：

#SQL #1
[[email protected]]> SELECT * FROM sbtest1 WHERE id=1;

#SQL #2
[[email protected]]> SELECT /*+CLICKHOUSE*/ * FROM sbtest1 WHERE id=1; 

第二條SQL利用MySQL的注釋語法巧妙地實現(xiàn)規(guī)則HINT。
查詢 stats_mysql_query_digest 表的結(jié)果予以確認：

proxysql> select hostgroup, schemaname, username, digest, digest_text from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+----------------------------------+
| hostgroup | schemaname | username | digest             | digest_text                      |
+-----------+------------+----------+--------------------+----------------------------------+
| 0         | sbtest     | app_user | 0x5662D7CF0442E794 | select * from sbtest1 where id=? |
| 1         | sbtest     | app_user | 0x5662D7CF0442E794 | select * from sbtest1 where id=? |
+-----------+------------+----------+--------------------+----------------------------------+

可以看到，兩條SQL看起來一樣，但分別轉(zhuǎn)發(fā)到不同的hostgroup了。
最后配置ProxySQL的監(jiān)控服務(wù)（可選，非必須）：

proxysql> set mysql-monitor_enabled="true"; 
proxysql> set mysql-monitor_username="monitor";
proxysql> set mysql-monitor_password="monitor";

proxysql> save mysql variables to disk; load mysql variables to runtime;

至此，一個全部基于開源應(yīng)用的簡易HTAP系統(tǒng)就構(gòu)建好了。

4. 性能對比

在這里，我選用ClickHouse官方提供的benchmark方案：Star Schema Benchmark。
編譯完成后先是利用ssb-dbgen生成測試數(shù)據(jù)（指定參數(shù) -s 50）：

./dbgen -s 50 -T c &
./dbgen -s 50 -T l &
./dbgen -s 50 -T p &
./dbgen -s 50 -T s &
./dbgen -s 50 -T d &

再創(chuàng)建幾個測試庫表，自行修改建表的DDL以適應(yīng)MySQL語法。而后導(dǎo)入測試數(shù)據(jù)，最后根據(jù)文檔并生成 lineorder_flat 表。

[[email protected]]> show table status;
+----------------+--------+---------+------------+-----------+----------------+--------------+
| Name           | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length  |
+----------------+--------+---------+------------+-----------+----------------+--------------+
| customer       | InnoDB |      10 | Dynamic    |   1378209 |            120 |    166363136 |
| lineorder      | InnoDB |      10 | Dynamic    | 297927870 |            100 |  29871833088 |
| lineorder_flat | InnoDB |      10 | Dynamic    | 292584926 |            430 | 125952851968 |
| part           | InnoDB |      10 | Dynamic    |   1192880 |            111 |    132792320 |
| supplier       | InnoDB |      10 | Dynamic    |     99730 |            110 |     11026432 |
+----------------+--------+---------+------------+-----------+----------------+--------------+

數(shù)據(jù)全部加載完畢后，再在ClickHouse中創(chuàng)建MaterializeMySQL復(fù)制通道：

clickhouse :) CREATE DATABASE ssb ENGINE = MaterializeMySQL(172.24.10.10:3380, ssb, repl, repl);

數(shù)據(jù)量比較大，耐心靜待它復(fù)制完成即可。
然后連接 ProxySQL，先簡單執(zhí)行大表count(*)，觀察耗時的不同：

#直接執(zhí)行count(*)，會轉(zhuǎn)發(fā)到后端 MySQL 實例
[[email protected]]> select count(*) from lineorder_flat;
+-----------+
| count(*)  |
+-----------+
| 300005811 |
+-----------+
1 row in set (3 min 2.14 sec)

#加上HINT規(guī)則，會轉(zhuǎn)發(fā)到后端 ClickHouse 實例
[[email protected]]> select /*+CLICKHOUSE*/ count(*) from lineorder_flat;
+-----------+
| count(*)  |
+-----------+
| 300005811 |
+-----------+
1 row in set (5.67 sec)

光是 count(*) 就差了好多倍。
再選取其中前4個SQL測試，記錄的耗時如下：
Query MySQL ClickHouse(從庫) ClickHouse(原生)
Q1.1 308.388684 0.149 0.107
Q1.2 320.373203 0.280 0.027
Q1.3 279.673361 0.346 0.030
Q2.1 286.451062 1.246 0.489
很明顯，直接在MySQL上查詢的效率實在太低了，而作為從庫的MaterializeMySQL和ClickHouse原生的MergeTree表雖然也有一定差距，但相差也沒那么大了，還算是很快的。

5. 其他說明

• ClickHouse的MaterializeMySQL中不支持 create like 語法。例如執(zhí)行 create table db2.a like db1.a，其中db1是要復(fù)制到ClickHouse的，而db2是留在MySQL端，即便這樣也會導(dǎo)致ClickHouse端復(fù)制報錯，需要重啟才行。
• ClickHouse的MaterializeMySQL中也不支持函數(shù)索引。
• 偶爾發(fā)現(xiàn)ProxySQL的監(jiān)控模塊連接到ClickHouse后，會發(fā)送 SET wait_timeout=N 命令，會導(dǎo)致ClickHouse報錯，但不影響正常使用。重啟ProxySQL，或者重啟監(jiān)控開關(guān)都可以解決。

作者：葉金榮
原文：https://mp.weixin.qq.com/s/2CzggQdHvBGX1-yCBz-1Bg