sql调优之数据库开发规范

数据库
数据库开发规范
也可用于PostgreSQL以及兼容PG的数据库
通用命名规则
【强制】 本规则适用于所有对象名，包括：库名、表名、列名、函数名、视图名、序列号名、别名等。

【强制】 对象名务必只使用小写字母，下划线，数字，首字母使用小写字母，常规表禁止使用_打头。

【强制】 不使用双引号即"包围，除非必须包含大写字母或空格等特殊字符

【强制】 禁止使用SQL保留字，可以先查看保留关键字列表。

【强制】 库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。

【强制】 禁止出现美元符号，禁止使用中文。

【推荐】 对象按类别带上标识，表t_，视图v_，函数fun_，临时表以tmp_开头。

【推荐】 主键索引应以pk_开头， 唯一索引要以 uk_开头，普通索引要以 i_开头。

字段命名规范
【强制】设计时字段统一， varchar，TEXT字段以C_开头，int，numeric以N_开头，日期以D_开头，时间以DT_开头，数组以ARR_开头，JSONB字段以J_开头

设计规范
整体设计要求

【强制】 数据库应使用UTF8字符集，连接应指定UTF8字符集。

【强制】 数据库级别不设置默认值。

【强制】 表必须有主键或唯一约束。

【强制】 数据库不使用外键约束，建议在应用层解决。

【强制】 禁止使用触发器。触发器会提高系统的复杂度与维护成本。

【强制】 为防止数据汇总依赖数据库本身的特性，应进行业务数据的增量设计。

【强制】 业务表不允许存在逻辑删除标志位。

【强制】 业务表不做代码值，代码名称此类简单冗余。

【强制】 设计逻辑外键，主键和逻辑外键类型必须一致，且逻辑外键必须定义索引。

注：主键为VC32外键也为VC32，CHAR和VARCHAR属于不同的类型

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

【强制】 OLTP系统，任意表最大列数不能超过80。

【强制】 多级子表逻辑外键设计要求。

一级子表要有主表的逻辑外键

二级子表要有主表、一级子表逻辑外键

三级子表要有主表、一级子表、二级子表逻辑外键

依次类推

【强制】 超大表建议考虑拆表。

大表（业务数据预估超过500W）考虑拆表，视具体业务和架构师讨论决定
【强制】 使用分区表，建议使用pg12及以上版本，低版本更新和删除会扫描所有分区。

【强制】 同一表禁止双向复制。

【强制】 如果需要存储LOB类型结构化信息，应与业务数据分开，设计单独表存储；不宜在数据库中存储非结构化信息。

【强制】 无论是开发环境还是生产环境，应配置数据库连接池，并且不应使用数据库管理员权限用户（如sa）作为数据库连接池用户；数据库连接池应使用Druid，最小连接数宜设置为20、最大连接数宜设置为50，最小连接数和最大连接数的参数值不应相同。

【推荐】 pg使用JSONB类型存储JSON数据，因jsonb是先解析再存储，查询效率比json高。且jsonb支持许多额外的操作符。

【推荐】 pg建议使用default NULL而不用default ''以节省存储空间 。

【推荐】 pg建议使用ip4，ip4r，ip6，ip6r，ipaddress，iprange 来存储IP，IP范围；使用macaddr来存储MAC (Media Access Control) address。

精度设计要求
【强制】 UUID类型主外键，设置为VC32。

【强制】 数字原则上只有integer和numeric(20,4)。对于长度小于10位（不包括）的数值字段应使用integer类型；大于10位的数值字段应使用number或 numeric ；金额应以”元”为单位存储，使用number(20,4)存储。

【强制】 文件大小设置为numeric(20)，单位为byte。

【强制】 金额，单位为元。设置为numeric(20,4)。

【强制】 使用varchar(N)，不使用char(N)，有利于节省空间。

【强制】 VC精度控制，推荐只有32/100/300/600/900五种精度。文本字段设计要求，主键VC32、单值代码VC100、其他文本按长度应设计为VC300、VC600、VC900，超出VC900范围应采用TEXT类型。

【强制】 名称汉字设置为VC300。

【强制】 外部存储文件的文件显示名称、文件名设置为VC300，存储路径设置为VC600。

【强制】 没有精度要求的，不要写精度：int/date/datetime/text等。

【强制】 timestamp类型精度设置为3。

和java程序数据类型精度保持一致
【强制】 密码不用二进制类型，用16进制字符串。

业务设计要求
【强制】 多表中同含义字段，名称、类型、精度必须统一。

【强制】 字段名称、字段中文描述、说明含义必须一致。

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

【强制】 明确空值排序规则

如在可空列上有排序需求，需要在查询与索引中明确指定NULLS FIRST还是NULLS LAST。
注意，DESC排序的默认规则是NULLS FIRST，即空值会出现在排序的最前面，通常这不是期望行为。
索引的排序条件必须与查询匹配，如：create index on tbl (id desc nulls last);
【强制】 禁止对数据库同一字段建立多个相同类型的索引

