数据库使用规范

一. 数据库命名规范
命名规则可以再详细一点

1. 数据库
   数据库的定义原则上对应在子系统一级，在不能满足实际需求的情况下也可以对应到具体的应用程序一级：
   命名：采用“系统名称_子系统名称_db ”或“系统名称_应用程序名称_db ”，去除子系统或应用程序名称中的 “.” 符号。命名长度不可超过20个字符，如果系统名称较长可采用缩写，如power_express可缩写为pe， 并且所有字母小写，每个单词用下划线分割。
   举例：
   加电订单系统DB： pe_order_db
   小哥信息系统DB： pe_xiaoge_db

2. 数据对象
    实体表：
   命名：表名长度必须不超过30个字符，表命名只能使用26个英文字母、下划线，并且所有字母小写，每个单词用下划线分割
   举例：
   订单表：order
   订单明细表：order_detail
   订单支付信息表：order_payment
   订单支付明细表：order_payment_detail
    临时表 ：
   命名： _tmp+ 当天时间 + 原表名
   举例： tmp201607026_power_order

3. 字段
    不同系统相同含义的字段使用同一命名，并且两者所有属性必须一致
   命名： 字段命名长度必须不超过20个字符 ，只能使用26个英文字母、数字、下划线，并且所有字母小写，每个单词用下划线分割
   举例： order_date
    当遇到主键字段是自增类型的使用表名_id 的形式
   举例： order 主键： order_id
    当字段表示为是否的意思时，使用 is_xxx 的形式
   举例：是否删除is_delete，是否可用is_enable
    字段命名中不允许出现保留词

4. 索引
   主键索引使用pk_表名命名，不可使用联合主键
   唯一键：uk_columnName
   普通索引：ix_columnName
   组合索引：ix_column1_column2_Column3

5. 视图
   视图命名：v_相关内容命名
   举例：v_orders

6. 存储过程
   命名：prc_主表名_by_主字段名_操作_output
   举例：prc_power_order_by_user_id_select_output
    不建议使用存储过程，存储过程难以调试和扩展，更没有移植性
   二. 表结构

7. 字段选择基本准则
   在满足业务需求的前提下，字段类型越短越好。
    更少的存储空间
    更少的磁盘IO
    更少的网络流量

8. 常见的字段类型选择：
    字符类型建议采用varchar/nvarchar数据类型
    存放可变长度字段
    相比char，innodb建议使用varchar
    枚举类型建议使用tinyint(4)
    金额货币建议采用decimel(xx,2)数据类型
    自增长标识建议采用bigint(20)数据类型
    时间类型建议采用为datetime数据类型，默认值非current_timestamp()下，建议设置为’1970-01-01 08:00:01’
    禁止使用text、blob数据类型
    原则上不允许这种字段，尽可能的拆分成小字段，如果特别需要，而又读写频繁，另外建一张表
    tinyint(4),smallint(6),int(11),bigint(20)，（）内的为默认值，不建议修改
    如果为非负数，必须是 unsigned
    避免误存负数，且扩大了表示范围

9. 时间戳字段：created_at和update_at
    新建表中的时间戳字段设计时必须指定默认属性：
    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’

10. 逻辑删除标识：is_delete
     新建表中的时间戳字段设计时必须指定默认属性：
     is_delete tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘逻辑删除标识’
     原则上禁止物理删除，如数据量增长过大可通过归档，归档后在表中物理删除
    三. 约束与索引

11. 主键
     每张表必须有主键，用于强制实体完整性
     单表只能有一个主键（不允许为空及重复数据）
     由Auto-Increment字段生成整型，不应与业务有关联关系
     如主键字段不能满足业务需求，另建unique约束业务字段

12. 不允许使用外键
     外键增加了表结构变更及数据迁移的复杂性
     外键对插入，更新的性能有影响，需要检查主外键约束
     数据一致性由程序控制
     外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数 据库更新风暴的风险;外键影响数据库的插入速度。

13. NULL属性
     新加的表，所有字段禁止NULL
     允许NULL值，会增加应用程序的复杂性。必须得增加特定的逻辑代码，以防止出现各种意外的bug
     旧表新加字段，需要允许为NULL
     避免全表数据更新 ，长期持锁阻塞导致阻塞

14. 索引设计准则
     应该对 WHERE 子句中经常使用的列创建索引
     应该对经常用于连接表的列创建索引
     应该对 ORDER BY 子句中经常使用的列创建索引
     建组合索引的时候，区分度最高的在最左边
     sql中的条件应注意与组合索引顺序一致
     不应该对小型的表（仅使用几个页的表）创建索引，这是因为完全表扫描操作可能比使用索引执行的查询快
     单表索引数不超过6个
     不要给选择性低的字段建单列索引
     适合创建索引的字段：order_id、user_id等
     充分利用唯一约束
     唯一索引给mysql提供了确保某一列绝对没有重复值的信息，当查询分析器通过唯一索引查找到一条记录则会立刻退出，不会继续查找索引
     索引包含的字段不超过5个
     索引加快了查询速度，但是却会影响写入性能
     一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
     组合索引的原则是，过滤性越好的字段越靠前
     索引过多不仅会增加编译时间，也会影响数据库选择最佳执行计划
     聚集索引建议
     字段长度小
     字段发生的更改小
     字段值递增，如自增长，时间字段
     字段值唯一（这个是必要条件）
     尽量使用单字段主键
     根据业务选择有意义的主键
     不要修改聚集索引
     为了维持Btree会带来大量的数据移动
     数据库引擎内部更新聚集索引动作会转换为一次删除和一次插入
    四. SQL查询规范

