SqlServer使用规范

SQL SERVER使用规范

一.        数据库命名规范................................................................................................... 3

1.     数据库............................................................................................................... 3

2.     数据对象........................................................................................................... 3

3.     字段.................................................................................................................. 3

4.     索引.................................................................................................................. 4

5.     视图.................................................................................................................. 4

二.        表结构.................................................................................................................. 4

1.     字段选择基本准则............................................................................................ 4

2.     常见的字段类型................................................................................................ 4

3.     常见的字段类型选择：.................................................................................... 5

4.     时间戳字段：datachange_createtime和datachange_lasttime.......................... 5

三.        约束与索引........................................................................................................... 6

1.     主键.................................................................................................................. 6

2.     不允许使用外键................................................................................................ 6

3.     NULL属性.......................................................................................................... 6

4.     索引设计准则................................................................................................... 6

5.     聚集索引建议................................................................................................... 7

四.        SQL查询规范........................................................................................................ 7

1.     SQL查询禁令.................................................................................................... 7

2.     SQL查询限制.................................................................................................... 9

3.     SQL查询常用优化........................................................................................... 10

五.        架构设计............................................................................................................. 11

1.     读写分离......................................................................................................... 12

2.     schema解耦.................................................................................................... 12

3.     数据生命周期.................................................................................................. 12

4.     分区或分表..................................................................................................... 12

5.     禁止新增复制分发链路................................................................................... 13

一. 数据库命名规范

1.    数据库

数据库的定义原则上对应在子系统一级，在不能满足实际需求的情况下也可以对应到具体的应用程序一级：

命名：采用“子系统名称&DB ”或“应用程序名称&DB ”，去除子系统或应用程序名称中的 “.” 符号。命名长度不可超过20个字符。

举例：

酒店产品子系统数据库：HtlProductDB

                   酒店产品子系统产品房态房价数据库：HtlProductRoomInfoDB

2.    数据对象

l  实体表：

命名：表名长度必须不超过30个字符，表命名只能使用26个英文字母、下划线，并且每个单词首字母大写其余小写

举例：O_orders

l  临时表 ：

命名： _del+ 当天时间 + 原表名

举例：_del201103030O_orders

l  分类状态位表

命名：统一使用 Dictionary

举例：Dictionary

l  订单迁移备份中转表

命名：子系统名_相关业务名_TransferOrderID_Change

举例：Payment_Casher_TransferOrderID_Change

l  订单相关表

订单相关表必须有OrderID字段，避免大量join

3.    字段

l  不同系统相同含义的字段使用同一命名，并且两者所有属性必须一致

命名： 字段命名长度必须不超过20个字符 ，只能使用26个英文字母、数字、下划线，并且每个单词首字母大写其余小写

举例： OrderDate

l  当遇到主键字段是自增类型的建议使用表名+ID 的形式

举例： O_orders 主键： OrderID

l  当字段表示为是否的意思时，建议使用 Is+XXX 的形式

举例：是否删除IsDeleted，是否可用IsEnabled

l  字段命名中不允许出现保留词

4.    索引

主键索引使用PK_表名命名，不可使用联合主键

非聚集索引必须使用idx_表名_x

举例： idx_O_Orders_OrderID

5.    视图

视图命名：v_相关内容命名

举例：v_Orders

二. 表结构

1.    字段选择基本准则

在满足业务需求的前提下，字段类型越短越好。

l  更少的存储空间

l  更少的磁盘IO

l  更少的网络流量

2.    常见的字段类型

l  常见的字段类型—精确数值

数 据 类 型	描 述	存 储 空 间
Bit	0、1或Null	1字节(8位)
Tinyint	0～255之间的整数	1字节
Smallint	–32768～32767之间的整数	2字节
Int	–2147483648～2147483647之间的整数	4字节
Bigint	–9223372036854775808～ 9223372036854775807 之间的整数	8字节
numeric(p,s)或decimal(p,s)	–1038＋1～1038–1之间的数值	最多17字节
Money	–922337203685477.5808～922337203685477.5807	8字节
Smallmoney	–214748.3648～214748.3647	4字节

 

l  常见的字段类型—近似数值

数 据 类 型	描 述	存 储 空 间
float[(n)]	–1.79E＋308~–2.23E–308,   0,   2.23E–308~1.79E＋308	N< =24－4字节 N> 24－8字节
real()	–3.40E＋38~–1.18E–38,   0,   1.18E–38~3.40E＋38	4字节

 

3.    常见的字段类型选择：

l  字符类型建议采用varchar/nvarchar数据类型

• 满足数据量增长
• 减少维护成本

l  金额货币建议采用money数据类型

l  科学计数建议采用numeric数据类型

l  自增长标识建议采用bigint数据类型

l  时间类型建议采用为datetime数据类型

l  禁止使用text、ntext、image老的数据类型

