重庆网站租赁空间,潮流设计网站,php中网站搜索功能实现,分销微信小程序数据库 数据库开发规范 也可用于PostgreSQL以及兼容PG的数据库 通用命名规则 【强制】 本规则适用于所有对象名#xff0c;包括#xff1a;库名、表名、列名、函数名、视图名、序列号名、别名等。
【强制】 对象名务必只使用小写字母#xff0c;下划线#xff0c;数字包括库名、表名、列名、函数名、视图名、序列号名、别名等。
【强制】 对象名务必只使用小写字母下划线数字首字母使用小写字母常规表禁止使用_打头。
【强制】 不使用双引号即包围除非必须包含大写字母或空格等特殊字符
【强制】 禁止使用SQL保留字可以先查看保留关键字列表。
【强制】 库名、表名限制命名长度建议表名及字段名字符总长度小于等于63。
【强制】 禁止出现美元符号禁止使用中文。
【推荐】 对象按类别带上标识表t_视图v_函数fun_临时表以tmp_开头。
【推荐】 主键索引应以pk_开头 唯一索引要以 uk_开头普通索引要以 i_开头。
字段命名规范 【强制】设计时字段统一 varcharTEXT字段以C_开头intnumeric以N_开头日期以D_开头时间以DT_开头数组以ARR_开头JSONB字段以J_开头
设计规范 整体设计要求
【强制】 数据库应使用UTF8字符集连接应指定UTF8字符集。
【强制】 数据库级别不设置默认值。
【强制】 表必须有主键或唯一约束。
【强制】 数据库不使用外键约束建议在应用层解决。
【强制】 禁止使用触发器。触发器会提高系统的复杂度与维护成本。
【强制】 为防止数据汇总依赖数据库本身的特性应进行业务数据的增量设计。
【强制】 业务表不允许存在逻辑删除标志位。
【强制】 业务表不做代码值代码名称此类简单冗余。
【强制】 设计逻辑外键主键和逻辑外键类型必须一致且逻辑外键必须定义索引。
注主键为VC32外键也为VC32CHAR和VARCHAR属于不同的类型
【强制】 多表中的相同列以及有JOIN需求的列必须保证列名一致数据类型一致。
【强制】 OLTP系统任意表最大列数不能超过80。
【强制】 多级子表逻辑外键设计要求。
一级子表要有主表的逻辑外键
二级子表要有主表、一级子表逻辑外键
三级子表要有主表、一级子表、二级子表逻辑外键
依次类推
【强制】 超大表建议考虑拆表。
大表业务数据预估超过500W考虑拆表视具体业务和架构师讨论决定 【强制】 使用分区表建议使用pg12及以上版本低版本更新和删除会扫描所有分区。
【强制】 同一表禁止双向复制。
【强制】 如果需要存储LOB类型结构化信息应与业务数据分开设计单独表存储不宜在数据库中存储非结构化信息。
【强制】 无论是开发环境还是生产环境应配置数据库连接池并且不应使用数据库管理员权限用户如sa作为数据库连接池用户数据库连接池应使用Druid最小连接数宜设置为20、最大连接数宜设置为50最小连接数和最大连接数的参数值不应相同。
【推荐】 pg使用JSONB类型存储JSON数据因jsonb是先解析再存储查询效率比json高。且jsonb支持许多额外的操作符。
【推荐】 pg建议使用default NULL而不用default 以节省存储空间 。
【推荐】 pg建议使用ip4ip4rip6ip6ripaddressiprange 来存储IPIP范围使用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 id1 and col?;– 其中id1为固定的条件 create index idx on tbl (col) where id1; 【推荐】 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对于频繁更新的表建议建表时指定表的fillfactor80每页预留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_sfyx1 GROUP BY c_ss_wg; c_sfyx为varcahr类型 5.禁止在where条件中添加1112这种表达式作为部分条件
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/310;可写成from t1 where c130 7.update语句禁止更新主键字段
update table set c_bh ‘123’n_ly2 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_ajbhzbaj.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_id2的这条数据 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必须有唯一索引
