5

水平拆分：对于表数据特别多的，可以进行水平拆分（表结构一致）；

垂直拆分：为了控制表的宽度，提供IO读取效率；

1、经常一起查询的列放一起；

2、text,blob等大字段拆分出到附表中；

为什么要垂直拆分？

列变宽会导致一页中的行数减少，降低IO效率

怎么做垂直拆分？

1. 经常一起查询的列放在一起
   

2. text, blob等大字段拆分出到附表中

为什么要水平拆分？控制表的大小

怎么做水平拆分？主键Hash

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

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

表的水平拆分

数据库表的垂直和水平拆分

            —— 表的垂直拆分

数据库表的垂直和水平拆分

            —— 表的垂直拆分

表的水平拆分：主键ID

表的垂直拆分

什么是数据库设计

数据库设计就是根据业务系统的具体要求，结合我们所选择的DBMS，为这个业务系统构造出最优的数据存储模型。并建立好数据库中的表结构及表于表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。

优良的数据库设计

1、减少数据冗余

2、避免数据维护异常

3、节约存储空间

4、高效访问

数据库设计大致步骤

1、需求分析（数据有哪些、数据有哪些属性，数据和属性它们各自的特点有哪些）

2、逻辑设计（ER图对数据库进行逻辑建模）

3、物理设计（根据选择数据库的特点把逻辑设计转换为物理设计）

4、维护优化（新的需求建表、索引优化、大表拆分）

数据库需求分析

1、了解系统中所要存储的数据

实体与实体之间的关系（1对1，1对多，多对多）

2、了解数据的存储特点

实体的属性特点（唯一标识一个实体）

3、了解数据的生命周期

数据的存储特点（永久、暂时）

数据库逻辑设计

1、将需求转化为数据库的逻辑模型

2、通过ER图的型式对逻辑模型进行展示

矩形：表示实体集

菱形：表示关系集

椭圆：表示实体属性

线段：将属性连接到实体集；将实体连接到联系集

3、与DBMS的选择无关

数据库逻辑设计中的规范

1、第一范式

要求数据库中的表都是二维表（由行和列组成）

2、第二范式

所有单关键字段的表都符合第二范式。

数据库中的表不存在非关键字段对任一候选关键字段的部分依赖

3、第三范式

数据表中的非关键字段对任意候选关键字段的传递函数依赖

4、BC范式

数据表中不存在任意字段对任意候选字段的传递函数依赖

数据库物理设计

1、选择合适的数据库管理系统

Oracle：商业数据库、基于服务器核心数收费、适用于企业级项目、金融业

SQLServer：商业数据库、基于服务器核心数收费

MySQL：开源数据库、互联网项目

PgSQL：开源数据库

2、定义数据表、表及字段的命名规范

a、可读性原则

b、表意性原则

c、长名原则

3、根据所选的DBMS系统选择合适的字段类型

列的数据类型一方面会影响数据存储空间的开销，另一方面也会影响数据查询性能

选择原则：优先考虑数字类型、其次是日期或二进制类型、最后是字符类型。对于相同级别 的数据类型，应该优先选择占用空间小的数据类型

选择角度：

1、对数据进行操作时，同样数据字符的处理要比数字处理慢

2、数据处理以页为单位，列的长度越小，利于性能提升

同级别的选择：

char和varchar

1、列中存储的数据长度都差不多一致时，选char否则选varchar

2、如果列的最大数据长度小于50Byte选char，否则varchar

3、如果这个列很少用，基于节省空间和减少I/O考虑，选择varchar

float和decimal

1、decimal精确存储，float非精确，精确存储选decimal

2、float存储空间开销一般比decimal小，非精确选float

int与datetime

1、字段长度int小于datetime

2、int每次使用要进行函数转换

3、int只能存储到2037-1-19-11-14-07

4、datetime要考虑时间粒度的问题

4、反范式化设计（空间换时间）

目的：为了性能和读取效率的考虑而适当对第三范式的要求进行违反

1、减少表的关联数量

2、增加数据的读取效率

3、反范式化一定要适度

5、其他注意事项

a、如何选择主键

业务主键：标识业务数据，进行表于表间的关联

数据库主键：为了优化数据存储（Innodb会自动生成6个字节的隐含主键）

b、主键是否要顺序增长

c、主键的字段类型所占的空间尽可能小

d、少用外键

1、降低数据的导入效率

2、增加维护成本

3、虽然不建议使用外键、但相关联的列上一定要建立索引

e、避免使用触发器

1、降低数据导入的效率

2、可能会出现意想不到的数据异常

3、使业务逻辑变的复杂

f、严禁使用预留字段（无任何意义）

MYSQL常用的存储引擎

MylSAM：不支持事务、支持表级锁、不适合读写频繁、但读写速度快

MRG_MYLSAM：不支持事务、支持表级锁、不适合全局查找较多、但适合分段归档，数据仓库

Innodb：支持事务、支持MVCC的行级锁、适合事务处理，读写高效

Archive：不支持事务、行级锁、但支持insert，select、占用空间小

Ndb cluster：支持事务、行级锁、高可用性

数据库的优化

1、维护数据字典

a、第三方工具对数据字典进行维护

b、利用数据库本身的备注字段来维护数据字典

2、维护索引

a、选择在where中、group by从句、order by从句中的列

b、可选择性高的列要放在索引前面

c、索引中不要包括太长的数据结构

注意

a、索引不是越多越好，过多的索引会降低读与写的效率

b、定期维护索引碎片

c、在SQL语句中不要使用强制索引关键字

3、维护表结构

a、使用在线变更表结构工具

b、同时对数据字典进行维护

c、控制表的宽度与大小

4、数据库适合操作

a、尽量使用批量操作而不是逐条操作

b、禁止使用select *

c、控制用户使用自定义函数

d、不要使用数据库中的全文索引

5、在适当的时候对表进行水平拆分或垂直拆分

a、垂直拆分

经常一起查询的列放在一起

text，blog等大字段拆分出到附加表

b、水平拆分

Hash Key的方式进行平均拆分

垂直拆分：

1. 经常一起查询的列放在一起

2. text，blob等大字段拆分出到附加表中

如何维护索引

如何选择合适的列建立索引

1、出现在where从句 group by 从句 order by 从句中的列

2、可选择性高的列要放到索引的前面

3、索引中不要包括太长的数据类型

注意事项

1、索引并不是越多越好，过多的索引不但会降低写效率而且会降低读的效率

2、定期维护索引碎片

3、在sql语句中不要使用强制索引关键字

如何维护表结构

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

mysql5.6之后本身支持在线表结构的变更

2、同时对数据字典进行维护

3、控制表的宽度和大小

数据库中适合的操作

1、批量操作VS逐条操作

2、禁止使用select * 这样的查询

3、控制使用用户自定义函数

4、不要使用数据库中的全文索引

表的垂直拆分

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

1、经常一起查询的列放在一起；

2、text,blob等大字段拆分出到附加表中

表的水平拆分

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

hash key 取模 等分表

表的水平拆分

表的垂直拆分

采用主键hash来进行水平拆分

表的水平拆分

表的垂直拆分

优良的数据库设计，减少数据冗余，避免数据维护异常，节约存储空间，高效的访问。