【MySQL性能优化】03. 数据库结构设计和优化

待我称王封你为后i 2022-01-17 05:11 220阅读 0赞

### 一、目的 ###

*  减少数据冗余，但不是完全没有。
 *  避免异常：插入异常，一个实体依赖另一个实体；更新异常，更新一个，其他的都要更新。

### 二、设计步骤： ###

1.  需求分析：  
    全面了解产品设计的存储需求、数据处理需求。  
    数据的安全性和完整性。
2.  逻辑设计：  
    设计数据的逻辑存储结构。解决数据冗余和数据维护异常。
3.  物理设计：  
    表结构设计。  
    关系型和非关系型数据库（缓存）。
4.  维护优化：  
    根据实际情况对索引和存储结构进行优化。

#### 三、数据设计范式： ####

*  第一范式：要求有主键，并且要求每一个字段原子性不可再分
 *  第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
 *  第三范式：所有非主键字段和主键字段之间不能产生传递依赖

##### 反范式设计： #####

空间换时间。  
提高查询效率或者为了业务需求，使用冗余数据或反范式的设计。

##### 范式: #####

优点：可以尽量地减少数据冗余，数据表更新快、体积小。  
缺点：  
查询需要多个表进行关联。  
更难进行索引优化。

##### 反范式： #####

减少表关联，更好地进行索引优化。  
存在数据冗余和数据维护异常，对数据修改需要更多的成本。

### 四、物理设计 ###

定义数据库、表及字段的命名规范。  
可读性，表意性，长名原则  
选择合适的存储引擎。

#### 1. 命名 ####

*  小写加下划线。
 *  禁止使用mysql关键字。
 *  见名识意。
 *  临时表tmp开头，备份表bak开头，时间戳结尾。
 *  列名和列类型一致。

#### 2. 为表的字段选择合适的数据类型。 ####

*  数字类型>日期或二进制>字符串。优先选择空间小的类型。  
    IP这样的字符串可以考虑转换成数字。
 *  varchar 用于存储边长字符串，只占用必要的存储空间。  
    适用于：最大长度远大于平均长度，很少更新，多字符集。
 *  char 定长，会删除末尾的空格，最大255  
    适用于：长度相近，短字符串，经常更新。
 *  日期：  
    datatime：与时区无关，占用8个字节。  
    timestamp：时间戳，4个字节，依赖于时区。  
    在行的数据修改时可以自动修改timestamp列的值。  
    用int存储日志不如用timestamp。
 *  避免使用blob、text、enum类型。
 *  每个字段尽量具有not null 属性。

#### 3. 其他考虑 ####

*  单表数据量控制在500万。
 *  谨慎使用分区。分区表示物理上是多个文件，逻辑是一个表。
 *  冷热数据分离。减少表的宽度，减少IO，保证热数据的缓存命中率。常用的数据放到一列。
 *  禁止在表中预留字段。
 *  控制索引，太少和太多都会影响效率。
 *  必须加主键，不要使用联合主键。不使用UUID、MD5、HSAH、字符串作为主键。
 *  最好用自增列做索引。