- 為啥有這篇文章博脑？
- 很多人好奇ClickHouse添履，都聽說過很快屁倔，但是到底有多恐怖？
- 新建表還要理解ClickHouse的引擎和數(shù)據(jù)類型暮胧，好麻煩
- 今天锐借，用一個(gè)簡單粗暴的功能，幫你一鍵導(dǎo)入MySQL的數(shù)據(jù)往衷，無需人肉建表
數(shù)據(jù)導(dǎo)入
第一組
# du出的表大小
5.5G article_clientuser_sum.ibd
# ClickHouse操作語句
CREATE TABLE article_clientuser_sum
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('host:port', 'db', 'article_clientuser_sum', 'user', 'password')
# 耗時(shí)和平均速度
0 rows in set. Elapsed: 137.251 sec. Processed 18.59 million rows, 7.34 GB (135.43 thousand rows/s., 53.48 MB/s.)
第二組
# 另一個(gè)表
20G xx_httpcode_minf.ibd
CREATE TABLE xx_httpcode_minf
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('host:port', 'db', 'tb', 'user', 'password')
# 不知道為啥這表這么快就導(dǎo)入了 貌似是行少钞翔，但是表的總大小大啊
0 rows in set. Elapsed: 44.389 sec. Processed 13.03 million rows, 1.44 GB (293.44 thousand rows/s., 32.35 MB/s.)
PK之count(*)
第一組
# 1800w
# ClickHouse
SELECT count(*)
FROM article_clientuser_sum
┌──count()─┐
│ 18587381 │
└──────────┘
1 rows in set. Elapsed: 0.033 sec. Processed 18.59 million rows, 74.35 MB (556.76 million rows/s., 2.23 GB/s.)
# MySQL
mysql> select count(*) from article_clientuser_sum ;
+----------+
| count(*) |
+----------+
| 18587381 |
+----------+
1 row in set (39.48 sec)
# 性能 1196X
第二組
# 1300w
# ClickHouse
SELECT count(*)
FROM xx_httpcode_minf
┌──count()─┐
│ 13025469 │
└──────────┘
1 rows in set. Elapsed: 0.032 sec. Processed 13.03 million rows, 52.10 MB (406.68 million rows/s., 1.63 GB/s.)
# MySQL
mysql> SELECT count(*)
-> FROM xx_httpcode_minf;
+----------+
| count(*) |
+----------+
| 13025469 |
+----------+
1 row in set (1 min 46.87 sec)
# 性能 3340X
PK之復(fù)雜查詢
第一組
# ClickHouse
SELECT SUM(size) AS size
FROM xx_network_flow
WHERE (date >= '2018-01-01') AND (date <= '2018-01-31') AND (netstat = 0) AND (project LIKE '保密%')
Row 1:
──────
size: 4132888693
1 rows in set. Elapsed: 0.039 sec. Processed 841.66 thousand rows, 9.46 MB (21.67 million rows/s., 243.70 MB/s.)
# MySQL
+------------+
| size |
+------------+
| 4132888693 |
+------------+
1 row in set (2.34 sec)
# 性能 60X
- SQL太長，截圖示例
- SQL里的xxx均為脫敏數(shù)據(jù)
# ClickHouse
┌─────size─┐
│ 76888224 │
└──────────┘
1 rows in set. Elapsed: 0.137 sec. Processed 841.66 thousand rows, 9.46 MB (6.13 million rows/s., 68.97 MB/s.)
# MySQL
+----------+
| size |
+----------+
| 76888224 |
+----------+
1 row in set (2.86 sec)
# 性能 21X
第二組
# ClickHouse
SELECT
project,
idc,
minf,
http_code,
sum(sumhit) AS num
FROM xx_httpcode_minf
WHERE (date = '2018-01-16') AND (httptype = 'download') AND \
(minf >= 0) AND (minf <= 288) AND \
(http_code IN ('200', '500', '404', '502', '503', '504'))
GROUP BY
project,
idc,
minf,
http_code
ORDER BY num DESC
LIMIT 3
┌─project─────────────────────────────────────┬─idc────┬─minf─┬─http_code─┬────num─┐
│ 域名1xxxx │ .1xx │ 195 │ 200 │ 247522 │
│ 域名2xxxx │ .2xx │ 185 │ 200 │ 246613 │
│ 域名3xxxx │ .3xx │ 188 │ 200 │ 245808 │
└─────────────────────────────────────────────┴────────┴──────┴───────────┴────────┘
3 rows in set. Elapsed: 0.161 sec. Processed 13.03 million rows, 284.63 MB (80.94 million rows/s., 1.77 GB/s.)
# MySQL
+---------------------------------------------+--------+------+-----------+--------+
| project | idc | minf | http_code | num |
+---------------------------------------------+--------+------+-----------+--------+
| 域名1xxxx| .1.xx | 195 | 200 | 247522 |
| 域名2xxxx | .2.xxx | 185 | 200 | 246613 |
| 域名3xxxx | .3xx | 188 | 200 | 245808 |
+---------------------------------------------+--------+------+-----------+--------+
3 rows in set (12.02 sec)
# 性能 75X
- SQL太長席舍，截圖示例
-
SQL里的xxx均為脫敏數(shù)據(jù)
# ClickHouse
┌─project────────────────────────────┬─idc────┬─minf─┬─http_code─┬───num─┐
│ 域名1 │ 1xxx│ 154 │ 404 │ 10792 │
│ 域名1 │ 2xxx │ 155 │ 404 │ 10395 │
│ 域名1│ 3xxx │ 272 │ 404 │ 10313 │
└────────────────────────────────────┴────────┴──────┴───────────┴───────┘
3 rows in set. Elapsed: 0.119 sec. Processed 13.03 million rows, 283.15 MB (109.10 million rows/s., 2.37 GB/s.)
# MySQL
+------------------------------------+--------+------+-----------+-------+
| project | idc | minf | http_code | num |
+------------------------------------+--------+------+-----------+-------+
| 域名1 | .1zz | 154 | 404 | 10792 |
| 域名1 | .3xx | 155 | 404 | 10395 |
| 域名1 | .3rr | 272 | 404 | 10313 |
+------------------------------------+--------+------+-----------+-------+
3 rows in set (2.19 sec)
# 性能 18X
壓縮對比
| 表名 | MySQL表容量 | ClickHouse表容量 | 壓縮倍數(shù) |
|---|---|---|---|
| article_clientuser_sum | 5.5GB | 1.2G | 4.6 |
| xx_httpcode_minf | 20GB | 243M | 84 |
| xx_network_flow | 189MB | 25M | 7.56 |
- 注：xx_httpcode_minf這個(gè)表的MySQL文件20個(gè)G布轿，應(yīng)該是有大量的空洞造成的，這也就是Facebook的人開發(fā)MyRocks的原因：減少空洞来颤，節(jié)省磁盤
風(fēng)險(xiǎn)
- 目前該功能還處于初級階段汰扭，有不完善的地方，比如數(shù)據(jù)導(dǎo)入的方式比較粗暴福铅，中間如果有異常萝毛，需要重新執(zhí)行（使用的ClickHouse版本為：1.1.54342）
- MySQL的參數(shù)需要修改，如max_allowed_packet
- 數(shù)據(jù)導(dǎo)入時(shí)需要注意帶寬滑黔，實(shí)測可以達(dá)到50MB/S
- 如果MySQL里的字段有decimal字符類型會怎么樣笆包？ClickHouse沒有雙精度的類型
- 部分SQL需要改寫
- 如雙引號改單引號
討論
-
ClickHouse為啥快？
- MySQL單條SQL是單線程的略荡，只能跑滿一個(gè)core庵佣，ClickHouse相反，有多少CPU撞芍，吃多少資源秧了，所以飛快
- ClickHouse不支持事務(wù)，不存在隔離級別序无。這里要額外說一下验毡， 有人覺得衡创，你一個(gè)數(shù)據(jù)庫都不支持事務(wù)，不支持ACID還玩?zhèn)€毛晶通。ClickHouse的定位是分析性數(shù)據(jù)庫璃氢，而不是嚴(yán)格的關(guān)系型數(shù)據(jù)庫。又有人要問了狮辽，數(shù)據(jù)都不一致一也，統(tǒng)計(jì)個(gè)毛。舉個(gè)例子喉脖，汽車的油表是100%準(zhǔn)確么椰苟？為了獲得一個(gè)100%準(zhǔn)確的值，難道每次測量你都要停車檢查么树叽？統(tǒng)計(jì)數(shù)據(jù)的意義在于用大量的數(shù)據(jù)看規(guī)律舆蝴，看趨勢，而不是100%準(zhǔn)確题诵。
- IO方面洁仗，MySQL是行存儲，ClickHouse是列存儲性锭，后者在count()這類操作天然有優(yōu)勢赠潦，同時(shí)，在IO方面草冈，MySQL需要大量隨機(jī)IO她奥，ClickHouse基本是順序IO。
- 有人可能覺得上面的數(shù)據(jù)導(dǎo)入的時(shí)候疲陕，數(shù)據(jù)肯定緩存在內(nèi)存里了方淤，這個(gè)的確，但是ClickHouse基本上是順序IO蹄殃，用過就知道了携茂，對IO基本沒有太高要求，當(dāng)然诅岩，磁盤越快讳苦，上層處理越快，但是99%的情況是吩谦，CPU先跑滿了（數(shù)據(jù)庫里太少見了鸳谜，大多數(shù)都是IO不夠用）。
說到MySQL上跑的各種復(fù)雜查詢式廷，那是相當(dāng)痛苦的回憶咐扭。從索引層面，很難對這些SQL進(jìn)行優(yōu)化，這也是我從MySQL DBA轉(zhuǎn)做數(shù)據(jù)分析后要解決的第一個(gè)問題
專業(yè)的事情讓專業(yè)的數(shù)據(jù)庫來做蝗肪，放開MySQL吧~
太?快了袜爪，還不趕緊來試試
