文档中心MogDBMogDB StackUqbar
生态工具
文档工具
EN
v3.1

文档:v3.1

支持的版本:
最新(v5.0)/
v3.1

其他版本:
v3.0/
v2.1/
v2.0/
v1.1

设计规范

database设计

  • 建议以业务功能来命名数据库，简单直观。

  • 业务数据库推荐以兼容PG的方式来创建。

  • 数据库编码推荐用utf8。

tablespace设计

  • 频繁使用的表和索引单独存放在一个表空间，此表空间应在性能好的磁盘上创建。

  • 以历史数据为主，或活跃度较低的表和索引可以存放在磁盘性能较差的表空间。

  • 表和索引可以单独存放在不同的表空间。

  • 表空间也可以按数据库分、按schema分或按业务功能来分。

  • 每个database/schema对应一个表空间和一个相应索引表空间。

schema设计

  • 在一个数据库下执行创建用户时，默认会在该数据库下创建一个同名schema。
  • 不建议在默认public schema下创建数据库对象。
  • 创建一个与用户名不同的schema给业务使用。

table设计

  • 设计表结构时，应该规划好，避免经常添加字段，或者修改字段类型或长度。

  • 必须为表添加注释信息，表名与注释信息相匹配。

  • 禁止使用unlogged/ temp/temporary关键字创建业务表。

  • 作为表间连接关系的字段，数据类型必须保持严格一致，避免索引无法正常使用。

  • 禁止使用VARCHAR或其他字符类型来存储日期值，如果使用，则不能在此字段上做运算，需要在数据规范中严格定义。

  • 对于频繁更新的astore表，建议建表时指定表的fillfactor=85，给HOT预留空间。

  • 频繁更新使用的表应该单独放在存储性能好的表空间。

  • 数据量超过亿级或占用磁盘超过10GB的表，建议考虑分区。

  • 表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生。

    说明：比如A表user_id字段数据类型定义为varchar，但是SQL语句查询为where user_id=1234;

partition table设计

  • 分区表的个数不建议超过1000个。

  • 分区表可以按使用频度选择不同的表空间。

  • 主键或唯一索引必须要包含分区键。

  • 对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。

  • 普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

  • 建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表。

  • 在分区表中不建议使用全局索引，因为做分区维护操作时可能会导致全局索引失效，造成难以维护。

分区表的使用

按照以下方式对范围分区表进行操作。

  • 创建表空间
MogDB=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
MogDB=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
MogDB=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
MogDB=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';

当结果显示为如下信息，则表示创建成功。

CREATE TABLESPACE
  • 创建分区表
MogDB=# CREATE TABLE mogdb_usr.customer_address
(
  ca_address_sk    integer          NOT NULL  ,
  ca_address_id    character(16)       NOT NULL  ,
  ca_street_number   character(10)            ,
  ca_street_name    character varying(60)        ,
  ca_street_type    character(15)            ,
  ca_suite_number   character(10)            ,
  ca_city       character varying(60)        ,
  ca_county      character varying(30)        ,
  ca_state       character(2)             ,
  ca_zip        character(10)            ,
  ca_country      character varying(20)        ,
  ca_gmt_offset    numeric(5,2)             ,
  ca_location_type   character(20)
)
TABLESPACE example1

PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;

当结果显示为如下信息，则表示创建成功。

CREATE TABLE

img 创建列存分区表的数量建议不超过1000个。

  • 插入数据

将表mogdb_usr.customer_address的数据插入到表mogdb_usr.customer_address_bak中。例如在数据库中创建了一个表mogdb_usr.customer_address的备份表mogdb_usr.customer_address_bak，现在需要将表mogdb_usr.customer_address中的数据插入到表mogdb_usr.customer_address_bak中，则可以执行如下命令。

