基于開源應用快速建構HTAP系統（2）

上述規則的意思是，當SELECT語句中包含 "+CLICKHOUSE" 關鍵字時，就會自動轉發到 ClickHouse 後端去處理，其餘的都發送到MySQL後端處理。例如下面這兩條SQL就會分别轉發到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的注釋文法巧妙地實作規則HINT。

查詢 stats_mysql_query_digest 表的結果予以确認：

roxysql> 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看起來一樣，但分别轉發到不同的hostgroup了。

最後配置ProxySQL的監控服務（可選，非必須）：

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;      

至此，一個全部基于開源應用的簡易HTAP系統就建構好了。

4. 性能對比

在這裡，我選用ClickHouse官方提供的benchmark方案：Star Schema Benchmark。

編譯完成後先是利用ssb-dbgen生成測試資料（指定參數 -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 &      

再建立幾個測試庫表，自行修改建表的DDL以适應MySQL文法。而後導入測試資料，最後根據文檔并生成 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 |
+----------------+--------+---------+------------+-----------+----------------+--------------+      

資料全部加載完畢後，再在ClickHouse中建立MaterializeMySQL複制通道：

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

資料量比較大，耐心靜待它複制完成即可。

然後連接配接 ProxySQL，先簡單執行大表count(*)，觀察耗時的不同：

#直接執行count(*)，會轉發到後端 MySQL 執行個體
[[email protected]]> select count(*) from lineorder_flat;
+-----------+
| count(*)  |
+-----------+
| 300005811 |
+-----------+
1 row in set (3 min 2.14 sec)

#加上HINT規則，會轉發到後端 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表雖然也有一定差距，但相差也沒那麼大了，還算是很快的。

4. 其他說明

• ClickHouse的MaterializeMySQL中不支援 create like 文法。例如執行 create table db2.a like db1.a，其中db1是要複制到ClickHouse的，而db2是留在MySQL端，即便這樣也會導緻ClickHouse端複制報錯，需要重新開機才行。
• ClickHouse的MaterializeMySQL中也不支援函數索引。
• 偶爾發現ProxySQL的監控子產品連接配接到ClickHouse後，會發送 SET wait_timeout=N 指令，會導緻ClickHouse報錯，但不影響正常使用。重新開機ProxySQL，或者重新開機監控開關都可以解決。

Enjoy it :)