Database 数据库设计概念

数据库 阅读量

从概念上整理下数据库基本设计笔记。

数据是项目的核心

简介

What ?

根据业务系统的具体需要，结合所选的 DBMS，为业务系统构造出最优的数据存储模型（建立好数据库中表结构、表与表之间关联），使之能高效的对应用系统中的数据进行存储和访问。

这个过程就是数据库设计。

Why ?

  • 减少数据冗余
  • 避免数据维护异常（插入、更新、删除）
  • 节约存储空间
  • 高效的访问

数据库设计的好坏会在用户量激增的时候体现出来，最典型的缺陷就是数据库设计之处没有一个良好的结构带来的 「慢查询」 问题，这种原因带来的慢查询也是非常难以维护的。

How ?

数据库设计的基本步骤如下：

  • 需求分析
  • 逻辑设计
  • 物理设计
  • 维护优化

下面将对它们进行详细解释。

需求分析

why ?

  • 了解系统所要存储的数据 => 数据是什么
  • 了解数据的存储特点 => 数据有哪些属性
  • 了解数据的 lifetime => 数据和属性各自的特点有哪些

how ?

  • 系统中有哪些实体、以及其用途

    往往一个数据库实体对应一个应用功能模块。

  • 实体包含的属性有什么

  • 哪些属性或属性的集合可以唯一标识一个实体

  • 实体有什么存储特点

比如：

  • 用户模块／user 实体：规模随系统上线时间增加，需要永久存储
  • 购物车模块／goodscar 实体：不用永久存储（需要设置归档、清理规则）
  • 商品模块／ goods 实体：对于下线商品可以归档存储
  • 订单模块／order 实体：永久存储，后期需要分表、分库

对于一些增长速度较快的实体，需要考虑分库、分表、归档。（如日志）

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

逻辑设计

what ?

  • 将需求转化为数据库的逻辑模型
  • 使用 ER 图形式对数据库进行逻辑建模和展示
  • 同选用的具体 DBMS 无关

逻辑概念

  • 关系： 就是表。一个关系通车对应一张表。
  • 元组： 表中的一行。
  • 属性： 表中的一列。每个属性都有一个属性名。
  • 选码/联合主键： 表中的某个属性组，可以唯一确定一个元组。
  • 主码/主键： 一个关系有多个候选码，选定其中一个为主码。
  • 域： 属性的取值范围。
  • 分量： 元组中的一个属性值。

ER 图例说明

  • 矩形： 表示实体集，矩形内写实体集的名字。属性名有下划线的表示主键。
  • 菱形： 表示联系集。

联系集的两端连线指明几对几关系，内部文字填写具体的联系关系。

比如联系实体集订单和商品的联系集名称应该是“包含”，联系实体集商品和购物车的联系集名称应该是“选购”，等等。

  • 椭圆： 表示实体的属性。

  • 线段： 连接属性和实体集、实体集和联系集、实体集和实体集。

设计范式

数据库设计范式，指的是：如何设计数据库，即如何存储实体之间的关系才更好。

一个好的设计范式代表了简洁、高效、结构清晰，同时最大限度地避免数据操作异常和数据冗余。

操作异常

插入异常

如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体是，那么这个实体对应的表就存在插入异常。

更新异常

如果更改表所对应的某个实体实例的单独属性时，需要将多行进行更新，那么该表就存在更新异常。

删除异常

如果删除表的某一行来反映某个实体实例失效时，导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常。

可见，如果一个表中存在插入异常，则必存在更新异常和删除异常。

数据冗余

是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，那么这个表就存在数据冗余。

大量的数据冗余会给数据一致性的维护带来困难。

常用范式

1NF

要求：

  • 表必须是二维表。
  • 表的每个字段，都是只有一种原子类型的、不可再分的单一值。

数据库表中的所有字段都是单一属性、不可再分的。这个单一属性是由基本的数据类型所构成的。

第一范式是为了要排除 重复组 的出现，所采用的方法是要求数据库的每个列的值域都是由原子值组成；每个字段的值都只能是单一值。

2NF

要求：

  • 必须符合 1NF。
  • 当表中主键为联合主键时，不存在 非关键字段，对联合主键的任一字段的部分函数依赖。（部分函数依赖指的是，存在某个非关键字，是由联合主键中的某个字段唯一决定，而不是联合主键同时决定的情况）

