目录
规范数据库的开发工作。
加强开发人员在开发过程对数据库的合理使用,提高数据库的使用效率和规范数据库的操作。
本规范适用于本公司的信息化系统运维、技术人员和系统供应商。
库名、表名、字段等数据库对象名称:
- 必须使用小写字母,并且使用下划线分割。
- 命名要能做到见名知意, 建议不超过32个字符。
- 禁止使用MySQL等数据库保留关键字。
- 临时表必须以tmp为前缀并以日期为后缀。
- 备份库,备份表必须以bak为前缀并以日期为后缀。
- 所有存储相同数据的列名和列数据类型必须一致。
表名、视图、序号、索引、触发器命名要求:
序号 | 类型 | 命名规则 | 举例/说明 |
1 | 表 | 模块简称_功能简称 | po_headers、po_lines、ap_invoices |
2 | 多语言表 | 模块简称_功能简称_tl | po_headers_tl、mtl_system_items_tl |
3 | 视图 | 模块简称_功能简称_v | ap_invoices_v |
4 | 序列 | 模块简称_功能简称_s | po_headers_s |
5 | 索引 | 模块简称_功能简称_n/u(1..99) | n代表Normal索引,u代表Unique索引 |
6 | 触发器 | 模块简称_功能简称_操作简称_trg | po_headers_i_trg、po_headers_u_trg(其中i代表插入,u代表更新数据时) |
表字段定义要求:
字段名称 | 类型 | 必须/ 建议 | 说明 |
created_by | 数字 | 必须 | 创建该表中记录的登录用户id |
creation_date | 日期 | 必须 | 创建该表中记录的时间 |
last_updated_by | 数字 | 必须 | 修改该表中记录的登录用户id |
last_update_date | 日期 | 必须 | 修改该表中记录的时间 |
deleted_falg | 字符串 | 必须 | 是否删除表示,默认为N,删除后表示为Y。防止硬删除 |
主键id | 字符串 | 必须 | 主键id(定义为唯一性索引) |
attribute1 | 字符串 | 建议 | 备用字段 |
attribute2 | 字符串 | 建议 | 备用字段 |
attribute3 | 字符串 | 建议 | 备用字段 |
attribute4 | 字符串 | 建议 | 备用字段 |
attribute5 | 字符串 | 建议 | 备用字段 |
object_version_number | 数字 | 建议 | insert记录时默认为1,每次update后加1,用于记录锁定及更改次数记录 |
status_code | 字符串 | 可选(非空) | 业务数据,处理状态字段,其值例如:ENTERED、APPROVED、PROCESSED等 |
enable_flag | 字符串 | 可选(非空) | 基础数据,是否启用状态字段,Y/N |
active_start_date | 日期 | 可选 | 启用开始日期 |
active_end_date | 日期 | 可选 | 启用结束日期 |
- 数据库和表的字符集统一使用UTF8,避免由于字符集转换产生的乱码。
- 所有的表和字段都需要添加注释。
- 尽量控制单表数据量的大小,建议控制在500万以内。
- 谨慎使用MySQL分区表,建议采用物理分表方式管理大数据。
- 尽量做到冷热数据分离,减小表的宽度;经常一起使用的列放到一个表中。
- 禁止在表中创建预留列。
- 禁止在数据库中存储图片,文件等二进制数据。
- 禁止在生产数据库在线做压力测试。
- 禁止从开发环境,测试环境直接连生产环境数据库。
- 限制单表的索引数量,建议单表的索引数量不超过5个;禁止给表中的每一列都建立单独的索引。
- 每个表必须有一个主键:
不使用更新频繁的列作为主键,不使用多列主键;
不使用UUID,MD5,HASH,字符串列作为主键;
主键建议选择使用自增ID值
- 常见索引列建议:
SELECT UPDATE.DELETE语句,WHERE从句的列;
包含在OEDER BY , GROUP BY , DISTINCT中的字段;
多表JOIN的关键列
- 索引命名规范
非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名
唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名
- 合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
- 索引列的顺序建议
索引列的顺序,区分度最高的列放在联合索引的最左侧;
尽量把字段长度小的列放在联合索引的最左侧;
使用最频繁的列放到联合索引的左侧
- 避免建立冗余索引和重复索引。
- 对于频繁的查询,优先考虑使用覆盖索引。
- 尽量避免使用外键。建议不使用外键约束,但是在表与表之间的关联键上建立索引。
- 优先选择符合存储需要的最小的数据类型
无符号相对于有符号可以省一倍的存储空间。
对于非负数采用无符号整型(UNSIGNED)进行存储。
VARCHAR(N)中的N代表的是字符数,而不是字节数,根据实际的宽度来选择N,尽量用小的N。使用UTF8存储汉字 varchar(255)=765字节,过大的长度会消耗更多的内存。
- 避免使用TEXT,BLOB数据类型。
建议把BLOB 或TEXT列 分离到单独的扩展表中。
TEXT,BLOB数据类型只能使用前缀索引。
- 避免使用ENUM数据类型,使用TINYINT来代替。
- 尽可能把所有的列定义为NOT NULL。
- 不要使用字符串存储日期型的数据。
- 使用TIMESTAMP或DATETIME存储时间, TIMESTAMP使用4字节,DATETIME使用8个字节。
- 存储精确浮点数,必须使用decimal类型(比如:财务相关的金额类数据)。
- 建议使用预编译语句进行数据库操作,可以提高性能并且避免SQL注入。
- WHERE条件中必须使用合适的类型,避免MySQL进行隐式类型转化。
- 避免使用双%号的查询条件,不建议使用%前缀模糊查询,例如LIKE “%TEST”。
- 一个SQL只能利用到复合索引中的一列进行范围查询。
- 合理利用存在的索引,而不是盲目增加索引。
- 避免使用JOIN, 使用left join或not exists来优化join in操作。
- 程序连接不同的数据库使用不同的账号,禁止跨库查询。
- SELECT语句只获取需要的字段:
必须使用SELECT <字段列表>;
禁止使用"SELECT *"。
- 禁止使用不含字段列表的INSERT语句,必须显式的指明字段名称。
- 避免使用子查询,可以优化为join操作。
- 避免使用JOIN关联太多的表,建议不超过5个。
- 使用合理的SQL语句,减少同数据库交互次数,进行批量操作。
- 使用IN代替OR,IN包含的值不应过多,建议不要超过500个。
- 禁止使用ORDER BY RAND()进行随机排序,使用其他方法替换。
- WHERE 从句中,禁止对列进行函数转换和计算。
- 在明显不会有重复值时使用UNION ALL而不是UNION。
- 拆分复杂的大SQL为多个小SQL。
- 禁止在从库上执行后台管理和统计类型功能的QUERY。
- 建议使用合理的分页方式以提高分页的效率。
- INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不应过多。
- 统计表中记录数时使用COUNT(*),而不是 COUNT(primary_key)和COUNT(1)。
- 超过100万行的批量写操作,要分批多次进行操作。
- 谨慎修改数据库的表结构。
- 对于大表使用pt-onine-schema-change,修改表结构。
- 禁止为程序使用账号赋予super权限。
- 对于程序连接数据库账号,遵循权限最小原则。
- 程序使用的账号只能在一个DB下使用,不能跨库。
- 程序使用的账号原则上不能有drop权限。