首先我们来看看两者的数据类型设计,如下表:
ClickHouse | MySQL | Comment |
UInt8, UInt16, UInt32, UInt64 | TINYINT UNSIGNED, SMALLINT UNSIGNED, INT UNSIGNED, BIGINT UNSIGNED | |
Int8, Int16, Int32, Int64 | TINYINT SIGNED, SMALLINT SIGNED, INT SIGNED, BIGINT SIGNED | |
Float32, Float64 | FLOAT, DOUBLE | Supports inf, -inf, nan, recommended NOT to use by ClickHouse! |
String | BLOB, TEXT, VARCHAR, VARBINARY | No encoding. Recommended to use UTF-8. In fact behaves like a BLOB. |
FixedString(n) | CHAR, BINARY | \0 padded. Less functions available than String, in fact it behaves like BINARY. |
Date | DATE | UNIX epoch date up to 2038. |
DateTime | DATETIME, TIMESTAMP | UNIX epoch timestamp up to 2038. |
Enum | ENUM | Similar to MySQL ENUM. Behaves like Int8/16. |
Array(type) | n.a. | Array of type. Closest equivalent in MySQL is JSON? Not well supported. |
Tuple() | n.a. | |
Nested() | n.a. | Closest equivalent in MySQL is JSON? |
AggregateFunction() | n.a. | |
Set | n.a. | |
Expression | n.a. |
首先来说说MySQL:
特点:
MySQL作为数据库的老大哥,面对海量数据,如果单独使用,往往无法满足千亿级企业的业务场景需求:
•大规模的数据导入会使MySQL读的性能大幅降低,甚至出现锁表的情况•海量数据导入,MySQL的查询性能会大幅降低,页面打开缓慢甚至超时•MySQL的单表存储数据有限,拆表、拆库、迁移数据等方式无法应对海量数据
而ClickHouse和Doris就是这些问题的完美终结者。
ClickHouse:
通过实践应用,ClickHouse完美解决了MySQL的查询瓶颈,20亿行以下的数据量级查询,90%都可以在亚秒(1秒内)给到结果,并深受腾讯、快手、今日头条、携程等一线大厂的青睐。 据携程酒店大数据高级研发经理透漏:ClickHouse可以满足每天十多亿的数据更新和近百万次的数据查询,支撑app性能98.3%在1秒内返回结果,pc端在98.5%在3秒内返回结果,携程酒店智能平台80%以上的业务在19年的时候都已接入ClickHouse。