• 这些类型后续SQL版本将不再支持

l  禁止使用XML数据类型

• XML数据解析会消耗大量CPU和内存资源

l  禁止使用varchar(max)、nvarchar(max)等数据类型

4.    时间戳字段：datachange_createtime和datachange_lasttime

l  新建表中的时间戳字段设计时必须指定默认属性：GetDate() NOT NULL

l  已有表新增时间戳字段设计时必须指定默认属性： GetDate() NULL

• 方便BI取数，方便用户行为分析
• 方便增量建CACHE
• 实际业务也大量需要这两个数据

 

三. 约束与索引

1.    主键

l  每张表必须有主键，用于强制实体完整性

l  单表只能有一个主键（不允许为空及重复数据）

l  尽量使用单字段主键

l  建议使用聚集类型主键

2.    不允许使用外键

l  外键增加了表结构变更及数据迁移的复杂性

l  外键对插入，更新的性能有影响，需要检查主外键约束

l  数据一致性由程序控制

3.    NULL属性

l  新加的表，所有字段禁止NULL

• 允许NULL值，会增加应用程序的复杂性。必须得增加特定的逻辑代码，以防止出现各种意外的bug

l  旧表新加字段，需要允许为NULL

• 避免全表数据更新 ，长期持锁阻塞导致阻塞

4.    索引设计准则

l  应该对 WHERE 子句中经常使用的列创建索引

l  应该对经常用于连接表的列创建索引

l  应该对 ORDER BY 子句中经常使用的列创建索引

l  不应该对小型的表（仅使用几个页的表）创建索引，这是因为完全表扫描操作可能比使用索引执行的查询快

l  单表索引数不超过6个

l  不要给选择性低的字段建单列索引

• 为了维持Btree会带来大量的数据移动
• 数据库引擎内部更新聚集索引动作会转换为一次删除和一次插入
• 适合创建索引的字段：ORDERID、UID等

l  充分利用唯一约束

• 唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息，当查询分析器通过唯一索引查找到一条记录则会立刻退出，不会继续查找索引

l  索引包含的字段不超过5个（包括include列）

• 索引加快了查询速度，但是却会影响写入性能
• 一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
• 组合索引的原则是，过滤性越好的字段越靠前，更新频繁的字段放入include列
• 索引过多不仅会增加编译时间，也会影响数据库选择最佳执行计划

l  聚集索引建议

• 字段长度小
• 字段发生的更改小
• 字段值递增，如自增长，时间字段
• 字段值唯一（这个是必要条件）
• 尽量使用单字段主键
• 根据业务选择有意义的主键
• 不要修改聚集索引

u  为了维持Btree会带来大量的数据移动

u  数据库引擎内部更新聚集索引动作会转换为一次删除和一次插入

四. SQL查询规范

1.    SQL查询禁令

l  禁止在数据库做复杂运算

• XML解析
• 字符串相似性比较
• 字符串搜索（Charindex）
• 复杂运算在程序端完成

l  禁止使用SELECT *

• 减少内存消耗和网络带宽
• 给查询优化器有机会从索引读取所需要的列
• 表结构变化时容易引起查询出错

l  禁止在索引列上使用函数或计算

• 在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描

举例：假设在字段Col1上建有一个索引，则下列场景将无法使用到索引

ABS[Col1]=1

[Col1]+1>9

[Col1] LIKE ‘%abc’

举例：假设在字段Col1上建有一个索引，则下列场景将可以使用到索引：

[Col1]=3.14

[Col1]>100

[Col1] BETWEEN 0 AND 99

[Col1] LIKE ‘abc%’

[Col1] IN(2,3,5,7)

l  LIKE查询尽量使用前缀查询

• like只能使用前缀索引，因此 ：

col like "abc%" 能用上索引

col like "%abc%" 不能用上索引

col like "%abc" 不能用上索引

 

l  禁止使用游标

• 关系数据库适合集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能。
• 游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。

l  禁止使用触发器

• 不要使用触发器(Trigger), 在存储过程中实现触发器逻辑

l  禁止在查询里指定索引

• 随着数据的变化查询语句指定的索引性能可能并不最佳
• 索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
• 新建的索引无法被应用立即使用，必须通过发布代码才能生效

l  禁止变量/参数/关联字段类型与字段类型不一致

• 避免类型转换额外消耗的CPU，引起的大表scan尤为严重

                                            

• C#中的string数据类型对应的字符集为UNICODE，在数据库中显示为N’ABC’,如数据库字段为Varchar/Char，则会产生额外的CPU消耗
• C#中的anistring数据类型对应的字符集为非UNICODE，在数据库中显示为’ABC’，如数据库字段为Varchar/Char，则不会产生额外的CPU消耗

l  禁止非参数化查询

• 以下方式可以对查询SQL进行参数化：

u  sp_executesql：

 

u  Prepared Queries：

                    

u  Stored procedures：

 

