MySQL数据库结构优化

青旅半醒 2022-03-01 05:30 251阅读 0赞

# 数据库结构优化 #

## 1. 介绍 ##

数据库结构优化的目的是为了

1.  减少数据冗余
2.  尽量避免数据维护中出现更新，插入和删除异常.
3.  节约数据存储空间
4.  提高查询效率

前三点可以通过范式来分表，而最后一点则需要反范式化（或索引等其他手段）来做到。

即使不了解范式的人，也能看出来反范式和范式是一对反义词，所以再实际情况下，应该根据情况进行判断.

## 2. 数据库结构设计 ##

### 2.1 需求分析 ###

> 几乎80%的问题都因为需求分析没有做好，或者需求发生了改变。

1.  全面了解产品设计的存储需求
2.  存储需求
3.  数据处理需求
4.  数据的安全性和完整性

### 2.2 逻辑设计 ###

> 就是将需求整理为对象和关系，E-R图，流程图等逻辑模型将在这一阶段被使用.

1.  设计数据的逻辑存储结构
2.  数据试题之间的逻辑关系，解决数据冗余和数据维护异常

> 数据库范式可以很大情况的帮助我们进行“**逻辑设计**”。

### 2.3 物理设计 ###

> 选择适合的数据库，然后建表，通过上一阶段的逻辑模型，选择好对应的主键，外键，索引等表的结构。

根据所使用的数据库特点进行表结构设计

关系型数据库：Oralce，SQLServer，MySQL，postgressSQL

非关系型数据库： mongo，Redis，Hadoop.

### 2.4 维护优化 ###

在后续漫长的运行阶段，需要不断的根据实际情况对索引，存储结构等进行优化,是大家说的不多，干的不少的一个典型。

## 3. 数据库设计范式 ##

1.  第一范式
    
     *  数据库表中的所有字段都自由单一属性
     *  单一属性的列是由基本的数据类型锁构成的
     *  设计出来的表都是简单的二维表
     *  综上： 几乎只要能在当今数据库中建立起来的表都是第一范式，但是很可能有很大的优化空间
     *  举例： 我们有一个学生选课表，
     *  
    
    <table> 
     <thead> 
      <tr> 
       <th>属性</th> 
       <th align="center">类型</th> 
       <th>描述</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>stu_id</td> 
       <td align="center">int(11)</td> 
       <td>学生id</td> 
      </tr> 
      <tr> 
       <td>stu_name</td> 
       <td align="center">VARCHAR(10)</td> 
       <td>学生姓名</td> 
      </tr> 
      <tr> 
       <td>stu_sex</td> 
       <td align="center">int(1)</td> 
       <td>学生性别</td> 
      </tr> 
      <tr> 
       <td>course_id</td> 
       <td align="center">int(8)</td> 
       <td>课程id</td> 
      </tr> 
      <tr> 
       <td>course_name</td> 
       <td align="center">VARCHAR(10)</td> 
       <td>课程名</td> 
      </tr> 
      <tr> 
       <td>course_score</td> 
       <td align="center">int(3)</td> 
       <td>学分</td> 
      </tr> 
      <tr> 
       <td>score</td> 
       <td align="center">int(3)</td> 
       <td>学生考试成绩</td> 
      </tr> 
     </tbody> 
    </table>
    
     *  太过于冗余，明显可以拆成：student表+stu\_course表+course表,但是这符合第一范式.
2.  第二范式
    
     *  要求一个表中只具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系。
     *  简单说： 就是上述学生选课表中，明显出现了可以拆分为以stu\_id为主键的部分和以course\_id为主键的部分，stu\_id和course\_id组成了复合主键，但是其他属性中有很大一部分只依赖其中一个就可以确定.
     *  所以进行改进，将其改为第二范式.
     *  学生表
     *  
    
    <table> 
     <thead> 
      <tr> 
       <th>属性</th> 
       <th>类型</th> 
       <th>描述</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>stu_id</td> 
       <td>int(11)</td> 
       <td>学生id</td> 
      </tr> 
      <tr> 
       <td>stu_name</td> 
       <td>VARCHAR(10)</td> 
       <td>学生姓名</td> 
      </tr> 
      <tr> 
       <td>stu_sex</td> 
       <td>int(1)</td> 
       <td>学生性别</td> 
      </tr> 
     </tbody> 
    </table>
    
     *  课程表
     *  
    
    <table> 
     <thead> 
      <tr> 
       <th>属性</th> 
       <th>类型</th> 
       <th>描述</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>course_id</td> 
       <td>int(8)</td> 
       <td>课程id</td> 
      </tr> 
      <tr> 
       <td>course_name</td> 
       <td>VARCHAR(10)</td> 
       <td>课程名</td> 
      </tr> 
      <tr> 
       <td>course_score</td> 
       <td>int(3)</td> 
       <td>学分</td> 
      </tr> 
     </tbody> 
    </table>
    
     *  学生选课关系表
     *  
    
    <table> 
     <thead> 
      <tr> 
       <th>属性</th> 
       <th>类型</th> 
       <th>描述</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>stu_id</td> 
       <td>int(11)</td> 
       <td>学生id</td> 
      </tr> 
      <tr> 
       <td>course_id</td> 
       <td>int(8)</td> 
       <td>课程id</td> 
      </tr> 
      <tr> 
       <td>score</td> 
       <td>int(3)</td> 
       <td>学生考试成绩</td> 
      </tr> 
     </tbody> 
    </table>
    
     *  这样满足第一范式的表就呗拆分成了满足第二范式的表
