Clickhouse高级教程：与MySQL的性能比较
#database #mysql #性能 #clickhouse

目录

1. Introduction

。 OLTP

i†OLAP

.3。 MySQL

1.4。 ClickHouse
2. Comparison Case Study

。 System Specification Benchmark Flow

。0。 Queries
3. Results

- EP 3.8。 Dataset Load

- 0。 Table Size - EP 3.10。 abiaooqian14

- EP 3.11。 Update Query Execution
4. Conclusion

介绍

没有什么是完美的。就数据库而言，您不能期望每个任务的最佳性能，也不能从部署的数据库中查询。但是，作为软件开发人员的重要一步是要知道他们的优势和劣势以及如何处理它们。

在这篇文章中，我将比较Clickhouse作为OLAP数据库和OLTP的MySQL的代表。这将帮助我们根据条件和欲望选择更好的解决方案来应对我们的挑战。在跳入主要环境之前，让我们讨论OLTP，OLAP，MySQL和Clickhouse。

OLTP

OLTP代表在线交易处理，用于日常操作，例如处理订单和更新客户信息。 OLTP最适合简短，快速交易，并且可以优化用于快速响应时间。必须确保数据的准确性和一致性并提供有效的方法来访问数据。

OLAP

OLAP代表在线分析处理，用于数据挖掘和分析。它使组织能够从多个角度分析大量数据并确定趋势和模式。 OLAP最适合复杂的查询和数据挖掘，可以通过传统的报告工具提供不可能的见解。

mysql

MySQL是一种流行的开源数据库管理系统。它用于存储和管理数据，并被网站和应用程序用于存储和管理信息。 MySQL是一个关系数据库管理系统，可将数据保存在表中，并允许用户查询数据。它还提供触发器，存储过程和视图之类的功能。 MySQL易于使用，并且具有广泛的功能，可用于创建功能强大，高效的应用程序。

Clickhouse

ClickHouse是由Yandex开发的一个开源列的数据库管理系统。它旨在为分析查询提供高性能。
ClickHouse使用类似SQL的查询语言来查询数据并支持不同的数据类型，包括整数，字符串，日期和浮点。它提供了各种功能，例如聚类，分布式查询处理和容错。它还支持复制和数据碎片。您可以通过访问本系列的第一部分了解有关此数据库的更多信息：

现在我们可以谈论性能比较。

比较案例研究

我遵循了案例研究的Clickbench存储库方法。它使用了从世界上最大的Web Analytics平台之一的实际流量记录获得的 hits 数据集。 hits作为一个单平台包含约100m行。该存储库研究了20多个有关数据集加载时间的数据库，经过43个OLAP查询的时间以及占用的存储时间。您可以访问他们可视化的结果here。

GitHub logo ClickHouse / ClickBench

ClickBench：分析数据库的基准

ClickBench: a Benchmark For Analytical Databases

https://benchmark.clickhouse.com/

讨论：https://news.ycombinator.com/item?id=32084571

概述

此基准标准在以下领域中表示典型的工作负载：ClickStream和流量分析，Web分析，机器生成的数据，结构化日志和事件数据。它涵盖了临时分析和实时仪表板中的典型查询。

该基准测试的数据集是从世界上最大的Web分析平台之一的实际流量记录中获得的。它是匿名的，同时保留数据的所有基本分布。查询集合以反映现实的工作负载，而查询并非直接来自生产。

目标

该基准的主要目标是：

可重复性

您可以在短短20分钟内快速重现每项测试（尽管有些系统可能需要几个小时），以半自动化的方式重现。记录了测试设置并使用廉价的云VM。测试过程以Shell脚本的形式进行了记录，涵盖了

View on GitHub

专门研究Clickhouse和MySQL性能，我将桌子的10m行分开，并选择了一些预定义的queries，这可以使我们的观点更加清楚。这些查询主要是以OLAP方式进行的，因此与MySQL相比，它们仅显示Clickhouse的优势（即MySQL在所有这些查询中都丢失了）。因此，我添加了其他查询，显示了相反的查询（OLTP查询）。尽管我将基准限制在这两个数据库中，但您可以将概念推广到其他面向行的和面向列的DBMS。

。

免责声明：此基准仅阐明有关其性能和用例的面向列和面向行的数据库之间的主要区别。不应将其视为您的用例的参考。因此，您应该通过查询执行基准来实现最佳决定。

