数据库性能优化：结构设计和优化

一、目的

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

二、设计步骤：

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、字符串作为主键。
• 最好用自增列做索引。