sql decimal转换timestamp sql decimal转varchar

转载

码海航行侠 2024-05-03 06:26:45

文章标签 sql decimal 转string sql null值替换0 sql去掉小数点后面的0 sql 数据 文章分类 架构后端开发

sql decimal转换timestamp sql decimal转varchar_sql decimal 转string

最近打算花时间看下 cockroachdb(这个名字太长后面全部用 cdb 代替, calcite 还会抽空继续~)，对 cdb 我还是一无所知，所以先从看文档(v19.1.2)开始，边看边 mark 下，然后就可以开始看代码~

首先 cdb 文档很全，我们从这个 https://www.cockroachlabs.com/docs/stable/architecture/overview.html 作为索引看下他的几个主要处理 layer (从上而下):

SQL: Translate client SQL queries to KV operations.
Transactional: Allow atomic changes to multiple KV entries.
Distribution: Present replicated KV ranges as a single entity.
Replication: Consistently and synchronously replicate KV ranges across many nodes. This layer also enables consistent reads via leases.
Storage: Write and read KV data on disk.

对于个人目前主要关注前 3 个，在开始看代码前先看下这个三个的文档~

SQL Layer

https://www.cockroachlabs.com/docs/stable/architecture/sql-layer.html#postgresql-wire-protocol

SQL 层主要负责暴露 SQL 接口并将 SQL 转换为 kv 操作发送给 Transaction Layer。

通过兼容 PostgreSQL 的协议处理网络请求和响应
根据类 yacc 的 parser 解析 SQL 为 AST
生成对应逻辑和物理计划并优化
执行物理计划, cdb 会将物理计划分发到一个或多个 node，节点会 spawns logical processor 来完成部分查询的计算，logical processor 之间通过 logical flow 来交互
请求下层 layer 或 logic processor 之间交互都需要数据的 encode/decode
在执行时 cdb 通过一个叫 DistSQL 的机制来处理 sql 分布式执行的问题

这部分文档没啥太多可以说的，比较有意思的其中三个 link，我们来看下

1) Costed-based Optimizer

https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html

在 cdb 新版中对于支持的语句会尝试用 cbo 进行优化(看上去和前面专栏文章介绍的 calcite volcano 类似，后面看代码再来详解)，为了 cbo 需要收集统计信息，优化器会做 plan cache，会在 cbo 中计算join reorder, 优化器会考虑 nearest index(看上去有意思), cbo 可以被关闭...

恩- - 这个文档其实没说细节了解下等后面看代码的时候我们再来细看...逃~

2) Encoding

https://github.com/cockroachdb/cockroach/blob/master/docs/tech-notes/encoding.md

数据最终需要保存到 kv 中，在 sql 层中需要将数据进行 encoding 成 kv 的格式，cdb 自己是替换过 encoding 的我们直接看下当前的 endoding。

Primary Index:

对于主键索引 cdb 会将主键值保存在 key ，其他数据保存在 value 中，来避免主键重复。

主键 key 由几个 fields 组成:

table-id
primary-index-id
primary-key-fields
column-family-id

会对每个 field 分别进行 prefix-free 的 encode， encode 时会添加 field type，对于主键需要保证 order 属性。

主键 value 则由：

kv pair 的 checksum(4 bytes)
value type
column-family 中指定列排除 primary 列后的其他列的值

组成, 一般 value type 是 TUPLE, 所以 value 会先根据 column-id 排序，然后 column-id-difference, column-value 加 1 byte 组合起来。

PS: 对于 sequence 的 value 不会按照 value 这个编码，为了方便直接使用 kv 的 inc 操作.

column-family-id = 0 的 cf 是会包含 primary key 的 cf，他对应的 kv pairs 被叫做 Sentinel KV pairs，不管有没有其他数据一定会有 Sentinel KV pairs 。

PS: 在 cdb 历史上的 v1 中没有 column-family 对于每个 column 都会搞一个 kv 出来(虽然有 rockdb 前缀压缩但 mvcc/checksum/write intent 一加上会有开销)，在发现性能问题后 cdb 引入了 column-families, 可以在建表的时候指定某几列属于某 cf 将多个列打包成一个 kv entry， cdb 在 create table 的时会根据规则尝试帮助划分 column families，用户自己建表的时候可以可以自己指定, cf 对性能有影响(可以做到更新小 column 不用读写旁边的大 column，另外看到大神有 pr 希望通过合理设计 cf 来减少 tpcc 冲突)。

如果 column family 中所有列的值都是 NULL，对应的 kv pair 可能被 suppressed。

对于 string 和 decimal 会做 composite encode 会同时放到 key 和 value 中。