3.  第三范式
    
     *  指每一个非主属性几步部分依赖于也不传递依赖于业务主键，也就是说在第二范式的基础上消除了非主属性对主键的传递依赖
     *  对于上一次的表，我们可以明显的看出没有数据依赖已经满足了第三范式，但是为了讲解，如果我们的学生表有系的信息（系名，系主任，系楼），那么其也满足第二范式，但是数据冗余，我们将系拆分出来作为一个表，学生中只包含一个外键（系名）即可.
4.  BC 范式（仅作了解即可）
    
     *  BC范式是第三范式的一种特例。
     *  比如，在第三范式中，如果学生中可以有自己的手机号，手机号是独一无二的，学生ID也是独一无二的，这在第三范式中是没有问题的，因为学生ID唯一确定学生手机号。
     *  在BC范式中，这不允许出现，因为BC范式要求不能从任何字段推出来主码（我们可以从学生手机号推出来学生ID)，也就是不能出现与主键一一对应的字段（有些苛刻）
     *  解决方法：将学生手机号单独提取出来一个表，与学生ID进行映射。
5.  第四范式（拓展而已）
    
     *  4NF一定属于BCNF
     *  第四范式是很蛋疼的一个东西，比如上述的学生表，通过第四范式的分解会变成
     *  stu\_id+stu\_name,（一个表）。stu\_id + stu\_sex（又是一个表）
     *  单纯因为stu\_id唯一确定一个stu\_name,stu\_id唯一确定stu\_sex，所以是太过于苛刻的范式。
6.  总结
    
    1.  并不是数据库范式越高越好，BC范式和第四范式已经到了苛刻的范围。
    2.  有时会在数据冗余与范式之间做出权衡，在实际的数据库开发过程中，往往会允许一部分的数据冗余来减少数据库连接。我们将这个步骤称为：反范式化设计。

## 反范式化设计 ##

反范式化设计就是在范式化设计的基础上，因为在各式各样的需求上，我们需要冗余数据来加快查询，或者不得不冗余数据，否则没办法完成需求.

我以前曾做过一款报表系统，其中就用到了反范式化设计。

需求：需要查询过去一年内，一月内，一周内所有需求超过10个 的订单的用户手机号，记住：我们需要的是当时的用户手机号，如果简单的使用外键，那么如果用户注销信息，我们就失去了用户手机号，所以在每个订单中我们不写入用户id，而使用用户name + tel的方式存储用户信息，这明显违背了第二范式，但是这却能完成需求。

即使没有这个需求，如果我们使用id的话，我们需要级联用户表，势必会让查询速度减慢，所以也可以使用冗余的字段来加快查询速度.

## 总结范式化和反范式化 ##

1.  范式化：
    
     *  优点
        
         *  可以尽量的减少数据冗余，数据表更新快体积小
         *  范式化的更新操作比反范式化更快(因为每一个表的大小都更小了)
     *  缺点：
        
        1.  降低查询性能，因为越高的范式往往意味着更多的级联，进行更多的查询。
        2.  更难进行索引优化（因为表多了，所以索引更难建立了）
2.  反范式化
    
     *  优点
        
        1.  可以减少表的级联
        2.  可以更好的进行索引优化
     *  缺点
        
        1.  存在数据冗余及数据维护异常
        2.  对数据的修改需要更多的成本(CPU,内存等硬件设备和人力资源)

## 物理设计阶段 ##

#### 定义数据库，表以及字段的命名规范 ####

*  可读性原则：下划线分割单词
 *  表意性原则：见名只意
 *  长名原则：尽可能别使用缩写。

#### 选择合适的存储引擎 ####

*  MySQL中，别问，问就是InnoDB

#### 为表中的字段选择合适的数据类型 ####

*  优先选择数字类型
    
     *  数字类型最好比较
 *  其次是日期或二进制类型
    
     *  日期有专用的函数可以供操作,二进制与数字类型类似，但是不太好理解
 *  最后才是字符类型
    
     *  比较起来太过于麻烦，而且占用空间太大。
     *  VARCHAR和CHAR之间的区别：建议选择VARCHAR。
        
         *  VARCHAR类型在低于255的时候，使用多余的一位来记录长度。
         *  VARCHAR类型在高于255的时候，使用多余的两位来记录长度。
         *  CHAR是定长
 *  相同级别的数据类型，占用空间越小越好。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTYxOTQ4_size_16_color_FFFFFF_t_70]

对于时间类型：我们建议使用TIMESTAMP（时间戳，支持时区）类型，而不建议使用Datetime类型。如果存储时间超过1970-01-01到2038-01-19，大于1000-01-01：0：0：0，小于9999-12-31：23：59：59的话，还是使用Datetime类型；

此外还有Date类型和time类型。一个专门记忆日期，一个专门记忆时间。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTYxOTQ4_size_16_color_FFFFFF_t_70 1]

小tips（针对MySQL5.5之后的默认引擎：InnoDB)：

> 1.  主键应该尽可能的小（因为索引都包含主键信息，过大会造成索引体积增加,降低速度)
> 2.  主键应该是顺序增长的，因为InnoDB的存储顺序和逻辑顺序相同，顺序增长可以避免随机IO的产生
> 3.  InnoDB的主键可以和业务主键可以不同，我一般习惯于单独建立一个自增的id作为主键，不与业务产生任何关系。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTYxOTQ4_size_16_color_FFFFFF_t_70]: /images/20220301/0cbf9f7f3fae42c5a52733a76f4f9d85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTYxOTQ4_size_16_color_FFFFFF_t_70 1]: /images/20220301/c76d4bda466a4aec8c892b461d92aad9.png