Andrew's Blog

Hello, I'm andrewzhyl, I'm a programmer, contact me with .

© 2024. All rights reserved.

《SQL 反模式》学习笔记

06 Mar 2015

第一章

CAP 组合

反模式分类

　　（1）逻辑数据库设计反模式

     在开始编码之前，需要决定数据库中存储什么信息以及最佳的数据组织方式和内在关联方式。
     这包含了如何设计数据库的表、字段和关系。

　　（2）物理数据库设计反模式

     在确定了需要存储哪些数据之后，使用你所知的RDBMS关系型数据库技术特性尽可能高效地实现数据库管理。
     这包含了定义表和索引，以及选择数据类型。也需要是要SQL的“数据定义语言”，比如Create Table语句。

　　（3）查询反模式

     SQL的查询是使用“数据操作语言”来完成，比如：Insert、Select、Update和Delete语句。

　　（4）应用程序开发反模式

     SQL应该会用在Java、.Net、C++、Php等语言构建的应用程序中，在应用程序中使用SQL的方式有好有坏。

第一部分 逻辑型数据库设计反模式

第二章：乱穿马路

目标：

存储多值属性，即实现多对一的关系

反模式：格式化的逗号分隔列表

反模式：将多个值以格式化的逗号分隔符存储在一个字段中

反模式导致的问题：

如何识别反模式

合理使用反模式

解决方案：

第三章：单纯的树

目标：分层存储与查询

反模式：总是依赖父节点

邻接表： 表中添加 parent_id 字段，引用同一张表中的其它回复。可以建一个外键来维护这种关系

邻接表 ERD： 邻接表

如何识别反模式

解决方案：使用其他树模型

路径枚举

优点：

缺点：

实现代码 mysql:

-- 创建路径枚举
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  path VARCHAR(1000),
  author VARCHAR(1000),
  comment TEXT NOT NULL
);

INSERT INTO Comments (comment_id, path, author, comment) VALUES(1, "1/", "Fran", "这个Bug的成因是什么");
INSERT INTO Comments (comment_id, path, author, comment) VALUES(2, "1/2/", "Ollie", "我觉得是一个空指针");
INSERT INTO Comments (comment_id, path, author, comment) VALUES(3, "1/2/3/", "Fran", "不，我查过了");
INSERT INTO Comments (comment_id, path, author, comment) VALUES(4, "1/4/", "Kukla", "我们需要查无效输入");
INSERT INTO Comments (comment_id, path, author, comment) VALUES(5, "1/4/5/", "Fran", "是的，那是个问题");
INSERT INTO Comments (comment_id, path, author, comment) VALUES(6, "1/4/6/", "Frank", "好，查一下吧");
INSERT INTO Comments (comment_id, path, author, comment) VALUES(7, "1/4/6/7/", "Frank", "解决了");

-- ancestors.sql
SELECT *
FROM Comments AS c
WHERE '1/4/6/7/' LIKE c.path || '%'; -- mysql 不能用，语法不同

-- descendants.sql
SELECT *
FROM Comments AS c
WHERE c.path LIKE '1/4/' || '%'; 

-- count.sql
SELECT COUNT(*)
FROM Comments AS c
WHERE c.path LIKE '1/4/' || '%'
GROUP BY c.author;

-- Insert.sql
INSERT INTO Comments (author, comment) VALUES ('Ollie', 'Good job!');
SELECT @PATH:=CONCAT((SELECT path FROM Comments   WHERE comment_id = 7), LAST_INSERT_ID(), '/');
UPDATE Comments  SET path=@PATH WHERE comment_id = LAST_INSERT_ID();
嵌套集

使用 nsleft 和 nsright 两个数字来编码每个节点， nsleft 的数值小于该节点所有后代的 ID，同 时 nsright 的值大于该节点所有后代的 ID

优点：

缺点：

实现代码 mysql:

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  nsleft INTEGER NOT NULL,
  nsright INTEGER NOT NULL,
  author VARCHAR(1000),
  comment TEXT NOT NULL
);

-- 插入
-- 根据“嵌套集合模型”的数学原理，子节点的左界是当前节点的右界，子节点的右界是当前节点的右界加1，并且所有在当前节点右侧的节点的左右界都加 2
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (1, 1, 1, 2, "Fran", "这个 Bug 的成因是什么");

UPDATE Comments SET nsleft=nsleft+2 WHERE nsleft>2;
UPDATE Comments SET nsright=nsright+2 WHERE nsright>=2;
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (2, 1+1, 2, 2+1, "Ollie", "我觉得是一个空指针");

UPDATE Comments SET nsleft=nsleft+2 WHERE nsleft>3;
UPDATE Comments SET nsright=nsright+2 WHERE nsright>=3;
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (3, 2+1, 3, 3+1, "Fran", "不，我查过了");

