数据库设计心得
数据库设计心得
什么是数据库设计
数据库设计就是根据业务系统的需求，综合我们选择的DBMS，为业务结构创造出最优的数据存储模型。并建立好数据库中的表结构与表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以提高对已经存储数据的访问。
优良的数据库设计
- 减少数据冗余
- 避免数据维护异常
- 节约存储空间
- 高效访问
数据库设计的大致步骤
- 需求分析（数据有哪些，数据有哪些属性，数据与属性它们各自的特点）
- 逻辑设计（ER图对数据库进行逻辑建模）
- 物理设计（根据选择的数据库的特点把逻辑设计转换成物理设计）
- 维护优化（新的需求建表，索引优化，大表拆分）
数据库需求分析
- 了解系统中所要存储的数据
- 实体与实体之间的关系（1对1，1对多，多对多）
- 了解数据存储的特点
- 实体属性的特点（唯一标识一个实体）
- 了解数据的生命周期
- 数据存储特点（永久，暂时）
数据库逻辑设计
- 将需求转化成数据库的逻辑模型
- 通过ER图的形式对逻辑模型进行展示
- 矩形：实体集
- 菱形：关系集
- 椭圆：实体属性
- 线段：将实体连上属性，将实体连上关系
- 数据库的逻辑设计与DBMS的选择无关
数据库逻辑设计规范
- 第一范式
- 要求数据库中的表都是二维表
- 第二范式
- 要求数据库中的表不存在非关键字段对候选字段的部分依赖
- 第三范式
- 要求数据库中的表不存在非关键字段对候选字段的传递依赖
数据库物理设计
- 选择合适的数据库管理系统
- Oracle：商业数据库，基于服务器核心数收费、适用于企业级项目和金融业
- SQLServer：商业数据库，基于服务器核心数收费，Windows独家。
- MySQL：开源数据库，互联网项目
- PgSQL：开源数据库
- 定义数据库命名规范
- 可读性原则
- 表意性原则
- 长名原则
- 根据选择的DBMS系统选择合适的字段
- 列的数据类型一方面会影响数据存储开销，另一方面会影响查询性能
- 选择原则
- 优先考虑数字类型，其次是日期二进制类型，最后是字符类型。
- 对于相同级别的数据类型，应该选择占用小的类型
- 选择角度
- 对于数据操作时，同用数据字符的处理要比数字的处理慢上很多
- 数据处理以页为单位，列的长度越小，利于性能提升
- 同级别选择
- char与varchar的区别
- 如果数据存储的长度差不多时，优先选择char
- 如果列的最大数据长度小于50Byte，优先选择char
- 如果这个列很少使用，基于节省空间与减少I/O考虑，优先选择varchar
- float和decimal的区别
- decimal实现精准存储，精度存储选择decimal
- float存储空间开销一般比decimal小
- 反范式设计
- 目的：为了性能与读写性能的考虑而适当对第三方式进行违反
- 减少表的关联数量
- 增加数据的读取效率
- 反范式化一定要适度
- 其他注意事项
- 如何选择主键
- 业务主键：标识业务数据，进行表与表间的关联
- 数据库主键：为了优化数据存储自动生成的主键（Innodb会自动生成6个字节的隐含主键）
- 主键是否要顺序增长
- 主键的字段类型要尽量小
- 少用外键
- 降低了数据的导入效率
- 增加维护成本
- 关联的列上一定要建立索引
- 避免使用触发器
- 降低数据的导入效率
- 可能会出现意想不到的数据异常
- 使业务逻辑变得复杂
- 严禁使用预留字段
MySQL常用数据引擎
- MySAM
- 不支持事务
- 不支持表级锁
- 不适合频繁读取
- 读写速度快
- MRG_MySAM
- 不支持事务
- 不支持表级锁
- 不适合全局查找
- 适合分段归档，数据仓库
- Innodb
- 支持事务
- 支持MVCC事务锁
- 适合事务处理
- 读写高效
- Archive
- 不支持事务
- 行级锁
- 仅支持insert，select
- 占用空间小
- Ndb cluster
- 支持事务
- 行级锁
- 高可用性
数据库优化
- 维护数据字典
- 第三方工具对数据字典进行维护
- 利用数据本身的备注字段来维护数据字典
- 维护索引
- 选在在where，group by从句，order by从句中的列
- 可选择性高的列要放在索引前面
- 索引中不要包括太长的数据结构
- 维护表结构
- 使用在线变更表结构工具
- 同时对数据字典进行维护
- 控制表的宽度与大小
- 数据库选择合适操作
- 尽量选择批操作，而不是逐条结婚
- 禁止使用select *
- 控制用户使用自定义函数
- 不要使用数据库中的全文索引
- 表拆分
- 垂直拆分
- 经常一起查询的列放一起
- text，blog等大字段拆分出到附加表
- 水平拆分
- 表过大时，选择Hash，Key进行平均拆分