Secondary Index:

cdb 的 secondary index 通过用户指定 indexed columns(索引中决定排序的部分) 和 stored columns(有点像 cluster index 减少回表) 来创建, 另外 secondary 也可以选择是否是 unique 的。

二级索引对应的 key 由:

table-id
index-id
"indexed columns" 部分的 columns 值
如果是非唯一或值是 null(唯一索引里可以有多个 null)，则这部分是 primary key 中去掉 "indexed columns" 重叠部分的值
如果是非唯一或值是 null，再把 "stored columns" 部分的值用 old format 附加在后面
用 0 站住"看上去应该是 column-family" 的位置

而二级索引对应的 value 则由:

唯一索引则放置 primary key 中去掉 "indexed columns" 重叠部分的值
唯一缩影则放置 "stored columns" 部分的值用 old format 附加在后面
如果需要不管是否唯一可能放置 TUPLE 编码的 non-null composite 和 new format 的stored column 值

最后 cdb 支持 Interleaving，可以将一个 index Interleave 到另一个 index 里(这两个 index 可以是不同表也可以是一个表中)，将有父子关系的数据放到一块(spanner paper 里好像有说过类似的)来提升关联查询效率，所以编码的时候会将 child 的 key 之前加上 parent 的 key。

Encoding 的文档中有很多例子推荐看下~ 不过可以看到 cdb 的 encoding 中 storing index, colum-family 和 interleaving 不错, 在应该在某些场景下会发挥作用。

3) DistSQL

https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20160421_distributed_sql.md

cdb 没有像其他一些数据库那样做计算和存储进程分离，每个 binary 都是一个即能运算又能存储的 node，客户端可以将 sql 发送到任意一个 node， sql 最初抵达的 node 会作为这个 sql 的 gateway node 负责这条 sql 的处理，处理可能需要向其他 node 读取或写入数据，因为 cdb 数据是通过 k/v range 的方式分布在不同节点 node 中(一个 node 能存所有那就不需要分布式数据库了- -)，所以最简单的实现是 gateway node 收到 sql 后向其他节点把相关数据都要过来，然后在 gateway 完成计算，如果是 update 还需要把计算结果写到存的 node 中。

DistSQL 的目的是优化这种每次都读取数据集中计算再回写的模式

将 filter 推到目标数据所在 node, 来让 filter 在多个 node 并行处理, 并减少传输数据量
让 update 和 delete 在数据所在 node 处理(update x = x + 1 不需要读回 +1 再写回)
分布式执行算子: Distributed joins， Distributed aggregation， Distributed sorting

cdb 的 distsql 自称启发于 Rob Pike 的 Sawzall 但:

预先定义了一组可配置但不可编程的 aggregators
定义了一个特殊的 aggregator -- evaluator 使用处理简单语言但每次只能 open 一行
在 query pipeline 中路由一个 aggregator 结果到下一个 aggregator
将 sql 转换为 data-location-agnostic 的逻辑模型, 但提供足够之后进行分布式执行的信息

3. 1) Logical Plan:

cdb 的 logical plan 的每个节点都叫做 aggregator， aggregator 可能会有 group，可以简单理解为 group by，一般分组越多越可以对每个分组并行分布计算(猜测因为突出 group 才把 logical 里都叫 aggregator 的吧), 对于只有一个分组(即对所有 column 做 group)的 aggregator 则只能在一个节点计算, 另外是没有 group 的 aggregator 则可以任意并发.

aggregator 可以从其他多个 node 获取输入，也可能会作为其他 aggregator 的输入，另外有些特殊的 aggregator 会用来 batch 结果并对 kv 进行读取或更新。

aggregator 需要考虑 ordering requirement 和 guarantee 这个和 calcite 的 require collation trait 类似，根据文档的说法是要: 保持 ordering, 添加必要的 sort, 消除无用 order

下面看几个 logical plan 的 aggregator:

evaluator aggregator 用于一次对一行进行处理，可以产生新数据(e.g. a + 1) 或过滤掉行, 有些像 calcite 中的 LogicalCal同时完成 project 和 filter
table-reader
join aggregator用于 join 2 个 inputstream，根据满足匹配条件的行进行 group
join reader: 用于将其中一个 input 去 point-lookup 另一个 input，可能会查 kv 也可能会创建新的 remote flows
muate: 用来做 insertions/deletions/updates
set operation: 做 union/difference 等集合操作
function aggregator: sum, count, count distinct, distinct , 根据 group 做聚合, 另外有可选的 output filter(可以理解就是 having)
sort: 根据指定的 columns 做排序，cdb 中的 sort 可以被 distributed，只保证局部 order，全局 order 靠后面要说的 final 和 limit
limit: 读指定个后停止
intent-collector: 在 gateway node 的 single-group aggregator , 收集并跟踪 mutate
final: 在 gateway node 收集所有结果并通过绑定的 pgwire 给客户端返回结果

