前言

在工作場景中，我們會采集工廠設備數據用于智能控制，數據的存儲用了 InfluxDB，隨著數據規模越來越大，InfluxDB 的性能越來越差，故考慮引入 ClickHouse 分擔 InfluxDB 大數據分析的壓力，再加上我們業務上也用到了 MySQL ，所以本文就來對比下 MySQL、InfluxDB、ClickHouse 在千萬數據量下的寫入耗時、聚合查詢耗時、磁盤占用等各方面性能指標。

結論先行

最終的結論是，直接使用 ClickHouse 官網提供的 6600w 數據集來做對比測試，在 MySQL、InfluxDB、ClickHouse 同樣分配 4c16g 資源的情況下，ClickHouse 無論是導入速度、磁盤占用、查詢性能都完全碾壓 MySQL 和 InfluxDB，具體對比指標如以下表格：

為了確保測試結果相對準確，以上每條sql起碼執行5次，然后取中間值。其中 InfluxDB 表現比想象中的要差，甚至還不如 MySQL，可能是數據樣本和測試用例不太適合 InfluxDB 場景導致的，如果大家對測試結果有疑問，可以 git clone [
https://github.com/stone0090/clickhouse-test.git](https://github.com/stone0090/clickhouse-test.git)項目，完整驗證以上對比全過程。

數據庫簡介

MySQL

MySQL 是一個關系型數據庫管理系統，由瑞典 MySQL AB 公司開發，屬于 Oracle 旗下產品，是最流行的關系型數據庫管理系統之一。它所使用的 SQL 語言是用于訪問數據庫的最常用標準化語言。它采用了雙授權政策，分為社區版和商業版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型和大型網站的開發都選擇 MySQL 作為網站數據庫?！陡咝阅躆ySQL》一書中開篇明義講到的最核心的一句話是“MySQL并不完美，但是卻足夠靈活”，它是架構中的萬金油，龐雜非單一的項目中總會有它的用武之地。

InfluxDB

InfluxDB 是一個由 InfluxData 公司開發的開源時序型數據庫，專注于海量時序數據的高性能讀、高性能寫、高效存儲與實時分析，在 DB-Engines Ranking 時序型數據庫排行榜上位列榜首，廣泛應用于DevOps監控、IoT監控、實時分析等場景。
傳統數據庫通常記錄數據的當前值，時序型數據庫則記錄所有的歷史數據，在處理當前時序數據時又要不斷接收新的時序數據，同時時序數據的查詢也總是以時間為基礎查詢條件，并專注于解決以下海量數據場景的問題：

• 時序數據的寫入：如何支持千萬級/秒數據的寫入；
• 時序數據的讀取：如何支持千萬級/秒數據的聚合和查詢；
• 成本敏感：海量數據存儲帶來的是成本問題，如何更低成本地存儲這些數據。

ClickHouse

ClickHouse 是 Yandex（俄羅斯最大的搜索引擎）開源的一個用于實時數據分析的基于列存儲的數據庫，其處理數據的速度比傳統方法快 100-1000 倍。ClickHouse 的性能超過了目前市場上可比的面向列的 DBMS，每秒鐘每臺服務器每秒處理數億至十億多行和數十千兆字節的數據。它是一個用于聯機分析（OLAP）的列式數據庫管理系統（DBMS），簡單介紹一下 OLTP 和 OLAP。

• OLTP：是傳統的關系型數據庫，主要操作增刪改查，強調事務一致性，比如銀行系統、電商系統。
• OLAP：是倉庫型數據庫，主要是讀取數據，做復雜數據分析，側重技術決策支持，提供直觀簡單的結果。

那 ClickHouse OLAP 適用場景有：1）讀多于寫；2）大寬表，讀大量行但是少量列，結果集較?。?）數據批量寫入，且數據不更新或少更新；4）無需事務，數據一致性要求低；5）靈活多變，不適合預先建模。

環境準備

在阿里云買一臺 16c64g 的服務器，操作系統 centos 7.8，使用 sealos 一鍵安裝 k8s，使用 helm 一鍵安裝 mysql（5.7）、influxdb（1.8）、clickhouse（22.3） ，每個應用各分配 4c16g 的資源。

# 下載 sealos$ wget https://github.com/labring/sealos/releases/download/v4.0.0/sealos_4.0.0_linux_amd64.tar.gz && tar zxvf sealos_4.0.0_linux_amd64.tar.gz sealos && chmod +x sealos && mv sealos /usr/bin# 初始化一個單節點 Kube.NETes$ sealos run labring/kubernetes:v1.24.0 labring/calico:v3.22.1 --masters [xxx.xxx.xxx.xxx] -p [your-ecs-password]# 去掉 master 的污點，允許安裝應用到 master 和 control-plane$ kubectl taint nodes --all node-role.kubernetes.io/master-$ kubectl taint nodes --all node-role.kubernetes.io/control-plane-# 獲取 mysql、influxdb、clickhouse 一鍵安裝 Helm-Charts$ wget https://github.com/stone0090/clickhouse-test/archive/refs/tags/v1.0.0.tar.gz$ tar -zxvf v1.0.0.tar.gz# 安裝 Kubernetes 包管理工具 Helm，以及 mysql、influxdb、clickhouse 3大數據庫$ sealos run labring/helm:v3.8.2$ helm install mysql clickhouse-test-1.0.0/helm-charts/mysql/$ helm install influxdb clickhouse-test-1.0.0/helm-charts/influxdb/$ helm install clickhouse clickhouse-test-1.0.0/helm-charts/clickhouse/

數據導入

直接使用 ClickHouse 官方提供的測試數據
https://clickhouse.com/docs/zh/getting-started/example-datasets/opensky，此數據集中的數據是從完整的 OpenSky 數據集中派生和清理而來的，以說明 COVID-19 新冠肺炎大流行期間空中交通的發展情況。它涵蓋了自2019年1月1日以來該網絡超過2500名成員看到的所有航班，總數據量有6600w。

# 在服務器 /home/flightlist 目錄執行以下命令，該目錄會被掛載到 mysql-pod、influxdb-pod、clickhouse-pod 內$ wget -O- https://zenodo.org/record/5092942 | grep -oP 'https://zenodo.org/record/5092942/files/flightlist_d+_d+.csv.gz' | xargs wget# 批量解壓 flightlist.gz 數據$ for file in flightlist_*.csv.gz; do gzip -d "$file"; done# 將 csv 處理成 influxdb 導入所需的 txt 格式（此過程大概耗時1小時）$ Python/ target=_blank class=infotextkey>Python clickhouse-test-1.0.0/influxdb_csv2txt.py

MySQL

# 進入 mysql pod$ kubectl exec -it [influxdb-podname] -- bash# 連上 mysql 建庫、建表$ mysql -uroot -p123456$ use test;$ CREATE TABLE `opensky` (`callsign` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`icao24` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`registration` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`typecode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`origin` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`destination` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`firstseen` datetime DEFAULT NULL,`lastseen` datetime DEFAULT NULL,`day` datetime DEFAULT NULL,`latitude_1` double DEFAULT NULL,`longitude_1` double DEFAULT NULL,`altitude_1` double DEFAULT NULL,`latitude_2` double DEFAULT NULL,`longitude_2` double DEFAULT NULL,`altitude_2` double DEFAULT NULL,KEY `idx_callsign` (`callsign`),KEY `idx_origin` (`origin`),KEY `idx_destination` (`destination`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;# 導入數據（大概耗時70分鐘）$ load data local infile 'flightlist_20190101_20190131.csv' into table opensky character set utf8mb4 fields terminated by ',' lines terminated by 'n' ignore 1 lines;# 省略其他29條導入命令：load data local infile 'flightlist_*_*.csv' into table opensky character set utf8mb4 fields terminated by ',' lines terminated by 'n' ignore 1 lines;# 檢查數據是否導入成功$ select count(*) from test.opensky;

InfluxDB

# 進入 influxdb pod$ kubectl exec -it [influxdb-podname] -- bash# 導入數據（大概耗時30分鐘）$ influx -username 'admin' -password 'admin123456' -import -path=/tmp/flightlist/flightlist_20190101_20190131.txt -precision=ns;# 省略其他29條導入命令：influx -username 'admin' -password 'admin123456' -import -path=/tmp/flightlist/flightlist_*_*.txt -precision=ns;# 檢查數據是否導入成功$ influx -username 'admin' -password 'admin123456'$ select count(latitude_1) from test.autogen.opensky;

ClickHouse

# 進入 clickhouse pod$ kubectl exec -it [clickhouse-podname] -- bash# 連上 clickhouse 建庫、建表$ clickhouse-client$ create database test;$ use test;$ CREATE TABLE opensky(callsign String,number String,icao24 String,registration String,typecode String,origin String,destination String,firstseen DateTime,lastseen DateTime,day DateTime,latitude_1 Float64,longitude_1 Float64,altitude_1 Float64,latitude_2 Float64,longitude_2 Float64,altitude_2 Float64) ENGINE = MergeTree ORDER BY (origin, destination, callsign);$ exit# 導入數據（大概耗時75秒）$ cd /tmp/flightlist$ for file in flightlist_*.csv; do cat "$file" | clickhouse-client --date_time_input_format best_effort --query "INSERT INTO test.opensky FORMAT CSVWithNames"; done# 檢查數據是否導入成功$ clickhouse-client$ SELECT count() FROM test.opensky;

測試場景

MySQL

$ mysql -uroot -p123456$ use test;-- 開啟性能分析set profiling = 1;-- 查詢磁盤空間select table_rows as `總行數`, (data_length + index_length)/1024/1024/1024 as `磁盤占用(G)` from information_schema.`TABLES` where table_name = 'opensky';-- 全表countselect count(latitude_1) from opensky;-- 全表max/minselect max(longitude_1),min(altitude_1) from opensky;-- 全表平均值select avg(latitude_2) from opensky;-- 全表方差select var_pop(longitude_2) from opensky;-- 復雜查詢1：全表多個字段聚合查詢select count(latitude_1),max(longitude_1),min(altitude_1),avg(latitude_2) from opensky;-- 復雜查詢2：從莫斯科三個主要機場起飛的航班數量SELECT origin, count(1) AS c FROM opensky WHERE origin IN ('UUEE', 'UUDD', 'UUWW') GROUP BY origin;-- 輸出分析結果show profiles;

InfluxDB

$ influx -username 'admin' -password 'admin123456'$ use test;-- 耗時統計，queryReqDurationNs 是累計查詢時間，2次任務的時間相減就是耗時select queryReq,queryReqDurationNs/1000/1000,queryRespBytes from _internal."monitor".httpd order by time desc limit 10;-- 查詢磁盤空間select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s group by "database";-- 全表countselect count(latitude_1) from opensky;-- 全表max/minselect max(longitude_1),min(altitude_1) from opensky;-- 全表平均值select mean(latitude_2) from opensky;-- 全表方差select stddev(longitude_2) from opensky;-- 復雜查詢1：全表多個字段聚合查詢select count(latitude_1),max(longitude_1),min(altitude_1),mean(latitude_2) from opensky;-- 復雜查詢2：從莫斯科三個主要機場起飛的航班數量SELECT count(latitude_1) AS c FROM opensky WHERE origin =~/^UUEE|UUDD|UUWW$/ GROUP BY origin;

ClickHouse

$ clickhouse-client$ use test;-- 耗時統計select event_time_microseconds,query_duration_ms,read_rows,result_rows,memory_usage,query from system.query_log where query like '%opensky%' and query_duration_ms <> 0 and query not like '%event_time_microseconds%' order by event_time_microseconds desc limit 5;-- 查詢磁盤空間SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'opensky';-- 全表countselect count(latitude_1) from opensky;-- 全表max/minselect max(longitude_1),min(altitude_1) from opensky;-- 全表平均值select avg(latitude_2) from opensky;-- 全表方差select var_pop(longitude_2) from opensky;-- 復雜查詢1：全表多個字段聚合查詢select count(latitude_1),max(longitude_1),min(altitude_1),avg(latitude_2) from opensky;-- 復雜查詢2：從莫斯科三個主要機場起飛的航班數量SELECT origin, count() AS c FROM opensky WHERE origin IN ('UUEE', 'UUDD', 'UUWW') GROUP BY origin;

ClickHouse 為什么快

1、列式存儲

數據是按列存儲，數據即是索引；查詢只訪問涉及的列，降低系統I/O；每一列都由一個線程來處理，高效利用CPU資源；還為向量化執行做好了鋪墊。

2、數據壓縮

數據壓縮的本質是按照一定的步長對數據進行匹配掃描，當發現重復數據的時候就進行編碼轉換。
因為是列式存儲，所以數據特征很相似，所以數據中的重復項多，則壓縮率越高，則數據體量越小，則磁盤I/O壓力越小，則網絡中傳輸越快。

3、向量化執行引擎

SIMD（Single Instruction Multiple Data）即單條指令操作多條數據，它是通過數據并行以提高性能的一種方式，可以簡單理解為在寄存器層面對程序中的數據做并行處理，Clickhouse 在能夠提升計算效率的地方大量使用了 SIMD，通過使用 SIMD，基本上能帶來幾倍的性能提升。

4、多線程和分布式

分布式領域存在一條定律，計算移動比數據移動更加劃算，這也是其核心所在，將數據的計算直接發放到數據所在的服務器，多機并行處理，再把最終的結果匯集在一起；另外 ClickHouse 也通過線程級別并行的方式為效率進一步提速，極致去利用服務器的資源。

5、多樣的表引擎

MergeTree 存儲結構對寫入的數據做排序然后進行有序存儲，有序存儲主要有兩大優勢：

• 列存文件在按塊做壓縮時，排序鍵中的列值是連續或者重復的，使得列存塊的數據可以獲得極致的壓縮比；
• 存儲有序本身可以加速查詢的索引結構，根據排序鍵中列的等值條件或者 rang 條件，我們可以快速找到目標所在的近似位置區間，并且這種索引結構是不會產生額外的存儲開銷。

MergeTree 是 ClickHouse 表引擎中最核心的引擎，其他引擎均以 MergeTree 引擎為基礎，并在數據合并過程中實現了不同的特性，從而構成了 MergeTree 表引擎家族。

ClickHouse 的優缺點

優點：極致的查詢分析性能，較低的存儲成本，高吞吐的數據寫入，多樣化的表引擎，完備的 DBMS 功能；
缺點：不支持事務，不支持真正的刪除/更新，分布式能力較弱；不支持高并發，官方建議 QPS 為100；非標準的 SQL，join 的實現比較特殊，且性能不好；頻繁小批量數據操作會影響查詢性能；
目前還沒有一個 OLAP 引擎能夠滿足各種場景的需求，其本質原因是，沒有一個系統能同時在查詢效率、時效性、可維護性三個方面做到完美，只能說 ClickHouse 是為了極致查詢性能做了一些取舍。
ClickHouse 優缺點都很明顯，是否采用還是要取決于和實際業務場景的契合度，適合自己的架構才是最好架構。

參考引用

• 識堂 | 筆記分享討論社區，讓知識說話
• InfluxDB優化配置項_sqtce的技術博客_51CTO博客
• influxDB系列（二）--查看數據庫的大小 - 立志做一個好的程序員 - 博客園
• Clickhouse技術分享_大數據_scalad_InfoQ寫作社區