系统规范

数据库安装在ubuntu 22.04 lts上的系统上，其规格：

  • cpu：intel®Coreâ¢i7-10510u cpu @1.80GHzâ8
  • RAM：16 GIB
  • 存储：256 GIB SSD

基准流

  1. 创建数据库。
  2. 该表是用defined DDL创建的。
  3. 数据（hits.tsv）被加载到表中，并测量其时间。
  4. 进行查询，并测量每个查询的经过的时间。

查询

查询号 语句 类型
1 SELECT COUNT(*) FROM hits; olap
2 SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits; olap
3 SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10; olap
4 SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10; olap
5 SELECT EventTime, WatchID FROM hits WHERE CounterID = 38 AND EventDate = '2013-07-15' AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239'; OLTP
6 SELECT Title, URL, Referer FROM hits WHERE CounterID = 38 AND EventDate = '2013-07-15' AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239'; OLTP
7 UPDATE hits SET Title='my title', URL='my url', Referer='my referer' WHERE CounterID = 38 AND EventDate = '2013-07-15' AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239'; OLTP

结果

我将研究四类以下的结果：

  • 数据集负载
  • 表尺寸
  • 阅读查询执行
  • 更新查询执行：我已经分别讨论了更新查询（查询编号7），因为它需要更多的讨论和关注。

数据集加载

clickhouse mysql 比率
65s 11M35S x10.7

感谢LSM和稀疏索引，ClickHouse加载时间比使用BTREE的MySQL快得多。但是，ClickHouse插入效率可以在批量插入物中观察到，而不是许多单独的插入物。这种行为来自以下事实：它为每个插入物创建不变的零件，并且不愿意更改，删除或创建其数据。

桌子大小

clickhouse（gib） mysql（gib） 比率
1.3 6.32 x4.86

以列为导向的结构赋予了Data Compression的能力，这在面向行的数据库中无法使用。这就是为什么Clickhouse可以对存储大量数据的团队有用，从而降低存储成本。

读取查询执行

查询号 clickhouse（s） mysql（s） 比率
1 0.005 7.79 x1558
2 0.030 16.0 x533.3
3 0.193 4.35 x22.5
4 2.600 180.93 x69.58
5 0.01 0.00 x0
6 0.011 0.00 x0

Clickhouse的sparse index和面向列的结构在所有OLAP查询中的表现都优于MySQL（数字1至4）。这就是为什么BI和数据分析师对Clickhouse的日常报告感到非常满意的原因。

但是，MySQL在OLTP查询方面赢得了战斗（数字5和6）。 BTREE（由MySQL配备）确实在您要求短的交易需要几行的尖头查询中表现更好。

更新查询执行

对于更新查询（数字7），我们应该在Clickhouse中执行其他查询，因为它不以幼稚的方式支持更新，并且必须使用Alter命令：

ALTER TABLE hits UPDATE JavaEnable=0 WHERE CounterID = 38 AND EventDate = '2013-07-15' AND UserID = '1387668437822950552' AND WatchID = '8899477221003616239';

此外，Clickhouse异步应用更新。要立即获得结果，您可以执行optimize命令：

OPTIMIZE TABLE hits FINAL;

通过执行queries table中显示的MySQL中显示的查询编号7语句和Clickhouse的两个SQL语句，我们实现了以下结果：

查询号 clickhouse（s） mysql（s） 比率
7 26 0.00 0

再次，与MySQL相比，ClickHouse突变仇恨使其成为实时更新（并类似地删除）的失败者。因此，可以使用其他方法（例如使用替换格式的重复数据删除）来处理更新。您可以在以下链接中找到宝贵的资源：

结论

在这篇文章中，我对MySQL和Clickhouse数据库进行了基准测试，以研究他们的一些优势和劣势，这些优点和缺点可能有助于我们选择合适的解决方案。总结：

  • MySQL在尖锐和OLTP查询上表现更好。
  • Clickhoues在OLAP查询上的表现更好。
  • Clickhouse不是为频繁更新和删除而设计的。您必须使用重复数据删除方法来处理它们。
  • Clickhouse凭借其面向列的结构降低了存储成本。
  • clickhouse批量插入加载时间比mysql好得多。