山东艾思软件科技有限公司, 最新的数据库设计规范. 没有良好的设计规范, 就没有优秀的软件, 请认真阅读并严格执行!
- 数据库设计的怎么样, 是一款程序稳定性, 可扩展性, 易用性等等一切优秀的基石. 没有数据库的规范也就没有了所有的规范
- 理解约定的含义, 很多规范往往不是最好的, 但就是这么约定的
- 除专有名词可以用拼音(全拼)外, 数据库名, 表名, 字段名, 变量名等, 全部以英文命名, 对于不确定的英文单词, 先查单词表, 单词表内没有合适的再查词典, 最后把新单词加入单词表
- 拼音的字与字之间使用下划线隔开, 英文单词与单词之间也用下划线隔开, 全部小写, 不允许使用驼峰等其它方式
- 库名, 表名, 字段名见名知意, 建议使用名词而不是动词
- 禁止在数据库中存储图片、文件。
- 除ID字段外, 每个字段都要有COMMENT注释, 表也要有COMMENT, 并要表达清楚, 准确
- 每个字段定义除特殊要求外, 要有default值, 数值类型默认值一般为’0/1’. 如: 用户可用默认状态为可用, 值为1(排序字段sort默认值为50), 字符串类型一般为’’, 字段默认值除必须为NULL时不能为NULL
- Bool类型用tinyint(1)类型代替, 0为false, 1为true
- 所有日期时间类型用时间格式(datatime), 名称以_time后缀结尾, 如:create_time创建时间, update_time更新时间
- 数据表ENGINE使用InnoDB格式 , CHARSET使用utf8mb4格式
- url类型一般为varchar(255), 如: http地址, 头像图片地址等
- 主键名统一使用: id int(10) 自增
- 类型字段不得使用字符串保存. 如:
- 禁用/正常(可用)状态的字段, 字段名统一使用: usable tinyint(1) not null default 0/1 comment “是否禁用: 0为禁用, 1为正常”.
- 性别使用: sex tinyint(1) not null default 0 comment “性别: 0为未知, 1为男, 2为女”
- 是/否状态的字段, 字段名统一使用is_开头, 如是否是菜单, 是否是会员: is_*** tinyint(1) not null default 0/1 comment “是否可用: 0为否, 1为是”;
- 建议使用的数据类型: varchar, int, tinyint, samllint, text; 除非必不得已, 不然不允许使用其它类型, float, double小数情况慎用
- 金额相关字段以分为单位, 使用int类型; 运算过程都以分为单位;
- 在不可控情况下, 比仿说要对接第三方接口, 第三方接口中有小数情况, 有varchar, int, tinyint, samllint, text之外的数据类型, 属于必不得已的情况之一; 这种情况是属于尽量避免数据类型转化的原则
- 表名前缀分为两级, 一级前缀为ln_, 二级前缀为模块名称: 如core_核心模块, im_为聊天模块, cms_客户管理
- 常用固定字段示例:
- input_user_id: `input_user_id` int(10) NOT NULL DEFAULT 0 COMMENT ‘新闻录入用户’,
- create_time, update_time(前端不建议传递这两个参数, 如果需要自定义创建时间, 需要另外建立字段, 如新闻发布日期)
- id: `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
- price: `price` int(10) NOT NULL DEFAULT 0 COMMENT ‘商品价格, 单位分’,
- quantity: `quantity` int(10) NOT NULL DEFAULT 0 COMMENT ‘购买数量, 无单位’,
- user_id: `user_id` int(10) NOT NULL DEFAULT 0 COMMENT ‘客户所属用户’,
- product_id: `product_id` int(10) NOT NULL DEFAULT 0 COMMENT ‘定单购买产品ID’,
- 关于软删除问题: 有回收站机制的, 使用假删, 没有回收站的使用直删, 但删除之前要判断关联表的数据已经没有关联数据, 如果有关联数据, 要求用户先删除关联数据再来删除数据. 比仿说, 要删除一条新闻分类, 要判断这个分类下是否有新闻, 如果新闻就提示用户先删除新闻; 删除新闻时再判断新闻是否是收藏记录和评论记录, 如果有, 要先删收藏记录和评论记录再删除新闻, 以此类推. 实际先以客户要求为准;
- 禁止在线上做数据库压力测试, 如有特殊需要, 需提前报备. 禁止客户端直接操作测试, 生产数据库.
- 建议使用UNSIGNED存储非负数值。
- 禁止在数据库中存储明文密码。
- 需要根据实际的宽度来选择VARCHAR(N)类型的宽度, 并在满足够用的情况下, 尽量缩小他的值
索引相关规范, 供参考
索引规范
1) 单张表的索引数量控制在5个以内。
2) 复合索引中的字段数建议不超过5个。
3) 非唯一索引必须按照"idx_字段名称_字段名称[_字段名]"进行命名。
4) 唯一索引必须按照"uniq_字段名称_字段名称[_字段名]"进行命名。
5) 合理利用覆盖索引。不使用更新频繁的列做为索引。
6) 对长字符串考虑使用前缀索引,前缀索引长度不超过8个字符。
7) 索引字段的顺序需要考虑字段值去重之后的个数,个数多的放在前面。
8) 使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort,UsingTemporary。
9) UPDATE、DELETE语句需要根据WHERE条件添加索引。
10) 合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c),但(a,c)只能用到部分索引。
索引禁忌
1) 不在选择性低的列上建立索引,例如"性别", "状态", "类型"。
2) 不在索引列进行数学运算和函数运算。
3) 尽量不使用外键。
4) 高并发场景不建议使用唯一索引。
5) 不使用前导查询,如like "%ab",like "%ab%"。
6 SQL语句规范
1) SQL语句中IN包含的值不应过多(不超过1000个)
2) UPDATE、DELETE语句不使用LIMIT。
3) WHERE条件中必须使用合适的类型,避免MySQL进行隐式类型转化。
4) SELECT语句只获取需要的字段。
5) SELECT、INSERT语句必须显式的指明字段名称,不使用SELECT *,不使用INSERT INTO table()。
6) WHERE条件中的非等值条件(IN、BETWEEN、<、<=、>、>=)会导致后面的条件使用不了索引。
7) 避免在SQL语句进行数学运算或者函数运算,容易将业务逻辑和DB耦合在一起。
8) INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不应过多。
9) 避免使用存储过程、触发器、函数等,容易将业务逻辑和DB耦合在一起,并且MySQL的存储过程、触发器、函数中存在一定的bug。
10) 避免使用JOIN。
11) 使用合理的SQL语句减少与数据库的交互次数。
12) 不使用ORDER BY RAND(),使用其他方法替换。
13) 建议使用合理的分页方式以提高分页的效率。
14) 统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1)。