SQL反模式

站长

2023年07月29日 14:53 · 阅读数 11

全书概览

SQL反模式1.逻辑数据库设计反模式2 乱穿马路3 单纯的树4 需要ID5 不用钥匙的入口6 实体_属性_值7 多态关联8 多列属性9 元数据分裂2.物理数据库设计反模式10 取整错误11 每日新花样12 幽灵文件13 乱用索引3.查询反模式14 对未知的恐惧15 模棱两可的分组16 随机选择17 可怜人的搜索引擎18 意大利面条式查询19 隐式的列4.应用程序开发反模式20 明文密码21 SQL注入22 伪键洁癖23 非礼勿视24 外交豁免权25 魔豆

逻辑数据库设计反模式：在编码前，需要决定数据库里存储什么信息以及最佳的数据组织方式和内在关联方式。这包含了如何设计数据库的表、字段和关系。
物理数据库设计反模式：在确定需要存储哪些数据之后，使用所知的RDBMS技术特性尽可能地实现数据管理。这包含了定义表和索引，以及选择数据类型。
查询反模式：向数据库中添加然后获取数据。
应用程序开发反模式：在应用程序中使用SQL。

Tip：这4部分可以说是按照日常应用程序开发的步骤展开的

BUG数据库ERD图

书中的章节实例基本上是基于这个ERD图所设计的

BugsBugStatusTagsScreenshotsBugsProductsCommentsAccountsProducts

逻辑数据库设计反模式

探讨多值属性存储

2 乱穿马路和8 多列属性都是探讨存储一个具有多个值的属性的解决方案。但乱穿马路是探讨多对多关系的存储，而多列属性是探讨一对多关系的存储。

混淆元数据和数据

6 实体_属性_值和7 多态关联将元数据标识（列名或表名）当做字符串存储，而8 多列属性和9 元数据分裂正好相反，将数据的值存储在列名或表名中。

4 需要ID

需要ID目标：建立主键规范反模式：以不变应万变冗余键值允许重复项意义不明的关键字无法使用USING关键字不愿使用组合键合理使用反模式解决方案：裁剪设计

目标：建立主键规范

主键该如何定义。

反模式：以不变应万变

很多书、文章以及程序框架告诉你，每个数据库的表都需要一个主键，且具有如下三个特性：

主键的列名叫做id；
数据类型是32位或者64位整型；
主键的值是自动生成来确保唯一的。

自：目前接触到的大部分项目几乎都是这样做，呜~~

冗余键值：id和bug_id都可以唯一地标识一条记录，id还有存在的必要？
允许重复项：BugsProducts 是交叉表，当在bug_id和product_id这两列上应用唯一性约束时，作用与id相同，id还有存在的必要？
意义不明的关键字：列名id并不会使查询变得更加清晰，当做联结查询并返回对应id时，需要给对应id做别名。但如果列名叫做bug_id或者account_id，事情就会变得更加简单；
无法使用USING关键字，SQL写的更哆嗦；
不愿使用组合键：觉得组合键难以使用而拒绝使用。

合理使用反模式

不是每张表都需要一个伪主键，更没必要将每个伪主键都定义成id。

一些面向对象的框架假设“惯例优于配置”从而简化其设计。它们期望每张表都使用同样的方法来定义它的主键：使用id作为列名，并且使用类型为整型的伪主键。如果使用这样的一个框架，就可能不得不遵守这样的约定，才能进一步使用这个框架所提供的其它特性。
对于太长而不方便实现的自然键来说，伪主键是很好的代替品。比如一个记录文件系统中所有文件属性的表中，文件路径是一个很好的自然键，但对一个字符串列做索引的开销会很大。

解决方案：裁剪设计

主键是约束而非数据类型。你可以定义任意列或任意多的列为主键，只要其数据类型支持索引。同时，还可以将一个列的数据类型定位自增长的整型而不设定其为主键。这两者是完全无关的。别被既有的惯例限制住设计。

直截了当地描述设计

为主键选择更有意义的名称，并且外键尽可能地和所引用的列使用相同的名称。

打破传统

面向对象的框架希望你使用id这个伪主键，但同时也允许勿视这个规则转而使用别的名字

拥抱自然键和组合键

如果你的表中包含一列能确保唯一、非空以及能够用来定位一条记录，就别仅仅因为传统而觉得有必要再加上一个伪主键。

6 实体_属性_值

目标：支持可变的属性

假设有这样的一个类图，数据模型该如何设计呢？

Issue

+Date_reported

+Reporter

+Priority

+Status

Bug

+Severity

+Version_affected

FeatureRequest

+Sponsor

反模式：使用泛型属性表（EAV模型）

IssueIssueAttributes

其中IssueAttributes中有如下三个字段：

实体：通常是一个指向父表的外键；
属性：对应传统表中列的名字；
值：对应传统表中某行记录的对应列的值。

优点

这两张表的列都很少；
新增的属性不会对现在的表结构造成影响，不需要新增列；
避免了由于空值而造成的表内容混乱。

缺点

查询属性的操作变得哆嗦；
放弃数据的完整性；
无法强制声明属性和限定属性的取值内容；
无法配置值的类型和限定值的取值内容；
重组列：需对属性和值做行列转换。

更多关于EAV模型使用详见：使用EAV模型构建可无限扩展的数据存储能力

合理使用反模式

明白使用EAV设计的风险和要做的额外工作，然后谨慎的使用它，使其副作用尽可能的小。或者考虑非关系技术。

解决方案：模型化子类型

单表继承

应用场景：数据的子类型很少，以及子类型特殊属性很少，并且使用Active Record模式访问单表数据库时。

将所有相关的类型都存在一张表中，为所有类型的所有属性都保留一列。同时，使用一个属性来定义每一行表示的子类型。

CREATE TABLE Issues (
  issue_id              SERIAL PRIMARY KEY,
  reported_by           BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED,
  priority              VARCHAR(20),
  version_resolved      VARCHAR(20),
  status                VARCHAR(20),
  issue_type            VARCHAR(10), -- BUG or FEATURE
  severity              VARCHAR(20), -- only for bugs
  version_affected      VARCHAR(20), -- only for bugs
  sponsor               VARCHAR(50), -- only for feature requests
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

缺点

新增属性列时需要变更表结构；
需要通过其它方式跟踪哪个属性属于哪个子类型；
非对应子类型的字段值为null。

实体表继承

应用场景：很少需要一次性查询所有子类型时。

为每个子类型创建一张独立的表。每个表包含那些属于基类的共有属性，同时也包含子类型特殊化的属性。

CREATE TABLE Bugs (
  issue_id              SERIAL PRIMARY KEY,
  reported_by           BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED,
  priority              VARCHAR(20),
  version_resolved      VARCHAR(20),
  status                VARCHAR(20),
  severity              VARCHAR(20), -- only for bugs
  version_affected      VARCHAR(20), -- only for bugs
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FeatureRequests (
  issue_id              SERIAL PRIMARY KEY,
  reported_by           BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED,
  priority              VARCHAR(20),
  version_resolved      VARCHAR(20),
  status                VARCHAR(20),
  sponsor               VARCHAR(50), -- only for feature requests
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

缺点

新增属性列时需要变更表结构；
需要通过其它方式跟踪哪些是共有属性（基类属性）；
难做一次性查询所有子类型，尤其是分页查询时，但可尝试创建视图。

类表继承

应用场景：经常查询所有子类型的公共列时。

模拟继承，创建一张基类表，包含所有子类型的公共属性，对于每个子类型，创建一个独立的表，通过外键和基类表相连。

CREATE TABLE Issues (
  issue_id              SERIAL PRIMARY KEY,
  reported_by           BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED,
  priority              VARCHAR(20),
  version_resolved      VARCHAR(20),
  status                VARCHAR(20),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Bugs (
  issue_id              SERIAL PRIMARY KEY,
  severity              VARCHAR(20), -- only for bugs
  version_affected      VARCHAR(20), -- only for bugs
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

CREATE TABLE FeatureRequests (
  issue_id              SERIAL PRIMARY KEY,
  sponsor               VARCHAR(50), -- only for feature requests
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

缺点

新增属性列时需要变更表结构；
查询子类型的特殊列较难，子类型较少时，可以创建视图或做联结查询。

半结构化数据模型

应用场景：有很多子类型或者属性需要支持高度灵活扩展时。

使用一个BLOB列来存储数据，用XML或者JSON格式--同时包含了属性的名字和值。Martin Fowler称这个模式为序列化大对象块（Serialized LOB）

CREATE TABLE Issues (
  issue_id              SERIAL PRIMARY KEY,
  reported_by           BIGINT UNSIGNED NOT NULL,
  product_id            BIGINT UNSIGNED,
  priority              VARCHAR(20),
  version_resolved      VARCHAR(20),
  status                VARCHAR(20),
  issue_type            VARCHAR(10), -- BUG or FEATURE
  attributes            TEXT NOT NULL, -- all dynamic attributes for the row
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

缺点

很难获取指定属性对应的记录；
不能在一行blob字段中简单地选择一个独立的属性，并对其进行限制、聚合运算、排序等其它操作。必须获取整个blob字段结构并通过程序去处理。

自：mysql可以使用json类型字段，但其能力依然有限。

EAV模型后处理

应用场景：历史遗漏系统或者属性高度灵活扩展的系统。

对查询后的结果做行列转换（在SQL或者应用程序中做行列转换）

7 多态关联

目标引用多个父表

假设有这样的一个类图--Bug和FeatureRequest是类似的实体，但想将它们评论的放在同一张表，数据模型该如何设计呢？

Bug

+...

Comments

+...

FeatureRequest

+...

反模式：使用双用途外键

有个流行的解决方案--多态关联，也叫杂乱关联，因为它可以同时引用多个表。

--issue_id失去了外键约束，同时通过issue_type区分评论来自于哪张表
CREATE TABLE Comments (
  comment_id            SERIAL PRIMARY KEY,
  issue_type            VARCHAR(20), -- Bugs or FeatureRequests
  issue_id              BIGINT UNSIGNED NOT NULL,
  author                BIGINT UNSIGNED NOT NULL,
  comment_date          DATETIME,
  comment               TEXT,
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

当通过一条给定的评论查找对应的Bug记录或特性需求时，SQL是这样的：

--父表多的时候，这将非常恐怖
SELECT *
FROM Comments AS c
  LEFT OUTER JOIN Bugs AS b
    ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs')
  LEFT OUTER JOIN FeatureRequests AS f
    ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests')

合理使用反模式

应尽可能地避免使用多态关联--应该使用外键约束等来确保引用完整性。多态关联通常过度依赖上层程序代码而不是数据库的元数据。

解决方案：让关系变得简单

反向引用

多态关联其实就是一个反向关联。

创建交叉表

应用场景：它们所关联的实体存在同一张表里

自：它们是相近的实体则应该优先考虑创建共用的超级表，如果不是则考虑创建交叉表

解除对Comments.issue_type列的依赖。元数据可以确保数据完整性，从而不再依赖于应用程序代码来维护数据间的关系。

BugsBugsCommentsCommentsFeaturesCommentsFeatureRequests

CREATE TABLE BugsComments (
  issue_id      BIGINT UNSIGNED NOT NULL,
  comment_id    BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);

CREATE TABLE FeaturesComments (
  issue_id      BIGINT UNSIGNED NOT NULL,
  comment_id    BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);

设立交通灯

交叉表通常是多对多关系的模型，当希望每条评论都只涉及一个Bug或者一个特性需求时，可以给每个交叉表上的comment_id列声明一个UNIQUE约束。但这个无法约束一条评论同时出现在一个Bug和一个特性需求上，这需要通过上层应用代码程序来处理。

CREATE TABLE BugsComments (
  issue_id      BIGINT UNSIGNED NOT NULL,
  comment_id    BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY   (comment_id),
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
)

CREATE TABLE FeaturesComments (
  issue_id      BIGINT UNSIGNED NOT NULL,
  comment_id    BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY   (comment_id),
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
)

创建共用的超级表

应用场景：它们所关联的实体存在同一张表里

自：它们是相近的实体则应该优先考虑创建共用的超级表，如果不是则考虑创建交叉表

解除对Comments.issue_type列的依赖。元数据可以确保数据完整性，从而不再依赖于应用程序代码来维护数据间的关系。

IssuesBugsCommentsFeatureRequests

CREATE TABLE Issues (
  issue_id      SERIAL PRIMARY KEY,
);

CREATE TABLE Bugs (
  issue_id      BIGINT UNSIGNED PRIMARY KEY,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  ...
);

CREATE TABLE FeatureRequests (
  issue_id      BIGINT UNSIGNED PRIMARY KEY,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  ...
);

CREATE TABLE Comments (
  comment_id    SERIAL PRIMARY KEY,
  issue_id      BIGINT UNSIGNED NOT NULL,
  author        BIGINT UNSIGNED NOT NULL,
  comment_date  DATETIME,
  comment       TEXT,
  FOREIGN KEY (issue_id) REFERENCES iSSUES(issue_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

8 多列属性

目标：存储多值属性

一个属性可能会有多个值，譬如可以给指定bug打多个标签，数据模型该如何设计呢？

反模式：创建多个列

我们知道每列最好只存储一个值，于是创建了多个列，看起来似乎很自然，可真正使用起来时甚至比每列存储多个值还要晦涩。

CREATE TABLE Bugs (
  bug_id      SERIAL PRIMARY KEY,
  description VARCHAR(1000),
  tag1        VARCHAR(20),
  tag2        VARCHAR(20),
  tag3        VARCHAR(20)
);

缺点

CRUD很难；
难于确保唯一性（同一个值可能出现在多个列中）；
难于处理不断增长的值集（也许三列并不够用--这几乎是必然的）。

合理使用反模式

在某些情况下，一个属性可能有固定数量的候选值，并且对应的存储位置和顺序都是固定的。比如，一个给定的Bug可能和多个用户账户相关，但每个关系的作用都是唯一的：一个是报告Bug的用户，另一个是修复Bug的开发人员，另一个是验证Bug修复状态的质量工程师。即使这几列里存储的值是相似的，它们的作用以及实际的业务逻辑都是不同的。

解决方案：创建从属表

创建一张从属表，仅使用一列来存储多值属性。将多个值存在多行中而不是多列中。

CREATE TABLE Tags (
  bug_id      BIGINT UNSIGNED PRIMARY KEY,
  tag         VARCHAR(20),
  PRIMARY KEY (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

9 元数据分裂

目标：支持可扩展性

优化数据库的结构来提升查询的性能以及支持表的平滑扩展。

反模式：克隆表与克隆列

根据经验，我们知道查询一张表时，其性能只和这张表中数据的条数相关，越少的记录，查询速度越快。于是我们推导出一个常见错误的结论：无论做什么，我们必须让每张表存储的记录尽可能少。这就导致了本章的反模式的两种表现形式。

将一张很长的表拆分成多张较小的表，使用表中某一个特定的数据字段来给这些拆分出来的表命名；
将一个列拆分成多个子列，使用别的列中的不同值给拆分出来的列命名。

为了要达成减少每张表记录数的目的，你不得不创建一些有很多列的表，或者创建很多很多表。但随着数据量的增长，会有越来越多的表或者列，因为新的数据迫使你创建新的Schema对象。

缺点

对于拆分成多张表而言

需要不断创建新的表；
手动管理数据的完整性（数据应只出现在对应的表中）；
同步数据变得哆嗦（跨表更新数据时，需要插入数据到一张表中，再删除另一张表的数据）；
需要通过额外的序列来确保主键在这些拆分表中是唯一的；
跨表查询变得越来越困难（如一些统计数据）；
手动同步元素（新增列时需要变更所有拆分表）
难于管理引用完整性（如为父子表时，拆分的是父表，则子表不能使用外键）。

对于拆分成多列而言

需要不断创建新的列。

合理使用反模式

手动分割表的一个合理使用场景是归档数据--将历史数据从日常使用的数据中移除。通常在过期数据的查询变得非常稀少时才这样做。

如果没有同时查询当前数据和历史数据的需求，将老数据从当前活动的表转移到其它地方是很合适的操作。

将数据归档到和当前表结构相兼容的新表中，既能支持偶尔做数据分析时的查询，同时也能让日常数据查询变得非常高效。

解决方案：分区及标准化

使用水平分区

定义一些规则来拆分一张逻辑表，然后让数据库管理余下的所有事情。

CREATE TABLE Bugs (
  bug_id        SERIAL PRIMARY KEY,
  -- other columns
  date_reported DATE
) PARTITION BY HASH (YEAR(date_reported)) PARTITIONS 4;

使用垂直分区

根据列来对表进行拆分。将某些不常使用的列或者BLOB、TEXT等类型的列拆分出去，可以提高存储和查询的性能。

CREATE TABLE Bugs (
  bug_id        SERIAL PRIMARY KEY,
  -- other columns
  date_reported DATE
);

CREATE TABLE BugImages (
  bug_id        BIGINT UNSIGNED PRIMARY KEY,
  image         BLOB,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

创建关联表

使用每行一个项目、每一列记录一年的Bug修复数量，还不如使用多行、仅用一列记录修复的Bug数量。

CREATE TABLE ProjectHistory (
  project_id        BIGINT,
  year              SMALLINT,
  bugs_fixed        INT,
  PRIMARY KEY       (project_id, year),
  FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

转载自:https://juejin.cn/post/7252526727607599163