Mysql数据库设计

设计的步骤

② 概念结构设计：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速度，列越短，即每个字段定义的大小。

• 反范式化设计

命名

1. 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否）。

2. 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

3. 表名不使用复数名词。

4. 临时表以tmp前缀，以日期为后缀。备份库以bak前缀，以日期后缀。

5. 禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。

6. 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。

7. 表的命名最好是加上“业务名称_表的作用”。

8. 库名与应用名称尽量一致。

字段类型

1. 合适的字符存储长度，如127 or 200百以上smallint、3 or 6万以上mediumint、8 or 16百int、20 or 40亿以上bingint
2. 小数类型为decimal，禁止使用float和double（除非不用精确）。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
3. 如果存储的字符串长度几乎相等，使用char定长字符串类型。如果列中最大数据长度小于50Byte，也用char，大于这个值就用VARCHAR。
4. varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
5. 所有存储相同数据的列名和列类型必须一致
6. 使用inet_aton 和 int_ntoa实现字符串和数字类型的转换

反范式化

字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

• 不是频繁修改的字段。

• 不是varchar超长字段，更不能是text字段。

商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

补充

1. 表必备三字段：id, gmt_create, gmt_modified。 其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。gmt_create,gmt_modified的类型均为datetime类型，前者现在时表示主动创建，后者过去分词表示被动更新。

2. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

3. 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

4. 库和表字符集合统一UTF8

5. 表和字段都需要添加注释

6. 禁止预留字段、存储图片等二进制数据、在线上做压力测试、从开发/测试环境连接数据库

7. 避免触发器

8. 冷热分表

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

数据库维护和优化

索引

强制

1. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

2. 超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

3. 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。（一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。）

4. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 （索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。）