版本：Next

数据库对象设计规范

建表规范

【强制】表必须有主键。

【强制】唯一索引，需要唯一索引所有字段非空（避免出现多条空值的重复记录）。

【强制】多表中的相同列，必须保证列名一致，数据类型一致。

【强制】使用外键时，必须要对foreign key建立索引，否则可能影响references列的更新或删除性能。

【强制】使用外键时，一定要设置fk的action，例如cascade，set null，set default。

【强制】对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。

【推荐】为了全球化的需求，所有的字符存储与表示，均以UTF-8编码

【推荐】对频繁访问的大表（通常指超过8GB的表，或者超过1000万记录的表）进行分区，从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等。单表过大，还需要考虑freeze等问题。

表字段设计规范

字段类型定义规范

整数类型：smallint(2字节)，integer(4字节)，bigint(8字节)
浮点类型：numeric
日期类型：date
时间类型：time
时间戳类型： timestamp
字符串类型：varchar
文本、长文本类型：text
二进制、长二进制类型：bytea

各类型的详细说明参考HALO数据类型定义说明。

【强制】不使用复杂数据类型和自定义类型

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

【推荐】设计时应尽可能选择合适的数据类型，能用数字的坚决不用字符串，能用树类型的，坚决不用字符串。使用好的数据类型，可以使用数据库的索引，操作符，函数，提高数据的查询效率。

【推荐】在设计表结构时，建议规划好，避免经常需要添加字段，或者修改字段类型或长度。某些操作可能触发表的重写，例如加字段并设置默认值，修改字段的类型。如果用户确实不好规划结构，建议使用jsonb数据类型存储用户数据。

索引设计规范

主键准则

【强制】表必须有主键，唯一索引必须有非空约束。

【推荐】不使用更新频繁的列作为主键。

【推荐】主键越短越好，最好是整型，避免选择字符串列作为主键，禁止使用应用定义随机类型值。

索引规范

【推荐】选择区分度大的列建立索引，不在低基数列上建立索引，例如：“性别”，“是否XX标志”。

【强制】索引null的位置定义必须与排序定义一致，否则可能导致索引不能使用。

【推荐】对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。

【推荐】范围查询，应该尽量使用范围类型，以及GIST索引，提高范围检索的查询性能。

【强制】如何并行创建索引，不堵塞表的DML，创建索引时加CONCURRENTLY关键字，就可以并行创建，不会堵塞DML操作，否则会堵塞DML操作。

例如：create index CONCURRENTLY idx on tbl(id);

【推荐】如何加快创建索引的速度，调大maintenance_work_mem，可以提升创建索引的速度，但是需要考虑实际的可用内存。

【推荐】线上表结构的变更包括添加字段，索引操作在业务低峰期进行。

【推荐】应尽量避免在where 子句中使用 != 或 <> 操作符，否则优化器将放弃使用索引而进行全表扫描。

如果业务确实有这种需求的查询，可以有几种优化方法：

部分索引

这个是最有效的方法，可以使用到索引扫描，如果有其他条件，也可以在其他条件的索引上建立partial index.

create index idx1 on tbl (id) where cond1 <> xx;

分区表

使用分区表，如果有 != 的查询条件，Halo会根据分区约束，避免扫描不需要扫描的表。

约束

 set constraint_exclusion=on;

 select * from tbl where cond1 <> xx;

在查询列上有约束的情况下，如果 != 或 <> 与约束违背，则可以提前返回查询，不会扫描表。

【强制】btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。

【推荐】b-tree索引优化，不建议对频繁访问的数据上使用非常离散的数据，例如UUID作为索引，索引页会频繁的分裂，重锁，重IO和CPU开销都比较高。
如何降低频繁更新索引字段的索引页IO，设置fillfactor为一个合适的值，默认90已经适合大部分场景。

【推荐】单张表的索引数量控制在5个以内，建议优先考虑覆盖索引，避免冗余索引。

【推荐】索引中的字段数建议不超过5个。

【推荐】唯一索引建议由 3 个或更少的字段组成。

【推荐】尽量不要在频繁更新的列上创建索引。

【推荐】最左前缀原则，使用联合索引时，从左向右匹配，比如索引 idx_c1_c2_c3 (c1,c2,c3)，相当于创建了 (c1)、(c1,c2)、(c1,c2,c3) 三个索引，where 条件包含上面三种情况的字段比较则可以用到索引且效率很高，但像 where c2=b and c3=c 或者 where c3=c作为查询条件的情况下，虽然可以使用到索引，但需要扫描更多的索引块，相比使用包含c1列的查询性能会降低；另外最好将区分度高的字段放在首位c1。

【推荐】ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面。

【推荐】当有规则表达式查询，或者文本近似度查询的需求时，建议对字段使用trgm gin索引，提升近似度匹配或规则表达式匹配的查询效率，同时覆盖了前后模糊的查询需求。如果没有创建trgm gin索引，则不推荐使用前后模糊查询例如like '%abc%'。

create extension pg_trgm;
create table t1(id int,c1 text);
create index t1_c1_gin_idx on t1 using gin(c1 gin_trgm_ops);
explain select * from t1 where c1 like '%羲和数据库%';

【强制】不建议在where条件索引列上使用数学运算或函数，会导致索引失效，如 lower(email)，如无法避免考虑使用函数索引。

建表规范​

表字段设计规范​

字段类型定义规范​

索引设计规范​

主键准则​

索引规范​