SQL Antipatterns——SQL 反模式（一）

最近设计家谱树的时候，找了相关资料，一般都是用的递归模式，其实还有一种SQL Antipatterns 的设计思路，以下会分两章来讲解SQL 反模式。

 

假设你是一个著名的科技新闻网站的软件开发者。这是一个现代化的网站，读者可以发表评论，甚至互相回复，

形成深层次的讨论线。你可以选择一个简单的解决方案来跟踪这些回复链：每个评论都引用它回复的评论。

 

CREATE TABLE Comments (

　　comment_id SERIAL PRIMARY KEY,

　　parent_id BIGINT UNSIGNED,

　　comment TEXT NOT NULL,

　　FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)

);

 

但是，很快就可以清楚地看到，很难在单个SQL查询中检索到一长串答复。你仅仅能得到直接的子节点或可能要加入的孙节点。

然而这些线可以有无限的深度。你需要运行许多SQL查询才能获得给定那条线上的所有评论。

 

你的另一个想法是检索所有的评论，并将它们组装到应用程序内存中的树数据结构中，使用你在学校中学到的传统树算法。

但是网站的出版商告诉你，他们每天发表数十篇文章，每篇文章都可以有数百条评论。

每次有人浏览该网站时，都会对数百万条评论进行排序，很显然这是不切实际的。

 

必须要有更好的方法来存储这些评论线，这样你才能简单有效地检索整条讨论线。

 

一、目标：存储和查询层次结构

数据具有递归关系是很常见的。数据可以以树状或分层的方式被组织化。

在树状数据结构中，每个条目称为节点。一个节点可能有若干个子节点和一个父节点。

顶部节点没有父节点，称为根节点。底部的节点没有子节点，称为叶子节点。中间的节点叫做非叶节点。

 

在以前的层次结构数据中，你可能需要查询单个项、集合的相关子集或整个集合。

树型数据结构的实例包括以下：

1、组织结构图：员工与管理者的关系是树型数据结构的典型例子。它出现在和SQL相关的无数书籍和文章中。

在组织结构图中，每个员工有一个管理者，在树型结构中代表员工的父节点，同样管理者也是一名员工。、

2、以线型方式组织的讨论：树型结构可用于评论链，以回应其他评论。在树中，评论节点的子节点是它的答复。

 

以下我们将以线型方式组织的讨论，来举例展示反模式及其解决方案。

 

 

二、反模式：总是依赖于父节点

很多书籍和文章中，常见的解决方案是添加一个parent_id列。此列引用同一表中的另一个评论，

你可以创建一个外键约束以强制执行他的关系。SQL表结构如下：

CREATE TABLE Comments (

　　comment_id SERIAL PRIMARY KEY,

　　parent_id BIGINT UNSIGNED,

　　bug_id BIGINT UNSIGNED NOT NULL,

　　author BIGINT UNSIGNED NOT NULL,

　　comment_date DATETIME NOT NULL,

　　comment TEXT NOT NULL,

　　FOREIGN KEY (parent_id) REFERENCES Comments(comment_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),

　　FOREIGN KEY (author) REFERENCES Accounts(account_id)

);

实体关系图如下:

#图3.1#

 

 

 

 

这种设计称为邻接表。它可能是开发人员用来存储分层数据结构最常见的设计方式。

下表是一些显示评论层次结构的示例数据，以及实例的树如下图3.2所示

#表3.2#

 

 

#图3.2#

 

 

 

用邻接表查询树

当邻接表是许多开发人员的默认选择时，它可能是反模式的，但它不能解决你需要使用树来执行的最常见任务之一：查询所有后代。

你可以使用一个相对简单的查询，检索一个评论及其直接子节点：

SELECT c1.*, c2.*

FROM Comments c1 LEFT OUTER JOIN Comments c2

ON c2.parent_id = c1.comment_id;

 

但是，这只查询两层结构的树。树的一个特点是它可以扩展到任意深度，所以你需要能够查询所有后代，而不考虑层次数。

例如，你可能需要计算评论的总数：COUNT() 或 机械组件中部件成本的总和：SUM()

 

当你使用邻接表时，这种查询是很尴尬的，因为树的每个层级对应于另一个连接，而SQL查询中的连接数必须是固定的。

以下查询检索一棵深度可达四层的树，但不能检索超过此深度的树：

SELECT c1.*, c2.*, c3.*, c4.*

FROM Comments c1 -- 1st level

LEFT OUTER JOIN Comments c2

ON c2.parent_id = c1.comment_id -- 2nd level

LEFT OUTER JOIN Comments c3

ON c3.parent_id = c2.comment_id -- 3rd level

LEFT OUTER JOIN Comments c4

ON c4.parent_id = c3.comment_id; -- 4th level

 

这个查询也很尴尬，因为它通过添加更多的列，来包含更深层次的后代。这使得计算诸如count()之类的聚合函数变得困难。

