CREATE TABLE `human_name` (
  `id` bigint(20) NOT NULL COMMENT 'ID',
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人名信息表';

（1）通过id查询name

行存储在索引上节约时间；列存储在磁盘IO上节约时间，数据量较小可以忽略差异，本回合二者持平。

（2）通过批id查询name

批查询是指有限区间查询或者有限集合查询，数据量百条以内。有限区间查询与有限集合查询，对应的数据量较小，性能表现差别不大。仔细分析过程，二者仍然存在明显的差异。

区间查询的效率比有限集合查询效率要高，原因如下：区间查询数据存储是连续的，单次数据索引，单页磁盘IO（数据量较小），紧凑的数据查询，按行存储略占优势，考虑到是查询单个字段，因此磁盘数据索引次数均为一次（按列查询多少列即索引多少次）。

集合查询由于查询条件非连续，需要单独索引并完成磁盘IO，集合中有N个元素（随机）需要索引N次，以页为单位的磁盘IO

（3）通过id查询整行数据

按列存储通常比按行存储的查询效率要高，对于宽表（几十列以上的聚合表），效果更加明显。对于查询，更多的需求是查询某列数据或者某几列数据，按列存储的数据库能够大大减少磁盘数据的扫描范围以及磁盘与内存之间的IO，从IO层面提高了查询效率。

极端情况

数据库存储id和name数据，两者都是非空的必选数据，这种情况下按行（列）存储从IO层面来讲是相似的，数据在磁盘上扫描范围和读写IO差不多。通过id查询name或者批量id查询name，借助于哈希索引，按行存储可能具有O(1)的时间复杂度。

实际数据不可能这么纯粹，行记录通常会有保存时间、修改时间、删除时间、部分核心字段的修改时间，数据量较少时，附属字段对查询的影响较小，一旦数据量超过一定阀值，对查询的影响逐步凸显。按列存储能够忽略附属字段的磁盘扫描与IO。

综合来讲，从查询的角度来讲，按列存储要优于按行存储。

三、基础知识

（一）表结构

clickhouse使用的表结构与常见的关系数据库有一定的区别。

1、排序

在合并树家族引擎中，表排序属性是必选项。通过ORDER BY关键字设置分区内数据的排序策略，数据在导入或者保存时按照排序策略有序存储，有序数据直接存储在磁盘中，查询时具有较高的效率。

排序列也是索引列，高频用作查询条件的字段添加到排序列有利于提高查询效率。

2、主键

主键的定义比较奇怪，仅仅是起到过滤查询索引的作用，没有唯一约束的效果。

当设置有主键时，主键字段必需包含在排序属性中，且从左到右依次展开。

3、默认值

Null类型几乎总是会拖累性能，原因如下：空值无法被索引；需要使用额外的特殊占位符单独处理。按列存储每列数据个数一致有利于数据查询。

数据在导入之前需要做空值处理，将空值替换成与业务无关的数据。

（二）表引擎

clickhouse表引擎非常丰富，其中最常用的是合并树家族引擎。

1、MergeTree

MergeTree引擎能够实现较大数据量的查询需求，由于主键没有唯一索引约束，存在重复行的情况。在数据迁移的过程中，不可避免会出现重复数据导入的情况，业务上能够容忍部分重复数据，或者从应用端处理重复数据，可以选择此引擎。

CREATE TABLE test_tbl (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
   PARTITION BY toYYYYMMDD(create_time)
     ORDER BY  (create_time)
     PRIMARY KEY (id)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;

MergeTree引擎必需指定排序字段。

MergeTree的主键的作用是加速查询，不是类似MySQL保持记录唯一。

2、ReplacingMergeTree

ReplacingMergeTree引擎用来去除重复行，此处的去重有三个层次的含义：在分区内去重；以主键字段为比较对象；数据去重实践只会在合并时发生。

-- 强制后台合并，去重时所在表停止服务
optimize table test_tbl_replacing final;

ReplacingMergeTree提供了主键去重的能力，但是仍旧有以下限制：

optimize是后台动作，无法预测具体执行时间点；

在没有彻底optimize之前，不能确定是否仍有重复数据；

手动执行optimize在海量数据场景下要消耗大量时间，无法满足业务即时查询的需求；

在分布式场景下，相同primary key的数据可能被sharding到不同节点上，不同shard间可能无法去重；

ReplacingMergeTree更多用于确保数据最终被去重，无法保证查询过程中主键不重复。

ReplacingMergeTree(create_time)填入参数为版本字段，重复记录保留版本号最大最在行；允许为空，默认保留重复行最后插入的记录。

去重深刻理解

这里的去重并不能达到关系型数据库严格意义去重的目的，使用时需要注意这个现象。另外不能以非黑即白的想法考虑这个问题，ClickHouse在提高查询速度时做了一定的妥协。

3、SummingMergeTree

SummingMergeTree提供的是一种预聚合引擎，等效为以order by字段为单位分组，然后执行聚合求和操作，不过这些结果是提前计算好了的，查询时不需要实时计算。

如果聚合的值不满足要求，可以在查询结果集上通过聚合函数再次聚合，此时属于实时计算。

（三）内置函数

常见的内置函数需要特别指出，新建表模式、数据导入等方面会有应用。

1、格式化日期

格式化分区函数常用于表的分区设置，以天为单位的分区是常见的分区设置。

select toYYYYMMDD(now())

 

2、哈希函数

以name字段的哈希字符串作为分区策略。

CREATE TABLE default.test02 (
`id` UInt16,
`name` String,
`create_time` Date) ENGINE = MergeTree() 
PARTITION BY LOWER(hex(MD5(name))) 
PRIMARY KEY id
ORDER BY (id,create_time);

表可以不设置主键，一旦设置主键，那么表必选排序属性必需以主键的顺序依次展开。

直接用原始字符串字段值作为分区策略也是可行的，考虑到字符串的值域范围比较广，用哈希函数处理会比较安全。

3、日期函数

获取各种日期函数，如果不指定时区，默认读取宿主机的时区信息。

SELECT toDateTime(now()) AS t1,
       toDate(now()) AS t2,
       toDate(now(), 'Asia/Shanghai') AS t3,
       toString(now()) AS t4

rpm -ivh clickhouse-server-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-client-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-common-static-20.8.19.4-2.x86_64.rpm

# 格式化XML文件
xmllint --format config.xml