【推荐】 pg利用部分索引，对于固定条件的查询，可以使用部分索引，减少索引的大小，同时提升查询效率。

select * from tbl where id=1 and col=?;– 其中id=1为固定的条件
create index idx on tbl (col) where id=1;
【推荐】 pg利用函数索引，对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。

select * from test where exp(xxx);

create index on test ( exp(xxx) );

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

【推荐】 当用户有prefix或者suffix的模糊查询需求时，pg可以使用索引，或反转索引来提升查询效率

【推荐】 pg10以前不建议使用Hash索引

【推荐】 建议用unique index代替unique constraints，便于后续维护，constraints不能使用CONCURRENTLY

【推荐】 建议不要建过多index，一般不要超过6个，核心table可适当增加index个数

其他
【推荐】 pg不建议使用public schema(不同业务共享的对象可以使用public schema)，应该为每个应用分配对应的schema。

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

SQL规范
【强制】 INSERT语句必须要穷举所有插入的字段名称

【强制】 数据行删除/更新使用delete/update时，必须带上WHERE子句

【强制】 禁止使用SELECT *查询，应穷举所有要查询的字段名

【强制】 不要使用count(列名)或count(常量)来代替count(*)

【强制】 SQL脚本应使用in来处理的情况，in里面的枚举个数不应超过100。

【强制】 新立项的OLTP产品/ 项目中， SQL长度限制原则为： 90%的SQL长度<=500字符； SQL最大长度不应超过1K。

【强制】 SQL中的变量应使用绑定变量（prepareStatement），避免SQL注入，并且提高数据库执行的效率。

【强制】 一般页面的SQL请求个数应在10之内，首页、统计页等页面的SQL请求个数应在50以内。

【强制】 不应在循环中执行查询（select）语句，应统一查询出来后处理；宜避免在循环中调用第三方接口。

【强制】 抽样查询禁止使用ORDER BY random()，abase建议使用tablesample system/tablesample bernoulli

【强制】 禁止使用=NULL/!=NULL，不是标准语法，判空写法为IS NULL/IS NOT NULL。

【强制】 分页查询语句全部都需要带有排序条件，且排序的字段需要唯一，除非业务方明确要求不要使用任何排序来随机展示数据

【强制】 在函数中，或程序中，不要使用count(*)判断是否有数据，很慢。 建议的方法是limit 1

【推荐】 建议将单个事务的多条SQL操作、分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量lock少的资源，避免lock、deadlock的产生

【推荐】 如果需要清除全表数据，建议使用truncate删除所有的行，但使用ARRS需要注意truncate不记录事务日志

【推荐】 建议复杂的统计查询可以尝试窗口函数Window Functions

【推荐】 相同字段的OR条件，建议使用IN代替

【推荐】不同字段的OR条件如果字段都有独立索引可以走索引，sybase建议使用UNION ALL代替

【推荐】 不建议使用NOT IN，建议使用NOT EXISTS

【推荐】 尽量避免在SELECT子句中使用子查询，替换为连接查询

【推荐】 应按照业务需要使用事务，同时应保持事务简短，避免大事务

【推荐】 SQL 语句尽可能避免超过 4 张表以上的联合复杂查询

【推荐】 应该尽量在业务层面避免死锁的产生，避免多个线程处理同一条数据

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

管理规范
【强制】 关注备份

每日全量备份，重要数据做实时增量备份，和异地备份，定期做备份还原验证
【强制】禁用trust认证

生产环境禁止在pg_hba.conf中配置trust认证（trust标识不需要密码就可以登陆，即使密码是错误的）
低版本建议使用md5，推荐使用scram-sha-256
【强制】 关注年龄

abase关注数据库与表的年龄，避免事物ID回卷。
【强制】 关注老化与膨胀

关注表与索引的膨胀率，避免性能劣化。
【强制】 关注复制延迟

监控复制延迟，使用复制槽时更必须十分留意。
【强制】 遵循最小权限原则

【强制】 并发地创建与删除索引

建议create或 drop index时、加 CONCURRENTLY参数，达到与写入数据并发的效果

对于生产表，必须使用CREATE INDEX CONCURRENTLY并发创建索引。

【强制】 审慎地进行模式变更

添加新列时必须使用不带默认值的语法，避免全表重写
变更类型时，必要时应当重建所有依赖该类型的函数。
【推荐】 切分大批量操作

大批量写入操作应当切分为小批量进行，避免一次产生大量WAL。
【推荐】 加速数据加载
先加载数据后创建索引

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

附：SQL查询优化
在书写sql的过程中，可谓百花争鸣。虽然最后都得到了相同的结果集但过程不一样，效率也不一样。为了避免这种情况所以制定一个sql书写规范

1.count问题、去除多余的外层嵌套count(*)

