clickhouse mysql引擎_ClickHouse 库引擎

最新推荐文章于 2024-01-28 11:02:34 发布

weixin_39714015

最新推荐文章于 2024-01-28 11:02:34 发布

阅读量360

点赞数

文章标签： clickhouse mysql引擎

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/weixin_39714015/article/details/111814978

版权

本文展示了如何在ClickHouse中创建MySQL类型的数据库并进行数据交互，通过示例展示了数据的插入、查询及聚合操作。实验表明，ClickHouse在数据聚合上的速度远超MySQL，例如COUNT操作约快2950倍，去重操作约快94倍，分组统计操作约快94倍，排序取TOP 10操作约快25倍，揭示了ClickHouse在大数据处理方面的高效能。

摘要由CSDN通过智能技术生成

-- 1在ClickHouse中创建MySQL类型的数据库，同时与MySQL服务器交换数据：

cdh3 :) CREATE DATABASE IF NOT EXISTS flink_test

:-] ENGINE = MySQL('cdh1:3306', 'flink_test', 'scm', 'scm');

CREATE DATABASE IF NOT EXISTS flink_test

ENGINE= MySQL('cdh1:3306', 'flink_test', 'scm', 'scm')

Ok.0 rows in set. Elapsed: 0.004sec.-- 2查看库

cdh3 :) SHOW DATABASES;

SHOW DATABASES

→ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)

┌─name───────┐

│default│

│ flink_test │

│ system │

└────────────┘

↘ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↓ Progress: 3.00 rows, 290.00 B (528.24 rows/s., 51.06 KB/s.)3 rows in set. Elapsed: 0.006sec.-- 3查看 flink_test 库中的表。此时在ClickHouse中便可以看到MySQL中的表。其它未用到的表已省略

cdh3 :) SHOW TABLES FROM flink_test;

SHOW TABLES FROM flink_test

↙ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)

┌─name───────────────────┐

│ vote_recordss_memory │

│ w3 │

└────────────────────────┘

← Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↖ Progress: 17.00 rows, 661.00 B (1.95 thousand rows/s., 75.99 KB/s.)17 rows in set. Elapsed: 0.009sec.-- 4选择库

cdh3 :) USE flink_test;

USE flink_test

Ok.0 rows in set. Elapsed: 0.005sec.-- 5插入数据(表名区分大小写)

cdh3 :) INSERT INTO w3 VALUES(3, 'Mercury');

INSERT INTO w3 VALUES

Ok.1 rows in set. Elapsed: 0.022sec.-- 6查询数据。数据插入后不支持删除和更新。

cdh3 :) SELECT*FROM w3;

SELECT*FROM w3

← Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)

┌─id─┬─f1──────┐

│3│ Mercury │

│5│ success │

└────┴─────────┘

↖ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↑ Progress: 2.00 rows, 42.00 B (202.58 rows/s., 4.25 KB/s.)2 rows in set. Elapsed: 0.010sec.-- 7查看 MySQL 和 ClickHouse 对数据的聚合能力-- 7.1 MySQL。可以看到在MySQL中统计一张将近2千万数据量的表花费了 29.54秒

mysql> SELECT COUNT(*) FROM vote_recordss_memory;+----------+

| COUNT(*) |

+----------+

| 19999998 |

+----------+

1 row in set (29.54sec)-- 7.2 ClickHouse 中执行一次COUNT，花费了 9.713秒

cdh3 :) SELECT COUNT(*) FROM vote_recordss_memory;

SELECT COUNT(*)

FROM vote_recordss_memory

↘ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↓ Progress: 131.07 thousand rows, 131.07 KB (1.14 million rows/s., 1.14 MB/s.) ↙ Progress: 327.68 thousand rows, 327.68 KB (1.52 million rows/s., 1.52 MB/s.) ← Progress: 524.29 thousand rows, 524.29 KB (1.66millio

┌──COUNT()─┐

│19999998│

└──────────┘

↓ Progress:19.79 million rows, 19.79 MB (2.04 million rows/s., 2.04 MB/s.) ↙ Progress: 20.00 million rows, 20.00 MB (2.06 million rows/s., 2.06 MB/s.)1 rows in set. Elapsed: 9.713 sec. Processed 20.00 million rows, 20.00 MB (2.06 million rows/s., 2.06 MB/s.)-- 7.3在查询时指定mysql的连接、库名、表名、登录信息，等价于上面的SQL。

cdh3 :) SELECT COUNT(*) FROM mysql('cdh1:3306', 'flink_test', 'vote_recordss_memory', 'root', '123456');-- 8使用 ClickHouse 的 MergeTree 表引擎-- 8.1切换到 ClickHouse 默认库下

