Hubble数据库开发使用规范

开发设计

命名规范

hubble数据库数据库命名规范可参考传统关系型postgresql数据库。

处理特定的错误码40001

在客户端接受到服务端返回的异常错误,并且错误错误码为40001时,这意味着这个操作影响到的数据与其他事务相冲突了,并在事务管理器内部尝试重试失败后,最后返回客户端。我们建议对此错误码进行特殊处理,即客户端接受40001错误码后,主动进行五次以内的重试。

多节点进行写入

hubble数据库为去中心话架构,确保了每个节点服务都可等价的对外提供服务。我们建议上游单个主题日志同步数据流,可以指定到固定的节点中。这样能一定程度的减少数据更新过程中的网络开销和事务冲突。不必担心可能出现的单点故障情况,因为hubble数据库在协议层,很好的兼容了postgersql,可直接使用postgresql-jdbc的高可用特性,配置多个连接目标服务。在出现单点因网络等问题无法访问的情况下,自动按照顺序依次尝试连接访问目标服务节点。当然多个不同的数据源可以由开发人员指定分散到等价的服务节点,避免单点的性能瓶颈,有效使用整个集群的资源。

查询可适用任何节点

hubble数据库为去中心话架构,针对于只有查询类的服务接口,可以通过HA Proxy、F5等来实现负载均衡。这样查询类的服务不需要关心实际访问是哪个具体服务节点,完全避免故障切换的问题。同时面对突然增长的连接数,整个集群也能有非常良好的吞吐表现。

尽可能确保表有主键

设计良好的多列组合主键,可以产生比uuid或自增主键更好的性能,这需要在前期模式设计工作与业务数据的理解。我们建议一些单调递增的字段要位于主键的第一个列之后。这能带来如下收益:

  • 主键具有足够的随机性,可以将表的数据和查询负载相对均匀的分布在集群中,从而避免出现热点。所谓足够的随机性,是指主键的前缀应该相对均匀地分布在其域,而这域与服务节点数量相对一致是最优的状态。
  • 一个有实际业务含义的列在主键中,往往对查询速度也有帮助。

索引设计

在不使用主键查询的场景中,合理的使用索引,可以大幅度的提升查询效率。同时索引的设计是需要权衡的,在带来查询速度极大提升的同时,也可能会略微影响列更新的操作,因为更新操作必须对表和索引都进行写操作。

我们建议为所有常见的查询创建索引,为了能设计出权衡后最优的索引,符合业务场景的使用,我们可以遵守如下两个设计准则:

  • 索引包含的列,尽可能满足所有谓词条件
  • 在索引中存有需要查询展现的列

在设计索引时,重要的是考虑查询条件是哪些列以及他们的顺序,以下是一些具体的建议:

  • 需要满足索引前缀的要求,如果当前有一个(A,B,C)索引,查询条件为(A)或(A,B)都是可以通过索引进行查询的,但(B)或(B,C)是无非是通过索引查询的。这意味着尽可能的避免创建单列索引,增加索引的通用性,即可在多个场景下复用。

  • 谓词条件中出现的列,需要注意等于和包含条件(= ,in),必须出现在范围(<,>)条件之前。否则无法通过索引查。

  • 索引中可以存储查用列,来避免回表的情况。命中率高的查询,是否回表对查询速度,将产生非常大的影响。

尽量避免大表后建索引的情况

hubble数据库具备在线建索引的特性,且在建索引过程中,不会出现锁表的情况。但我们需要注意一点,hubble数据库在完成创建索引前,需要做索引与数据一致性校验的必要动作,这个行为对于超大数据量的表,无法避免的是一个大IO的任务。如果无法为超大数据量表创建索引,提供合理的运维窗口,请给这个索引创建任务留出较大的时间窗口,耐心等待即可,尤其在非SSD存储介质基础上。当然几亿数据的表,创建索引还是可以保证效率的,这里的大表,是指10亿、20亿上的表。

连接数的合理规划

hubble数据库创建多个活动连接,可以有效的使用可用的数据库资源。对于创建连接需要经过身份验证的过程,这个过程是cpu和内存密集型的,客户端等待数据库验证连接的过程中还增加了延迟。因此合理的控制连接数是必要的工作。我们建议连接数的量参考集群core数量进行衡量,计算公式是core数据的两倍。

当然HA proxy模式也对连接数的规划会产生影响,如较高维度分析型的并发任务,实际最优连接数会比计算公式得出的结果要低。如果集群使用ssd硬盘,能一定程度提升连接数的上限。

如应用类程序使用连接池,除了设置最大连接池大小外,还必须设置空闲连接池大小。我们的建议是将空间连接池大小设置为最大连接池相等。这个设置可能会占用更多的应用程序服务器内存,但在并发较高的情况中,可以避免创建新连接带来的性能损耗。

建表复制数据CREATE TABLE AS SELECT

当执行CREATE TABLE AS SELECT语句进行建表并且进行数据复制的时候,将会导致新创建的表中丢失主键分区和外键等约束。影响数据检索查询效率。强烈推荐先执行create table t2(like t1 INCLUDING ALL EXCLUDING CONSTRAINTS)复制表结构和约束 ,再执行insert into T2 select * from T1方式导入数据。

