文章目录
- 设计的步骤
- 需求分析
- 1、数据是什么、有什么属性、特点（时效性？核心数据？增长情况？）、哪些属性或属性组合可以唯一标识一个实体
- 2、实体与实体之间的关系（1对1？1对多？多对多？）
- 概念结构设计
- 局部E-R图设计
- 合并成总体E-R图
- 逻辑结构设计
- 逻辑设计的一些条件
- 函数依赖
- 范式
- 物理结构设计
- 命名
- 字段类型
- 反范式化
- 补充
- 数据库维护和优化
- 索引
设计的步骤
② 概念结构设计：E-R图
③ 逻辑结构设计：将E-R图转换为某一种数据模型，并优化。
④ 物理结构设计：选哪种数据库
⑤ 数据库实施
⑥ 数据库维护和优化：建表、索引优化、大表拆分骤
需求分析
1、数据是什么、有什么属性、特点（时效性？核心数据？增长情况？）、哪些属性或属性组合可以唯一标识一个实体
用户模块：数据需要永久存储、随时间逐渐增加、是否需要分库分表？
商品模块：永久存储，下线商品归档存储
订单模块：永久存储、分库分表
购物车模块：不用永久存储（设置归档、清理规则）
供应商模块：永久存储
2、实体与实体之间的关系（1对1？1对多？多对多？）
概念结构设计
关系（一个关系对应一张表）、元组（一行）、属性（一列）、候选码（唯一确定一个元组的属性组）、主码（其中一个候选码）、主属性（可以组成候选码的属性）、域（属性取值范围）、分量（元组中的一个属性值）
E-R图：矩形（实体集，上面的数据模块）、菱形（联系集（关系名称），上面实体与实体之间的关系）、椭圆（实体属性、下划线标识主码）
局部E-R图设计
1．确定局部范围
各个部门或各个主要功能作为局部
2．确定实体与属性
① 属性是不能再分的数据项；
② 联系只发生在两实体之间；
③ 原则上，能够作为属性，就不要作为实体。
合并成总体E-R图
1．消除各局部E-R图的冲突问题。
2．按公共实体名合并，生成初步E-R图。
3．消除冗余的属性和冗余的联系，生成总体E-R图。
逻辑结构设计
通过ER图将需求转化为数据库的逻辑模型，与具体的DBMS系统无关
逻辑设计的一些条件
① 冗余应尽可能少；
多个地方存在或者可以通过某列计算得到
② 应尽可能避免插入、更新、删除异常；
插入异常（有这个就会有下面两个）：某实体随另一个实体的存在而存在，如新开课程没有学生选修时，新开课程的课程号、课程名插不进去。
更新异常：更改某实体的某一属性，需要更新多行
删除异常：删除某一行数据后，另一个不同实体信息丢失。
③ 消去关系中不合适的属性依赖关系。(如选修某门课的学生毕业了，在删除学生信息的同时，把课程信息也删除掉。)
函数依赖
完全函数依赖：x’是x的真子集，存在x→y，但对每一个x’都有x’!→y，则称y完全函数依赖于x。如（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在R关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；
部分函数依赖：存在x→y，若x’是x的真子集，存在x’→y，则称y部分函数依赖于x。如（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）
传递函数依赖：x→y,y→z,但y!→x, 则x传递函数依赖z，如(学号)->(宿舍),宿舍！=学号，(宿舍)->(费用),费用!=宿舍
范式
一个关系的非主属性函数依赖于主码的程度。一个关系从低级范式向高级范式的转换过程称为关系规范化。
第一范式（1NF）条件：若关系R的所有属性不能再分，即没有HBase中的列族。
第二范式（2NF）:若关系R∈1NF，消除非主属性对主码的部分依赖，则称R∈2NF。通常实现有增加一列unique标识，或者拆分。例如：如果一个表有商品名称、供应商名称和其他非主属性的商品相关列，而相同商品名称的行仅仅是供应商名称不同（所以商品名称、供应商名称这两列都可作为唯一标识），从而存在部分依赖。解决方法是商品和供应商各自单独一张表，并加上一张映射两表关系的表。
第三范式（3NF）：消去非主属性对主码的传递依赖。例子：一个表有商品名称、分类和分类描述，这里存在“商品名称 - 分类 - 分类描述”的依赖关系，分类描述对主码是传递依赖。那么就应该把 “商品名称” 和 “分类和分类描述” 拆分为两张表，并加上一张映射两表关系的表。
BCNF范式：复合关键字之间不存在依赖关系，即去除主属性对于候选码的部分函数依赖与传递函数依赖。例子：一个表有商品id、供应商、供应商联系人，其中供应商和联系人一一对应。此时“商品id + 供应商” 或 “商品id + 供应商联系人”可以确定一行信息，而供应商和“供应商联系人+商品id”存在部分依赖关系。解决方法是把这个表拆分为“供应商 + 商品id” 和 “供应商 + 供应商联系人”两个表。
物理结构设计
- 选择数据库管理系统、存储引擎（如无意外都是Innodb）
- 定义数据库、表及字段命名规范
- 字段类型：处理速度，数字 > 日期 > 字符；IO速度，列越短，即每个字段定义的大小。
- 反范式化设计
命名
- 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否）。
- 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
- 表名不使用复数名词。
- 临时表以tmp前缀，以日期为后缀。备份库以bak前缀，以日期后缀。
- 禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。
- 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。
- 表的命名最好是加上“业务名称_表的作用”。
- 库名与应用名称尽量一致。
字段类型
- 合适的字符存储长度，如127 or 200百以上smallint、3 or 6万以上mediumint、8 or 16百int、20 or 40亿以上bingint
- 小数类型为decimal，禁止使用float和double（除非不用精确）。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
- 如果存储的字符串长度几乎相等，使用char定长字符串类型。如果列中最大数据长度小于50Byte，也用char，大于这个值就用VARCHAR。
- varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
- 所有存储相同数据的列名和列类型必须一致
- 使用inet_aton 和 int_ntoa实现字符串和数字类型的转换
反范式化
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
- 不是频繁修改的字段。
- 不是varchar超长字段，更不能是text字段。
商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。
补充
- 表必备三字段：id, gmt_create, gmt_modified。 其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。gmt_create,gmt_modified的类型均为datetime类型，前者现在时表示主动创建，后者过去分词表示被动更新。
- 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
- 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。
- 库和表字符集合统一UTF8
- 表和字段都需要添加注释
- 禁止预留字段、存储图片等二进制数据、在线上做压力测试、从开发/测试环境连接数据库
- 避免触发器
- 冷热分表
- 对大表使用pt-online-schema-change修改表结构
数据库维护和优化
索引
强制
- 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
- 超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。
- 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。（一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。）
- 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 （索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。）
