电商数据库设计之MySQL 数据库设计规范_某大型电商平台需要设计一个数据库来存储用户信息和商品信息,以支持用户注册、登

生产环境如下

MySql 版本 MySql 5.7
图形管理工具Navicat
Linux 系统

项目说明

电商的基本模块有如下几个, 按照用户使用的流程

通过流程图 , 我们需要设计以下几个模块

接下来就是数据库设计, 先说一说数据库的设计规范

在实际工作中,一般情况下,各个公司都有自己的数据库设计规范,在遵循公司内部规范的同时,我们还有业内共识通用的设计规范,在此,就本次的电商数据库设计,说明以下部分规范.这个规范也同样适用与大部分业务的数据库设计.

数据库命名规范

• 所有的数据库对象名称必须使用小写字母并使用下划线分割(mysql 数据库对大小写敏感)
• 所有数据库对象名称禁止使用MySqL保留关键字 eg. from关键字
• MySQL关键字查询
• 数据库对象的命名要见名之意 , 最好不要超过32个字符.
• 数据库操作的时候 ,总会导入导出表 , 有一些临时表 , 临时表的最好以tmp为前缀并且以日期为后缀
• 备份表的命名, 最好以bak为前缀并且以日期为后缀
• 所有存储相同数据的列名和列类型必须一致

数据结构基本设计规范

• 所有的表必须使用Innodb存储引擎
  MySQL5.6 以后 Innodb 引擎成了默认的存储引擎 . 支持事务,行及锁,有更好的恢复性,高并发下性能好

• 数据库和标的字符集统一使用UTF-8
  MySQL UTF-8字符集汉子占3个字节 , ASSIC占1个字节

• 所有的表和字段都要添加注释

• 尽量控制单表数据量的大小 , 尽量在500万行以内
  如果必须超过这个量 , 一般采用历史数据归档(eg. 日志类表) , 分库分表(主要是业务数据)等方式.

• 尽量做到冷热数据分离,减小表的宽度
  这样做的目的是较少磁盘的IO , 保证热数据的内存缓存命中率
  利用更有效的利用缓存,避免读入更无用的冷数据(少使用 select *)
  经常使用的列放到一个表中

• 禁止在表中建立预留字段
  很难做到见名之意
  无法确认存储的数据类型.
  对预留字段进行修改时,会对表造成锁定

• 禁止在数据库中存储图片, 文件等二进制数据 .

• 禁止在线上数据库做数据库压力测试

• 禁止从开发环境,测试环境直连生产环境数据库
  否则很容易造成数据库污染

数据库索引设计规范

• 限制每张表的上的索引数量 , 建议单张表索引不超过 5 个

  • Innodb是按照主键的顺序来组织表的
  • 不使用更新频繁的列作为主键 , 不使用多列主键
  • 不使用UUID , MD5 , HASH , 字符串列作为主键
  • 主键建议使用自增ID使用主键

• 常见索引列建议

  • select ,update , delete 语句中的where 从句中的列
  • 包含在order by ,group by ,distinct 中的字段
  • 多表JOIN的关联列
• 如何选择索引列的顺序
  
  • 区分度最高的列放在联合索引的最左侧(主键, 唯一索引列)
  • 尽量把字段长度最小的列放在联合索引的最左侧
  • 使用频繁的列放到最左侧

数据字段设计规范

• 优先选择符合存储需要的最小的数据类型

  • 将字符串转化为数字类型存储 INET_ATON('255.255.255.255') = 429467295 IETN_NTOA(429467295) = '255.255.255.255'
  • 对于非负整数来说, 优先使用无符号整型
  • MySQL 中 varchar(n) 这里的n 代表的是字符数 , 不是字节数 ,varchar(255) 可以存 255 个汉字 , 需要占用 765 个字节
  • 过大的长度会消耗更多的内存

• 避免使用 TEXT , BLOB数据类型

  • 如果必须使用 , 将BLOB或者是TEXT列分离到单独的扩展表

• 避免使用 ENUM数据类型

• 尽可能把列定义为 NOT NULL

• 使用 timestamp 或者datetime 类型存储时间

  • timestamp 存储时间 范围 为 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

  • timestamp 区间不够, 使用datetime 存储

• 同财务相关的金额类数据 , 必须使用 decimal 类型

  • decimal 类型为精准浮点数 , 在计算时不会丢失精度

数据库SQL开发规范

• 充分使用表已经存在的索引查询

  • 使用left join 或者 not exists 优化 not in 操作

• 禁止使用 select * 查询

  • 会消耗更多的cpu 和io 以及网络带宽资源
  • 无法使用覆盖索引
  • 可较少表结构变更带来的影响

• 禁止使用不含字段列表的 insert 语句

• 避免使用子查询 , 可以把子查询优化 join 操作

  • 子查询结果集无法使用索引
  • 子查询会产生临时表操作 , 如果子查询数据量大则严重影响效率

• 避免使用 join 关联太多的表

  • 建议不超过 5 个

• 减少同数据库的交互次数

  • 数据库更适合处理批量操作

• 使用 in 代替 or

• 禁止使用 order by rand () 进行随机排序

• where 从句中禁止对列进行函数转换和计算

  • where date(createtime) = ‘20170101’ (禁止这样)
  • 上面的可以这样改 where createtime >='20170101' and createtime <'20170102'

• 再明显不会有重复值时 使用 union all 而不是 union

• 拆分复杂的大 sql 为多个 小 sql

数据库操作行为规范

• 超过 100 万行的批量写操作 ,要 分批分次进行操作

• 禁止为程序使用的账号赋予super 权限

• 对于程序连接数据库账号 , 遵循权限最小原则

  • 程序使用数据库账号只能在一个DB下使用 , 不准跨库
  • 程序使用的账号原则上不准有 drop 权限