cdh1 :) USEdefault;

USEdefaultOk.0 rows in set. Elapsed: 0.007sec.-- 8.2创建表并指定 MergeTree 表引擎，将MySQL数据加载进来，同时指定排序规则主键值为准

cdh1 :) CREATE TABLE vote_recordss

:-] ENGINE = MergeTree--(id, create_time)

:-] ORDER BY id AS

:-] SELECT * FROM mysql('cdh1:3306', 'flink_test', 'vote_recordss_memory', 'root', '123456');

CREATE TABLE vote_recordss

ENGINE=MergeTree

ORDER BY id AS

SELECT*FROM mysql('cdh1:3306', 'flink_test', 'vote_recordss_memory', 'root', '123456')

↖ Progress:65.54 thousand rows, 3.01 MB (299.97 thousand rows/s., 13.80 MB/s.) ↑ Progress: 131.07 thousand rows, 6.03 MB (411.12 thousand rows/s., 18.91 MB/s.) ↗ Progress: 196.61 thousand rows, 9.04 MB (468.88 thousand rows/s., 21.57 MB/s.) → Progress: 262.14thousand Ok.0 rows in set. Elapsed: 27.917 sec. Processed 20.00 million rows, 920.00 MB (716.40 thousand rows/s., 32.95 MB/s.)-- 8.3查询。可以看到是count某个值的速度速度约为MySQL的2950倍

cdh1 :) SELECT COUNT(*) FROM vote_recordss;

SELECT COUNT(*)

FROM vote_recordss

↙ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)

┌──COUNT()─┐

│19999998│

└──────────┘

← Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↖ Progress: 20.00 million rows, 80.00 MB (2.26 billion rows/s., 9.06 GB/s.) 98%

1 rows in set. Elapsed: 0.009 sec. Processed 20.00 million rows, 80.00 MB (2.20 billion rows/s., 8.79 GB/s.)-- 8.4去重。可以看到ClickHouse速度约为MySQL的94倍

mysql> SELECT DISTINCT group_id fromvote_recordss_memory ;+----------+

| group_id |

+----------+

| 1 |

| 2 |

| 0 |

+----------+

3 rows in set (12.79sec)--ClickHouse中执行

cdh1 :) SELECT DISTINCT group_idfromvote_recordss;

SELECT DISTINCT group_id

FROM vote_recordss

↑ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)

┌─group_id─┐

│0│

│2│

│1│

└──────────┘

↗ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) → Progress: 19.04 million rows, 76.17 MB (145.18 million rows/s., 580.70 MB/s.) 94%↘ Progress: 20.00 million rows, 80.00 MB (147.97 million rows/s., 591.87 MB/s.) 98%

3 rows in set. Elapsed: 0.136 sec. Processed 20.00 million rows, 80.00 MB (147.44 million rows/s., 589.76 MB/s.)-- 8.5分组统计。可以看到ClickHouse速度约为MySQL的94倍

mysql> SELECT SUM(vote_num),group_id fromvote_recordss_memory GROUP BY group_id;+---------------+----------+

| SUM(vote_num) | group_id |

+---------------+----------+

| 33344339689 | 0 |

| 33315889226 | 1 |

| 33351509121 | 2 |

+---------------+----------+

3 rows in set (16.26sec)--ClickHouse中执行

cdh1 :) SELECT SUM(vote_num),group_idfromvote_recordss GROUP BY group_id;

SELECT

SUM(vote_num),

group_id

FROM vote_recordss

GROUP BY group_id

↙ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ← Progress: 11.43 million rows, 91.42 MB (101.40 million rows/s., 811.20 MB/s.) 56%┌─SUM(vote_num)─┬─group_id─┐

│33344339689 │ 0│

│33351509121 │ 2│

│33315889226 │ 1│

└───────────────┴──────────┘

↖ Progress:11.43 million rows, 91.42 MB (66.08 million rows/s., 528.64 MB/s.) 56%↑ Progress: 20.00 million rows, 160.00 MB (115.61 million rows/s., 924.84 MB/s.) 98%

3 rows in set. Elapsed: 0.173 sec. Processed 20.00 million rows, 160.00 MB (115.56 million rows/s., 924.45 MB/s.)-- 8.6 排序取TOP 10。可以看到ClickHouse速度约为MySQL的25倍

