clickHouse 与mysql等关系型数据库对比

深碍√TFBOYSˉ_ 2022-08-28 13:48 465阅读 0赞

关于clickHouse数据库，它也是一种关系型数据库。但是区别于传统关系型数据库mysql以及Oracle。其中最大的区别就是传统的关系型数据库是**行式存储**，而clickHouse是**列式存储**。请记住这个列式存储方式。这种结构存储方式，具备了一种天然的优势，就是做统计分析，聚类分析。

本身数据库没有绝对的优劣之分。关于clickHouse和mysql的对比，但空间唯独上可以抽象为行（横轴）列（纵轴）。作为数据库的使用者，我们要尽可能的了解它们各自的优势，然后物尽其用，解决我们遇到的场景问题。不要单纯的去说哪种数据库就是最好的，它们只是在自己的维度上是比较好的。

# 关系型数据库的基本定位 #

*  OLTP：是传统的关系型数据库，主要操作增删改查，**强调事务一致性**，比如银行系统、电商系统。常见的数据库：mysql，Oracle等。
 *  OLAP：是仓库型数据库，主要是读取数据，做复杂数据分析，侧重技术决策支持，提供直观简单的结果。常见的列式数据库有： Vertica、 Paraccel (Actian Matrix，Amazon Redshift)、 Sybase IQ、 Exasol、 Infobright、 InfiniDB、 MonetDB (VectorWise， Actian Vector)、 LucidDB、 SAP HANA、 Google Dremel、 Google PowerDrill、 Druid、 kdb+。

# 什么是行式存储什么是列式存储？ #

###  **先用一张图帮助理解两者的本质上的区**别 ###

![053ccf54a649f2e9182930eabc1472cb.png][]

### 官网上是这样解释的 ###

在传统的行式数据库系统中，数据按如下顺序存储：

<table> 
 <thead> 
  <tr> 
   <th>Row</th> 
   <th>WatchID</th> 
   <th>JavaEnable</th> 
   <th>Title</th> 
   <th>GoodEvent</th> 
   <th>EventTime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>#0</td> 
   <td>89354350662</td> 
   <td>1</td> 
   <td>Investor Relations</td> 
   <td>1</td> 
   <td>2016-05-18 05:19:20</td> 
  </tr> 
  <tr> 
   <td>#1</td> 
   <td>90329509958</td> 
   <td>0</td> 
   <td>Contact us</td> 
   <td>1</td> 
   <td>2016-05-18 08:10:20</td> 
  </tr> 
  <tr> 
   <td>#2</td> 
   <td>89953706054</td> 
   <td>1</td> 
   <td>Mission</td> 
   <td>1</td> 
   <td>2016-05-18 07:38:00</td> 
  </tr> 
  <tr> 
   <td>#N</td> 
   <td>…</td> 
   <td>…</td> 
   <td>…</td> 
   <td>…</td> 
   <td>…</td> 
  </tr> 
 </tbody> 
</table>

处于同一行中的数据总是被物理的存储在一起。

常见的行式数据库系统有：`MySQL`、`Postgres`和`MS SQL Server`。

在列式数据库系统中，数据按如下的顺序存储：

<table> 
 <thead> 
  <tr> 
   <th>Row:</th> 
   <th>#0</th> 
   <th>#1</th> 
   <th>#2</th> 
   <th>#N</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>WatchID:</td> 
   <td>89354350662</td> 
   <td>90329509958</td> 
   <td>89953706054</td> 
   <td>…</td> 
  </tr> 
  <tr> 
   <td>JavaEnable:</td> 
   <td>1</td> 
   <td>0</td> 
   <td>1</td> 
   <td>…</td> 
  </tr> 
  <tr> 
   <td>Title:</td> 
   <td>Investor Relations</td> 
   <td>Contact us</td> 
   <td>Mission</td> 
   <td>…</td> 
  </tr> 
  <tr> 
   <td>GoodEvent:</td> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
   <td>…</td> 
  </tr> 
  <tr> 
   <td>EventTime:</td> 
   <td>2016-05-18 05:19:20</td> 
   <td>2016-05-18 08:10:20</td> 
   <td>2016-05-18 07:38:00</td> 
   <td>…</td> 
  </tr> 
 </tbody> 
</table>

这些示例只显示了数据的排列顺序。来自不同列的值被单独存储，来自同一列的数据被存储在一起。

# 对比之下，列式存储clickHouse又有什么优势？ #

**列式数据库更适合OLAP！**

列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)，下面详细解释了原因(通过图片更有利于直观理解)：