UPDATE Comments SET nsleft=nsleft+2 WHERE nsleft>6;
UPDATE Comments SET nsright=nsright+2 WHERE nsright>=6;
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (4, 1+1, 6, 6+1, "Kukla", "我们需要查无效输入");

UPDATE Comments SET nsleft=nsleft+2 WHERE nsleft>7;
UPDATE Comments SET nsright=nsright+2 WHERE nsright>=7;
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (5, 2+1, 7, 7+1, "Ollie", "是的，那是个问题");

UPDATE Comments SET nsleft=nsleft+2 WHERE nsleft>9;
UPDATE Comments SET nsright=nsright+2 WHERE nsright>=9;
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (6, 2+1, 9, 9+1, "Fran", "好，查一下吧");

UPDATE Comments SET nsleft=nsleft+2 WHERE nsleft>10;
UPDATE Comments SET nsright=nsright+2 WHERE nsright>=10;
INSERT INTO Comments (comment_id, depth, nsleft, nsright, author, comment) VALUES (7, 3+1, 10, 10+1, "Kukla", "解决了");

-- 查询所有叶节点
-- 根据此模型，叶节点就是右界比左界大1的那些节点，SQL语句很简单。
SELECT * FROM Comments WHERE nsright = nsleft + 1;

-- 取得单条路径
SELECT parent.comment_id, parent.comment FROM Comments  AS node, Comments AS parent
  WHERE parent.nsleft <= node.nsleft AND node.nsleft <= parent.nsright AND node.comment_id = 7
  ORDER BY parent.nsleft;

-- 查询节点的深度
SELECT depth FROM Comments WHERE comment_id=7;

-- 整棵树及其深度
SELECT depth, CONCAT( REPEAT('- ', depth - 1), comment) AS name FROM Comments ORDER BY nsleft;

-- 子树及深度
SELECT depth, CONCAT( REPEAT('- ', depth - 1), comment) AS comment FROM Comments WHERE nsleft>=2 AND nsright<=11 ORDER BY nsleft;

-- 直接的子节点
-- 查询根节点的直接子节点
SELECT comment_id, depth, comment FROM Comments WHERE nsleft>1 AND nsright<14 AND depth=1+1 ORDER BY nsleft;

-- 直接的父节点
SELECT parent.comment_id, parent.comment FROM Comments AS node, Comments AS parent
  WHERE parent.nsleft < node.nsleft AND node.nsleft < parent.nsright AND node.comment_id = 7
  ORDER BY parent.nsleft DESC LIMIT 1;
闭包表

Closure Tree 闭包表，额外创建了一张 TreePaths 的表，以空间换取时间的思路，它包含两列，每一列都是一个指向 Comments 中的 CommentId 的外键，明晰表示两点间的关系

实现代码 mysql:


-- 创建评论表
CREATE TABLE Comments (
  id SERIAL PRIMARY KEY,
  comment TEXT NOT NULL,
  author VARCHAR(1000)
) DEFAULT CHARSET = utf8;

-- 创建关系表
CREATE TABLE comments_closure (
  ancestor INT NOT NULL,    -- 祖先节点
  descendant INT NOT NULL,  -- 后代节点
  depth INT NOT NULL,       -- 层级
  PRIMARY KEY (ancestor, descendant)
)

-- 插入数据

INSERT INTO Comments (id, author, comment) VALUES (1, "Fran", "这个 Bug 的成因是什么");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 1 FROM comments_closure AS t WHERE t.descendant=1 UNION ALL SELECT 1,1;


INSERT INTO Comments (id, author, comment) VALUES (2, "Ollie", "我觉得是一个空指针");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 2 FROM comments_closure AS t WHERE t.descendant=1 UNION ALL SELECT 2,2;

INSERT INTO Comments (id, author, comment) VALUES (3, "Fran", "不，我查过了");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 3 FROM comments_closure AS t WHERE t.descendant=2 UNION ALL SELECT 3,3;


INSERT INTO Comments (id, author, comment) VALUES (4, "Kukla", "我们需要查无效输入");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 4 FROM comments_closure AS t WHERE t.descendant=1 UNION ALL SELECT 4,4;

INSERT INTO Comments (id, author, comment) VALUES (5, "Ollie", "是的，那是个问题");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 5 FROM comments_closure AS t WHERE t.descendant=4 UNION ALL SELECT 5,5;


INSERT INTO Comments (id, author, comment) VALUES (6, "Fran", "好，查一下吧");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 6 FROM comments_closure AS t WHERE t.descendant=4 UNION ALL SELECT 6,6;