mysql> SELECT * FROM vote_recordss_memory ORDER BY create_time DESC,vote_num LIMIT 10;+----------+----------------------+----------+----------+--------+---------------------+

| id | user_id | vote_num | group_id | status | create_time |

+----------+----------------------+----------+----------+--------+---------------------+

| 19999993 | 4u6PJYvsDD4khghreFvm | 2388 | 0 | 1 | 2019-10-15 01:00:20 |

| 19999998 | shTrosZpT5zux3wiKH5a | 4991 | 2 | 1 | 2019-10-15 01:00:20 |

| 19999995 | xRwQuMgQeuBoXvsBusFO | 6737 | 2 | 1 | 2019-10-15 01:00:20 |

| 19999996 | 5QNgMYoQUSsuX7Aqarw8 | 7490 | 2 | 2 | 2019-10-15 01:00:20 |

| 19999997 | eY12Wq9iSm0MH1PUTChk | 7953 | 0 | 2 | 2019-10-15 01:00:20 |

| 19999994 | ZpS0dWRm1TdhzTxTHCSj | 9714 | 0 | 1 | 2019-10-15 01:00:20 |

| 19999946 | kf7FOTUHAICP5Mv2xodI | 32 | 2 | 2 | 2019-10-15 01:00:19 |

| 19999738 | ER90qVc4CJCKH5bxXYTo | 57 | 1 | 2 | 2019-10-15 01:00:19 |

| 19999810 | gJHbBkGf0bJViwy5BB2d | 190 | 1 | 2 | 2019-10-15 01:00:19 |

| 19999977 | Wq7bogXRiHubhFlAHBJH | 208 | 0 | 2 | 2019-10-15 01:00:19 |

+----------+----------------------+----------+----------+--------+---------------------+

10 rows in set (15.31sec)--ClickHouse中执行

cdh1 :) SELECT* FROM vote_recordss ORDER BY create_time DESC,vote_num LIMIT 10;

SELECT*FROM vote_recordss

ORDER BY

create_time DESC,

vote_num ASC

LIMIT10↗ Progress:0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) → Progress: 2.34 million rows, 107.77 MB (21.21 million rows/s., 975.60 MB/s.) 11%↘ Progress: 5.31 million rows, 244.19 MB (24.97 million rows/s., 1.15 GB/s.) 26%↓ Progress: 8.75 million rows, 402.46 MB (27.97 mi%┌───────id─┬─user_id──────────────┬─vote_num─┬─group_id─┬─status─┬─────────create_time─┐

│19999993 │ 4u6PJYvsDD4khghreFvm │ 2388 │ 0 │ 1 │ 2019-10-15 01:00:20│

│19999998 │ shTrosZpT5zux3wiKH5a │ 4991 │ 2 │ 1 │ 2019-10-15 01:00:20│

│19999995 │ xRwQuMgQeuBoXvsBusFO │ 6737 │ 2 │ 1 │ 2019-10-15 01:00:20│

│19999996 │ 5QNgMYoQUSsuX7Aqarw8 │ 7490 │ 2 │ 2 │ 2019-10-15 01:00:20│

│19999997 │ eY12Wq9iSm0MH1PUTChk │ 7953 │ 0 │ 2 │ 2019-10-15 01:00:20│

│19999994 │ ZpS0dWRm1TdhzTxTHCSj │ 9714 │ 0 │ 1 │ 2019-10-15 01:00:20│

│19999946 │ kf7FOTUHAICP5Mv2xodI │ 32 │ 2 │ 2 │ 2019-10-15 01:00:19│

│19999738 │ ER90qVc4CJCKH5bxXYTo │ 57 │ 1 │ 2 │ 2019-10-15 01:00:19│

│19999810 │ gJHbBkGf0bJViwy5BB2d │ 190 │ 1 │ 2 │ 2019-10-15 01:00:19│

│19999977 │ Wq7bogXRiHubhFlAHBJH │ 208 │ 0 │ 2 │ 2019-10-15 01:00:19│

└──────────┴──────────────────────┴──────────┴──────────┴────────┴─────────────────────┘

↖ Progress:16.65 million rows, 766.10 MB (27.46 million rows/s., 1.26 GB/s.) 82%↑ Progress: 20.00 million rows, 920.00 MB (32.98 million rows/s., 1.52 GB/s.) 98%

10 rows in set. Elapsed: 0.607 sec. Processed 20.00 million rows, 920.00 MB (32.93 million rows/s., 1.51 GB/s.)

weixin_39714015

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。