昆明java培训学校告诉你Mysql数据库的设计和优化？

在JAVA开发中数据库的学习也是我们需要了解的，截下来几篇文章都是关于数据库的设计和应用，那么java课程培训机构http://www.kmbdqn.cn/废话不多说开始学习吧！

数据库的设计

数据库设计是基础，数据库优化是建立在设计基础之上的。好的数据库一定拥有好的设计。

数据库设计的目标是为用户和各种应用系统提供一个信息基础设施和高效的运行环境。

数据库的三大范式

第一范式1NF:所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第二范式2Nf:第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式3Nf:所有字段必须与主键直接相关，而不是间接相关。也可以理解为字段不要和其他非主键字段相关.

注意:这三个范式尽可能去遵守,不是一定要墨守成规.这只是让我们设计的表的时候,越靠近这些范式,可以使字段尽量的减小冗余.但是有时候也可以根据实际需要小小的违背一下.但是第三范式违反一下还可以接受,但是第一范式别违反.

数据库设计的步骤

需求分析阶段

准确了解与分析用户需求(包括数据与处理)。是整个设计过程的基础，是最困难、最耗费时间的一步。

概念结构设计阶段

是整个数据库设计的关键--设计数据库的E-R模型图，确认需求信息的正确和完整

Entity_Relationship---实体之间的关系

一对一

一对多

多对一

Log File物理结构

从 ib_logfile0和 ib_logfile1这两个文件的物理结构可以看出，在Log Header部分还是有些许差异的， ib_logfile0会多一些额外的信息，主要是checkpoint信息。

并且每个Block的单位是512字节，对应到磁盘每个扇区也是512字节，因此redo log写磁盘是原子写，保证能够写成功，而不像index page一样需要double write来保证安全写入。

我们依次从上到下来看每个Block的结构

Log File Header Block

Log Goup ID，可能会配置多个redo组，每个组对应一个id，当前都是0，占用4字节

Start LSN，这个redo log文件开始日志的lsn，占用8字节

Log File Number，总是为0，占用4字节

Created By，备份程序所占用的字节数，占用32字节

另外在ib_logfile0中会有两个checkpoint block，分别是 LOG_CHECKPOINT_1/ LOG_CHECKPOINT_2，两个记录InnoDB Checkpoint信息的字段，分别从文件头的第二个和第四个block开始记录，并且只在每组log的第一个文件中存在，组内其他文件虽然没有checkpoint相关信息，但是也会预留相应的空间出来。这里为什么有两个checkpoint的呢？原因是设计为交替写入，避免因为介质失败而导致无法找到可用的checkpoint的情况。

Log blocks

请点击输入图片描述

log block结构分为日志头段、日志记录、日志尾部

Block Header，占用12字节

Data部分

Block tailer，占用4字节

Block Header

这个部分是每个Block的头部，主要记录的块的信息

Block Number，表示这是第几个block，占用4字节，是通过LSN计算得来的，占用4字节

Block data len，表示该block中有多少字节已经被使用了，占用2字节

First Rec offet，表示该block中作为第一个新的mtr开始的偏移量，占用2字节

Checkpoint number，表示该log block最后被写入时的检查点的值，占用4字节

这篇文章是一篇小小的总结，如有疏漏请指出，万分感谢。

一个分销系统当中，基本有几个要点，我以商品二级分销模型为例子。

1.销售人员发展客户，自己和上级能够获得奖励。

2.当客户购买产品的时候，销售人员和上级也能获得奖励。

3.发展用户或者销售人员时，需要绑定他们的关系。

4.销售人员能够看自己下级的人员并查看自己的销售业绩。

That's all.

当时刚接触这个需求的时候，由于本来用户表有一个 pid(parent_id) 的字段，来表示自己的上级销售人员。我当时竟然可怕的是用了 ppid，pppid来表示更上级的人员。。。。。。我的天，居然有人会做出这种事。多么可怕的一段黑历史。

目前在维护一个有上千亿行数据的文件系统，分表分8192张。表结构里面有一个字段，专门维护文件夹层级，里面的值遵循一定的数据格式，长得很像linux的文件系统层级结构（例如:/usr/bin/local/），可以说是一模一样。突然，这不就是树结构吗？然后想到了分销系统的树结构

用户表字段设计：user_id，parent_id，tree，level，money

这里涉及到一些家喻户晓的人，分别是，A，B和Cn

他们有相关从属的关系。

用表来表示：

A进了某养生品公司，注册了会员。

于是A成为了这个系统的第一个用户

| user_id| parent_id | tree| level| money|

| ------------- |:-------------:| -----:|

|A|没有爸爸|/|1|0|

这时A走在大街上，看到B，说：“你听说过安利吗? blablabla”

然后B扫码成为了A的下级会员，A获得一块钱的发展会员奖励，并告诉他，你可以告诉你的新朋好友哦，卖出这么好的产品，可以分成哦。

| user_id| parent_id | tree| level| money|

| ------------- |:-------------:| -----:|

|A|没有爸爸|/|1|1|

|B|A|/A/|2|0|

那么B开始努力推广安利这个产品

给认识的熟人（C1，C2，C3）安利了一发

| user_id| parent_id | tree| level| money|

| ------------- |:-------------:| -----:|

|A|没有爸爸|/|1|4(1+3)|

|B|A|/A/|2|3(0+3)|

|C1|B|/A/B/|3|0|

|C2|B|/A/B/|3|0|

|C3|B|/A/B/|3|0|

这个表设计的优点

1.可以通过 tree 很快的找到 C1 用户的所有上级用户进行相关 *** 作

2.可以通过 like tree *** 作，利用索引，查询 A 用户所有的下级进行相关计数 *** 作，可以提高 A 的推广积极性

3.通过用 tree 属性去关联订单表，给上级返利或者查询自己的推广所得，也是很方便。

其实这个分销的树形结构像极了文件系统的标识符，我也是在做相关mysql存储文件关系的业务当中类比出如此高效快速的查询方法。

这个设计模式的缺点也很明显，一旦关系转移(类似文件夹移动)，就会产生大量的tree字段修改。所幸的是：关系转移可以异步处理，但查询关系就必须同步。鉴于这种 *** 作特点，也就可以采取这种模式。

---