从邻接表中查询树型结构的另一种方式，是检索集合中的所有行，然后重建应用程序中的层次结构，才能把它当作树型结构使用。

SELECT * FROM Comments WHERE bug_id = 1234;

在进行分析之前，将大量的数据从数据库复制到应用程序，这是非常低效的。

你可能只需要一个子树，而不是从顶部开始的整个树。你可能只需要数据的集合信息，如评论的计数COUNT()。

 

用邻接表维护树

诚然，有些操作很容易用邻接表来完成。

例如添加一个新的叶子节点：

INSERT INTO Comments (bug_id, parent_id, author, comment) VALUES (1234, 7, 'Kukla' , 'Thanks!' );

迁移单个节点或子树也很容易：

UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;

但是，从树中删除节点要复杂得多。如果要删除整个子树，则必须要多个查询才能找到所有后代节点。

然后从最底层删除后代节点，以满足外键的完整性。

SELECT comment_id FROM Comments WHERE parent_id = 4; -- returns 5 and 6

SELECT comment_id FROM Comments WHERE parent_id = 5; -- returns none

SELECT comment_id FROM Comments WHERE parent_id = 6; -- returns 7

SELECT comment_id FROM Comments WHERE parent_id = 7; -- returns none

DELETE FROM Comments WHERE comment_id IN ( 7 );

DELETE FROM Comments WHERE comment_id IN ( 5, 6 );

DELETE FROM Comments WHERE comment_id = 4;

 

你可以使用外键与ON DELETE CASCADE 修饰符自动匹配，只要你知道你总是想删除后代节点，而不是提升或重新定位他们。

如果你想要删除一个非叶节点并提升它的子节点，或者将它们移动到树中的另一个位置，那么首先需要更改子节点的parent_id，然后删除所需的节点。

SELECT parent_id FROM Comments WHERE comment_id = 6; -- returns 4

UPDATE Comments SET parent_id = 4 WHERE parent_id = 6;

DELETE FROM Comments WHERE comment_id = 6;

 

以上是在使用邻接表设计时需要多个步骤的操作示例。这是你为了让数据库应该更简单、更高效，而必须要编写的代码。

 

 

三、如何辨别是否使用反模式？

如果你听到下面这样的问题，那么这是一个树的反模式正在被使用的线索：

--我们需要在树模型中支持多少层？

在不使用递归查询的情况下，你很难查询某个节点的所有祖先或者所有子后代节点，你可能妥协地仅支持有限深度的一棵树，

但是自然联想到下一个问题，多深是足够深？

--我害怕碰到管理树数据结构的代码。

你已经采用了一种更复杂的管理层次结构解决方案，但你使用的是错误的解决方案。

每种技术都使一些任务更容易，但通常以其他任务变得更复杂为代价。

对于在应用中使用层次结构的方式，你可能选择了一个不是最佳选择的解决方案。

--我需要定期运行一个脚本来清理树中孤立的行。

你的应用程序在树中删除非叶节点时，会创建断开连接的节点。当你在数据库中存储复杂数据结构时，

在进行任何更改后，你需要将结构保持在一个一致并且有效的状态。你可以使用后面介绍的解决方案之一，

以及触发器和级联外键约束。来存储具有弹性的数据结构，而不是脆弱的数据结构。

 

四、合理使用反模式

邻接表设计可以很好地支持你在应用程序中需要做的工作。它的强大之处是检索指定节点的直接父节点或子节点。

插入行也很容易。如果这是你对分层数据所需要的全部操作，那么邻接表可以很好地处理。

一些RDBMS支持对SQL的扩展，来支持存储在邻接表格式中的层次结构。SQL99标准在普通表语法后使用with关键字来定义递归查询。

WITH CommentTree

(comment_id, bug_id, parent_id, author, comment, depth)

AS (

SELECT *, 0 AS depth FROM Comments WHERE parent_id IS NULL

UNION ALL

SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct

JOIN Comments c ON (ct.comment_id = c.parent_id)

)

SELECT * FROM CommentTree WHERE bug_id = 1234;

MicrosoftSQLServer 2005、Oracle11g、IBMDB 2和PostgreSQL8.4支持使用公共表表达式的递归查询，如前面所示。

MySQL、SQLite和Informix是不支持这种语法的数据库品牌的例子。

对于仍然被广泛使用的Oracle 10g也是如此。我们可能会假设递归查询语法将能用于所有流行品牌，然后使用邻接表将不会有如此限制。

 

Oracle 9i和10g支持With 语句，但不是递归查询。然后，有专用的语法：START WITH 和 CONNECT BY PRIOR。可以使用此语法执行递归查询：：

SELECT * FROM Comments

START WITH comment_id = 9876

CONNECT BY PRIOR parent_id = comment_id;

 

本文翻译自 Bill_Karwin-SQL_Antipatterns-EN 书籍第三章

如有转载，请标明来源：http://www.cnblogs.com/wjq310/p/8849852.html