场景分析

业务在数据层的表现

业务大多数是读请求，存储宽表，无大字段，较少的并发(单台100-200qps左右)。
数据批写入（1000条以上，线上业务建议5w-10w），不修改或少修改已添加的数据。
无事务要求，对数据一致性要求低。
对于简单查询，允许延迟大约50毫秒，每一个查询除了一个大表外都很小。
处理单个查询时需要高吞吐量（每个服务器每秒高达数十亿行）。

具体业务场景

用户行为分析，精细化运营分析：日活，留存率分析，路径分析，有序漏斗转化率分析，Session分析等。
实时日志分析，监控分析。
实时数仓。

业务数据的大宽表

订单、商品、用户的大宽表

是单表还是多个表进行关联。
在mysql中产生变更后进行大宽表的更新效率问题。
检索效率。

方案

不分区且表,使用批量写入+定时写入

分析

订单数据量为百万级别，订单作为基本关联键，需要关联商品，用户，业绩，线索，任务等相关的数据。表字段千个。

存储

clickhouse支持array和nested数据还有map类型，所以关联关系是支持。
还会使用lz4压缩数据，所以存储应该在合理范围。

查询

clickhouse设计原生是列存储，所以没有行扫描的问题，查询速度慢不了。为array和nested数据还有map类型数据提供了大量函数。

clickhouse统计有巨大的优势

更新

单点数据更新不是强项，需要合并更新。如果需要频繁的单点更新，则会对服务带来性能问题。如果使用批量+定时更新，那么带来的问题就是数据延迟问题。

问题

clickhouse不支持事务，无法保证强一致性。
数据更新延迟。

商标全量数据

商标检索分词。
商标统计分析，聚合统计。
高频写入或更新。

方案

不分区且批量写入

分析

订单数据量为三亿级别，商标名称查询是主要查询条件。是批量更新，不会有单点更新的情况。

存储

clickhouse支持array和nested数据还有map类型，所以关联关系是支持。
还会使用lz4压缩数据，所以存储应该在合理范围。

查询

由于是列存储，所以没有行扫描的问题，且clickhouse设计原生极快，查询速度慢不了。并且支持array和nested数据还有map类型。

clickhouse统计是巨大的优势

更新

单点数据更新不是强项，需要合并更新。如果需要频繁的单点更新，则会对服务带来性能问题。

如果使用批量+定时更新，那么带来的问题就是数据延迟问题。

问题

clickhouse不支持事务，无法保证强一致性。
数据更新延迟。
like搜索不是强项，还需要拿数据测试。
并发不是强项。

实验过程

新建表

SET allow_experimental_object_type = 1;
CREATE TABLE test.trademark
(
    _id String,
    regNumber String,
    term FixedString(2),
    name String,
    type FixedString(2),
    agencyCode LowCardinality(String),
    specialUse String,
    designDescription String,
    colourDescription String,
    waiverOfExclusiveRightsDescription String,
    shape String,
    geography String,
    colour String,
    _noticeInline Nested (
        key LowCardinality(String),
        value String
    ),
    _dateInline Nested (
        key LowCardinality(String),
        value Int64
    ),
    _boolInline Nested (
        key LowCardinality(String),
        value Bool
    ),
    _versionInline JSON
)ENGINE = MergeTree()
ORDER BY (regNumber, name, term);

导入60w条商标数据

使用csv文件导入数据,示例数据: 商标数据

-- 允许错误行 允许错误比例
--set input_format_allow_errors_num=10000
--set input_format_allow_errors_ratio=0.1

INSERT INTO
    test.trademark
