为了加强各系统软件开发标准规范，进一步提高各系统的稳定性，因此对现有的数据库开发规范进行了梳理，制定31条数据库开发管理规范.此规范主要是指导开发人员在进行开发时编写高效的SQL语句和以合适的方式创建表和索引，已达到系统在不断更新和升级时仍能保持良好的稳定性。

1 大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。

方法一：

insert into tablename values(1,2);

insert into tablename values(1,3);

insert into tablename values(1,4);

方法二：

Insert into tablename values(1,2),(1,3),(1,4);

选择后一种方法的原因有二。

1.减少SQL语句解析的操作， polardb没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；

2.SQL语句较短，可以减少网络传输的IO。

2 避免出现select *

select * 操作在任何类型数据库中都不是一个好的SQL开发习惯。

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议评估业务实际需要的列数，指定列名以取代select *

规范：

Select col1,col2,col3… from t1;

不规范：

Select * from t1;

3 避免出现count(*)

使用 SELECT 1 FROM tbl WHERE xxx LIMIT 1 判断是否存满足条件的列，要比Count快。

可以使用 select exists(select * FROM app.sjqq where xxx limit 1) 将存在性结果转换为布尔值。

4 优化group by语句

默认情况下，Polardb排序所有 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，Polardb可以毫不减速地对它进行优化，尽管仍然进行排序。

如果查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁止排序。

例如：

SELECT a, COUNT(1) FROM table GROUP BY a ORDER BY NULL;

5 优化order by语句

在某些情况中，Polardb 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者都是降序。

例如：下列 SQL 可以使用索引。

SELECT col1 FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT col1 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以上复合索引包含字段key_part1，key_part2...

但是以下情况不使用索引：

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

以上由于order by 的字段混合 ASC和 DESC

SELECT col1 FROM t1 WHERE key2=constant ORDER BY key1；

以上用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT col1 FROM t1 ORDER BY key1, key2；

对不同的索引关键字使用 ORDER BY：

6 优化join语句

Polardb中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：

假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID

FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM customerinfo

LEFT JOIN salesinfoON ON customerinfo.CustomerID=salesinfo.CustomerID

WHERE salesinfo.CustomerID IS NULL

7 优化or条件

对于 or 子句，如果要利用索引，则or 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

8 优化union查询

Posgresql通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，Polardb会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

SELECT COL1, COL2, COL3

FROM TABLE

WHERE COL1 = 10

UNION ALL

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效

SELECT COL1, COL2, COL3

FROM TABLE

WHERE COL1 = 10

UNION

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

9 优化IN操作符

· 使用 EXISTS 子句代替 IN 操作符，效果更佳。

· 使用 =ANY(ARRAY[1,2,3,4]) 代替 IN (1,2,3,4) ，效果更佳。

10 不建议使用左模糊搜索

· 左模糊搜索WHERE col LIKE '%xxx'无法充分利用B树索引，如有需要，可用reverse表达式函数索引。

11 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录pg_wal日志，当确认需要删除全表时，会产生很大量的pg_wa并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位。

12 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率

针对展现等分页需求，合适的分页方式能够提高分页的效率

案例1：

select * from t

where thread_id = 10000

and deleted = 0

order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

案例2：

select t.* from (

select id from t

where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t

where a.id = t.id;

上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

13 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用QUERY CACHE。

14 在线查询必须有配套索引

· 所有在线查询必须针对其访问模式设计相应索引，除极个别小表外不允许全表扫描。

· 索引有代价，不允许创建不使用的索引。

15 禁止在大字段上建立索引

· 被索引字段大小无法超过2KB（1/3的页容量），原则上禁止超过64个字符。

· 如有大字段索引需求，可以考虑对大字段取哈希，并建立函数索引。或使用其他类型的索引（GIN）

16 明确空值排序规则

· 如在可空列上有排序需求，需要在查询与索引中明确指定 NULLS FIRST 还是 NULLS LAST 。

· 注意， DESC 排序的默认规则是 NULLS FIRST ，即空值会出现在排序的最前面，通常这不是期望行为。

· 索引的排序条件必须与查询匹配，如： create index on tbl (id desc nulls last);

17 利用GiST索引应对近邻查询问题

· 传统B树索引无法提供对KNN问题的良好支持，应当使用GiST索引。

18 利用函数索引

· 任何可以由同一行其他字段推断得出的冗余字段，可以使用函数索引替代。

· 对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。

· 典型场景：建立大字段上的哈希函数索引，为需要左模糊查询的文本列建立reverse函数索引。

19 字符编码必须为UTF8

· 禁止使用其他任何字符编码。

20 容量规划

· 单表记录过亿，或超过10GB的量级，可以考虑开始进行分表。

· 单表容量超过1T，单库容量超过2T。需要考虑分片。

21 不要滥用存储过程

· 存储过程适用于封装事务，减少并发冲突，减少网络往返，减少返回数据量，执行少量自定义逻辑。

· 存储过程不适合进行复杂计算，不适合进行平凡/频繁的类型转换与包装。

22 主键与身份列

· 每个表都必须有身份列，原则上必须有主键，最低要求为拥有非空唯一约束。

· 身份列用于唯一标识表中的任一元组，逻辑复制与诸多三方工具有赖于此。

23 外键

· 不建议使用外键，建议在应用层解决。使用外键时，引用必须设置相应的动作： SET NULL , SET DEFAULT , CASCADE ，慎用级联操作。

24 慎用宽表

· 字段数目超过15个的表视作宽表，宽表应当考虑进行纵向拆分，通过相同的主键与主表相互引用。

· 因为MVCC机制，宽表的写放大现象比较明显，尽量减少对宽表的频繁更新

25 配置合适的默认值

· 有默认值的列必须添加 DEFAULT子句指定默认值。

· 可以在默认值中使用函数，动态生成默认值（例如主键发号器）。

26 合理应对空值

· 字段语义上没有零值与空值区分的，不允许空值存在，须为列配置NOT NULL约束。

27 唯一约束通过数据库强制

· 唯一约束须由数据库保证，任何唯一列须有唯一约束。

· EXCLUDE 约束是泛化的唯一约束，可以在低频更新场景下用于保证数据完整性。

28 主键类型

· 主键通常使用整型，建议使用BIGINT，允许使用不超过64字节的字符串。

· 主键允许使用Serial自动生成，建议使用Default next_id()发号器函数。

29 选择合适的类型

· 能使用专有类型的，不使用字符串。（数值，枚举，网络地址，货币，JSON，UUID等）

· 使用正确的数据类型，能显著提高数据存储，查询，索引，计算的效率，并提高可维护性。

30 使用枚举类型

· 较稳定的，取值空间较小（十几个内）的字段应当使用枚举类型，不要使用整型与字符串表示。

· 使用枚举类型有性能、存储、可维护性上的优势。

31 选择合适的文本类型

· PG的文本类型包括 char , char(n) , varchar ,varchar(n) , text 。除 char(n) 外并无本质存储区别。

· 带有 (n) 修饰符的类型会检查字符串长度，会导致微小的额外开销，对字符串长度有限制时应当使用 varchar(n) ，避免插入过长的脏数据。

· 避免使用 char(n) ，为了与SQL标准兼容，该类型存在不合直觉的行为表现，且并没有存储和性能优势。

Postgresql 31条数据库开发规范