**行式**

![Row oriented][]

**列式**

![Column oriented][]

# 列式存储的为什么会有优势？原理是什么？ #

关于这个问题，我们应该从计算机的底层开始说起。

## I/O，列式存储在检索的时候减少了IO ##

1.  针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。
2.  由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。
3.  由于I/O的降低，这将帮助更多的数据被系统缓存。

例如，查询«统计每个广告平台的记录数量»需要读取«广告平台ID»这一列，它在未压缩的情况下需要1个字节进行存储。如果大部分流量不是来自广告平台，那么这一列至少可以以十倍的压缩率被压缩。当采用快速压缩算法，它的解压速度最少在十亿字节(未压缩数据)每秒。换句话说，这个查询可以在单个服务器上以每秒大约几十亿行的速度进行处理。这实际上是当前实现的速度。

## CPU ##

由于执行一个查询需要处理大量的行，因此在整个向量上执行所有操作将比在每一行上执行所有操作更加高效。同时这将有助于实现一个几乎没有调用成本的查询引擎。如果你不这样做，使用任何一个机械硬盘，查询引擎都不可避免的停止CPU进行等待。所以，在数据按列存储并且按列执行是很有意义的。

有两种方法可以做到这一点：

1.  向量引擎：所有的操作都是为向量而不是为单个值编写的。这意味着多个操作之间的不再需要频繁的调用，并且调用的成本基本可以忽略不计。操作代码包含一个优化的内部循环。
2.  代码生成：生成一段代码，包含查询中的所有操作。

这是不应该在一个通用数据库中实现的，因为这在运行简单查询时是没有意义的。但是也有例外，例如，MemSQL使用代码生成来减少处理SQL查询的延迟(只是为了比较，分析型数据库通常需要优化的是吞吐而不是延迟)。

请注意，为了提高CPU效率，查询语言必须是声明型的(SQL或MDX)， 或者至少一个向量(J，K)。 查询应该只包含隐式循环，允许进行优化。

## 算法优化 - 数据压缩 ##

数据压缩，在一定程度上也是为了提高IO，提升吞吐量。

在一些列式数据库管理系统中(例如：InfiniDB CE 和 MonetDB) 并没有使用数据压缩。但是, 若想达到比较优异的性能，数据压缩确实起到了至关重要的作用。

除了在磁盘空间和CPU消耗之间进行不同权衡的高效通用压缩编解码器之外，ClickHouse还提供针对特定类型数据的[专用编解码器][Link 1]，这使得ClickHouse能够与更小的数据库(如时间序列数据库)竞争并超越它们。

想要了解更多数据压缩方面的，可以看官网：[ClickHouse的特性 | ClickHouse文档][ClickHouse_ _ ClickHouse]

# 再了解一下 clickHouse的短板 #

在考虑使用clickHouse的时候一定不能避免它的短板。有一下场景的，要考虑使用其它的数据库。

1.  **不支持事务**。不要把clickHouse直接当做像mysql这样的关系型数据库。clickHouse本身的定位就是用于联机分析(OLAP)的列式数据库管理系统(DBMS)。
2.  关于**实时性**。缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据，但这符合 [GDPR][]。就像elasticsearch搜索引擎，它也是近实时的。如果对实时性有钱强烈要求的，应该避免使用clickHouse，或者通过业务上的设计，合理避免实时性问题。
3.  稀疏索引使得ClickHouse不适合通过其键检索单行的点查询。

参考文章

[查询速度提升200倍，ClickHouse到底有多快？ - 51CTO.COM][200_ClickHouse_ - 51CTO.COM]

clickHouse官方文档：[什么是ClickHouse？ | ClickHouse文档][ClickHouse_ _ ClickHouse 1]

[053ccf54a649f2e9182930eabc1472cb.png]: /images/20220828/1f4fdcc48ec44430b4b65b1e4c66e2be.png
[Row oriented]: /images/20220828/dfccf8ff78ef4816b1b1d3870a114c6c.png
[Column oriented]: /images/20220828/f8d1115b13404f85a38e9883cd30a661.png
[Link 1]: https://clickhouse.tech/docs/zh/sql-reference/statements/create/#create-query-specialized-codecs
[ClickHouse_ _ ClickHouse]: https://clickhouse.tech/docs/zh/introduction/distinctive-features/
[GDPR]: https://gdpr-info.eu/
[200_ClickHouse_ - 51CTO.COM]: https://database.51cto.com/art/202008/624611.htm
[ClickHouse_ _ ClickHouse 1]: https://clickhouse.tech/docs/zh/