数据库规范

一、命名规范

1、对象命名规则:长度不超过30字节（ASCII码），命名字符为小写a-z， 数字0-9及下划线_，禁止使用拼音

2、表名：tb_业务名称_表的作用 ，应见文知义。

3、schema_name(术语，mysql传统称作'数据库名')与应用名称尽量一致，下划线“_”允许但不可使用中横线“-”。 

4、主键索引名

• 定义为主键即可，无需特定名称
• 唯一索引 UK_列名（组合）
• 普通索引 IDX_列名（组合）        

5、敏感信息列命

敏感词	列名	数据类型
手机号	mobile	varchar2(15)
电话号	telephone	varchar2(20)
身份证号	idcard_no	varchar2(20)
银行卡号	bcard_no	varchar2(20)

6、通用列名

创建时间：create_time 修改时间：update_time

二、建表规则

1. 字符集

默认选用utf8mb4 ，collation(排序)为utf8mb4_general_ci，确有其他需求单独提出

1. 存储引擎

默认使用InnoDB存储引擎，禁止使用Myiasm存储引擎。 

1. 不同系统（或称“schema”）中不允许存在同名的表。
2. 不得使用外键与级联，一切外键概念必须在应用层解决。
3. 字段长度很短的整数型必须使用 tinyint

如：status、sex字段等，mysql没有“布尔”类型；表示 “是与否”概念的字段使用is_**数据类型unsigned tinyint 。

1. 禁止使用枚举ENUM，建议使用tinyint代替。
2. NULL相关表字段必须为NOT NULL,确实需要定义为NULL的，需经开发leader及dba确认后方可定义。允许NULL定义的白名单（经讨论许定义为NULL的场景，在此明确定义后可用）

说明：注意   is not null 进行查询时不走索引（注意 is not null 时查询优化），  mysql下 count(可以为null的列)   返回数量不统计为null的列！ 

1. 禁止使用浮点类型数据，使用bigint代替倍到，可以将金额扩大100分级别。  
2. 不建议使用大字段(text, blob )，有longtext字段的表需要为此类字段单独一张表存储，关键流水表大于1000长度的varchar与主表分离，特殊场景专门讨论评审。
3. 每个表必须有表级和字段级的注释，字段级的注释值较少的情况下应详细说明，更新表时应及时更新相关注释。
4. 合理使用分区表对大表设计及后期维护可带来极大便利，mysql技术发展使得分区表性能得到保证 （暂定在非分库分表场景使用）
5. 相同业务系统中表示相同业务含义的列，字段名和类型应完全一致。
6. IP的表字段设置为INT(10)就好，如果IP获取不到可以直接存0代表获取不到IP。使用inet_aton和inet_ntoa进行转换  
7. 字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况
8. 特别说明主键设计

• 必须有主键，且尽可能精简（unsigned ：int 、bigint）
• 因为innodb为主键有序的簇表，要求主键自增或具备增长趋势
• 主键通常为无业务含义的代理键，考虑到数据传递、变动、安全等各方面，自然键（实体自然具备的属性：如身份证号码、手机号码、业务上组合唯一的多个列等）禁止做主键
• 表设计中已有符合上述条件的候选键，则直接作为主键， 不要再增加额外代理键做主键（会导致：候选键则退化为唯一索引，不仅增加开销 ，还会导致本来可主键高效访问的操作退化为唯一索引操作！）

16、回收表权限管理，允许特定人员修改表结构，但是必须经过评审

三、数据及SQL规则

1、禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

2、程序代码或者数据修改脚本中大批量的数据更新（增删改），必须设计为分步提交，控制在1000行/1提交以内。

3、业务代码禁止使用SELECT * 操作

4、建议不要使用 count(列名)或 count(常量)来替代 count(*)

说明：count(*)会统计值为NULL的行，而count（列名）不会统计此列为NULL值的行

5、禁止insert into table values(1,2,3,4,5),容易在增加和删除字段后出现bug。

6、所有数据不允许物理删除，只允许逻辑删除（用状态标识），设定规则，定期进行转移存档。

四、索引规则

1. 不能在索引字段使用处理函数或表达式，因为这种操作会导致索引失效   

PS：“对字符串进行前缀搜索除外”，如 select ×,×,× from t where t.*column like '1234%'

2、过滤字段的传入值应该保持与字段的数据类型一致，关联字段应使用相同的数据类型，避免因隐式转换导致索引失效

3、建立复合索引时，基数大的字段和经常查询的字段应该放在最前面

4、不能在相同的字段重复建立冗余的索引

5、对单表的多次alter修改表操作，应合在一个SQL中执行。

6、利用覆盖索引来进行查询操作，来避免回表操作。

7、在varchar字段上建立索引时，必须指定索引长度，特别是字段较长时，可计算实际文本区分度确定索引长度

8、索引的个数上线为3个，超过3个需要评审

五、脚本命名规范

1、脚本命名为：my_编号_schma_name_SQL类型+sql

    my:mysql 缩写；编号：2位数字的编号；同一版本发布内的脚本执行顺序，从编号开始于01至99；数据库名；SQL类型：DDL,DML

   举例：my -01-slbase-ddl.sql

2、表名必须前置schma_name名，避免脚本执行失误及降低人为多环节的沟通成本，提升效率   

错误：update table1 set aa=xxxx where bb=xxxx;

正确：update slbase.table1 set aa=xxxx where bb=xxxx;

3、版本发布的SQL脚本必须以UTF-8无BOM编码格式存储。

4、 需要拼接字符串形成变更sql的，仅限上线时才能稳定的数据，需要提前讨论准备上线与回滚方案。

5、静止脚本里面有USE切换数据库操作

六、用户命名规范 

1、原生业务用户名称应该跟其数据存储的主体 schena_name名称保持一致（不局限于mysql）

2、个人查询账号： 拼音实名 

七、参考推荐部分

1、尽量减少查询关联表的数量，控制3张表以下，关联字段需要建立索引。

2、单表一到两年内数据量超过2000w或数据容量超过10G考虑分表，且需要提前考虑历史数据迁移或应用自行删除历史数据。

3、Like操作前模糊匹配，比如： like '%keyword%' 、 like '%keyword' 会导致索引失效，有此类操作且数据量很大要使用搜索引擎代替

4、尽量使用短索引，如果 varchar(200) 前20个字符就能唯一确定值的唯一了那就使用前20个字符建立就好了，短索引的存储空间比普通索引小，查询效率和存储效率更高

5、尽量避免子查询，如： FROM子句中的子查询可以坚决避免使用， WHERE子句中的子查询可以使用表关联代替，SELECT子句中子查询一定要在分页完之后才能使用。

6、!=查询操作无法使用到索引，尽量修改成 = 操作。

7、TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。