数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系：每个非主键属性必须独立于任意一个候选键的任意一部分属性。如果有哪些属性只和一个主键的一部分有关的话，就得把它们独立出来变成另一个数据表。

如果一个数据表的主键只有单个字段的话，它就一定匹配第二范式。

检查数据表里的每个字段，确认它们是不是都和关键词（主键）完全相关， 这样才能知道这个数据表是不是匹配第二范式； 如果不是的话，就把那些不完全相关的字段移到独立的数据表里。

3NF

要求：

  • 必须符合 2NF。
  • 确保所有不是主键的字段都和彼此没有相依关系。

如果表中，不存在非关键字段，对任意候选关键字段的传递函数依赖，则符合第三范式。

比如表 goods 中有字段 name, category, category_desc，name 是主键（即候选关键字段），category_desc 其实是由 category 决定的，而 category 又是由 name 决定的，因此 category_desc 间接依赖了 name，这种间接依赖关系就叫做传递函数依赖。

此外，两个非主键 category_desc 和 category 存在相依关系，因此该 goods 表不符合 3NF。

这个例子中，符合 3NF 的表设计应该是：

  • goods 表：id, name
  • category 表：id, name, desc
  • category 表：id, name, desc

BCNF

要求：

  • 必须满足 3NF。
  • 当表的主键是联合主键时，联合关键字之间也不能存在函数依赖关系
  • 数据间的关系是奠基在键上、以整个键为考量、而且除了键之外不考虑其他因素。

BCNF 是 3NF 的更严格版规范，3NF 只对非关键字段和候选关键字之间是够有传递依赖函数关系有限制，而 BCNF 新增了对联合关键字之间依赖关系的限制。

如何正确使用范式

前 3 大范式，基本代表了大多数据库设计所要遵循的范式。不符合 2NF 和 3NF 的表，往往同时存在插入异常、删除异常、更新异常和数据冗余。

现在数据库设计最多满足 3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库 IO更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。

物理设计

根据数据库自身特点，把逻辑设计转换为物理设计。本过程需要完成以下任务：

选择合适的 DBMS

不同的 DBMS 对数据库、表、字段等的命名可能有差别，因此需要先选择合适的 DBMS。

对于企业级项目来说，商业数据库，比如 Oracle/SQLServer 等更适合（根据服务器 CPU 核数收费）；而对于互联网项目来说，开源数据库，比如 MySQL/MariaDB/PgSQL 等更适合。

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

统一大小写、表前缀、字段前缀，命名规则等。

注意有些 DBMS 是大小写敏感，MySQL 可以设置是否大小写敏感。

命名规范在团队开发中是很有必要的，甚至需要建立专用的数据字典和手册。

对象命名原则

  • 可读性：合理使用大小写和下划线

goods_categtory 或者 GoodsCategory 总比 goodscategory 易读。

  • 表意性：顾名思义

对于表，表名应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能。

  • 长名原则：避免歧义

一般来说，除了数据库名字之外，其他命名，尽可能少使用或者不使用缩写。

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

首先要熟悉所选 DBMS 的字段类型有哪些，使用场景，优劣。

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

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

这是因为：

  • 对数据进行比较操作（查询条件、join 条件、排序等）时，同样的数据，字符处理速度往往比数字慢

  • 在数据库中，数据处理以页码为单位，列的长度越小，越利于性能提升。因为列越小，一个页中能存储的数据也就越多，在加载相同的数据的时候加载页的数量就会变少，从而提高 I/O 性能。

不同是 DBMS 中页的大小是不同的，MySQL 默认( Innodb ) 一页是 16 KB，SQLServer 默认是 8 KB。

  • 对字符串的处理是需要根据数据字典中的排序规则有关的，即需要搜索字典中的排序规则，所以比数字的效率低。

常见考虑实例参考

char 和 varchar

在 char 和 varchar 之间如果性能优先则考虑 char。同时，存储等量数据 char 比 varchar 占用的空间更小。

如果列中的最大数据长度小于 50 Byte，则一般也考虑用 char。同时，在 MySQL 中，由于不同的字符集占用的字节数是不同的，因此，上面的 50 个字节需要根据不同的字符集来灵活计算字符数，比如是 UTF8 的时候 50 个字节大概只能存储 16 左右个字符。