15. SQL查询禁令
     禁止在数据库做复杂运算
     XML解析
     字符串相似性比较
     字符串搜索（Charindex）
     复杂运算在程序端完成
     禁止使用SELECT *
     减少内存消耗和网络带宽
     给查询优化器有机会从索引读取所需要的列
     表结构变化时容易引起查询出错
     禁止在索引列上使用函数或计算
     在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)
     LIKE查询尽量使用前缀查询
     like只能使用前缀索引，因此 ：
    col like “abc%” 能用上索引
    col like “%abc%” 不能用上索引
    col like “%abc” 不能用上索引
     禁止使用游标
     关系数据库适合集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能。
     游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。
     禁止使用触发器
     不要使用触发器(Trigger)
     禁止在查询里指定索引
     随着数据的变化查询语句指定的索引性能可能并不最佳
     索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
     新建的索引无法被应用立即使用，必须通过发布代码才能生效
     禁止在没有匹配索引的表上进行for update这类的操作，会锁定整个表
     禁止在产品环境进行代码逻辑或者SQL语句性能的测试，这类操作应在开发或者测试环境进行

16. SQL查询限制
     限制JOIN个数
     单个SQL语句的表JOIN个数不能超过5个
     过多的JOIN个数会导致查询分析器走错执行计划
     过多JOIN在编译执行计划时消耗很大
     限制SQL语句长度及IN子句个数
     尽量不使用IN操作
     如需使用，在 IN 子句中包括数量非常多的值（数以千计）可能会消耗资源，要求IN子句中条件个数限制在100个以内
     更新、删除前，要先select
     避免出现误删除，确认无误才能执行更新语句。
     限制大事务操作
     只在数据需要更新时开始事务，减少资源锁持有时间
     增加事务异常捕获预处理机制
     禁止使用数据库上的分布式事务
     限制大量数据查询，使用分页
     合理限制记录返回数，避免IO、网络带宽出现瓶颈

17. SQL查询常用优化
     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，考虑通过外连接，并判断为空来实现，连接的查询条件通过索引查找
     IN 和 EXISTS选择
     子查询表大的用exists，子查询表小的用in
     使用UNION ALL替换UNION
     UNION会对SQL结果集去重排序，增加CPU、内存等消耗
     尽量避免使用OR运算符
     对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集
     增加事务异常处理机制
     应用程序做好意外处理，及时做Rollback。
     输出列使用二段式命名格式
     二段式命名格式：表名.字段名
     有JOIN关系的SQL，字段必须指明字段是属于哪个表的，否则未来表结构变更后，有可能发生程序兼容错误
     SQL中应添加合理的注释，便于定位故障
     注释中标识出AppID或源代码路径，便于出现问题时排障
     注释中不应添加当前时间等容易变化的信息，避免认为是不同的SQL而反复编译
     不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关
    五. 使用规范
     数据库的结构只有数据库管理员能够修改，任何开发人员不得自己增加数据库对象或者修改数据库对象
     任何开发人员不得自己建立索引，如果开发过程中需要建立索引，需要跟数据库管理申请
     不在业务高峰期批量更新、查询数据库
     应用程序账号应独立，不得具有DDL权限

六. 应用规范
1.事务
 只读性查询应开启只读事务（@Transactional(readOnly=true)）
 如果不开启事务控制，db默认提交为false时，事务不会被提交，会block住ddl操作
 需要设置事务的隔离级别为rr，保证一次业务查询中读到的数据的一致性
 更新事务应根据业务需求正确选择事务隔离级别
 rr级别会使用间隙锁，某些场景下可能锁住不需要被锁的数据从而导致死锁发生
 没有索引时，采用表锁
 有非唯一索引时，采用Next-Key Locking
 唯一索引时，采用行锁
 rc级别使用行锁，不能防止重复插入
 需要注意spring声明式事务
 同一类内方法调用时失效，一个类中， 方法A调用方法B， 方法B添加事务注解，则该事务不会生效
 默认配置下，spring只有在抛出的异常为运行时unchecked异常时才回滚该事务。可以明确的配置在抛出哪些异常时回滚事务
 避免长事务
 可以异步进行的业务不需要放在事务中
 真正有必要的时候才开启事务
 采用最终一致性方案
 不同业务中注意保持锁的顺序一致
2.ORM
 不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出
 不要写一个大而全的数据更新接口
 只更新业务需要更新的字段
 POJO 类的 Boolean 属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中 进行字段与属性之间的映射
 JavaBean规范中规定boolean的getter/setter 为isXXX/setXXX，包装类Boolean的getter/setter 为getXXX/setXXX，当编写代码出现不规范命名，将boolean 变量声明为isXXX时，为了保证序列化与反序列化，需将isXXX/setXXX改为isIsXXX/setIsXXX，然而IDE并不会为