SELECT
    _id
    , regNumber
    , term     AS term
    , name
    , type     AS type
    , agencyCode
    , specialUse
    , designDescription
    , colourDescription
    , waiverOfExclusiveRightsDescription
    , shape
    , geography
    , colour
    , arrayMap(x -> JSONExtractString(x, 'key'),JSONExtractArrayRaw(CONCAT('{"d":', _noticeInline, '}'), 'd'))    AS "_noticeInline.key"
    , arrayMap(x -> JSONExtractString(x, 'value'),JSONExtractArrayRaw(CONCAT('{"d":', _noticeInline, '}'), 'd'))    AS "_noticeInline.value"
    , arrayMap(x -> JSONExtractString(x, 'key'),JSONExtractArrayRaw(CONCAT('{"d":', _dateInline, '}'), 'd'))      AS "_dateInline.key"
    , arrayMap(x -> JSONExtractInt(x, 'value'),JSONExtractArrayRaw(CONCAT('{"d":', _dateInline, '}'), 'd'))      AS "_dateInline.value"
    , arrayMap(x -> JSONExtractString(x, 'key'),JSONExtractArrayRaw(CONCAT('{"d":', _boolInline, '}'), 'd'))      AS "_boolInline.key"
    , arrayMap(x -> JSONExtractBool(x, 'value'),JSONExtractArrayRaw(CONCAT('{"d":', _boolInline, '}'), 'd'))      AS "_boolInline.value"
    , map(
        'batch', _versionInline.batch,
        'batchNo', _versionInline.batchNo,
        'filename', _versionInline.filename,
        'source', _versionInline.source,
        'version', _versionInline.version,
        'year', _versionInline.year
    )        AS _versionInline
FROM
    file('trademark.csv', 'CSVWithNames', '
    _boolInline String,
    _dateInline String,
    _id String,
    _noticeInline String,
    "_versionInline.batch" String,
    "_versionInline.batchNo" String,
    "_versionInline.filename" String,
    "_versionInline.source" String,
    "_versionInline.version" String,
    "_versionInline.year" String,
    agencyCode String,
    colour String,
    colourDescription String,
    designDescription String,
    geography String,
    name String,
    regNumber String,
    shape String,
    specialUse String,
    term String,
    type String,
    waiverOfExclusiveRightsDescription String
    ')
WHERE
    LENGTH(type) < 3
AND LENGTH(term) < 3

计算存储量

trademark.csv 的文本大小: 5.9G

-- 数据总量
SELECT
    COUNT()
FROM
  trademark

┌─count()─┐
│ 9837443 │
└─────────┘

-- 数据存储量
SELECT
    formatReadableSize(total_bytes) AS total_bytes
FROM
    system.tables
WHERE
    name = 'trademark'

┌─total_bytes─┐
│ 410.25 MiB  │
└─────────────┘

查询示例

-- eq查询
SELECT
    term
  , name
  , regNumber
FROM
    test.trademark
WHERE
    regNumber = '10427154'
LIMIT 1
-- 1 row in set. Elapsed: 0.003 sec. Processed 32.77 thousand rows, 609.37 KB (10.48 million rows/s., 194.82 MB/s.)

-- like 查询

SELECT
    term
  , name
  , regNumber
FROM
    test.trademark
WHERE
    term = '11'
AND (name LIKE '江%' OR name LIKE '%杉' OR name LIKE '%牛' OR name LIKE '%宝宝')
LIMIT 10
-- 10 rows in set. Elapsed: 0.040 sec. Processed 892.93 thousand rows, 31.65 MB (22.41 million rows/s., 794.15 MB/s.)
-- 意味着扫描了89w行数据

-- 统计
SELECT
    term
  , COUNT() AS count
FROM
  test.trademark
WHERE
  (name LIKE '江%' OR name LIKE '%杉' OR name LIKE '%牛' OR name LIKE '%宝宝')
GROUP BY
  term
LIMIT 10

-- 10 rows in set. Elapsed: 0.131 sec. Processed 9.84 million rows, 202.78 MB (75.11 million rows/s., 1.55 GB/s.)

无论查询还是统计,对比MySQL速度相当快

参考资料

QQ音乐PB级ClickHouse实时数据平台架构演进之路