13.数据库设计规范

数据库设计规范

1.规范总览

• 命名规范
• 基本设计规范
• 字段设计规范
• SQL开发规范
• 操作行为规范

2.命名规范

• 名称必须使用小写字母并用下划线分割：Linux系统下MySQL数据库大小写敏感，可能会因为大小写不同导致找不到库表
• 所有名称禁止使用MySQL保留关键字：执行SQL时可能会报错
• 数据库对像的命名要能做到见名识义，并且最好不要超过32个字符：见名知意
• 临时库表必须以tmp为前缀并以日期为后缀：区分持久表和临时表，利于处理
• 备份库表必须以bak为前缀并以日期为后缀：区分持久表和临时表，利于处理
• 所有存储相同数据的列名和列类型必须一致：表连接的时候防止出现隐式类型转换，然后出现索引失效，最终导致查询效率大幅降低

3.基本设计规范

• 所有表必须使用InnoDB存储引擎
• 数据库和表的字符集尽量使用utf8mb3或utf8mb4
• 数据库的库表列的字符集要统一
• 所有的表和列都要添加注释
• 尽量控制单表数据量的大小，建议控制在500万以内
• 超过500万数据量的表可以考虑进行历史数据归档或分库分表等手段来控制数据量大小
• 谨慎使用MySQL分区表
• 尽量做到冷热数据分离（大字段与使用少的字段分离为单独一个表），减少表的宽度，也就是垂直分表
• 禁止在表中建立预留字段
• 禁止在数据库中存储图片，文件等二进制数据
• 禁止在线上做数据库压力测试
• 禁止从开发环境，测试环境直连生产环境数据库，要按照环境对数据库进行隔离

4.字段设计规范

• 优先选择符合存储需要的最小的数据类型
• 存储ip地址，建议转换为整形存储
• 对于非负型的数据来说，要优先使用无符号整型来存储
• varcahr(n)或char(n)中的n代表的是字符数，不是字节数
• 避免使用text，blob这样的数据类型
• text或bolb类型只能使用前缀索引
• 避免使用enum数据类型
• 尽可能把所有列定义为NOT NULL
• 不要使用字符串存储日期数据
• 金额类数据，必须使用decimal类型或long类型

5.SQL开发规范

• 建议使用预编译语句进行数据库操作
• 避免数据类型隐式转换
• 避免使用全模糊或左模糊
• 使用left join或not exists来优化not in操作
• 程序连接不同的数据库使用不同的账号，禁止跨库查询
• 禁止使用select *
• 禁止使用不含字段列表的insert语句
• 避免使用子查询，可以把子查询优化为join操作
• 避免join太多表，建议不超过5个
• 减少同数据库的交互次数，也就是尽量进行批量操作
• 使用in代替or
• 禁止使用order by rand()进行随机排序
• 禁止对列进行函数转换和计算
• 在明显不会有重复值时使用union all而不是union
• 拆分复杂的大SQL为多个小SQL
• 超100万行的批量写，要分多批进行操作

6.操作行为规范

• 对大表数据结构的修改一定要谨慎，会造成严重的锁表操作。尤其是生产环境，是不能忍受的
• 对于大表使用pt-online-schema-change修改表结构
• 禁止为程序使用的账号赋予super权限
• 对于程序连接数据库账号，遵循权限最小原则