参考示例如下:

CREATE TABLE t1 (id INT PRIMARY KEY, name INT NOT NULL, INDEX(name));
CREATE TABLE t2 (LIKE t1 INCLUDING ALL EXCLUDING CONSTRAINTS);
insert into T2 select * from T1;

SQL规范建议

使用UUID生成唯一主键

建表设计过程中,无法使用业务字段作为主键,我们建议使用UUID来作为主键字段。这能提供更好的数据分布情况。

插入同时返回数据

在插入数据后,需要其更新行的值,可使用returning语法,此语法可减少一次查询的操作。这对于使用系统自动生成的UUID主键非常友好。

insert into t_table(id,name,age) values(default,'willy',18) returning id;

批量写入速度优化

  • 表初始化数据使用import方式

    import导入数据的方式,是将表临时下线后,写底层数据文件的机制实现的。这意味避免了客户端与服务端的网络、sql解析等不必要的开销。同时import大数据量方式,能很好的解决大数据量导入场景中最难处理的数据一致性问题。

  • 将小批量数据在一个语句中完成插入

    如10行,100行,1000行均可理解为是小批量的数据插入。在面对可预期的数据插入行为场景中(即数据源为消息中间件的场景,这个情况下不会有回滚),采用小批量的的导入方式,可以有效的提升插入效率,且提升效率至少在10倍之上。

使用多行的DML

对于insertupdatedelete语句,一个多行DML比多个单行DML执行更快。如果业务逻辑上允许,我们建议尽可能使用多行DML,而不是多个单行DML。

执行计划分析

在sql优化优化过程中,建议使用explain查看sql的执行计划,这个过程是非常有必要的,也是我们了解sql真实的执行过程的唯一方式。为了避免sql出现性能问题,我们需要判断以下几个重点:

  • 是否有全表扫描的情况,即执行计划中是否有出现spans ALL的关键字

  • 索引查询的方式与预期是否一致

  • 多表join的次序,是否与预期一致

大数据量的分析如何提升性能

我们将大数据量的查询定义为命中数据多(命中率高)的查询,同时查询涉及多表关联。

针对与大查询的优化可以从如下方面入手:

  • 通过合理的索引设计,减少表扫描的性能开销

  • 使用合理的join方式,尽可能避免计算过程中对内存的占用

  • 开启副本读的方式,使用分布式数据库多副本的特性,合理利用集群副本资源进行计算 AS OF SYSTEM TIME experimental_follower_read_timestamp()

  • 在确保当前业务逻辑不变更的前提下,SQL的实现方式是否有合理的优化空间

  • 高维度的查询是否可做降维处理,先汇聚中层维度的计算结果

列族使用

在创建表时,默认所有字段都存储在一个列族中,这样对大部分的场景综合考量都是性能最优的。然而对于一些频繁更新的情况下,用户可以自行将频繁更新的字段,单独设计一个列簇。因为在更新过程中,单独规划列族的字段,是不需要影响其他不更新字段的列族空间的。

CREATE TABLE t_t2 (
    fundid INT8 NOT NULL,
    trans_amount decimal(18,2) NULL,
    data BYTES NULL,
    CONSTRAINT "primary" PRIMARY KEY (fundid ASC),
    FAMILY f1 (fundid, trans_amount),
    FAMILY f2 (data)
);

优雅的清空表

当需要清空一个表时候,建议使用truncate语句:其实际执行的方式是drop掉当前表后,重建一张新表。这相比delete在各方面都更好,因为delete需要在执行过程中生成事务。

避免使用CTAS与SFU(select for update)

需要在生产环境中尽可能的避免create table as selectinsert into table selectupdate table select这类sql。

这类sql在执行过程中,由于可能存在大数量的查询,在查询过程中,同时存在数据被更新的并发事务,数据库会生成一个大事务级别的意向锁,这是为了避免数据在大查询未完成阶段被其他事务修改数据。往往这样的操作,在数据库流量较大的情况下,会对在线业务形成危害。

合理的使用视图

虚拟视图往往可以使sql实现变得更为简洁。

删除大量数据

为了删除大量的行,我们建议分批次迭代执行,直到删除所有不需要的行,当然这是在通过索引条件删除的前提下。

首先我们对删除大量数据的定义是指一万行数据以上,面对这类情况,建议拆分为小批量删除迭代进行,一直到需要被删除的数据全部删除完成。

当然为了快速定位需要删除的数据,删除条件也有必要能符合索引的规划。

删除对业务没有帮助的索引

索引可以有效的提升查询效率,但索引也是有代价的,会在每次写入时增加额外的开销。在大部分情况下,我们都需要进行权衡。在长期使用的过程中,如因为业务调整等原因,出现不需要使用的索引,我们建议将其删除。

部署规范

交换分区

在进行hubble集群部署的时候,要对交换分区进行关闭,因为hubble数据库属于对IO敏感型基础架构,swapping会将主内存交换到磁盘,从而对性能造成负面影响。建议永久性关闭交换分区,更多操作请参考部署目录下安装前服务配置。