数据库设计

为什么要数据库设计

优良的设计

• 减少数据冗余
• 避免数据维护异常
• 节约存储空间
• 高效的访问

糟糕的设计

• 存在大量数据冗余
• 存在数据插入，更新，删除异常
• 浪费大量存储空间
• 访问数据低效

设计步骤

• 需求分析（数据是什么，数据有哪些属性，数据和属性各自的特点有哪些）
• 逻辑设计（使用ER图进行逻辑建模）
• 物理设计（根据数据库自身的特点吧逻辑设计转换为物理设计）
• 维护优化（新的需求进行建表，索引优化，大表拆分）

需求分析

• 了解系统中所要存储的数据
• 了解数据的存储特点
• 了解数据的生命周期

###要搞清楚的一些问题

1. 实体及实体之间的关系（1对1，1对多，多对多）
2. 实体所包含的属性有什么？
3. 那些属性或属性的组合可以唯一标识一个实体

逻辑设计

• 第一范式 数据库表中的所有字段都是单一属性，不可再分的。（第一范式要求数据库中的表都是二维表）
• 第二范式 数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。部分函数依赖是指存在着组合关键字中的某一关键字觉得非关键字的情况。
• 第三范式 如果数据表中不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。
• BC范式 数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。

物理设计

1. 选择合适的数据库管理系统。
2. 定义数据库、表及字段的命名规范。
3. 根据所选的DBMS系统选择合适的字段类型。

###数据库选择 常见的DBMS系统：oracle、sqlserver商业数据（企业级项目），mysql、pgsql开源数据库（互联网项目） oracle大的事务操作有优势。 sqlserver只用于winserver，与.net结合好。

mysql常用的存储引擎

表与字段的命名规则

1. 可读性原则 使用大写和小写来格式化的库对象名字以获得良好的可读性。例如：使用CustAddress
2. 表意性原则 对象的名字应该能够描述它所标识的对象。
3. 长名性原则 尽可能少用或不用缩写。

字段类型选择原则

列的数据类型一方面影响数据存储的开销，另一方面也会影响数据查询性能。 当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或者二进制类型，最好是字符类型。 对于相同级别的数据类型，应该优先选择占用空间小的类型。

日期和时间数据类型

date 3字节，日期，格式：2014-09-18

time 3字节，时间，格式：08:42:30

datetime 8字节，日期时间，格式：2014-09-18 08:42:30

timestamp 4字节，自动存储记录修改的时间

year 1字节，年份

整型

tinyint 1字节，范围（-128~127）

smallint 2字节，范围（-32768~32767）

mediumint 3字节，范围（-8388608~8388607）

int 4字节，范围（-2147483648~2147483647）

bigint 8字节，范围（+-9.22*10的18次方）

浮点型

float(m, d) 4字节，单精度浮点型，m总个数，d小数位

double(m, d) 8字节，双精度浮点型，m总个数，d小数位

decimal(m, d) decimal是存储为字符串的浮点数

字符串数据类型

char(n) 固定长度，最多255个字符

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

1. 在对数据进行比较（查询条件、join条件及排序）操作时：同样的数据，字符处理往往比数字慢。
2. 在数据库中，数据处理以也为单位，列长度越小，利于性能提升。

char与varchar如何选择

1. 如果列中要存储的长度差不多是一致的，则应该考虑用char；否则考虑用varchar。
2. 如果列中最大数据小于50byte，则一般也考虑用char。（当然，如果这个列很少用，则基于节省空间和减少I/O的考虑，还是可以选择用varchar）
3. 一般不宜定于大于50byte的char类型咧。

decimal与float如何选择

1. decimal用于村粗精确数据，而float只能用户存储非精确数据。
2. float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，精确到15位小数只需要8个字节）。

###时间类型如何存储###

1. 使用int来存储时间字段的优缺点。

优点：字段长度比datetime小。 缺点：使用不方面，要进行函数转换。 限制：只能存储到2038-1-19 11:14:07即2^32

2. 需要存储的时间粒度

年 月 日 小时 分 秒 周

###其他注意事项###

如何选择主键

1. 区分业务主键和数据库主键

业务主键用户标识业务数据，进行表与表之间的关联 数据库逐渐为了优化数据存储

2. 根据数据库的类型，考虑逐渐是否要顺序增长
3. 逐渐的字段所占空间要尽可能的小

避免使用外键约束

1. 降低数据导入的效率
2. 增加维护成本
3. 虽然不建议使用外键约束，但是相关联的列上一定要建立索引

避免使用触发器

1. 降低数据导入的效率。
2. 可能会出现意想不到的数据异常。
3. 使业务逻辑变的复杂。

关于预留字段

1. 无法准确的知道预留字段的类型。
2. 无法准确的指导预留字段中所存储的内容。
3. 后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的。
4. 严禁使用预留字段。

###反范式化表设计###

为了性能和读取效率的考虑，而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，即使有空间来换取时间。

1. 减少表的关联数量
2. 增加数据的读取效率
3. 反范式化一定要适度

##维护和优化##

1. 维护数据字典
2. 维护索引
3. 维护表结果
4. 在适当的时候对表进行水平拆分或垂直拆分

###维护数据字典###

1. 使用第三方工具对数据字典进行维护
2. 使用数据库本身的备注字段来维护数据字典。

mysql为例，增加comment字段。

3. 到出数据字段

   select a.table_name,b.table_comment,a.column_name,a.column_type,a.column_comment from information_schema.columns a join information_schema.tables b on a.table_schema = b.table_schema and a.table_name = b.table_name where a.table_name='table_name';

###维护索引###

1. 出现在where从句，group by从句，order by 从句的列
2. 可选择性高的列要放在索引的前面（非必须，数据库大都会经过sql优化器优化）
3. 索引中不要包括太长的数据类型

注意事项

1. 索引并不是越多越好，过多的索引不但会降低写效率，而且会降低读的效率（sql优化器会在多个索引中选择，降低了效率）
2. 定期维护索引碎片
3. 在sql语句中不要使用强制索引关键字

###表结构的维护###

1. 使用在线变更表结构的工具

mysql 5.5之前可以使用pt-online-shcema-change mysql 5.6之后本身支持在线表结构的变更

2. 同时对数据字典进行维护
3. 控制表的宽度和大小

数据库中适合的操作

1. 批量操作 VS 逐条操作
2. 禁止使用select * 这样的查询
3. 控制使用用户自定义函数
4. 不要使用数据库中的全文索引

###表的垂直拆分###

为了控制表的宽度可以进行表的垂直拆分

列1	列2	列3	列4	列5	列6	列7
0:2	1:2	2:2	3:2	4:2	5:2	6:2

拆分成

列1	列2	列3	列4
0:2	1:2	2:2	3:2

列1	列5	列6	列7
0:2	4:2	5:2	6:2

1. 经常一起查询的列放到一起
2. text，blob等大字段拆分出到附加表中

###表的垂直拆分###

为了控制表的大小可以进行表的水平拆分

主键hash key拆分