3.2) Physical Plan:

最终通过 aggregators 和 logical streams 描述的 logical plan 会转换成 physical plan，主要基于这几个 facts:

每个 group 都可以独立进行并行计算，直到只有一个 group
logical plan 中已经保证的 order，在生成 physical 时继续遵循 order
对于 limit,final 这样 group 所有 column 的节点需要在单节点计算

所以可以通过这样的规则进行 distribute:

对 table-reader 根据 range 拆分为多个 instance，每个 instance 在负责对应 range 的 leader node 进行并行计算, 并作为物理 stream 的起点
stream 继续保持并行直到遇到 aggregator, 如果有 group 则根据 key 做 hash 进行 redistribute 到不同节点, 如果是 group all 则在一个节点进行保 order 的合并
sort aggregate 不会合并数据，只是保证局部排序

所以最后逻辑计划

TABLE-READER src
  Table: Orders
  Table schema: Oid:INT, Cid:INT, Value:DECIMAL, Date:DATE
  Output filter: (Date > 2015)
  Output schema: Cid:INT, Value:DECIMAL
  Ordering guarantee: Oid

AGGREGATOR summer
  Input schema: Cid:INT, Value:DECIMAL
  Output schema: Cid:INT, ValueSum:DECIMAL
  Group Key: Cid
  Ordering characterization: if input ordered by Cid, output ordered by Cid

EVALUATOR sortval
  Input schema: Cid:INT, ValueSum:DECIMAL
  Output schema: SortVal:DECIMAL, Cid:INT, ValueSum:DECIMAL
  Ordering characterization: if input ordered by [Cid,]ValueSum[,Cid], output ordered by [Cid,]-ValueSum[,Cid]
  SQL Expressions: E(x:INT) INT = (1 - x)
  Code {
    EMIT E(ValueSum), CId, ValueSum
  }

可能生成这样的物理计划:

sql decimal转换timestamp sql decimal转varchar_数据_02

每个框是一个 processor

3.3) Processors:

processors 是在一个 node 上执行物理计划的每个 processor 由三部分组成:

input synchronizer: 将多个输入 stream 合并成一个, 可选 single-input, unsynchronized, ordered
data processor: 具体进行数据转换和处理的逻辑
output router: 将输出拆分为多个 stream: 可选 single-output, mirror, hashing, by range

3.4) Join:

首先是 join-reader，在 cdb 中查询索引后回表的操作是通过 join-reader 来进行，如果 index 和表不是一个就可以完成 index-lookup-join 的效果， join-reader 可以实现 plumbing 输入到输出的效果(可能可以减少 decode 索引中已经 decode 过的列), 并提到 join-reader 可以做 2 个改进: 1. 做 batch lookup 2. 父亲在 output router 中做 by range 将 join-reader 放到合适的 node 上，并讨论了一些实现上细节问题。

然后就是 join-stream, 主要是在输入的 output router 中做 hash 或 mirror 来做分布式 join

3.5) Inter-stream ordering

文档还提到一个优化点，除了 order 外 logical plan 还会维护一个叫 inter-stream ordering 的 trait，主要解决 2 个 node 分别负责 [1, 10), [10, 20), 如果 final 有 order by 或者是 limit，可以在读完 node1 之后再去读 node2 来避免同时读取的排序或尽早满足 limit 快速结束(避免那种加了 limit 还扫几千行的问题- -)。

4）执行的基础设施

cdb 在生成 physical plan 之后进行分发计划到 node 上执行，各个节点自己本地调度执行，并和其他节点交互进行 output routers 和 input synchronizers 逻辑，除了开始 gateway 会发送 plan 外后续执行节点不需要其他更多和 gateway 的协调交互。

4.1) ScheduleFlow

gateway node 在开始执行时向所有 node 发送 ScheduleFlow 请求告诉节点自己负责的 sub-plan，一个 node 可能负责在一个 DAG 中的多个小部分，将他们每个叫做 flow， flow 包括一系列 physical plan node 和他们之间的 connection 和他们输入输出，因为一个 node 可能会负责多个 ranges，所以一个 node 会负责多个 flows。

所以 ScheduleFlow RPC 就是用于发送一组 flow 到节点，让节点准备好输入输出 mailbox 并建立好 local processor 并开始执行。

4.2) flow 的本地调度

