电商数据库设计

电商数据库设计

围绕购物流程

mysql5.7
mysql SQLyog
Linux Shell脚本

用户登录-选购商品-加购物车-检查库存-提交订单-货到付款-订单付款-发货

模块设计

用户模块 用户注册登录验证
商品模块 前后台商品管理
订单模块 订单购物车生成和管理
仓配模块 仓库库存和物流的管理

数据库设计规范

逻辑设计-物理设计
实际工作中 逻辑设计+物理设计
表名 字段名 字段类型

数据库字段设计规范
-数据库对象名称必须使用小写字段 下划线分隔 DbName not Equal dbname

-数据库对象禁止使用mysql保留关键字
select id,username,from,age from tb_user 报错
select id,username,from,age from tb_user 不报错

-数据库对象命名见名识意 不超过32字符 用户数据库mc_userdb 用户数据表user_account

-所有临时表以tmp为前缀并且以日期为后缀

-备份库 备份表必须以bak为前缀并以日期为后缀

-所有储存相同数据的列名和列类型必须一致

-所有表是使用Innodb存储引擎 支持事务 行级锁 更好的恢复性 高并发下性能好

-数据库和表的字符集统一使用UTF8

统一字符集可以避免由于字符集转换成的乱码
-使用comment从句添加表和列的注释 数据字典的维护

-控制单表数据量的大小 控制在500w行内

-限制取决于存储设置和文件系统

-分区表在物理上表现为多个文件 逻辑上为一个表

-谨慎选择分区键 跨分区效率更低 物理分表方式管理大数据

-禁止表中使用预留字段

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

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

-禁止开发测试环境连接生成环境数据库

索引设计规范
-限制表索引数量 建议单张表索引不超过5个

-每个Innodb表必须有一个主键 不能使用频繁更新的列作为主键

-不使用UUID MD5 HASH 字符串作为主键

-SELECT UPDATE DELETE语句的WHERE从句中的列包含在ORDER BY,GROUP BY,DISTINCT中的字段多表的关联列

-避免建立冗余索引和重复

-对于频繁查询优先考虑覆盖索引

-尽力避免使用外键

-外键用于保障数据参照完整性 建议在业务端实现

数据库字段设计规范
-优先选择符合存储需要的最小的数据类型

-将字符串转换为数字类型存储
INET_ATON('255.255.255.255') = 4294967295
INET_NTOA(4294967295) = '255.255.255.255'
(字符串需要15个字节 int只需要4个字节)

-对于非负数数据采用无符号整型进行存储
SIGNED INT -2147483648~2147483648
UNSIGNED INT 0~4213213123

-VARCHAR(N) 中的N代表的是字符数 不是字节数
使用UTF-8存储汉字 Varchar(255) = 765个字节

-避免TEXT BLOG类型数据

-避免使用ENUM数据类型 (修改用ALTER )

-尽可能把所有列定义为NOT NULL (索引NULL需要增加额外空间)

-使用TIMESTAMP或者DATETIME存储时间
TIMESTAMP 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
超出时间用DATETIME

-数据类型
财务相关 decimal类型 计算时不会丢失精度

数据库SQL开发规范
-充分利用表上已有的索引 避免使用双%号的查询条件 如:a like '%123%'

-一个sql只能利用到复合索引中的一列进行范围查询

-使用left join或者not exist优化not in操作

-设计时 对以后的数据库扩展考虑

-程序连接不同的数据库

-为数据库迁移和分库分表留余地

-禁止使用SELECT * 必须使用 SELCT <字段列表查询> (会消耗更多的CPU和IO以及网络带宽资源) 可以减少表结构表化的影响

-禁止使用不含字段列表的INSERT语句
INSERT into t values('a','b','c');
INSERT into t(c1,c2,c3) values('a','b','c');
可以减少表结构表化的影响

-避免使用子查询 可以把子查询优化为join查询(子查询结果集无法使用索引)
子查询会产生临时表操作 子查询数据量大会严重影响效率

-避免使用join关联太多表 不要超过5个

-减少同数据的交互次数
数据库更适合批量操作
合并多个相同的操作到一起 可以提高处理效率
alter table t1 add column c1 int,change column c2 c2 int

-使用in代替or

-禁止使用order by rand()随机排序
会把表中符合条件的数据装载到内存中排序 消耗大量CPU
推荐程序中获取随机值 然后从数据中获取

-where从句中禁止对列进行函数转换和计算
where date(createtime)= '20160901' 会无法使用索引
改为where createtime >= 20160901 and createtime < '20160902'

-明显不会有重复值时使用UNION ALL而不是UNION
union会把所有数据放到临时表中再操作 会去重操作
union all不会再对结果集重新操作

-拆分复杂的大sql为小sql

数据库操作行为规范
-超过100w行的批量写操作 要分批多次操作
大批量操作可能会造成严重主从延迟
避免产生大量日志

-对于大表使用pt-online-schema-change修改表结构

-禁止为程序使用的账号使用super

-程序使用账号只能在一个DB上使用 原则上不能有drop权限

posted @ 2017-11-16 15:45 WWSASUKE 阅读(...) 评论(...) 编辑 收藏