数据库规范2.0

山东艾思软件科技有限公司, 最新的数据库设计规范. 没有良好的设计规范, 就没有优秀的软件, 请认真阅读并严格执行!

1. 数据库设计的怎么样, 是一款程序稳定性, 可扩展性, 易用性等等一切优秀的基石. 没有数据库的规范也就没有了所有的规范
2. 理解约定的含义, 很多规范往往不是最好的, 但就是这么约定的
3. 除专有名词可以用拼音(全拼)外, 数据库名, 表名, 字段名, 变量名等, 全部以英文命名, 对于不确定的英文单词, 先查单词表, 单词表内没有合适的再查词典, 最后把新单词加入单词表
4. 拼音的字与字之间使用下划线隔开, 英文单词与单词之间也用下划线隔开, 全部小写, 不允许使用驼峰等其它方式
5. 库名, 表名, 字段名见名知意, 建议使用名词而不是动词
6. 禁止在数据库中存储图片、文件。
7. 除ID字段外, 每个字段都要有COMMENT注释, 表也要有COMMENT, 并要表达清楚, 准确
8. 每个字段定义除特殊要求外, 要有default值, 数值类型默认值一般为’0/1’. 如: 用户可用默认状态为可用, 值为1(排序字段sort默认值为50), 字符串类型一般为’’,  字段默认值除必须为NULL时不能为NULL
9. Bool类型用tinyint(1)类型代替, 0为false, 1为true
10. 所有日期时间类型用时间格式(datatime), 名称以_time后缀结尾, 如:create_time创建时间, update_time更新时间
11. 数据表ENGINE使用InnoDB格式 , CHARSET使用utf8mb4格式
12. url类型一般为varchar(255), 如: http地址, 头像图片地址等
13. 主键名统一使用: id int(10) 自增
14. 类型字段不得使用字符串保存. 如:
    1. 禁用/正常(可用)状态的字段, 字段名统一使用: usable tinyint(1)  not null default 0/1 comment “是否禁用: 0为禁用, 1为正常”.
    2. 性别使用: sex tinyint(1) not null default 0 comment “性别:  0为未知, 1为男, 2为女”
    3. 是/否状态的字段, 字段名统一使用is_开头, 如是否是菜单, 是否是会员: is_*** tinyint(1)  not null default 0/1 comment “是否可用: 0为否, 1为是”;
15. 建议使用的数据类型: varchar, int, tinyint, samllint, text; 除非必不得已, 不然不允许使用其它类型, float, double小数情况慎用
16. 金额相关字段以分为单位, 使用int类型; 运算过程都以分为单位;
17. 在不可控情况下, 比仿说要对接第三方接口, 第三方接口中有小数情况, 有varchar, int, tinyint, samllint, text之外的数据类型, 属于必不得已的情况之一; 这种情况是属于尽量避免数据类型转化的原则
18. 表名前缀分为两级, 一级前缀为ln_, 二级前缀为模块名称: 如core_核心模块, im_为聊天模块, cms_客户管理
19. 常用固定字段示例:
    1. input_user_id: `input_user_id` int(10) NOT NULL  DEFAULT 0 COMMENT ‘新闻录入用户’,
    2. create_time, update_time(前端不建议传递这两个参数, 如果需要自定义创建时间, 需要另外建立字段, 如新闻发布日期)
    3. id: `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    4. price: `price` int(10) NOT NULL DEFAULT 0  COMMENT ‘商品价格, 单位分’,
    5. quantity: `quantity` int(10) NOT NULL DEFAULT 0  COMMENT ‘购买数量, 无单位’,
    6. user_id: `user_id` int(10) NOT NULL  DEFAULT 0 COMMENT ‘客户所属用户’,
    7. product_id: `product_id` int(10) NOT NULL  DEFAULT 0 COMMENT ‘定单购买产品ID’,
20. 关于软删除问题: 有回收站机制的, 使用假删, 没有回收站的使用直删, 但删除之前要判断关联表的数据已经没有关联数据, 如果有关联数据, 要求用户先删除关联数据再来删除数据. 比仿说, 要删除一条新闻分类, 要判断这个分类下是否有新闻, 如果新闻就提示用户先删除新闻; 删除新闻时再判断新闻是否是收藏记录和评论记录, 如果有, 要先删收藏记录和评论记录再删除新闻, 以此类推. 实际先以客户要求为准;
21. 禁止在线上做数据库压力测试, 如有特殊需要, 需提前报备. 禁止客户端直接操作测试, 生产数据库.
22. 建议使用UNSIGNED存储非负数值。
23. 禁止在数据库中存储明文密码。
24. 需要根据实际的宽度来选择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)。