看文档是说 data processor, synchronizer 和 router 都起 goroutine 并用 channel 协调，具体后面要看代码～

4.3) Mailbox

为了 processor 处理可以早于建立数据传输 GRPC stream，在 ScheduleFlow 的时候会准备好 Mailbox，等 consumer 发送 StreamMailbox 后才开始建立 stream 实际发送(当然 StreamMailbox 可能比 ScheduleFlow 早, 另外代码看 StreamMailbox 已经搜不到 orz)

Transactional Layer

https://www.cockroachlabs.com/docs/stable/architecture/transaction-layer.html

sql 层下面是的 transaction layer，用于支持 ACID 事务, cdb 通过 2PC 来实现 transaction

Overview:

Writes and read(Phase 1)

Write: 事务中写操作不会直接写入磁盘而是会先保存:

Transaction Record: 记录在被写的第一个 range 中会保存当前事务的状态(pennding, committed, aborted)
Write intents: 写入的数据会作为 intents 临时保存格式和正常的 mvcc values 一样但会通过一个 pointer 指向 transaction record

在创建 write intents 后会检查是否有新被提交的值，如果有则重启事务，write intent 必须进行 transaction conflict 处理(后面会说)，其他原因比如 sql 约束不通过等则会 abort 事务。

Read: 事务没被 abort，读操作如果读取的是正常 mvcc 记录则一切正常，如果读遇到 write intent 则也需要进行 transaction conflict 处理。

Commit(Phase 2)

cdb 检查事务的 transaction record，如果有 abort 的则 restart 事务。如果检查通过则改变 transaction record 为 commited, 并返回客户端提交成功继续接收这个客户端的其他请求.

Cleanup(asynchronous Phase3)

事务已经 resolved 后需要将 write intent 也 resolve，也就是将 write intent 保存到 mvcc，并清除 write intent，这个过程可以也可以通过后续读到 write intent 的请求来完成清理。

transaction layer 接收 sql layer 发来的 kv operation，并控制向下层 distribution layer 的发送流程。

Components

Time and HLC

cdb 依赖于一定大于等于 wall clock 的 HLC 来帮助实现 ordering and causality，每个事务会通过分配 HLC 作为 timestamp, 后续 mvcc 值和 isolation 都依赖这个 timestamp。node 向其他 node 发送请求中会带上自己本地 HLC，接收方会用 sender 发过来的 HCL 通知自己本地 HLC。当节点发现时间未同步(at half of the other nodes in the cluster by 80% of the maximum offset allowed (500ms by default)) 则节点会自己 crash 来防止更严重的 stale reads 和 write skews 问题，所以需要通过 NTP 或其他时间同步服务保证时间同步不出现类似问题。

Timestamp cache

cdb 保证提供 serializability，所以当有读一个值时会将操作的 timestamp 保存到 timestamp cache 中，从而表示 "values 被读取过的 high 水位线"。

当写操作会把自己的 timestmap 和 timestamp cache 比较，如果自己的早于 cache 的表面要写入修改的值，已经有其他事务读取过老值且其他事务的 timestamp 更新，这时需要将当前操作的 timestamp 尝试进行 push，如果 push 不成功则需要重试这个事务(push 为了减少重试, 后面会说)

client.Txn and TxnCoordSender

cdb 的 kv layer 提供事务接口是 client.Txn , 但 tx 层在使用的时候会包一层 TxnCoordSender 用来:

异步发送心跳到 transaction record，当心跳停止时 transaction record 会被 abort(这里还有种做法是通过超时来检测 transaction 发起者自己挂了，但 cdb 通过心跳的方式看上去可以减少不必要等超时 ?)
跟踪事务中的 written key 和 key range
当事务 committed 或 aborted 后清理 write intent，他会记录所有 write intent 并尝试做清理，这样可以减少其他请求碰到 write intent 再去做清理的开销

这些都搞定后会通过 DistSender 向 distribution layer 发请求。

latch manger

对于写操作操作一个 range 时， lease holder 会通过 latch 来 serializes 写操作，其他操作需要等持有 latch 的释放后才能操作对应 key 数据, 而持 latch 后的操作可以被 uncontested 的访问对应数据。

transaction record

前面已经介绍过 transaction record， TxnCoordSender 保证他始终会被创建在 tx 写入的第一个 key 的 range 中, 但 cdb 做了一个优化 transaction record 会被延迟写入只有当:

写操作提交的时候
第一次收到 heartbeat 的时候
事务被强制 abort 的时候

