电商数据库设计之MySQL 数据库设计规范

生产环境如下

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

项目说明

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

Created with Raphaël 2.1.0 用户登录 选购商品 加入购物车 检查库存 提交订单 货到付款? 发货 订单付款 yes no

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

模块模块说明
用户模块完成用户注册和登录验证
商品模块前后台商品管理和浏览
订单模块订单及购物车的生成和管理
仓配模块仓库库存和物流管理
接下来就是数据库设计, 先说一说数据库的设计规范

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

数据库命名规范

  • 所有的数据库对象名称必须使用小写字母并使用下划线分割(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 权限
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值