SQL反模式（二）
by snoopyxdy
at 2013-02-19 14:16:00
original http://snoopyxdy.blog.163.com/blog/static/601174402013118112556568
案例1、一对多反范式：
数据库中的表如下：
例如我们要找评论id为7的所有祖先，可以用如下sql：
假设我们正在开发一个bug追踪的系统，每个bug都属于某一个产品，并且为每个产品我们设置了一个字段，存储这个产品的一个联系人。某一天需求改变了，我们需要对某一个产品支持多个联系人。
此时对数据库中原来存储的单一联系人用户标示改成用逗号分隔的方案似乎是改动最小最简单的解决方案。
很快老板老问你，“工程部那边最多只能添加5个联系人，多了就出错，怎么回事？”
你点头说：“是的，只能在项目中加这么多人，就是这么设计，我可以帮您加到5-10人，不过这还根据用户注册的时间早晚决定，因为ID号加上逗号的总长度不能超过字符串长度的最大值。”
老板皱起了眉头，你感觉今天又要加班了。。。
程序员通常使用逗号分隔来避免创建关系表，这样看似性能感觉很好，但是真正在使用起来必须面临如下几个问题：
1、查询指定联系人的产品
在mysql中可以用如下语句：
在mysql中可以用如下语句：
SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]'
这条语句性能是大问题，而且这条语句只适用于mysql所以无法跨平台
2、查询指定产品的联系人
可以使用如下sql语句进行查询
SELECT * FROM Products AS p JOIN Accounts AS a ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.product_id = 123;
mysql中使用concat()函数进行字符串连接
这样的语句将毁掉索引
3、更新指定产品的联系人
UPDATE Products SET account_id = account_id || ',' || 12
WHERE product_id = 123;
这样更新数据并不能保证顺序，当要删除其中某一个联系人时，不得不借助编程语言来实现，例如：php
4、其他问题
对于验证用户id无能为力，可能重复输入用户id或者无效的id。如何保证分隔符不会被联系人的id用到也是一个问题。
当然就像开始那样列表的长度是一个不得不考虑的问题，如果设置过短，就会发生只能添加5人的尴尬问题。
并不是完全否定使用逗号分隔存储一对多关系的反范式设计，当然如果考虑这样设计数据库表则必须谨慎在谨慎。
我们可以创建一张交叉表来解决这个问题
查询指定联系人的产品：
SELECT p.* FROM Products AS p JOIN Contacts as c ON (p.account_id = c.account_id)
WHERE c.account_id = 123;
更新指定产品的联系人：
INSERT INTO Contacts (product_id, account_id) VALUES (123, 56);
DELETE FROM Contacts WHERE product_id = 123 AND account_id = 56;
增加了联系表同时取消了长度的限制，而且设置了外键验证了不存在的account_id。
案例2、树
百度贴吧，或者网易评论都可以叠楼，就是对回复进行回复，这是很常见的一个需求，我们一般的解决方案是建立一个评论表Comments，将评论或者回复保存在里面，然后设置一个parent_id来表述他们之间的父子关系，如下图：
可能我们还有一个主题的id，theme_id用来区分不同的帖子或者留言主题，这里无关紧要，我们用下图表示的更加清晰一点：
这种表设计我们称为邻接表，但是它无法完成树操作的最普通一项，无法查询一个节点的所有后台，只能使用一个关联查询知道节点的直接后台。
1、增加一个子节点非常方便
INSERT INTO Comments (bug_id, parent_id, author, comment) VALUES (1234,7,'kkk','thanks')
2、删除一个节点就比较麻烦了，执行多次来找到所有的后台节点，然后删除这些节点
3、删除一个叶子节点并且提升它的子节点也是比较麻烦的一件事情，必须先修改子节点的parent_id字段，然后才能删除改叶子节点，必须一些额外的代码
警示：不要过度设计，如果只有父子嵌套，没有深层嵌套，并且这些关系并不会频繁的变更，那么邻接表方法是很不错的选择。
解决方案1、使用路径枚举
邻接表的缺点之一是从树中获取一个给定节点的所有祖先开销很大，路径枚举的设计通过将所有祖先的信息联合成一个字符串，并保存为每一个节点的一个属性，巧妙的解决了这个问题。
SELECT * FROM Comments AS c WHERE ‘1/4/6/7/’ LIKE c.path || '%';
这条语句会去查找所有path为 1/4/6/% 或 1/4/% 或 1/% 的节点，这些节点都是7的祖先。
同样我们可以很方便的查找id为4的所有后代：
SELECT * FROM Comments AS c WHERE c.path LIKE '1/4/' || '%';
这条语句能查出 1/4/5/ 、1/4/6/ 以及 1/4/6/7/
我们还可以方便的计算出某一个叶子节点下的每个用户的所有评论数，这在邻接表设计是很复杂的：
SELECT COUNT(*) FROM Comments AS c WHERE c.path LIKE '1/4/' || '%' GROUP BY c.author;
插入一个节点有点麻烦，需要先插入记录，然后查询处此回复的comment_id然后修改path字段
枚举方法还可以根据path字符串的分隔符数量来知道子节点的深度
路径枚举方法也有它的缺陷，无论将path字段设置为多大，都面临多长的风险。而且不能保证所有path上的路径都是有效，可能被误删了某一条记录
解决方案2、闭包表
闭包表是解决分级存储的一个简单而优雅的方案，它记录了树中所有节点间的关系，而不是仅仅的父子关系。我们不再使用comments表来保存树结构，而是使用TreePath表的一行来表示
通过treepath表来获取一个id为4的后代：
SELECT c.* FROM Comments AS c JOIN TreePaths AS t ON c.comment_id = t.descendart WHERE t.ancestor = 4;
获取id为4的祖先
SELECT c.* FROM Comments AS c JOIN TreePaths AS t ON c.comment_id = t.ancestor WHERE t.descendart = 4;
插入一个新的叶子节点，须先插入自己到自己的关系，然后增加该节点的祖先-后代的关系，比如要删除一个叶子节点id为7的内容，需要删除所有后代id为7的记录即可。
闭包表通过存储空间来减少操作过程中的冗余计算造成的消耗。