当然，如果这个列很少用，则基于节省空间和减少 I/O 的角度，还是可以考虑 varchar。

varchar 需要额外的字节来存储变长数据的字典和起始位置(检索)，所以长度太小就不 适合使用 varchar 存储。

decimal 与 float

选择原则：

  • decimal 用于存储精确数据，而 float/double 只能用于存储非精确数据。
  • 由于 float 的存储空间开销一般比 decimal 小（精确到 7 位小数只需要 4 个字节，到 15 位需要 8 字节），因此在非精确数据优先的情况下，优先选择 float 类型。

时间类型

  • 使用 int 类型存储时间字段的优缺点
    • 优点：字段长度比 datetime 小
    • 缺点：使用不方便，要进行函数转换
    • 限制：只能存储到 2038-01-19 11:14:07 秒
  • 需要存储的时间粒度：年、月、日、时、分、秒、周。粒度要求不高则考虑节省空间的数据结构。
  • 时间戳

timestamp 的不足之处，在于只能表示 1970 ~ 2038 年，因为 INT 只能表示 2^32 （2147483648）内的范围，此外，还有新旧版本零值不兼容的问题。

如何选择主键

  • 区分业务主键和数据库主键

业务主键，用于标识业务数据，进行表与表之间的关联。

数据库主键，为了优化数据存储。（InnoDB 会生成 6 个字节的隐含主键）

  • 根据数据库的类型，考虑主键是否要顺序增长

有些数据库是按主键的顺序逻辑存储的。

  • 主键的字段类型所占空间要尽可能的小

对于使用聚合索引方式存储的表，每个索引后都会附加主键信息。因此为了节省空间最好是人为定义一个小于 6 个字节的主键。

避免使用外键约束

外键的缺点：

  • 降低数据库导入的效率

在数据导入的时候，会检查是否存在外键约束，检查外键约束是非常耗时的。所以，在高并发的应用场景下不要使用外键约束。

  • 增加维护成本

虽然不建议使用外键约束，但是两张表相关联的列上一定要建立索引。建立索引的目的是为了提高检索速度。

避免使用触发器

触发器的缺点：

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

InnoDB 中每一种触发器支持的触发器个数（ 6种：before／after … ）是有限制的，如果使用其他的工具再次使用触发器的时候就可能出现不能创建触发器的情况此外，在业务逻辑变更而其他人员不知道的情况下就会影响业务逻辑的正常运行。

严禁使用预留字段

有些时候为了提高扩展性，可能会预留一些字段，但是这样做弊大于利：

  • 无法准确知道预留字段的类型
  • 无法准确知道预留字段需要存储的内容
  • 后期维护预留字段的成本，和增加一个字段需要的成本是相同的

在表中使用预留字段，是一种过度设计的行为，而正确的应该是按需设计。

反范式化设计

反范式化是为了性能和读取效率的考虑而适当地对 3NF 的要求进行违反，而允许存在少量的数据冗余。

数据操作异常、数据冗余不是说不能有，但是要可控。

大多数情况下遵循范式化设计是没有问题的，但是少数情况下，不得不或者说更好的做法是为了效率，采用「空间换时间」的做法，因为范式化设计毫无疑问会使表数量增多，大量的连表查询等等，增加 DBMS IO 负担，在一些追求效率的应用场景下或许并不适合，这时候可以考虑采用反范式化设计。

总的来说，进行反范式化设计的理由如下：

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

什么时候可以反范式化？

那么，实际项目中，什么情况下进行范式化设计才合理呢？

先严格按照 3NF 设计好表，然后用一些复杂的业务查询逻辑来测试这些表，如果为了获得完整的业务数据，需要的连表次数超过 3 次，而该查询场景频率高、追求效率，那么，此时可以考虑反范式化设计。

DBMS 周边环境

操作系统、开发语言、应用场景，谁更合适就用谁。

可参考的依据有：社区成熟度、社区活跃度、开发者数量、更新频率。

维护优化

当数据库逻辑设计完成后，就是根据使用过程中的具体情况，选择进行维护和优化操作。

在维护和优化过程中需要做的事有以下几点：

数据字典

数据字典是对表、列的描述，对于业务升级来说是重要的依据。

由于需求是经常变化的，所以先前的索引，表结构等可能后来不再适用于现在的场合，因此需要做一些维护操作以适应新的需求。