MogDB=# CREATE TABLE mogdb_usr.customer_address_bak
(
  ca_address_sk    integer          NOT NULL  ,
  ca_address_id    character(16)       NOT NULL  ,
  ca_street_number   character(10)            ,
  ca_street_name    character varying(60)        ,
  ca_street_type    character(15)            ,
  ca_suite_number   character(10)            ,
  ca_city       character varying(60)        ,
  ca_county      character varying(30)        ,
  ca_state       character(2)             ,
  ca_zip        character(10)            ,
  ca_country      character varying(20)        ,
  ca_gmt_offset    numeric(5,2)             ,
  ca_location_type   character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
CREATE TABLE
MogDB=# INSERT INTO mogdb_usr.customer_address_bak SELECT * FROM mogdb_usr.customer_address;
INSERT 0 0
  • 修改分区表行迁移属性
MogDB=# ALTER TABLE mogdb_usr.customer_address_bak DISABLE ROW MOVEMENT;
ALTER TABLE
  • 删除分区

删除分区P8。

MogDB=# ALTER TABLE mogdb_usr.customer_address_bak DROP PARTITION P8;
ALTER TABLE
  • 增加分区

增加分区P8，范围为 40000<= P8<=MAXVALUE。

MogDB=# ALTER TABLE mogdb_usr.customer_address_bak ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
ALTER TABLE
  • 重命名分区

重命名分区P8为P_9。

MogDB=# ALTER TABLE mogdb_usr.customer_address_bak RENAME PARTITION P8 TO P_9;
ALTER TABLE

重命名分区P_9为P8。

MogDB=# ALTER TABLE mogdb_usr.customer_address_bak RENAME PARTITION FOR (40000) TO P8;
ALTER TABLE
  • 修改分区的表空间

修改分区P6的表空间为example3。

MogDB=#  ALTER TABLE mogdb_usr.customer_address_bak MOVE PARTITION P6 TABLESPACE example3;
ALTER TABLE

修改分区P4的表空间为example4。

MogDB=#  ALTER TABLE mogdb_usr.customer_address_bak MOVE PARTITION P4 TABLESPACE example4;
ALTER TABLE
  • 查询分区

查询分区P6。

MogDB=# SELECT * FROM mogdb_usr.customer_address_bak PARTITION (P6);
MogDB=# SELECT * FROM mogdb_usr.customer_address_bak PARTITION FOR (35888);
  • 删除分区表和表空间
MogDB=# DROP TABLE mogdb_usr.customer_address_bak;
DROP TABLE
MogDB=# DROP TABLESPACE example1;
MogDB=# DROP TABLESPACE example2;
MogDB=# DROP TABLESPACE example3;
MogDB=# DROP TABLESPACE example4;
DROP TABLESPACE

column设计

  • 避免与系统表的列名重复。

  • 字段含义及数据类型要与程序代码设计保持一致。

  • 所有字段必须要添加comment注释信息。

  • 能使用数值类型，就不要使用字符类型。

  • 禁止用字符类型存储日期数据。

  • 时间类型字段统一使用timestamptz。

  • 字段尽量要求not null，为字段提供默认值。

  • MogDB新建数据库默认兼容oracle，not null约束不允许传入空字符串，空字符串默认会转换为null，兼容PG模式的数据库不会有这个问题。

序列设计

  • 禁止手动创建与表相关的序列，应指定serial/bingserial类型方式创建。

  • 序列的步长建议设置为1。

  • 不建议设置minvalue和maxvalue。

  • 不建议设置cache，设置cache后序列号不连续。

  • 禁止开启cycle。

  • 序列应与代码中变量定义类型及范围一致，防止无法插入数据。

constraint设计

主键约束

  • 每个table必须包含主键。

  • 建议不要用有业务含义的名称作为主键，比如身份证或者国家名称，尽管其是unique的。

  • 建议主键的一步到位的写法：id serial primary key或id bigserial primary key。

  • 建议内容系统中size较大的table主键的等效写法如下，便于后续维护。

    create table test(id serial not null );
create unique index CONCURRENTLY ON test (id);

唯一约束

除主键外，需存在唯一性约束的，可通过创建以“uk_”为前缀的唯一索引实现。

外键约束

  • 存在外键关系的表上尽量创建外键约束。

  • 性能要求高而安全性自己控制的系统不建议使用外键。

  • 使用外键时，一定要设置fk的action，例如cascade、set null、set default。

非空列

  • 所有非空列必须添加not null约束

检查约束

  • 对于字段有检查性约束，一般要求指定check规则。例如：性别、状态等字段。

index设计

  • 频繁DML操作的表索引数量不建议超过5个。
  • create/drop index时添加concurrently参数。
  • 真正创建索引前可以使用虚拟索引确定索引的有效性。
  • 经常出现在关键字order by、group by、distinct后面的字段，建立索引。
  • 经常用作查询选择的字段，建立索引。
  • 经常用作表连接的属性上，建立索引。
  • 复合索引的字段数不建议超过3个。
  • 复合索引得一个字段是常用检索条件。
  • 复合索引第一个字段不应存在单字段索引。
  • 对数据很少被更新的表，经常只查询其中的几个字段，考虑使用索引覆盖。
  • 不要在有大量相同取值的字段上建立索引。
  • 建议用unique index代替unique constraints便于后续维护。
  • 建议对where中带多个字段and条件的高频query，参考数据分布情况，建多个字段的复合index。
  • 无用的索引以及重复索引应删除，避免对执行计划及数据库性能造成负面影响。

view设计

  • 尽量使用简单视图，尽可能少使用复杂视图。

    简单视图定义：数据来自单个表，且无分组（DISTINCT/GROUP BY）、无函数。

    复杂视图定义：数据来自多个表，或有分组，有函数，表的个数不能超过3个。

  • 尽量不要使用嵌套视图，如果必须使用，不能超过2层嵌套。

function设计

  • 函数必须检索数据库表记录或数据库其他对象，甚至修改（执行Insert、Delete、Update、Drop、Create等操作）数据库信息。

  • 如果某项功能不需要和数据库打交道，则不得通过数据库函数的方式实现。

  • 在函数中避免采用DML或DDL语句。

首页

反馈
Copyright © 2011-2024 www.enmotech.com All rights reserved.