数据库规范

数据库规范 

1.命名规范

1）库名表名列名【强制】

数据库对象名（库名，表名，列名）必须用小写，只能使用英文字母，数字，下划线。以英文字母开头，禁止以数字开头。库名，表名，列名必须见名知义，长度不要超过32字符

临时库，临时表名必须以tmp为前缀，并以日期为后缀，中间用下划线分割.tmp_aaaa_20181010

备份库，备份表必须以bak为前缀，并以日期为后缀，中间用下划线分割  .bak_aaaa_20181010

2）索引命名【强制】

非唯一表名_字段名称[_字段名] ”进行命名

唯一索引按照“唯一表名_字段名称[_字段名] ”进行命名

复合索引按照“idx_com_表名_字段1首字母_字段2首字母[_字段Ñ首字母]”

3）约束命名【强制】

主键约束：pk_表名称。

唯一约束：唯一表名称_字段名。

4）触发器命名【强制】

trg_表名_操作。

5）函数过程命名【强制】

pro_动名词。

6）序列命名【强制】

seq_表名

 

2.表设计规范

1 ）	建表统一使用utf8mb4字符集。【强制】
2 ）	单表创建的列尽量不要超出30个。【建议】
3 ）	一般情况下避免使用分区表，需要使用分区表先与DBA沟通。【建议】

 

3.字段设计规范

1 ）	每个Innodb 表都必须有一个主键，且不使用更新频繁的列作为主键，不使用多列主键。建议选择使用自增列作为主键。（bigint unsigned NOT NULL AUTO_INCREMENT）【强制】 【强制】
2 ）	如果字段值非空，尽量使用NOT NULL 。【建议】
3 ）	每个字段（除主键）添加COMMENT ，提高表的业务可读性。【建议】
4 ）	整形定义中不添加长度，比如使用INT，而不是INT[4]。【强制】
5）	金额字段使用decimal。存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。【强制】【强制
6）	时间字段一律使用datetime or timestamp or or datetime(精度) timetamp(精度)。【强制】
7）	标示或状态字段(0~127)一律使用tinyint(1)，比如flag。【强制】  【强制】
8）	char适合存储固定长度列或者很短的列，如性别，电话、手机、身份证使用char。【建议】
9）	varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于5000，定义字段类型为text。【建议】
10）	每张表数据量建议控制在2000W以内，单库500G内。【建议】
11）	禁止在数据库中存储明文密码。【强制】  【
12）	尽量不使用TEXT、BLOB类型。 【建议】
13）	慎用外键。因每次修改数据都需要去另外一个表检查数据，需要获取额外的锁，高并发环境下出现性能问题。【建议】

#创建表：

CREATE TABLE `products` (

    `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键'，

    `productid` INT NOT NULL COMMENT '产品ID'，

    `productcode` CHAR (3) NOT NULL COMMENT '产品编码'，

    `name` VARCHAR (30) COMMENT '名称'，

    `quantity` INT COMMENT '数量'，

    `price` DECIMAL (7， 2) COMMENT '价格'，

    `sale_date` TIMESTAMP COMMENT '销售日期'，

    PRIMARY KEY (`id`)

) COMMENT = '产品表' ENGINE = INNODB;

 

4.索引

1）	在WHERE子句中最频繁使用的字段【建议】
2）	选择高选择性的字段，即重复值少的字段【建议】
3）	联接语句中的联接字段【建议】
4）	索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；【强制】
5）	不在索引列进行数学运算和函数运算【建议】
6）	尽量建单列索引，不要建复合索引【建议】
7）	避免冗余的索引，如：primary key(id)，index(id)，unique index(id) 【建议】
8）	避免重复的索引，如：index(a，b，c)，index(a，b)，index(a) 【建议】

5.SQL规范

1）	最好给表起个别名，每个字段指定表名做前缀。【建议】
2）	SELECT只获取必要的字段，禁止使用SELECT *。  【建议】
3）	避免隐式转换。数值类型禁止加引号;字符串类型必须加引号。【建议】
4）	尽量避免使用子查询。必要时推荐用JOIN代替子查询。 【建议】
5）	INSERT语句必须指定字段列表 【建议】
6）	INSERT语句可以使用batch提交（INSERT INTO table VALUES()，()，()…）【建议】
7）	禁止单条SQL语句同时更新多个表。 【强制】
8）	尽量避免使用存储过程、触发器、视图、自定义函数。【建议】
9）	limit分页注意效率。Limit越大，效率越低。可以利用关联查询优化分页。【建议】
10）	一个事物应有捕获SQL异常的处理机制，必要时通过rollback显式回滚。 【建议】
11）	尽量不使用负向查询，如not in，not like。【建议】
12）	可以使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现:Using File Sort、 Using Temporary。 【建议】
13）	获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M。
14）	事务要简单，整个事务的时间长度不要太长。Sql尽量简单，大的sql想办法拆成小的sql语句， 避免大事务。 【建议】
15）	UPDATE、DELETE语句不使用LIMIT。【强制】
16）	UPDATE多个字段时，更新字段之间用“，”，不要用“and”。正确写法： update t01 set sales=100， bonus=200 where id=1; 【强制】

#分页

select id，name，bonus from t1 order by bonus，id limit 9，3;

=>

select t1.id， t1.name， t1.bonus

  from t1

  join (select id from t1 order by bonus，id limit 9， 3) t2

  on t1.id = t2.id;

 

6.行为规范

1）	重要项目的数据库方案选型和设计必须提前通知DBA参与。 【强制】
2）	重要业务的表结构变更必须通知DBA进行审核。 【强制】
3）	提交线上建表改表需求，必须详细注明涉及到的SQL语句，便于DBA进行审核和优化。 【强制】
4）	批量导入、导出数据必须通过DBA审核，并在执行过程中观察服务。 【强制】
5）	数据订正时，DELETE或UPDATE记录时，要先SELECT，避免出现误删除，确认无误才能执行更新语句。【强制】
6）	产品出现非数据库导致的故障时，如被攻击，必须及时通知DBA，便于维护服务稳定。 【强制】
7）	业务部门程序出现BUG等影响数据库服务的问题，必须及时通知DBA，便于维护服务稳定。 【强制】
8）	业务部门推广活动或上线新功能，必须提前通知DBA进行服务和访问量评估，并留出必要时间以便DBA完成扩容。 【强制】
9）	出现业务部门人为误操作导致数据丢失，需要恢复数据的，必须第一时间通知DBA，并提供准确时间点、误操作语句等重要线索。 【强制】
10）	线上业务，严格禁止使用replace覆盖数据。 【强制】
11）	禁止有SUPER、DDL、DCL权限的应用程序账号存在。 【强制】
12）	开发、测试环境无用冗余数据应及时清理，留出磁盘空间；生产无用冗余数据，及时归档后清理。 【强制】

 

7.数据库规范

1）	使用INNODB存储引擎。默认INNODB引擎。【强制】
2）	禁止在数据库中存储图片、文件等大数据。 【强制】
3）	尽可能不使用使用存储过程，视图，触发器，Event；这些对数据库性能影响较大，在应用层实现。【建议】
4）	禁止在线上做数据库压力测试。 【强制】
5）	生产环境和测试环境的数据库配置必须一致。 【强制】
6）	重要业务数据库，必须有主从库；或者使用公司统一的集群。确保数据库高可用。 【强制】