2.    SQL查询限制

l  限制JOIN个数

• 单个SQL语句的表JOIN个数不能超过5个
• 过多的JOIN个数会导致查询分析器走错执行计划
• 过多JOIN在编译执行计划时消耗很大

l  限制SQL语句长度及IN子句个数

• 在 IN 子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误 8623 或 8632，要求IN子句中条件个数限制在100个以内

l  限制TVP包含的行数

• 行数限制在200以内

l  限制大事务操作

• 只在数据需要更新时开始事务，减少资源锁持有时间
• 增加事务异常捕获预处理机制
• 禁止使用数据库上的分布式事务

l  限制递归查询层级

• 使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环

l  限制大量数据查询，使用分页或TOP

• 合理限制记录返回数，避免IO、网络带宽出现瓶颈

l  除非业务特殊需要，SELECT语句都必须加上NOLOCK

• 指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设  置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）

 

l  关闭影响的行计数信息返回

• 在SQL语句中显示设置Set Nocount On，取消影响的行计数信息返回，减少网络流量

 

 3. SQL查询常用优化

l  NOT EXISTS替代NOT IN

• 对于NOT IN与NOT EXISTS，并非等价替换，只有当子查询中不含有NULL值时，二者才会产生同样的结果；若子查询中有NULL值，NOT IN将返回不正确的空集结果，而NOT EXISTS将返回正确的结果。
• 因为NOT IN (Value1, Value2, NULL)，将返回NOT TRUE或者NOT UNKNOWN(NOT UNKNOWN 等同于UNKNOWN，还是false)，都是非TRUE条件，所以始终返回空集
• 对于NOT IN, NOT EXISTS，考虑通过外连接，并判断为空来实现，连接的查询条件通过索引查找

l  使用UNION ALL替换UNION

• UNION会对SQL结果集去重排序，增加CPU、内存等消耗

l  临时表与表变量

• 临时表具有统计信息，可以在临时表上建立索引，而表变量没有统计信息，不能建索引。临时表对大数据能做更多优化，但维护成本较高，因此临时表适合于返回大的结果集；表变量适合于小的结果集

l  使用本地变量选择中庸执行计划

• 在存储过程或查询中，访问了一张数据分布很不平均的表格，这样往往会让存储过程或查询使用了次优甚至于较差的执行计划上，造成High CPU及大量IO Read等问题，使用本地变量防止走错执行计划。
• 采用本地变量的方式，SQL在编译的时候是不知道这个本地变量的值，这时候SQL会根据表格里数据的一般分布，“猜测”一个返回值。不管用户在调用存储过程或语句的时候代入的变量值是多少，生成的计划都是一样的。这样的计划一般会比较中庸一些，不一定是最优的计划，但一般也不会是最差的计划
• 如果查询中本地变量使用了不等式运算符，查询分析器使用了一个简单的 30% 的算式来预估

Estimated Rows =(Total Rows * 30)/100

• 如果查询中本地变量使用了等式运算符，则查询分析器使用：精确度 * 表记录总数来预估

Estimated Rows = Density * Total Rows

l  尽量避免使用OR运算符

• 对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集

l  增加事务异常处理机制

• 应用程序做好意外处理，及时做Rollback。
• 设置连接属性 "set xact_abort on"

l  输出列使用二段式命名格式

• 二段式命名格式：表名.字段名
• 有JOIN关系的TSQL，字段必须指明字段是属于哪个表的，否则未来表结构变更后，有可能发生Ambiguous column name的程序兼容错误

l  SQL中应添加合理的注释，便于定位故障

• 注释中标识出AppID或源代码路径，便于出现问题时排障
• 注释中不应添加当前时间等容易变化的信息，避免SQL Server认为是不同的SQL而反复编译

 

五. 架构设计

1.    读写分离

l  设计之初就考虑读写分离，哪怕读写同一个库，有利于快速扩容

l  按照读特征把读分为实时读和可延迟读分别对应到写库和读库

l  读写分离应该考虑在读不可用情况下自动切换到写端

 

2.    schema解耦

l  禁止跨库JOIN

3.    数据生命周期

l  根据数据的使用频繁度，对大表定期分库归档

l  主库/归档库物理分离

4.    分区或分表

l  日志类型的表应分区或分表

• 对于大的表格要进行分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，大幅减少IO资源消耗

l  频繁写入的表，需要分区或分表

• 自增长与Latch Lock

自增长会引发闩锁（Latch Lock），闩锁是sql Server自己内部申请和控制，用户没有办法来干预，用来保证内存里面数据结构的完整性，锁级别是页级锁

 

 

• 减少Latch Lock争用的方法：建立分区表或一张物理表拆分多张物理表

 

5.    禁止新增复制分发链路

l  减少复杂度及维护成本

l  减少故障恢复耗时

l  新版本SQL Server将不再支持复制分发