数据库开发规范和操作手册v2.0
1. 基础规范
· 数据库及表统一使用UTF8mb4字符集，特殊需求提前跟DBA评审。
· 数据库及表统一使用innodb存储引擎。
· 统一使用mysql隔离级别 REPEATABLE-READ。
· 创建表使用row_format=Dynamic 格式。
· 禁止使用存储过程和触发器、视图、自定义函数等。
· 不在数据库中存储图片、文件、视频等大数据。
· 大数据量进入数据库(10w),需提前通知DBA评审和协助观察。
· 大数据量更新，进入数据库时，需要批量操作，一个事物不能高于5000条。
· 禁止在线上数据库做压力测试。
· 禁止在程序代码中进行DDL，DML操作。
· 禁止从开发环境和测试环境直接连接生产数据库。
· 禁止从生产环境查询数据或导出数据，有需求在数据平台查询或寻找DBA协助。
· 原则上不要在周五做发版或者大量数据修改。
· 禁止在数据库中存储明文密码，如有需要手机号使用加密处理。
2. 库表命名规范
2.1数据库命名规范
· 数据库名不能超过30个字符。
· 数据库命名必须为项目英文名称或有意义的简写。
· 命名应使用小写。
· 数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8MB4。示例见设计规范。
· 附： MySQL中Unicode字符集列表：
|
字符集名称
|
字节占用
|
字符集兼容性
|
Unicode字符支持
|
|
UCS2
|
每字符2字节
|
所有Unicode 3.0字符
|
|
|
UTF16
|
每字符2字节，或4字节。
|
与UCS2兼容
|
所有Unicode 5.0和Unicode 6.0字符，包括扩展字符。
|
|
UTF16LE
|
与UTF16相同，只是字节顺序相反。
|
所有Unicode 5.0和Unicode 6.0字符，包括扩展字符。
|
|
|
UTF8
|
每字符1到3字节。
|
所有Unicode 3.0字符
|
|
|
UTF8MB4
|
每字符1到4字节。
|
与UTF8兼容
|
所有Unicode 5.0和Unicode 6.0字符，包括扩展字符。
|
|
UTF32
|
每字符4字节。
|
所有Unicode 5.0和Unicode 6.0字符，包括扩展字符。
|
2.2 表命名规范
· 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。
· 多个单词以下划线_分隔。
· 普通表名以t_开头，表示为table，命名规则为t_模块名。
· 临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则：加上tmp前缀和8位时间后缀。如(tmp_test_user_20200821)。
· 备份表（运营、开发或数据库人员备份用作保存历史数据的中间表）命名规则：加上bak前缀和8位时间后缀（bak_test_user_20200821）。
· 命名应使用小写。
2.3 字段命名规范
· 字段命名需要表示其实际含义的英文单词或简写，单词之间用下划线_进行连接。
· 各表之间相同意义的字段必须同名。
· 命名应使用小写。
2.4 索引命名规范
· 二级（辅助）索引以IDX_开头，唯一索引以UNI_开头。后面紧跟索引所在的字段名。如要在id列上添加二级索引，则应为IDX_ID。
· 组合索引命名应注意字段顺序。如在字段member和字段userid上创建组合索引，则可以命名为idx_user_mb(uerid,member)。
· 命名应该使用大写。
2.5 存储过程命名规范
· 存储过程命名以proc_开头，表示procedure，之后多个单词要以_连接，命名中应体现其功能。
· 命名应使用小写。
3.库表设计规范
3.1 表设计规范
· 不同组件间所对应的数据库表之间的关联应尽可能减少，如果不同组件间的表需要外键关联也尽量不要创建外键关联，而只是记录关联表的一个主键，确保组件对应的表之间的独立性，为系统或表结构的重构提供可控性。
· 表必须有主键。
· 一个字段只表示一个含义。
· 表不应该有重复列，比如，年月日用不同的字段设计是不允许的。
· 总是包含四个字段:created_time(创建日期),updated_time (修改日期), created_by(创建人),updated_by(修改人) 且这四个字段不应该包含有额外的业务逻辑,在创建或修改记录的时候,必须创建或修改这四个字段。示例：created_time timestamp not null default current_timestampupdated
· update_time timestamp not null default current_timestamp on update current_timestamp
· 需要join的字段(连接键),数据类型必须保持绝对一致，避免隐式转换。
· 设计应至少满足第二范式,尽量减少数据冗余。一些特殊场景允许反范式化设计，但在项目评审时需要对冗余字段的设计给出解释。
· 需要定期删除或转移过期数据的表，通过分表来解决。
· 单表字段不应太多，建议最多不要大于50个。
· 数据量大的表尽量使用分区表。
3.2 字段设计规范
· INT：如无特殊需求，存放整列数字使用UNSIGNED INT型。整数字段后的数字代表显示长度。
· DECIMAL(M,D)：定点小数使用此DECIMAL类型,且明确标识出为无符号型(UNSIGNED),除非确实会出现负数。
· 所有只需要精确到天的字段全部使用DATE类型,而不应该使用TIMESTAMP或者DATETIME类型。
· DATETIME：所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型(除了created_time,updated_time)。
· VARCHAR：所有动态长度字符串全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替；
· TEXT:仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放。如无特殊需要，严禁开发人员使用MEDIUMTEXT、TEXT、LONGTEXT类型。
· 使用INT UNSIGNED型存储IPV4。PHP程序推荐使用long型存储IPV4。
· 对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE。
· 如无特殊需要，严禁使用BLOB类型。
· 如无特殊需要，字段必须使用NOT NULL属性，可用默认值代替NULL。MySQL NULL类型会进入索引中。此外，NULL在索引中的处理也是特殊的，也会占用额外的存放空间。
· 每个列定义的时候必须加上comments。
· 自增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且自增字段必须是主键或者主键的一部分。
3.3 索引设计规范
· 索引必须加在选择性高的字段上面索引选择性：索引列中不同值的数目与表中记录数的比值。如SEX字段共100条记录，只存放男、女两个值，则在SEX列上创建的索引idx_sex的索引选择性为2/100=0.02.*组合索引的首字段,必须在where条件中
· 对于确定需要组成组合索引的多个字段,建议将选择性高的字段靠前放。
· Text类型字段必须使用前缀索引。
· 单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引。
· 组合索引中的字段数建议不超过5个。
· 避免冗余和重复索引，如 (a，b，c) 相当于 (a) 、(a，b) 。
· 使用覆盖索引减少 IO，避免排序，例如：
where tenant_id =’xxx’ and is_deleted=0 索引：（tenant_id,is_deleted）；
where tenant_id =’xxx’ order by create_time desc索引：（tenant_id,create_time desc）。
· ORDER BY，GROUP BY，DISTINCT的字段尽量添加在索引的后面，形成覆盖索引。
3.4 SQL编写规范
* 所有的sql必须要求简洁 ， 逻辑简单 。
* 在所有Query的Where条件中必须使用和过滤字段完全一致的数据类型,杜绝任何隐式类型转换,避免造成因为数据类型不匹配而导致Query执行计划的出错,造成性能问题。
* 所有进行连接sql使用join不允许超过两个。
* 复杂SQL拆分为多个小 SQL，避免长事务。
* 严禁使用 _select * from table_ 而不加任何where条件。
* 在取出字段上可以使用相关函数，但应尽可能避免出现 _now(),rand(),sysdate(),current_user()_ 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。
* 分页查询语句全部都需要带有排序条件,除非应用方明确要求不要使用任何排序来随机展示数据。
* WHERE条件中严禁在索引列上进行数学运算或函数运算。
* 严禁使用%前缀进行模糊前缀查询。
* 尽量避免使用 select * ， select * 可能导致只需要访问索引即可完成的查询需要回表取数。
* 进行大批量操作时必须分批提交，每次数据量操作不能超过10W条。
* 用in() /union替换or，并注意in的个数小于300。
* INSERT语句必须指定字段列表，禁止使用INSERT INTO TABLE()
* Where条件尽可能避免非等值条件，!=会导致后面的条件使用不了索引。
* 使用union all 代替union。排序操作应当在union all前的子查询中执行。union all不需要对结果集再进行排序。
* 对同一表的多次alter操作必须合并为一次操作。
* 不允许对数据进行物理删除，应采用逻辑删除，除必须进行物理删除，提前与DBA进行沟通。
* 禁止单条 SQL语句同时更新多个表。
3.5 流程规范
1. 表结构变更、添加索引告知DBA进行审核；
2. 批量导入、导出数据必须通过 DBA审核；
3. 批量更新数据，如UPDATE、DELETE操作，经DBA进行审核；
4. 上线新功能，涉及DB的变更，通知 DBA进行评估保障；
5. 提交线上建表改表需求，必须详细注明涉及到的所有SQL语句（包括INSERT、DELETE、UPDATE），便于DBA进行审核；
6. 避免在业务高峰期批量更新、修改表结构；
7. 人为误操作导致数据丢失，需要恢复数据的，必须第一时间通知 DBA，并提供准确时间点、误操作语句等重要线索；
8. 字符集修改不使用，alter table，通知DBA配合处理；
9. 对于表没有主键、没有索引的，申请驳回。
浙公网安备 33010602011771号