MySQL数据库结构优化(一)

数据库结构优化的目的

1、减少数据的冗余

2、尽量避免数据维护中出现更新、插入、删除异常

3、节约数据存储空间

4、提高查询效率

数据库设计步骤

1、需求分析：全面了解产品设计的存储需求、数据处理需求、数据的安全性和完整性

2、逻辑设计：设计数据的逻辑存储结构(数据实体间的关系、解决数据冗余和数据维护异常)

3、物理设计：根据所使用的的数据库特点进行数据库设计(数据库类型、字段类型、存储引擎)

4、维护优化：根据实际情况对索引、存储结构等进行优化

数据库范式化设计(这里只分析常用的前三种)

第一范式：(列不能够再拆分成其他几列)

数据库表中的所有字段都只具有单一属性；

单一属性的列是由基本数据类型所构成；

设计出来的表都是简单的二维表；

例如：一表中有联系人，性别，电话字段，这就没有达到第一范式要求，电话还可以拆分成移动电话和固定电话

第二范式：基于第一范式

要求一个表中只具有一个业务主键；

没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分；

例如：一张订单ORDER表，含 ORDER_ID、PRODUCT_ID、PRODUCT_NAME、NUMBER、PRICE、TOTALPRICE、ORDERTIME  字段，这样的表显然是不符合第二范式规则的，ORDERTIME、NUMBER、PRICE都只依赖于主键ORDER_ID，但是PRODUCT_NAME只依赖于PRODUCT_ID，故可以将订单表拆分为订单表和商品表，主键分别为ORDER_ID与PRODUCT_ID

第三范式：基于第二范式

非主键列必须直接依赖于主键，不能存在传递依赖

例如：一张订单ORDER表，含ORDER_ID、USER_ID、USER_NAME、ORDER_DATE等字段，USER_ID、USER_NAME、ORDER_DATE非主键列均依赖于主键ORDER_ID，在满足范式二的基础上，USER_NAME直接依赖的是非主键列USER_ID，通过USER_ID来挂钩传递ORDER_ID，故不满足范式三。

优点：

可以尽量的减少数据冗余；范式化的更新操作比反范式化更快；范式化的表通常比反范式化的表小

缺点：

对于查询需要对多个表进行关联；更难进行索引优化

反范式化设计：通过增加冗余数据或数据分组来提高数据库读性能

优点：

可以减少表的关联；可以更好的进行索引优化

缺点：

存在数据冗余和维护异常；对数据的修改成本更高；

物理设计阶段

1.定义数据库、表、字段的命名规范

可读性原则、表意性原则、长名原则

2.选择合适的存储引擎：

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

当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应优先选择占用空间小的数据类型。

整数类型

tinyint：存储空间占1个字节；smallint：占2个字节；mediumint：占2个字节；int：占4个字节；bigint：占8个字节

实数类型

字符串类型

varchar：用于存储变长字符串，只占必要的存储空间；选择上来说，应该使用最小的符合需求的长度；varchar(n) n表示字符数，例如，varchar(5)可以存下 '12345' 或 'asdfg' 或 '好久不见了'

char：固定长度；char(10) 如果只存储了 '012345'，则会补充4个空格

日期类型

 DATETIME类型：YYYY-MM-DD HH:MM:SS[微秒]；datetime类型与时区无关； 

 TIMESTAMP类型：依赖于指定的时区；在行的修改数据时，可以自动修改timestamp列的值；

DATE和TIME类型：……

建议：不要使用字符串类型来存储日期时间类型；

4.建立数据库结构