how ?

  • 使用第三方工具，不同的 DBMS 有不同的三方工具
  • 利用数据库本身的备注字段来维护

注意： 更改表备注的时候只能更改表备注而不要更改其他任何数据。

  • 导出数据字典

比如 MySQL 就可以直接查询 information_schema 数据得到

1
2
3
4
5
6
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 = 'users';

索引

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

  • 出现在 where、group by、order by 从句中的列
  • 可选择性高的列要放到索引的前面

目前的 DBMS 中在执行 SQL 语句之前都会经过 SQL 优化器进行重新编译，这个过程会自动根据索引信息和统计信息来进行排序和优化从而自动选择出适合该 SQL 的索引，所以这点不是必须的。

  • 索引中不要包含太长的数据类型

由于数据库是以页来存储数据的，比如 MySQL 一页就是 16 KB，一页中如果条数越多则索引查找速度越快，因此索引中的列就不要太长。

MySQL 支持对前缀进行索引，因此假如是 text 等大类型的数据要建立索引的话，就可以只对前 16 个字节的数据进行索引，或者是转换为 md5 后再建立索引。

如何维护索引

  • 索引不是越多越好，过多的索引会同时降低读写效率。

如果一个 SQL 中索引太多， SQL 优化器在进行分析的时候就会耗费大量的时间。

  • 定期维护索引碎片。

索引碎片就是零碎的小文件，类似磁盘碎片，碎片太多会减少每页的数据存储，从而影响查询效率。

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

Oracle 中可以强制索引关键字，但是 MySQL 最好不要这样做，因为数据量是在不断变化的，原来的索引可能不再适用而被更名或删除，而如果开发人员不知道的话，原来的 SQL 语句就会出错。

表结构

how ?

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

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

5.5 之前的 MySQL 在使用 pt-online-schema-change 等三方工具的之前，表中尽量不要出现触发器，因为该工具本身就会为表加上 3 个触发器，比如增、删、改触发器，而 MySQL 对于一个表中的触发器类型只能有一个，如果原表已经使用了工具中将要使用到的触发器，那么就不能正常工作。

  • 同时对数据字典进行维护

表结构变了，相应的字段功能、注释等一般也随着改变，因此需要同步更新。

  • 控制表的宽度和大小

一页总存储的数据越多，I/O 效率更高。

当表结构随业务需求变宽，需要考虑垂直拆分。

大表拆分

优化数据库表 I/O 两种方式：

  • 水平拆分解决了表中数据量的问题，
  • 垂直拆分解决了表的宽度问题。无论何种拆分，总的数据量是不能减少的，每一份小表中的数据是不同的。

垂直拆分

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

所谓的垂直拆分，意思就是把一张表的多个字段分散到多张表中。

比如，表 t 有 10 个字段：c1, c2, …, c10。c1 是主键。那么可以将表 t 拆分为 t1 和 t2 子表，t1 含有的字段为：c1, c2, …, c9；t2 含有的字段为：c1, c10。

当表变宽后，势必导致一页中存储的数据越少，从而降低 I/O 效率，此时需要进行垂直拆分。

垂直拆分的拆分依据

  • 经常一起查询的列放到一起，避免关联操作
  • text、blob 等二进制大字段拆分到附加表中

水平拆分

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

当表中的数据量非常大的时候，对大表的主键进行哈希操作后，平均分配到多张小表中。

数据库中适合的操作

  • 批量操作 VS 逐条操作

数据库中适合进行批量操作，逐条操作适合在程序中进行。

  • 禁止使用 select * 查询

select * 的使用过多会造成 I/O 的浪费，因为有大量不需要的数据记录。

  • 控制使用用户自定义函数

自定义函数过多会影响到索引的工作（函数会使列中的索引失效）。

  • 不要使用 MySQL 中的全文索引

MySQL 中的全文索引需要建立额外的索引文件，以及对中文的支持不好，建议使用专业的搜索引擎工具，而不要使用数据库自带的全文索引功能。

数据库性能瓶颈是 I/O

现在数据库的性能瓶颈不在于 CPU 而在于磁盘的 I/O，因此，在考虑性能优化的时候，优先考虑能够减少磁盘 I/O 数的措施。

FAQ

  • 在表使用 JSON 类型字段是否破坏了 1NF ？

参考

Powered by Hexo and Hexo-theme-hiker

Copyright © 2017 - 2023 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :