MySQL进阶的增删改查操作,包含各类查询的详细解释和操作以及表的设计讲解

2.表的设计

2.1表的分类

对于大多数企业级应用系统而言，数据库是整个应用系统的基石，因此数据库的表设计（也被称为schema设计）也就成为整个系统设计的重中之重。表设计的不好或者不合理，不仅会影响系统性能，而且会增加开发和集成的复杂性，甚至埋下隐患，最终会导致一系列的问题，例如数据不一致性问题等。概括而言，需要根据业务需求和系统功能，采用如下步骤设计表。

1. 确定数据库类型。
2. 判断表的类型，并据此构建表的列，确定表名。
3. 设计表的主键以及表之间的关联关系。
4. 优化设计，评估访问性能并根据查询模式添加索引。

应用系统往往涉及很多表,这些表的涉及存在先后顺序,而表之间也具有各种关联和依赖关系.因此,表的涉及并不是一蹴而就的，步骤2、3和4在大多数时候也不是清晰可分的，甚至整个设计也是一个循环迭代的过程，需要在多个步骤之间反复多次，以逐步的细化和优化。

基于所支持应用的不同，数据库可以划分为两大类，分别为面向分析型应用的 Online Analytical Processing （简称为OLAP）和面向事务型应用的 Online Transaction Processing （简称为OLTP）。OLAP用于 支持数据挖掘 、 统计报表 、 数据预测 等统计分析功能， 插入 、 更新 和 删除 这些写入操作较少，但是对于 数据读取 和 数据处理 的要求非常高。OLTP用于支持 事务处理 功能，需要全面满足操作的 原子性 （Atomicity）、 一致性 （Consistency）、 事务隔离性 （Isolation）和 持久性 （Durability），即所谓的ACID标准。

表设计的第一个步是根据业务需求，确定应用是 分析型 ，还是 事务型 ，从而确定数据库的类型是 OLAP ，还是 OLTP 。两种类型的数据库在设计理念和设计方法是完全不同的。本文主要讨论针对于OLTP的表设计，在下文中如果没有特别指出，那么所指的都是 OLTP 数据库。在大多数场景中，事务型应用也需要数据统计分析功能，但是通常并不会为此构建专门的 OLAP 数据库，而是基于现有的OLTP数据库支持这些统计分析功能。为此，需要数据库以 支持在线事务处理 为主，同时 辅助支持统计分析 功能。这种情况造成数据库的表往往可以划分为如下三类:

• 业务表 ，其操作特点是 随机增 、 随机删 、 随机改 、 随机查 。业务表来源于需要应用系统处理的业务实体，业务实体所包含的基本属性和状态被称为 业务属性 ，其与业务表的列之间存在对应关系。
• 日志表 ，其操作特点是 顺序增 、 不删 、 不改 、 多查 。日志表用于 记录原始日志或者历史数据 ，顾名思义，之所以被成为日志表，是因为这些日志和数据一旦生成，就不会被更改，也不会被删除。
• 汇总表 ，其操作特点是 顺序增 、 不删 、 少改 、 多查 。对于数据统计分析功能，如果直接使用日志表无法满足性能要求，则可以引入汇总表。在本质上汇总表是以 预先汇总数据 和 存储汇总结果 为代价，来 提高数据统计分析功能的访问性能 。基于不同的业务场景和业务需求，既可以在将 数据插入日志表 后，实时地以 逐条方式插入或者更新汇总表 ，也可以 间隔一定周期 ，定时地以批量方式读取日志表中的数据并执行汇总操作，然后批量地将汇总结果插入汇报表。

2.2表的构建

对于业务表、日志表和汇总表三种不同类型的表，不仅所对应的功能不同，设计目标不同，而且表的构造过程也不尽相同。

业务表的目标是 减小冗余数据 ，以提高数据的 完整性 （Integrity）和 一致性 （Consistency），其构造过程如下所示。

1. 归纳和抽象出需要持久化的业务实体 。业务实体是指在系统应用中需要依据业务规则/逻辑进行各种处理的对象，其中一部分业务实体需要 设计专门的表 ，以在数据库中存储相应的数据，而其他业务实体，或者衍生自需要持久化的业务实体，或者为临时性业务实体，因此无需为其设计表。
2. 总结相应的业务属性 。不同于业实体，业务属性往往为基本的数据类型，例如 小数、整数、字符串 等。应用系统依据业务逻辑对业务实体执行一系列的处理，这些处理最终能够被分解为对于业务属性的增、删、改、查操作。
3. 基于 业务实体 和 业务属性 ， 设计对应的表和列 。表和表的列分别对应于业务实体和业务属性。根据功能需求，有时还会增加一些额外的列，例如created_time和status等，用于支持一些辅助性的处理功能。

冗余数据的字面意思是 多余的和不必要的数据 。冗余数据的一种 极端情况 是 重复数据 ，可能所在列名不同，甚至所在表也可能不同，但是在两列中对应的数据是完全相同的。冗余数据的另一种常见情况是 数据依赖 ，即一列数据可以由其他一列或者几列数据推导出来。例如商品表中的三列： 原价、现价和折扣 ，因为 现价=原价×折扣 ，所以其中任何一列都可以由其他两列计算而来，因此存在数据依赖，仅仅需要保留其中两列即可，具体去除那一列需要根据 业务需求 和 使用场景 来决定。此外，关系冗余也是一种常见的冗余数据，包括 部分依赖 和 传递依赖 。 部分依赖 是指在使用复合主键时非主键的列仅仅依赖于部分主键，而不是依赖于整个主键。 传递依赖 是指非主键的列 不直接 依赖于主键，而是依赖于其他非主键的列。为了消除冗余关系，需要根据具体情况，或者删除一些相关的列，或者 将冗余关系抽取出来独立建表 。无论属于那种数据冗余，都需要从业务逻辑和业务含义上来分析和判断，尝试更改一列数据，看看更改此列数据后，是否需要进一步地更改这个表或者其他表的列，以判断是否存在依赖此列的数据。对于业务表而言，一旦出现冗余数据，那么在更新部分冗余数据时，必需同时额外地更新冗余数据的其他部分，否则将会出现数据不一致问题。显而易见， 冗余数据将会大大增加数据更新的复杂性 ，所以必须在业务表中消除各种形式的冗余数据。

设计业务表 主键 时，采用如下原则。

1. 建议优先使用 存在业务含义 的、 类型为整数 的并且 具有唯一性 的 一列或者多列 为主键，即现有的一列或者多列为整数类型并且 能够唯一地标识一行数据 ，那么就采用这一列或者多列做主键。如果主键为多列，那么也被称为复合主键。
2. 如果不存在列满足设计原则1，那么建议 创建自增长主键 ，即设计由关键字 NOT NULL 和 AUTO_INCREMENT 修饰的整数列作为主键。

针对于业务表主键的设计原则，有如下几点说明。其一，之所以建议优先采用具有业务含义的主键，是因为可以获得更好的性能。 具有业务含义的列常常作为查询或者更新条件 ，作为主键可以 更加快速地定位数据在磁盘上的存储位置 。虽然自增长主键能够实现顺序插入，具有更好的插入性能，但是数据查询和数据更新的操作频率要远远大于数据插入操作， 减小查询和更新操作的时延 可以获得更好的数据库平均访问性能。其二，在一个表中没有业务含义的自增长主键，在其他表中就存在确定的业务含义，例如在如下代码片段中虽然member表的id是自增长主键，并没有业务意义，但是在表last_login中这个id（member_id）就具有了业务含义， 能够代表一个特定的member 。其三，如果是 复合主键 ，那么在复合主键列中 不同列的排列顺序需要仔细考量 ，一般依据查询模式，越经常作为查询条件的列，在主键中的位置越靠左（ 越靠前 ）。其四，ENUM、DATE、DATETIME、TIME和较短的CHAR这些类型所占的存储空间较小，在设计主键时可以等同于整型对待。其五，如果存在一列或者多列，其具有业务含义并且能够唯一标识一行数据，但不是整数（ 通常为字符串 ），则有两种解决方案。方案一，如下代码片段中member表所示， email具有唯一性 ，但 不是整数 ，可以 采用自增长主键+唯一索引 的方式，其中如果为唯一索引为多列，那么在唯一索引中的排列顺序 依据复合主键的排列原则 来处理。方案二，类似于member2和email表，将多列中 非整数型的列 提取出来建立一个独立的表，假设表t1中存在三列能够唯一标识每行数据，分别为c1、c2和c3，其中c1为整数，c2和c3为VARCHAR类型，则从t1表中抽离两列c2和c3，分别建立两个新的字典表t2和t3，在新表t2和t3中采用自增长主键，并且在两个表中c2和c3所对应的列分别建立唯一索引，而在原表t1中采用t2表和t3表的主键t2_id和t3_id替换c2和c3列，并用c1和t2_id、t3_id三列做复合主键。

CREATE TABLE member (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ...

  email VARCHAR(63) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE last_login (
  member_id INT UNSIGNED NOT NULL,
  ...

  PRIMARY KEY (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE member2 (
  email_id INT UNSIGNED NOT NULL,
  ...

  PRIMARY KEY (email_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE email (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  value VARCHAR(63) NOT NULL,
  PR