才回去创建 transaction record，所以除了前面说的三个状态还可能看到 record 不存在，这时需要看 write intent 如果在 liveness threshold 则算 pending 否则算 aborted 处理; 最后当 write intent 转换为 mvcc 后 transaction record 就可以被清理。

resolving write intent

当碰到 write intent 到时候，都会进行 resolve，具体根据 write intent 指向的 transaction record 状态:

committed: 对于 committed 的write intent 会转换为 mvcc 并进行清理
aborted: 可以无视并清理掉
pending: 进行 transaction conflict 处理
record 不存在，根据 liveness 进行 aborted 或 pending 处理

Transaction conflict

前面看到 transaction conflict 需要处理, cdb 会有 2 中 conflict:

write/write: 同一个 key 上有 2 个 write intent
write/read: 读请求遇到比自己 timestamp 小的 write intent

举个例子 txnA 先执行和 txnB 执行时看到 a 的 write intent，则:

比较事务的优先级，低的那个如果是 write/write 则直接被 aborted，如果是 write/read 则尝试 push timestamp
看下 transaction record 有没有过期, 过期(没 tx record 且超过 liveness 或 liveness 没被心跳过)则 abort 过期那个
否则 txnB 进入 TxnWaitQueue 中等待 txnA 完成

其他冲突的情况:

read/write：冲突通过 timestamp cache 来发现并按需 push timestamp
read within uncertainty window: 当读到一个更大的 timestamp 时不能确定是不是 clock skew，则需要 push timestamp 超过 uncertain value，另外提到 retry 不会有这种问题

txnWaitQueue:

维护了所有不能被 push timestamp 而需要 blocking 等待其他事务完成后再执行的 txn 信息, 是一个 map

txnA -> txn1, txn2
txnB -> txn3, txn4, txn5

需要在单个节点处理， cdb 的选择是放 transaction record 的 range 的 leader, 当事务被 commit 或 abort 的时会通知 txnWaitQueue 唤醒等待自己的其他事务执行，另外被 blocked 的事务也会检查自己状态如果被 abort 会从 txnWaitQueue remove; 等待可能出现 deadlock 相互等待则随机 abort 一个。

Read refreshing

当 txn 被 push timestamp 后需要做进步一检查才能用 pushed 的新 timestamp， cdb 的检查是通过查看 txn 读取过得所有记录，看在有没有其他 write 出现在原 timestamp 和新 timestamp 之间，如果没有则检查通过可以用 pushed timestamp，否则需要 restart 事务；所以需要通过 RefreshRequest 维护所有读过的记录来帮助实现。

Transaction pipelining

cdb 的新版中有实现 transaction pipelining, 就是在 write intent 只写 leader 不等 replica 完成就返回，这样 txn 中的多条语句不用每条执行都等 replica，等提交的时候再去保证都 replica, 这个优化会提升性能不过看有网友说会带来一些问题, 另外还有这个 rfcs 等待看下

Distribution Layer

https://www.cockroachlabs.com/docs/stable/architecture/distribution-layer.html

最后看下 Distribution layer, cdb 抽象是一个分布式的 sorted map 提供:

lookups
scan

两个操作对上层提供, 数据被分为多个 ranges，所以 sorted map 需要包括:

meta ranges: 描述 range 在哪里
table data：具体的数据

cdb 的 meta ranges 是 two-level index，保存首先 meta1 会能找到 meta2, 再通过 meta2 能找到具体 range 在哪.

meta1 和 meta2 都是正常的 kv range 保存在 kv 中。

metaX/successorKey -> LeaseholderAddress, [list of other nodes containing data]

e.g. 
# Points to meta2 range for keys [A-M)
meta1/M -> node1:26257, node2:26257, node3:26257
# Points to meta2 range for keys [M-Z]
meta1/maxKey -> node4:26257, node5:26257, node6:26257
# Contains [A-G)
meta2/G -> node1:26257, node2:26257, node3:26257
# Contains [G-M)
meta2/M -> node1:26257, node2:26257, node3:26257
#Contains [M-Z)
meta2/Z -> node4:26257, node5:26257, node6:26257
#Contains [Z-maxKey)
meta2/maxKey->  node4:26257, node5:26257, node6:26257

每个 node 可以通过 range descriptor 来知道 meta1 的位置，且 meta1 不会被 split; 各个节点会缓存曾经访问过得 meta2 信息到内存并及时淘汰(等看代码看看)

table data 则是创建表的时候 table 和 second index 都在一个 range，但超过 64 MiB 会被 split 可能 index 和 table 不在一个 range， 64 MiB 是为了拷贝数据快速考虑，如果更大其实更容易让数据在一起

range descriptor

每个 range 都有 metadata 描述，就是 range descriptor: