数据库设计建议

　　1。概述
　　本开发设计建议约定数据库建模和数据库应用程序开发过程中，应当遵守的设计规范。依据这些规范进行建模，能够更好的契合openGauss的分布式处理架构，输出更高效的业务SQL代码。
　　本开发设计建议中所陈述的建议和关注含义如下：建议：用户应当遵守的设计规则。遵守这些规则，能够保证业务的高效运行；违反这些规则，将导致业务性能的大幅下降或某些业务逻辑错误。关注：在业务开发过程中客户需要注意的细则。用于标识容易导致客户理解错误的知识点（实际上遵守SQL标准的SQL行为），或者程序中潜在的客户不易感知的默认行为。2。数据库对象命名
　　数据库对象命名需要满足约束：非时序表长度不超过63个字节，时序表长度不超过53个字符，以字母或下划线开头，中间字符可以是字母、数字、下划线、、。【建议】避免使用保留或者非保留关键字命名数据库对象。
　　说明：可以使用selectfrompggetkeywords（）查询openGauss的关键字，或者在关键字章节中查看。【建议】避免使用双引号括起来的字符串来定义数据库对象名称，除非需要限制数据库对象名称的大小写。数据库对象名称大小写敏感会使定位问题难度增加。【建议】数据库对象命名风格务必保持统一。增量开发的业务系统或进行业务迁移的系统，建议遵守历史的命名风格。建议使用多个单词组成，以下划线分割。数据库对象名称建议能够望文知意，尽量避免使用自定义缩写（可以使用通用的术语缩写进行命名）。例如，在命名中可以使用具有实际业务含义的英文词汇或汉语拼音，但规则应该在数据库实例范围内保持一致。变量名的关键是要具有描述性，即变量名称要有一定的意义，变量名要有前缀标明该变量的类型。【建议】表对象的命名应该可以表征该表的重要特征。例如，在表对象命名时区分该表是普通表、临时表还是非日志表：普通表名按照数据集的业务含义命名。临时表以tmp后缀命名。非日志表以ul后缀命名。外表以f后缀命名。不创建以redis为前缀的数据库对象。不创建以mlog和以matviewmap为前缀的数据库对象。【建议】非时序表对象命名建议不要超过63字节。如果过该长度内核会对表名进行截断，从而造成和设置值不一致的现象。且在不同字符集下，可能造成字符被截断，出现预期外的字符。3。数据库对象设计3。1Database和Schema设计
　　openGauss中可以使用Database和Schema实现业务的隔离，区别在于Database的隔离更加彻底，各个Database之间共享资源极少，可实现连接隔离、权限隔离等，Database之间无法直接互访。Schema隔离的方式共用资源较多，可以通过grant与revoke语法便捷地控制不同用户对各Schema及其下属对象的权限。从便捷性和资源共享效率上考虑，推荐使用Schema进行业务隔离。建议系统管理员创建Schema和Database，再赋予相关用户对应的权限。Database设计建议【规则】在实际业务中，根据需要创建新的Database，不建议直接使用数据库实例默认的postgres数据库。【建议】一个数据库实例内，用户自定义的Database数量建议不超过3个。【建议】为了适应全球化的需求，使数据库编码能够存储与表示绝大多数的字符，建议创建Database的时候使用UTF8编码。【关注】创建Database时，需要重点关注字符集编码（ENCODING）和兼容性（DBCOMPATIBILITY）两个配置项。openGauss支持A、B、C和PG四种兼容模式，分别表示兼容O语法、MY语法、TD语法和POSTGRES语法，不同兼容模式下的语法行为存在一定差异，默认为A兼容模式。【关注】Database的owner默认拥有该Database下所有对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用。Schema设计建议【关注】如果该用户不具有sysadmin权限或者不是该Schema的owner，要访问Schema下的对象，需要同时给用户赋予Schema的usage权限和对象的相应权限。【关注】如果要在Schema下创建对象，需要授予操作用户该Schema的create权限。【关注】Schema的owner默认拥有该Schema下对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用
　　3。2表设计
　　openGauss是分布式架构。数据分布在各个DN上。总体上讲，良好的表设计需要遵循以下原则：【关注】将表数据均匀分布在各个DN上。数据均匀分布，可以防止数据在部分DN上集中分布，从而导致因存储倾斜造成数据库实例有效容量下降。通过选择合适的分布列，可以避免数据倾斜。【关注】将表的扫描压力均匀分散在各个DN上。避免扫描压力集中在部分DN上，而导致性能瓶颈。例如，在事实表上使用等值过滤条件时，将会导致扫描压力不均匀。【关注】减少需要扫描的数据量。通过分区表的剪枝机制可以大幅减少数据的扫描量。【关注】尽量减少随机IO。通过聚簇局部聚簇可以实现热数据的连续存储，将随机IO转换为连续IO，从而减少扫描的IO代价。【关注】尽量避免数据shuffle。shuffle，是指在物理上，数据从一个节点，传输到另一个节点。shuffle占用了大量宝贵的网络资源，减小不必要的数据shuffle，可以减少网络压力，使数据的处理本地化，提高数据库实例的性能和可支持的并发度。通过对关联条件和分组条件的仔细设计，能够尽可能地减少不必要的数据shuffle。选择存储方案
　　【建议】表的存储类型是表定义设计的第一步，客户业务类型是决定表的存储类型的主要因素，表存储类型的选择依据请参考表1。
　　表1表的存储类型及场景
　　存储类型
　　适用场景
　　行存点查询（返回记录少，基于索引的简单查询）。增、删、改操作较多的场景。
　　列存统计分析类查询（关联、分组操作较多的场景）。即席查询（查询条件不确定，行存表扫描难以使用索引）。选择分布方案
　　【建议】表的分布方式的选择一般遵循以下原则：
　　表2表的分布方式及使用场景
　　分布方式
　　描述
　　适用场景
　　Hash
　　表数据通过Hash方式散列到数据库实例中的所有DN上。
　　数据量较大的事实表。
　　Replication
　　数据库实例中每一个DN都有一份全量表数据。
　　维度表、数据量较小的事实表。
　　Range
　　表数据对指定列按照范围进行映射，分布到对应DN。
　　用户需要自定义分布规则的场景。
　　List
　　表数据对指定列按照具体值进行映射，分布到对应DN。
　　用户需要自定义分布规则的场景。选择分区方案
　　当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则：【建议】使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。【建议】分区名称应当体现分区的数据特征。例如，关键字区间特征。【建议】将分区上边界的分区值定义为MAXVALUE，以防止可能出现的数据溢出。
　　典型的分区表定义如下：复制代码CREATETABLEstaffSp1（staffIDNUMBER（6）notnull，FIRSTNAMEVARCHAR2（20），LASTNAMEVARCHAR2（25），EMAILVARCHAR2（25），PHONENUMBERVARCHAR2（20），HIREDATEDATE，employmentIDVARCHAR2（10），SALARYNUMBER（8，2），COMMISSIONPCTNUMBER（4，2），MANAGERIDNUMBER（6），sectionIDNUMBER（4））PARTITIONBYRANGE（HIREDATE）（PARTITIONHIRE19950501VALUESLESSTHAN（1995050100：00：00），PARTITIONHIRE19950502VALUESLESSTHAN（1995050200：00：00），PARTITIONHIREmaxvalueVALUESLESSTHAN（MAXVALUE））；选择分布键
　　Hash表的分布键选取至关重要，如果分布键选择不当，可能会导致数据倾斜，从而导致查询时，IO负载集中在部分DN上，影响整体查询性能。因此，在确定Hash表的分布策略之后，需要对表数据进行倾斜性检查，以确保数据的均匀分布。分布键的选择一般需要遵循以下原则：【建议】选作分布键的字段取值应该比较离散，以便数据能在各个DN上均匀分布。当单个字段无法满足离散条件时，可以考虑使用多个字段一起作为分布键。一般情况下，可以考虑选择表的主键作为分布键。例如，在人员信息表中选择证件号码作为分布键。【建议】在满足第一条原则的情况下，尽量不要选取在查询中存在常量过滤条件的字段作为分布键。例如，在表dwcjk相关的查询中，字段zqdh存在常量过滤条件zqdh000001，那么就应当尽量不选择zqdh字段做为分布键。【建议】在满足前两条原则的情况，尽量选择查询中的关联条件为分布键。当关联条件作为分布键时，join任务的相关数据都分布在DN本地，将极大减少DN之间的数据流动代价。3。3字段设计选择数据类型
　　字段设计时，基于查询效率的考虑，一般遵循以下原则：【建议】尽量使用高效数据类型。选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数、NUMERIC。【建议】当多个表存在逻辑关系时，表示同一含义的字段应该使用相同的数据类型。【建议】对于字符串数据，建议使用变长字符串数据类型，并指定最大长度。请务必确保指定的最大长度大于需要存储的最大字符数，避免超出最大长度时出现字符截断现象。除非明确知道数据类型为固定长度字符串，否则，不建议使用CHAR（n）、BPCHAR（n）、NCHAR（n）、CHARACTER（n）。关于字符串类型的详细说明，请参见常用字符串类型介绍。常用字符串类型介绍
　　在进行字段设计时，需要根据数据特征选择相应的数据类型。字符串类型在使用时比较容易混淆，下表列出了openGauss中常见的字符串类型：
　　表1常用字符串类型
　　名称
　　描述
　　最大存储空间
　　CHAR（n）
　　定长字符串，n描述了存储的字节长度，如果输入的字符串字节格式小于n，那么后面会自动用空字符补齐至n个字节。
　　10MB
　　CHARACTER（n）
　　定长字符串，n描述了存储的字节长度，如果输入的字符串字节格式小于n，那么后面会自动用空字符补齐至n个字节。
　　10MB
　　NCHAR（n）
　　定长字符串，n描述了存储的字节长度，如果输入的字符串字节格式小于n，那么后面会自动用空字符补齐至n个字节。
　　10MB
　　BPCHAR（n）
　　定长字符串，n描述了存储的字节长度，如果输入的字符串字节格式小于n，那么后面会自动用空字符补齐至n个字节。
　　10MB
　　VARCHAR（n）
　　变长字符串，n描述了可以存储的最大字节长度。
　　10MB
　　CHARACTERVARYING（n）
　　变长字符串，n描述了可以存储的最大字节长度；此数据类型和VARCHAR（n）是同一数据类型的不同表达形式。
　　10MB
　　VARCHAR2（n）
　　变长字符串，n描述了可以存储的最大字节长度，此数据类型是为兼容Oracle类型新增的，行为和VARCHAR（n）一致。
　　10MB
　　NVARCHAR2（n）
　　变长字符串，n描述了可以存储的最大字节长度。
　　10MB
　　TEXT
　　不限长度（不超过1GB8203字节）变长字符串。
　　1GB8203字节3。4约束设计
　　DEFAULT和NULL约束【建议】如果能够从业务层面补全字段值，那么，就不建议使用DEFAULT约束，避免数据加载时产生不符合预期的结果。【建议】给明确不存在NULL值的字段加上NOTNULL约束，优化器会在特定场景下对其进行自动优化。【建议】给可以显式命名的约束显式命名。除了NOTNULL和DEFAULT约束外，其他约束都可以显式命名。
　　局部聚簇
　　PartialClusterKey（局部聚簇，简称PCK）是列存表的一种局部聚簇技术，在openGauss中，使用PCK可以通过minmax稀疏索引实现事实表快速过滤扫描。PCK的选取遵循以下原则：【关注】一张表上只能建立一个PCK，一个PCK可以包含多列，但是一般不建议超过2列。【建议】在查询中的简单表达式过滤条件上创建PCK。这种过滤条件一般形如colopconst，其中col为列名，op为操作符、、、、，const为常量值。【建议】在满足上面条件的前提下，选择distinct值比较多的列上建PCK。
　　唯一约束【关注】行存表、列存表均支持唯一约束。【建议】从命名上明确标识唯一约束，例如，命名为UNI构成字段。
　　主键约束【关注】行存表、列存表均支持主键约束。【建议】从命名上明确标识主键约束，例如，将主键约束命名为PK字段名。
　　外键约束【关注】行存表支持外键约束，列存表不支持外键约束。【建议】从命名上明确标识外键约束，例如，将外键约束命名为FK字段名。
　　检查约束【关注】行存表支持检查约束，而列存表不支持。【建议】从命名上明确标识检查约束，例如，将检查约束命名为CK字段名。3。5视图和关联表设计视图设计【建议】除非视图之间存在强依赖关系，否则不建议视图嵌套。【建议】视图定义中尽量避免排序操作。关联表设计【建议】表之间的关联字段应该尽量少。【建议】关联字段的数据类型应该保持一致。【建议】关联字段在命名上，应该可以明显体现出关联关系。例如，采用同样名称来命名。4。JDBC配置
　　目前，openGauss相关的第三方工具都是通过JDBC进行连接的，此部分将介绍工具配置时的注意事项。连接参数【关注】第三方工具通过JDBC连接openGauss时，JDBC向openGauss发起连接请求，会默认添加以下配置参数，详见JDBC代码ConnectionFactoryImpl类的实现。复制代码params｛｛user，user｝，｛database，database｝，｛clientencoding，UTF8｝，｛DateStyle，ISO｝，｛extrafloatdigits，2｝，｛TimeZone，createPostgresTimeZone（）｝，｝；这些参数可能会导致JDBC客户端的行为与gsql客户端的行为不一致，例如，Date数据显示方式、浮点数精度表示、timezone显示。如果实际期望和这些配置不符，建议在java连接设置代码中显式设定这些参数。【建议】通过JDBC连接数据库时，应该保证下面三个时区设置一致：JDBC客户端所在主机的时区。openGauss数据库实例所在主机的时区。openGauss数据库实例配置过程中时区。
　　说明：时区设置相关的操作，请参考《安装指南》中企业版安装安装准备准备软硬件安装环境同步系统时间章节内容。fetchsize
　　【关注】在应用程序中，如果需要使用fetchsize，必须关闭autocommit。开启autocommit，会令fetchsize配置失效。autocommit
　　【建议】在JDBC向openGauss申请连接的代码中，建议显式打开autocommit开关。如果基于性能或者其它方面考虑，需要关闭autocommit时，需要应用程序自己来保证事务的提交。例如，在指定的业务SQL执行完之后做显式提交，特别是客户端退出之前务必保证所有的事务已经提交。释放连接
　　【建议】推荐使用连接池限制应用程序的连接数。每执行一条SQL就连接一次数据库，是一种不好的SQL编写习惯。
　　【建议】在应用程序完成作业任务之后，应当及时断开和openGauss的连接，释放资源。建议在任务中设置session超时时间参数。
　　【建议】使用JDBC连接池，在将连接释放给连接池前，需要执行以下操作，重置会话环境。否则，可能会产生因为历史会话信息导致的对象冲突。如果在连接中设置了GUC参数，那么在将连接归还连接池之前，必须使用SETSESSIONAUTHORIZATIONDEFAULT；RESETALL；将连接的状态清空。如果使用了临时表，那么在将连接归还连接池之前，必须将临时表删除。CopyManager
　　【建议】在不使用ETL工具，数据入库实时性要求又比较高的情况下，建议在开发应用程序时，使用openGaussJDBC驱动的copyManger接口进行微批导入。5。SQL编写DDL【建议】在openGauss中，建议DDL（建表、comments等）操作统一执行，在批处理作业中尽量避免DDL操作。避免大量并发事务对性能的影响。【建议】在非日志表（unloggedtable）使用完后，立即执行数据清理（truncate）操作。因为在异常场景下，openGauss不保证非日志表（unloggedtable）数据的安全性。【建议】临时表和非日志表的存储方式建议和基表相同。当基表为行存（列存）表时，临时表和非日志表也推荐创建为行存（列存）表，可以避免行列混合关联带来的高计算代价。【建议】索引字段的总长度不超过50字节。否则，索引大小会膨胀比较严重，带来较大的存储开销，同时索引性能也会下降。【建议】不要使用DROPCASCADE方式删除对象，除非已经明确对象间的依赖关系，以免误删。数据加载和卸载【建议】在insert语句中显式给出插入的字段列表。例如：复制代码INSERTINTOtask（name，id，comment）VALUES（task1，100，第100个任务）；【建议】在批量数据入库之后，或者数据增量达到一定阈值后，建议对表进行analyze操作，防止统计信息不准确而导致的执行计划劣化。【建议】如果要清理表中的所有数据，建议使用truncatetable方式，不要使用deletetable方式。deletetable方式删除性能差，且不会释放那些已经删除了的数据占用的磁盘空间。类型转换【建议】在需要数据类型转换（不同数据类型进行比较或转换）时，使用强制类型转换，以防隐式类型转换结果与预期不符。【建议】在查询中，对常量要显式指定数据类型，不要试图依赖任何隐式的数据类型转换。【关注】若sqlcompatibility参数设置为A，在导入数据时，空字符串会自动转化为NULL。如果需要保留空字符串需要sqlcompatibility参数设置为C。查询操作【建议】除ETL程序外，应该尽量避免向客户端返回大量结果集的操作。如果结果集过大，应考虑业务设计是否合理。【建议】使用事务方式执行DDL和DML操作。例如，truncatetable、updatetable、deletetable、droptable等操作，一旦执行提交就无法恢复。对于这类操作，建议使用事务进行封装，必要时可以进行回滚。【建议】在查询编写时，建议明确列出查询涉及的所有字段，不建议使用SELECT这种写法。一方面基于性能考虑，尽量减少查询输出列；另一方面避免增删字段对前端业务兼容性的影响。【建议】在访问表对象时带上schema前缀，可以避免因schema切换导致访问到非预期的表。【建议】超过3张表或视图进行关联（特别是FULLJOIN）时，执行代价难以估算。建议使用WITHTABLEAS语句创建中间临时表的方式增加SQL语句的可读性。【建议】尽量避免使用笛卡尔积和FULLJOIN。这些操作会造成结果集的急剧膨胀，同时其执行性能也很低。【关注】NULL值的比较只能使用ISNULL或者ISNOTNULL的方式判断，其他任何形式的逻辑判断都返回NULL。例如：NULLNULL、NULLNULL和NULL1返回结果都是NULL，而不是期望的布尔值。【关注】需要统计表中所有记录数时，不要使用count（col）来替代count（）。count（）会统计NULL值（真实行数），而count（col）不会统计。【关注】在执行count（col）时，将值为NULL的记录行计数为0。在执行sum（col）时，当所有记录都为NULL时，最终将返回NULL；当不全为NULL时，值为NULL的记录行将被计数为0。【关注】count（多个字段）时，多个字段名必须用圆括号括起来。例如，count（（col1，col2，col3））。注意：通过多字段统计行数时，即使所选字段都为NULL，该行也被计数，效果与count（）一致。【关注】count（distinctcol）用来计算该列不重复的非NULL的数量，NULL将不被计数。【关注】count（distinct（col1，col2，））用来统计多列的唯一值数量，当所有统计字段都为NULL时，也会被计数，同时这些记录被认为是相同的。【建议】使用连接操作符替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推，导致查询性能严重劣化。【建议】使用下面时间相关的宏替换now函数来获取当前时间。因为now函数生成的执行计划无法下推，导致查询性能严重劣化。表1时间相关的宏
　　宏名称
　　描述
　　示例
　　CURRENTDATE
　　获取当前日期，不包含时分秒。复制代码openGaussselectCURRENTDATE；date20180202（1row）
　　CURRENTTIME
　　获取当前时间，不包含年月日。复制代码openGaussselectCURRENTTIME；timetz00：39：34。63393808（1row）
　　CURRENTTIMESTAMP（n）获取当前日期和时间，包含年月日时分秒。
　　说明：
　　n表示存储的毫秒位数。复制代码openGaussselectCURRENTTIMESTAMP（6）；timestamptz2018020200：39：55。23168908（1row）复制代码openGaussselectCURRENTTIMESTAMP（6）；timestamptz2018020200：39：55。23168908（1row）【建议】尽量避免标量子查询语句的出现。标量子查询是出现在select语句输出列表中的子查询，在下面例子中，下划线部分即为一个标量子查询语句：复制代码SELECTid，（SELECTCOUNT（）FROMfilmsfWHEREf。dids。id）FROMstaffsp1s；标量子查询往往会导致查询性能急剧劣化，在应用开发过程中，应当根据业务逻辑，对标量子查询进行等价转换，将其写为表关联。【建议】在where子句中，应当对过滤条件进行排序，把选择读较小（筛选出的记录数较少）的条件排在前面。【建议】where子句中的过滤条件，尽量符合单边规则。即把字段名放在比较条件的一边，优化器在某些场景下会自动进行剪枝优化。形如colopexpression，其中col为表的一个列，op为‘’、‘’的等比较操作符，expression为不含列名的表达式。例如，复制代码SELECTid，fromimageid，frompersonid，fromvideoidFROMfacedataWHEREcurrenttimestamp（6）time1days：：interval；改写为：复制代码SELECTid，fromimageid，frompersonid，fromvideoidFROMfacedatawheretimecurrenttimestamp（6）1days：：interval；【建议】尽量避免不必要的排序操作。排序需要耗费大量的内存及CPU，如果业务逻辑许可，可以组合使用ORDERBY和LIMIT，减小资源开销。openGauss默认按照ASCNULLLAST进行排序。【建议】使用ORDERBY子句进行排序时，显式指定排序方式（ASCDESC），NULL的排序方式（NULLFIRSTNULLLAST）。【建议】不要单独依赖limit子句返回特定顺序的结果集。如果部分特定结果集，可以将ORDERBY子句与Limit子句组合使用，必要时也可以使用OFFSET跳过特定结果。【建议】在保障业务逻辑准确的情况下，建议尽量使用UNIONALL来代替UNION。【建议】如果过滤条件只有OR表达式，可以将OR表达式转化为UNIONALL以提升性能。使用OR的SQL语句经常无法优化，导致执行速度慢。例如，将下面语句复制代码SELECTFROMscdc。pubmenuWHERE（cdp300ANDinline301）OR（cdp301ANDinline302）OR（cdp302ANDinline301）；转换为：复制代码SELECTFROMscdc。pubmenuWHERE（cdp300ANDinline301）unionallSELECTFROMscdc。pubmenuWHERE（cdp301ANDinline302）unionallSELECTFROMtablenameWHERE（cdp302ANDinline301）【建议】当in（val1，val2，val3）表达式中字段较多时，建议使用in（values（val1），（val2），（val3））语句进行替换。优化器会自动把in约束转换为非关联子查询，从而提升查询性能。【建议】在关联字段不存在NULL值的情况下，使用（not）exist代替（not）in。例如，在下面查询语句中，当T1。C1列不存在NULL值时，可以先为T1。C1字段添加NOTNULL约束，再进行如下改写。复制代码SELECTFROMT1WHERET1。C1NOTIN（SELECTT2。C2FROMT2）；
　　可以改写为：复制代码SELECTFROMT1WHERENOTEXISTS（SELECTFROMT1，T2WHERET1。C1T2。C2）；说明：
　　如果不能保证T1。C1列的值为NOTNULL的情况下，就不能进行上述改写。
　　如果T1。C1为子查询的输出，要根据业务逻辑确认其输出是否为NOTNULL。【建议】通过游标进行翻页查询，而不是使用LIMITOFFSET语法，避免多次执行带来的资源开销。游标必须在事务中使用，执行完后务必关闭游标并提交事务。
　　来源：https：my。oschina。netgaussdbblog5486884
　　做程序员，圈子和学习最重要因为有有了圈子可以让你少走弯路，扩宽人脉，扩展思路，学习他人的一些经验及学习方法！同时在这分享一下是一直以来整理的Java后端进阶笔记文档和学习资料免费分享给大家！需要资料的朋友私信我扣【06】