数据库性能优化:结构设计和优化
2019-11-02 本文已影响0人
快乐的提千万
一、目的
- 减少数据冗余,但不是完全没有。
- 避免异常:插入异常,一个实体依赖另一个实体;更新异常,更新一个,其他的都要更新。
二、设计步骤:
-
需求分析:
全面了解产品设计的存储需求、数据处理需求。
数据的安全性和完整性。 -
逻辑设计:
设计数据的逻辑存储结构。解决数据冗余和数据维护异常。 -
物理设计:
表结构设计。
关系型和非关系型数据库(缓存)。 -
维护优化:
根据实际情况对索引和存储结构进行优化。
三、数据设计范式:
- 第一范式:要求有主键,并且要求每一个字段原子性不可再分
- 第二范式:要求所有非主键字段完全依赖主键,不能产生部分依赖
- 第三范式:所有非主键字段和主键字段之间不能产生传递依赖
反范式设计:
空间换时间。
提高查询效率或者为了业务需求,使用冗余数据或反范式的设计。
范式:
优点:可以尽量地减少数据冗余,数据表更新快、体积小。
缺点:
查询需要多个表进行关联。
更难进行索引优化。
反范式:
减少表关联,更好地进行索引优化。
存在数据冗余和数据维护异常,对数据修改需要更多的成本。
四、物理设计
定义数据库、表及字段的命名规范。
可读性,表意性,长名原则
选择合适的存储引擎。
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、字符串作为主键。
- 最好用自增列做索引。