select count (*)
FROM
(SELECT aj.C_BH,aj.C_AH,aj.C_BQJG,aj.N_ZZM,aj.N_XFBGLX,aj.N_CBR,aj.C_ZMZS,aj.C_DSR
FROM
T_XS_AJ aj
WHERE
aj.N_XFBGLX IN (1, 2)
AND aj.N_SPCX = 12
AND aj.N_CBR = 157286789
AND aj.DT_FASJ IS NOT NULL
AND aj.N_AJJZJD = 10
) t_19b4ae3444
从执行计划来看数据库会自动提升子查询，效率上没有差别

2.严禁使用select * 形式的语句

select xtgn.* from DB_ZXXT…T_XTGN xtgn where xtgn.N_FY = 1;
消耗更多的CPU和io以及网络带宽资源
无法使用覆盖索引
可减少表结构变更带来的影响
必须指出select的具体字段、如select col1,col2,…from table1 where …;
3.统一使用select count(*)

不使用select count(1)，select count(0)，select count(col)，select count(c_bh)
4.禁止隐含的数据类型转换

string类型传入了int类型或者numberic和int类型相加
SELECT COUNT(*) n_scs,c_ss_wg FROM wgxt.t_sc_sx WHERE c_sfyx=1 GROUP BY c_ss_wg;
c_sfyx为varcahr类型
5.禁止在where条件中添加1=1，1=2这种表达式作为部分条件

LEFT JOIN db_rmtj.t_tjgzgl gl ON tcgz.c_bh = gl.c_gzbh
WHERE 1 = 1 AND t1.c_pdm = ‘1301’
6.禁止在where子句中对列进行计算、数据库函数、计算表达式等放置在等号右边

from t1 where c1/3>10;可写成from t1 where c1>30
7.update语句禁止更新主键字段

update table set c_bh =‘123’，n_ly=2 where c_bh = ‘123’
如果再gp中c_bh作为分布键，该语句会报错。
8.插入需要指定列名insert into table values(?,?,?)
必须指出具体对应的列、即insert into table (col1,col2…) values (?,?.)
Inert into db_zxzhld.t_zhld_db values (…
解读：容易在增加或删除字段后出现程序bug
9.字段取别名使用as，而非空格

from db_zxzhld.t_zhld_db as db
10.连接多个表时，请使用表的别名并把前缀用于每个列上，这样可以减少解析时间，并且减少列歧义引起的语法错误。

11.建议在sql中使用多行注释、单行注释后期在排查sql问题时不知道那部分sql是已经注释的、建议使用/ /。

ajxx.c_ajbh=zbaj.c_ajbh --)res --WHERE res.c_bh is null or (res.c_bh is not null AND res.c_ajbh is null)
12.or和and共同使用时建议多打一对括号、默认and优先级高于or

tydftftjl0_.DT_KSSJ<=‘07/19/2017 16:25:00.000’ and
tydftftjl0_.DT_JSSJ>‘07/19/2017 16:25:00.000’ or
tydftftjl0_.DT_KSSJ<‘07/19/2017 16:30:00.000’ and
tydftftjl0_.DT_JSSJ>=‘07/19/2017 16:30:00.000’ or
tydftftjl0_.DT_KSSJ>‘07/19/2017 16:25:00.000’ and
tydftftjl0_.DT_JSSJ<‘07/19/2017 16:30:00.000’
13.建议使用预编译语句进行数据库操作
好处：只传参数，比传递SQL语句更高效
相同语句可以一次解析，多次使用，提高处理效率
如果用户需要在插入数据和，删除数据前，或者修改数据后马上拿到插入或被删除或修改后的数据，建议使用RETURNING子句，减少数据库交互次数。
– returning后面可以返回 * 、指定某几个字段 或者 主键等
– 插入，返回n_id
db_sqlfx=# insert into test(n_id) values(1) RETURNING n_id;
insert into test(n_id) values(1) RETURNING *;
– 更新
update test set c_name= ‘李四’ where n_id = 1 RETURNING n_id;

– 删除
delete from test where n_id = 1 RETURNING n_id;
15.abase推荐使用UPSERT简化逻辑

upsert语法类似于oracle的merge语法，数据不存在则插入，存在则更新

–查看n_id=2的这条数据
db_sqlfx=# select * from test where n_id = 2;

–不做任何操作，也不会报错
db_sqlfx=# insert into test(n_id,c_name) values(2,‘lisi’) on conflict(n_id) do nothing;
–如果删除主键
db_sqlfx=# alter table test drop constraint test_pkey;
ALTER TABLE
db_sqlfx=# insert into test(n_id,c_name) values(2,‘lisi’) on conflict(n_id) do update set c_name = ‘lisi’ where test.n_id = 2;
错误: 没有匹配ON CONFLICT说明的唯一或者排除约束
注意：conflict(n_id)中n_id必须有唯一索引