INSERT INTO Comments (id, author, comment) VALUES (7, "Kukla", "解决了");
INSERT INTO comments_closure (ancestor, descendant)
  SELECT t.ancestor, 7 FROM comments_closure AS t WHERE t.descendant=6 UNION ALL SELECT 7,7;

-- 查询后代
-- 查询 #4 的后代
SELECT c.* FROM Comments AS c JOIN comments_closure AS t ON c.id=t.descendant WHERE t.ancestor=4;

-- 查询祖先，#6 的祖先
SELECT c.* FROM Comments AS c JOIN comments_closure AS t ON c.id=t.ancestor WHERE t.descendant=6;


-- 删除一个叶子节点， #7
DELETE FROM comments_closure WHERE descendant=7;

-- 删除一颗完整的树,比如 #4 he 它的所有后代
DELETE FROM comments_closure WHERE descendant IN (SELECT descendant FROM comments_closure WHERE ancestor=4);

第 4 章:需要 ID

4.1 目标：建立主键规范

主键约束是很重要的情况：

数据库伪主键实现

一个对于表的域模型无意义的新列来存储一个伪值，这种类型的主键列我们通常称其为伪主键或者代理键。

特性 支持的数据库
AUTO_INCREMENT MySQL
GENERATOR Firebird, InterBase
IDENTITY DB2, Derby, Microsoft SQL Server, Sybase
ROWID SQLite
SEQUENCE DB2, Firebird, Informix, Ingres, Oracle,PostgreSQL
SERIAL MySQL, PostgreSQL

反模式：以不变应万变

允许重复项

id 这一列作为主键，约束就不再是bug_id和product_id的组合必须唯一

CREATE TABLE BugsProducts (
id SERIAL PRIMARY KEY,
bug_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
INSERT INTO BugsProducts (bug_id, product_id)
VALUES (1234, 1), (1234, 1), (1234, 1); -- 重复项也是可以输入的

但是，当你在bug_id和product_id这两列上应用了唯一性约束，id这一列就会变成多余的

CREATE TABLE BugsProducts (
id SERIAL PRIMARY KEY,
bug_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
UNIQUE KEY (bug_id, product_id),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

使用USING关键字

SQL 支持简洁的表达式来表示两张表的联结。如果两张表都有同样的列名，就可以用如下的表达式查询：

SELECT * FROM Bugs JOIN BugsProducts USING (bug_id);

使用组合键之难

内置函数来获取一个序列生成的最后一个值:

其它

如果使用框架的话，比如 rails 之类的，后面的就不要看了，虽然组合键作为主键是可以的，但是框架也可以帮我们做重复项的约束，所以使用伪主键也是没问题的

第 5 章: 不用钥匙的入口

MySQL默认的存储引擎是MyISAM，并不支持外键约束，数据库的设计中有很多的逻辑关系，但无法保证引用完整性

目标：简化数据库架构

不使用外键的原因:

反模式：无视约束

省略外键约束能使得数据库设计更加简单、灵活，或者执行更加高效, 但是,必须增加额外的代码来手动维护引用完整性

合理使用反模式

被迫使用不支持外键约束的数据库产品:

5.5 解决方案：声明约束

第6章 实体—属性—值

按日期来统计记录条数:

SELECT date_reported, COUNT(*)
FROM Bugs
GROUP BY date_reported;

目标：支持可变的属性

Bug和FeatureRequest有一些公共属性，我们将其提炼为一个基类，称为Issue

反模式：使用泛型属性表

EAV（Entity，Attribute，Value）模型

好处：

缺点：

合理使用反模式

解决方案：模型化子类型

单表继承

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

实体表继承

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

类表继承

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

半结构化数据模型

第 7 章：多态关联

评论有两张表，Bugs 和 FeatureRequests 是类似的实体，想要按行联结不同的表

目标：引用多个父表

反模式：

查询示例：

SELECT *
FROM Bugs AS b JOIN Comments AS c
ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs')
WHERE b.issue_id = 1234;

如何识别反模式：当出现以下情况时，可能是反模式

（1）这种标记框架可以让你将标记(或者其他属性)和数据库中的任何其他资源联系起来。就像EAV的设计一样，应该怀疑任何生成有无线扩展性的设计。 （2）不能在数据库中声明外键。 （3）使用混合数据与元数据设计，父表的名字是存储在 issue_type 这样的列中

合理使用反模式

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

第 8 章：多列属性

目标：存储多值属性

反模式：创建多个列

如何识别反模式

当出现以下情况时，可能是反模式：

合理使用反模式

解决方案：创建从属表

第 9 章：元数据分裂

目标：支持可扩展性

反模式：克隆表与克隆列

缺点：

如何识别反模式

元数据分裂反模式在你的数据库中繁衍生长的暗示:

合理使用反模式

解决方案：分区及标准化

Related Posts