数据库规范
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
增加事务异常处理机制
应用程序做好意外处理，及时做Rollback。
设置连接属性“set xact_abort on”
架构设计
读写分离
schema解耦
数据生命周期
读写分离
设计之初就考虑读写分离源自文库哪怕读写同一个库，有利于快速扩容
按照读特征把读分为实时读和可延迟读分别对应到写库和读库
读写分离应该考虑在读不可用情况下自动切换到写端
[Col1] IN(2,3,5,7)
LIKE查询的索引问题
1.[Col1] like “abc%”? –index seek ?这个就用到了索引查询
2.[Col1] like “%abc%”? –index scan ?而这个就并未用到索引查询
3.[Col1] like “%abc”? –index scan这个也并未用到索引查询
Schema解耦
禁止跨库JOIN
数据生命周期
根据数据的使用频繁度，对大表定期分库归档
主库/归档库物理分离
日志类型的表应分区或分表
对于大的表格要进行分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，大幅减少IO资源消耗
频繁写入的表，需要分区或分表
注释范例?
过程注释：
5.禁止使用SELECT *
6.减少内存消耗和网络带宽
7.给查询优化器有机会从索引读取所需要的列
8.表结构变化时容易引起查询出错
禁止在索引列上使用函数或计算
假设在字段Col1上建有一个索引，则下列场景将可以使用到索引：
[Col1]=
[Col1]>100
[Col1] BETWEEN 0 AND 99
[Col1] LIKE ‘abc%’
31.尽量减少使用ORDER BY与GROUP BY操作影响性能
32.所有写入上线程序的SQL语句，都必须提取保存并写清注释与所在位置模块
33.创建共享SQL语句，并写清帮助文档
34.避免变量循环赋值、使用
索引设计准则
1.应该对WHERE子句中经常使用的列创建索引
2.应该对经常用于连接表的列创建索引
10.SQL SERVER对索引字段的选择性有要求，如果选择性太低SQL SERVER会放弃使用
11.不适合创建索引的字段：性别、0/1、TRUE/FALSE
12.适合创建索引的字段：ORDERID、UID等
13.充分利用唯一索引
14.唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息，当查询分析器通过唯一索引查找到一条记录则会立刻退出，不会继续查找索引
parameter:p_textContext_in in varchar2参数描述?
parameter:p_textContext_outoutvarchar2参数描述?
create date:2003-04-1?
creater:创建人员
desc:过程总功能描述?
****************************************************************************/?
查询大量数据使用分页或TOP
合理限制记录返回数，避免IO、网络带宽出现瓶颈
递归查询层次限制
使用MAXRECURSION来防止不合理的递归CTE进入无限循环
尽量避免使用OR运算符
对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集
3.应该对ORDER BY子句中经常使用的列创建索引
4.不应该对小型的表（仅使用几个页的表）创建索引，这是因为完全表扫描操作可能比使用索引执行的查询快
5.单表索引数不超过6个
6.不要给选择性低的字段建单列索引
7.充分利用唯一约束
8.索引包含的字段不超过5个（包括include列）
9.不要给选择性低的字段创建单列索引
d)(3)对于复合索引，SQL语句必须使用主索引列?
e)(4)索引中，尽量避免使用NULL。?
f)(5)对于索引的比较，尽量避免使用NOT=（!=）?
g)(6)查询列和排序列与索引列次序保持一致
(7)禁止在更新频繁、区分度不高（如：性别）的字段上建立索引
(8)建立组合索引，必须把区分度高的字段放在前面
过程都以sp_开头，注意过程名称要符合命名要求?
参数：p_变量名_in（传入）、p_变量名_out（传出）
游标：cur_变量名
/**************************************************************************?
name:sp_Write_log
6.禁止使用外键，如有完整性约束，需要应用程序控制
7.禁止使用程序配置文件内的账号访问线上数据库
8.禁止非DBA对线上数据库进行写操作
9.开发、测试、线上环境分离
10.所以提交的SQL语句必须经过测试
11.禁止存储大文件或大照片
12.库名、表名、字段名：小写，下划线分割，不超过32个字符，必须见名知意，禁止拼音英文混用
禁止使用触发器
触发器对应用不透明（应用层面都不知道会什么时候触发触发器，发生也也不知道，感觉莫名……）
禁止在查询里指定索引
With(index=XXX)（?在查询里我们指定索引一般都用With(index=XXX) ??）
随着数据的变化查询语句指定的索引性能可能并不最佳
索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
新建的索引无法被应用立即使用，必须通过发布代码才能生效
变量/参数/关联字段类型必须与字段类型一致
避免类型转换额外消耗的CPU，引起的大表scan尤为严重
限制JOIN个数
单个SQL语句的表JOIN个数不能超过5个
过多的JOIN个数会导致查询分析器走错执行计划
过多JOIN在编译执行计划时消耗很大
限制IN子句中条件个数
(
SELECT DISTINCT
batchid,
companyid,
userid,
direction,
fromwkfid,
towkfid
FROM
T_WorkflowLog
WHERE
dotime BETWEEN '2008-09-02'
AND '2008-09-03' --医疗保险缴费时间
) AS twfl
name:v_Unit_Ms?
parameter:p_Name in varchar2参数描述?
create date:2003-04-1?
creater:创建人员
desc:视图描述
****************************************************************************/
在IN子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误8623或8632，要求IN子句中条件个数限制在100个以内
尽量避免大事务操作
只在数据需要更新时开始事务，减少资源锁持有时间
增加事务异常捕获预处理机制
使用UNION ALL替换UNION
UNION会对SQL结果集去重排序，增加CPU、内存等消耗
create date:2003-04-1?
creater:创建人员?
desc:函数总功能描述?
****************************************************************************/
视图注释?：
视图以v开头，命名符合命名标准?
/**************************************************************************
数据库相关规范
1.使用utf8mb4字符集
2.所有表、字段必须写清中文注释
3.金额字段禁止使用小数存储（单位：分）
4.禁止使用字段属性隐式转换（如：“WHERE ms_no = 1234”ms_no为字符串类型）
5.尽量不使用负向查询（NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等）
17.禁止使用SELECT *，只获取必要的字段
18.禁止使用INSERT INTO t_xxx VALUES(xxx)，必须指定插入的列名
19.禁止在WHERE条件的属性上使用函数或表达式
20.禁止%开头的模糊查询
21.禁止使用OR条件
22.应用程序必须捕获SQL异常，并作出相应处理
23.逻辑删除代替物理删除
WHERE
=
AND =
AND =
AND =
AND = 1 --人员类别（1：正式员工）
AND =
GROUP BY
,
,
,
;
15.表索引数不超过6个
16.表索引数不超过6个（这个规则只是携程DBA经过试验之后制定的。。。）
17.索引加快了查询速度，但是却会影响写入性能
18.一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
19.组合索引的原则是，过滤性越好的字段越靠前
20.索引过多不仅会增加编译时间，也会影响数据库选择最佳执行计划
我想从上而三个例子中，大家应该明白，最好不要在LIKE条件前面用模糊匹配，否则就用不到索引查询。
禁止使用游标
关系数据库适合集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能。
游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。
-- SQL语句编写格式样例
--关键词换行，换行后空两个空格
SELECT
, --用户名称
,
,
COUNT(1) trueworkload --单位缴费个人帐户个数
FROM
t_systemuserinfo ts, --医疗保险单位缴费表
t_genworkflow tg,
t_batch tb,
t_companyinfotmp tc,
12.使用UNION ALL替换UNION
13.查询大量数据使用分页或TOP
14.递归查询层级限制
15.NOT EXISTS替代NOT IN
16.尽量避免使用OR运算符
17.增加事务异常处理机制
禁止在数据库做复杂运算
1.XML解析
2.字符串相似性比较
3.字符串搜索（Charindex）
4.复杂运算在程序端完成
24.选择最有效的表名、查询条件顺序（从右到左）
25.减少访问数据库的次数
26.SQL中的关键字均使用大写字母，数据表最好起别名
27.查询条件中“>=”代替“>”
28.等号两边使用空格，逗号后使用空格
29.多表操作必须使用别名
30.整条语句必须写明注释，关键逻辑单独书写注释，说明算法、功能
a)注释风格：注释单独成行、放在语句前面。?
13.表必须有主键
14.必须把字段定义为NOT NULL并设置默认值
15.必须使用varchar（20）来存储手机号
16.单表索引控制在5个以内，单索引字段数不许超过5个
a)索引的使用。?
b)(1)尽量避免对索引列进行计算。如计算较多，请提请管理员建立函数索引。?
c)(2)尽量注意比较值与索引列数据类型的一致性。?
SQL查询
1.禁止在数据库做复杂运算
2.禁止使用SELECT *
3.禁止在索引列上使用函数或计算
4.禁止使用游标
5.禁止使用触发器
6.禁止在查询里指定索引
7.变量/参数/关联字段类型必须与字段类型一致
8.限制JOIN个数
9.限制SQL语句长度及IN子句个数
10.尽量避免大事务操作
11.关闭影响的行计数信息返回
函数注释：
函数以f开头，命名符合命名标准
/**************************************************************************?
name:f_Get_JobId?
parameter:p_Name in varchar2参数描述
return number:返回值描述
b)(1)应对不易理解的分支条件表达式加注释；?
c)(2)对重要的计算应说明其功能；?
d)(3)过长的函数实现，应将其语句按实现的功能分段加以概括性说明；?
e)(4)每条SQL语句均应有注释说明（表名、字段名）。?
f)(5)常量及变量注释时，应注释被保存值的含义(必须)，合法取值的范围(可选)?
g)(6)可采用单行/多行注释。（--或/* */方式）?
应用程序做好意外处理，及时做Rollback。
设置连接属性“set xact_abort on”
架构设计
读写分离
schema解耦
数据生命周期
读写分离
设计之初就考虑读写分离源自文库哪怕读写同一个库，有利于快速扩容
按照读特征把读分为实时读和可延迟读分别对应到写库和读库
读写分离应该考虑在读不可用情况下自动切换到写端
[Col1] IN(2,3,5,7)
LIKE查询的索引问题
1.[Col1] like “abc%”? –index seek ?这个就用到了索引查询
2.[Col1] like “%abc%”? –index scan ?而这个就并未用到索引查询
3.[Col1] like “%abc”? –index scan这个也并未用到索引查询
Schema解耦
禁止跨库JOIN
数据生命周期
根据数据的使用频繁度，对大表定期分库归档
主库/归档库物理分离
日志类型的表应分区或分表
对于大的表格要进行分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，大幅减少IO资源消耗
频繁写入的表，需要分区或分表
注释范例?
过程注释：
5.禁止使用SELECT *
6.减少内存消耗和网络带宽
7.给查询优化器有机会从索引读取所需要的列
8.表结构变化时容易引起查询出错
禁止在索引列上使用函数或计算
假设在字段Col1上建有一个索引，则下列场景将可以使用到索引：
[Col1]=
[Col1]>100
[Col1] BETWEEN 0 AND 99
[Col1] LIKE ‘abc%’
31.尽量减少使用ORDER BY与GROUP BY操作影响性能
32.所有写入上线程序的SQL语句，都必须提取保存并写清注释与所在位置模块
33.创建共享SQL语句，并写清帮助文档
34.避免变量循环赋值、使用
索引设计准则
1.应该对WHERE子句中经常使用的列创建索引
2.应该对经常用于连接表的列创建索引
10.SQL SERVER对索引字段的选择性有要求，如果选择性太低SQL SERVER会放弃使用
11.不适合创建索引的字段：性别、0/1、TRUE/FALSE
12.适合创建索引的字段：ORDERID、UID等
13.充分利用唯一索引
14.唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息，当查询分析器通过唯一索引查找到一条记录则会立刻退出，不会继续查找索引
parameter:p_textContext_in in varchar2参数描述?
parameter:p_textContext_outoutvarchar2参数描述?
create date:2003-04-1?
creater:创建人员
desc:过程总功能描述?
****************************************************************************/?
查询大量数据使用分页或TOP
合理限制记录返回数，避免IO、网络带宽出现瓶颈
递归查询层次限制
使用MAXRECURSION来防止不合理的递归CTE进入无限循环
尽量避免使用OR运算符
对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集
3.应该对ORDER BY子句中经常使用的列创建索引
4.不应该对小型的表（仅使用几个页的表）创建索引，这是因为完全表扫描操作可能比使用索引执行的查询快
5.单表索引数不超过6个
6.不要给选择性低的字段建单列索引
7.充分利用唯一约束
8.索引包含的字段不超过5个（包括include列）
9.不要给选择性低的字段创建单列索引
d)(3)对于复合索引，SQL语句必须使用主索引列?
e)(4)索引中，尽量避免使用NULL。?
f)(5)对于索引的比较，尽量避免使用NOT=（!=）?
g)(6)查询列和排序列与索引列次序保持一致
(7)禁止在更新频繁、区分度不高（如：性别）的字段上建立索引
(8)建立组合索引，必须把区分度高的字段放在前面
过程都以sp_开头，注意过程名称要符合命名要求?
参数：p_变量名_in（传入）、p_变量名_out（传出）
游标：cur_变量名
/**************************************************************************?
name:sp_Write_log
6.禁止使用外键，如有完整性约束，需要应用程序控制
7.禁止使用程序配置文件内的账号访问线上数据库
8.禁止非DBA对线上数据库进行写操作
9.开发、测试、线上环境分离
10.所以提交的SQL语句必须经过测试
11.禁止存储大文件或大照片
12.库名、表名、字段名：小写，下划线分割，不超过32个字符，必须见名知意，禁止拼音英文混用
禁止使用触发器
触发器对应用不透明（应用层面都不知道会什么时候触发触发器，发生也也不知道，感觉莫名……）
禁止在查询里指定索引
With(index=XXX)（?在查询里我们指定索引一般都用With(index=XXX) ??）
随着数据的变化查询语句指定的索引性能可能并不最佳
索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
新建的索引无法被应用立即使用，必须通过发布代码才能生效
变量/参数/关联字段类型必须与字段类型一致
避免类型转换额外消耗的CPU，引起的大表scan尤为严重
限制JOIN个数
单个SQL语句的表JOIN个数不能超过5个
过多的JOIN个数会导致查询分析器走错执行计划
过多JOIN在编译执行计划时消耗很大
限制IN子句中条件个数
(
SELECT DISTINCT
batchid,
companyid,
userid,
direction,
fromwkfid,
towkfid
FROM
T_WorkflowLog
WHERE
dotime BETWEEN '2008-09-02'
AND '2008-09-03' --医疗保险缴费时间
) AS twfl
name:v_Unit_Ms?
parameter:p_Name in varchar2参数描述?
create date:2003-04-1?
creater:创建人员
desc:视图描述
****************************************************************************/
在IN子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误8623或8632，要求IN子句中条件个数限制在100个以内
尽量避免大事务操作
只在数据需要更新时开始事务，减少资源锁持有时间
增加事务异常捕获预处理机制
使用UNION ALL替换UNION
UNION会对SQL结果集去重排序，增加CPU、内存等消耗
create date:2003-04-1?
creater:创建人员?
desc:函数总功能描述?
****************************************************************************/
视图注释?：
视图以v开头，命名符合命名标准?
/**************************************************************************
数据库相关规范
1.使用utf8mb4字符集
2.所有表、字段必须写清中文注释
3.金额字段禁止使用小数存储（单位：分）
4.禁止使用字段属性隐式转换（如：“WHERE ms_no = 1234”ms_no为字符串类型）
5.尽量不使用负向查询（NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等）
17.禁止使用SELECT *，只获取必要的字段
18.禁止使用INSERT INTO t_xxx VALUES(xxx)，必须指定插入的列名
19.禁止在WHERE条件的属性上使用函数或表达式
20.禁止%开头的模糊查询
21.禁止使用OR条件
22.应用程序必须捕获SQL异常，并作出相应处理
23.逻辑删除代替物理删除
WHERE
=
AND =
AND =
AND =
AND = 1 --人员类别（1：正式员工）
AND =
GROUP BY
,
,
,
;
15.表索引数不超过6个
16.表索引数不超过6个（这个规则只是携程DBA经过试验之后制定的。。。）
17.索引加快了查询速度，但是却会影响写入性能
18.一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
19.组合索引的原则是，过滤性越好的字段越靠前
20.索引过多不仅会增加编译时间，也会影响数据库选择最佳执行计划
我想从上而三个例子中，大家应该明白，最好不要在LIKE条件前面用模糊匹配，否则就用不到索引查询。
禁止使用游标
关系数据库适合集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能。
游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。
-- SQL语句编写格式样例
--关键词换行，换行后空两个空格
SELECT
, --用户名称
,
,
COUNT(1) trueworkload --单位缴费个人帐户个数
FROM
t_systemuserinfo ts, --医疗保险单位缴费表
t_genworkflow tg,
t_batch tb,
t_companyinfotmp tc,
12.使用UNION ALL替换UNION
13.查询大量数据使用分页或TOP
14.递归查询层级限制
15.NOT EXISTS替代NOT IN
16.尽量避免使用OR运算符
17.增加事务异常处理机制
禁止在数据库做复杂运算
1.XML解析
2.字符串相似性比较
3.字符串搜索（Charindex）
4.复杂运算在程序端完成
24.选择最有效的表名、查询条件顺序（从右到左）
25.减少访问数据库的次数
26.SQL中的关键字均使用大写字母，数据表最好起别名
27.查询条件中“>=”代替“>”
28.等号两边使用空格，逗号后使用空格
29.多表操作必须使用别名
30.整条语句必须写明注释，关键逻辑单独书写注释，说明算法、功能
a)注释风格：注释单独成行、放在语句前面。?
13.表必须有主键
14.必须把字段定义为NOT NULL并设置默认值
15.必须使用varchar（20）来存储手机号
16.单表索引控制在5个以内，单索引字段数不许超过5个
a)索引的使用。?
b)(1)尽量避免对索引列进行计算。如计算较多，请提请管理员建立函数索引。?
c)(2)尽量注意比较值与索引列数据类型的一致性。?
SQL查询
1.禁止在数据库做复杂运算
2.禁止使用SELECT *
3.禁止在索引列上使用函数或计算
4.禁止使用游标
5.禁止使用触发器
6.禁止在查询里指定索引
7.变量/参数/关联字段类型必须与字段类型一致
8.限制JOIN个数
9.限制SQL语句长度及IN子句个数
10.尽量避免大事务操作
11.关闭影响的行计数信息返回
函数注释：
函数以f开头，命名符合命名标准
/**************************************************************************?
name:f_Get_JobId?
parameter:p_Name in varchar2参数描述
return number:返回值描述
b)(1)应对不易理解的分支条件表达式加注释；?
c)(2)对重要的计算应说明其功能；?
d)(3)过长的函数实现，应将其语句按实现的功能分段加以概括性说明；?
e)(4)每条SQL语句均应有注释说明（表名、字段名）。?
f)(5)常量及变量注释时，应注释被保存值的含义(必须)，合法取值的范围(可选)?
g)(6)可采用单行/多行注释。（--或/* */方式）?