Mysql数据库设计

Mysql数据库设计
https://www.cnblogs.com/code2one/p/9871912.html

目录
•设计的步骤
•需求分析
•概念结构设计
•逻辑结构设计
•物理结构设计
•数据库维护和优化◦索引
◦分区
◦其他

目的：减少数据冗余、避免数据维护异常、节约存储空间、高效访问

设计的步骤

① 需求分析
② 概念结构设计：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的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。）

推荐

1.如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
where a=? and b=? order by c; 索引：a_b_c
WHERE a>10 ORDER BY b; 索引a_b无法排序。索引中有范围查找，那么索引有序性无法利用

2.利用覆盖索引来进行查询操作，避免回表。能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。

3.利用延迟关联或者子查询优化超多分页场景。即要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
SELECT a.* FROM 表1 a,
(select id from 表1 where 条件 LIMIT 100000,20 ) b
where a.id=b.id

4.至少要达到 range 级别，要求是ref级别，如果可以是consts最好。

5.建组合索引的时候，区分度最高的在最左边（另外字段长度小、最频繁的也是在左边）。但如果存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。（一个SQL只能利用复合索引中的一列进行范围查询，用了非等号的列，后面列的索引就不会用到了）

6.防止因字段类型不同造成的隐式转换，导致索引失效。如where语句中使用的格式与设定不同

7.主键选择：建议自增ID。不使用UUID、MD5、HASH、字符串；不使用频繁更新的列

8.索引选择：select、update、delete语句中的where从句中的列；包含在order by、group by、distinct中的字段、join的关联列

9.重复索引：如id int primay key和unique(id)重复

冗余索引：如组合索引包含了主键；index(a,b,c), index(a,b)这里一旦涉及到a的查询，就会用到第一个index。

维护表结构

表拆分

分区

Hash分区：根据分区键、分区数来划分，其中键值必须为int或通过函数可转化为int

Range分区：默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值。场景：日期或时间类型、定期按分区范围清理历史数据。如果用了range分区，所有查询最好包括分区键

List分区：自定义具体哪个值方法哪个分区。

避免跨分区查询，在where从句中包含分区键。

主键或唯一索引会是分区键的一部分
PARTITION BY HASH(customer_id)/Hash(UNIX TIMESTAMP(login_time))
PARTITIONS 4;

PARTITION BY RANGE (customer_id) (
PARTITION p0 VALUES LESS THAN (10000),
PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (10000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

PARTITION BY LIST (login_type) (
PARTITION p0 VALUES (1,3,5,7,9),
PARTITION p1 VALUES (2,4,6,8)
);

#增加/删除分区
ALTER TABLE customer_login_log ADD PARTITION (PARTITION p4 VALUES LESS THAN(2018))
ALTER TABLE customer_login_log DROP PARTITION p0;

归档

分区数据归档迁移条件

•Mysql >= 5.7

•结构相同

•归档到的数据表一定要是非分区表

•非临时表；不能有外键约束

•归档引擎为archive

迁移和删除（否则新数据还会插入该分区）

ALTER TABLE customer_login_log EXCHANGE PARTITION p1 WITH TABLE arch_customer_login_log
ALTER TABLE customer_login_log DROP PARTITION p1

更改引擎（之后只能读不能写）

ALTER TABLE arch_customer_login_log ENGINE=ARCHIVE

其他
1.连接不同库要用不同账号

参考：

高性能可扩展MySQL数据库设计及架构优化 电商项目，sqlercn，https://coding.imooc.com/class/79.html

阿里巴巴Java开发手册