生产环境如下
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 权限