sql反模式分析1

第二章：乱穿马路

  2.1 目标：存储多值属性  

  2.2 反模式：格式化的逗号分隔列表   模糊匹配无法使用索引，影响性能；多表关联麻烦，却极大影响性能；执行聚合查询不方便开发和调试；更新某个字段值必须执行两次；字段内容出错数据很难恢复修正；选择一个用不用到的分隔符，无法确认不适用；列表长度限制；

  2.3 解决方案：创建一张交叉表，实现两张表的多对多的关联

第三章：单纯的树

  目标：分层存储和查询 案例：文章和评论 每篇文章有多篇评论，每个评论可以引用另外的评论。

  反模式： 最简单的解决方案就是在同一张表中存储该评论id的父节点id（parent_id)

           邻接表的使用查询：select c1.*,c2.* from comments c1 left outer join comments c2 on c2.parent_id=c1.comment_id，但是这样只能查询两层的数据。如果需要三级，四级，则必须使用更多的邻接表，这种查询当然就无法直接树的无限级扩展了。查询一棵完整的树数据或者评论总数之类的会导致查询数据库的冗余数据。使用邻接表增加更新非常方便，但是删除就涉及到级联了。

  解决方案：如果需要能够确认深度，如果只有简单的两层，则用邻接表会很方便。类似的oracle，sql server，db2等支持with语法的递归查询，而mysql，sqlite则还不支持。

            其他的树模型：

             1.路径枚举 使用一个字段存储该评论的路径比如：评论id为7，则path字段存为：1/4/6/7,跟linux的路径标识一样，标识7的父评论id为6,6的父id为4,4的父id为1，那么查询的时候就可以使用：select * from conments as c where '/1/4/6/7'like c.path || '%';来查询评论7的所有父节点id，同样可以使用select * from conments as c where c.path like; '1/4' || '%';来查询路径为1/4的所有子节点。更新的时候，可以使用mysql的last_insert_id来把一个评论的id加在父评论路径的后面来标识新评论的路径。

            2. 嵌套集：使用nsleft和nsright来保存其后代节点id的范围，一个评论id的所有叶子节点是那些评论id大于nsleft，小于nsright。确认（nsleft，commen_id，nsright）需要对树进行一次深度优先遍历。我们可以通过select c2.* from comnents as c1 join comments as c2 on c.nsleft between c1.nsleft and c1.nsright where c1.comment_id =4 来查询id为4的所有子节点。同样也能来查哪些节点包含了节点4.嵌套集不需要保存分层关系，删除一个节点不会有任何影响。不过插入新节点时，需要重新计算父节点，相邻节点等的这两个值。如果数据库更多的来支持简单快速查询，那么嵌套集是比较好的选择。

           3.闭包集：重新用一张treepath表来存储树中所有节点的关系。里面有两个字段（ancestor，descendant）来存储祖先-后代关系，包括不是直接的夫子关系信息。比如：1/4/6,那么表中有这几条记录（1,1）（1,4),(1,6)(4,6)。查询评论4的祖先用select c.* from comments as c join treepaths as t on c.comment_id=t.descendant where t.ancestor=4，查询子节点也很简单。如果要插入一条记录，直接insert，然后加上一条自己的引用就可以了,比如查询一条记录为8，父节点为5.insert into treepaths(ancestor,descendant) select t.ancestor,8 from threepaths as t where t.descendant = 5 union all select 8,8.删除更简单，比如删除id为7，则delete from treepaths where descendant=7.如果要删除一个完整的子数，比如删除评论4及对应后代的子树，delete from treepaths where descendant in(select descendant from threepaths where ancestor=4)。

 邻接表示最方便的设计，如果数据库支持with或者connect by prior的递归查询，则邻接表查询更高效。枚举路径只管，但是不能确保引用完整性，设计比较脆弱。嵌套集是比较聪明，但是也不能确保引用完整性。闭包表是最通用的设计。使用空间换时间的方法提高性能。

第四章： 需要id

目标：建立主键规范 主键约束应用场景：1.确保一张表中的数据不会出现重复行；2.在查询中引用单独的一行记录；3.支持外键。

      伪主键的支持：mysql：AUTO_INCEMENT,SERIAL;oracle:SEQUENCE;sql server,db2 sybase:INDENTITY;SQLite:ROWID

反模式：给每张表都增加一列id，使用显得太过随意。

        1.冗余键值 比如一个bugs表中，id跟bug_id有着相似的功能，都是为了唯一的标识一条记录。

        2.允许重复值，如果需要其他的组合键 bug_id和product_id在整张表中只出现一次，则需要使用unique约束。但是使用unique约束时，id这一列就变成多余的了。

        3.意义不同的关键字：如果关联查询要查询两张表的两个id，则会导致id会覆盖，并且意义不明确。需要使用类似bug_id或者account_id来定位一条记录。

        4.使用using关键词。select * from bugx as b join bugsProducts as bp on(b.bug_id=bp.bug_id),sql也支持使用using，连接列名一样的话。select * from bugs join bugsProducts using(bug_id).如果所有表都定义一个叫id的伪主键，则外键的列将不能使用相同的列名，而且只能使用on表达式了。

       5.使用组合键之难。写法麻烦，难用。

       不能使用当前使用的最大值+1来获得一条新记录的id，并发问题。序列则将运算和事务在逻辑上分离来解决并发问题。

解决方案： 1直接了当的描述设计，使用有意义的主键id列名，比如bug_id。

           2.无视id这个伪主键，使用其他字段作为主键。

          3.拥抱自然健和组合键。

 

 规范仅仅在它有帮助时才是好的。

 

第五章 不用钥匙的入口

   目标： 简化数据库架构  引用完整性使用外键约束

   反模式： 无视约束

          1.假设无暇代码 插入时手工检测是否外键所引用的列是否存在，删除时需要合理的更新所有相关表。 在高并发下可能存在问题，如果显示的对整张表加锁，则可能降低并发量。

          2.检查错误 通过写外部脚本检测数据异常，所有引用关系的表都需要检测。

         3.不是我的错。代码改动需要确保所有点都已修改。

         4.进退维谷 更新记录时需要同步更新两章表，但是如果做到同步更新，导致程序员不适用外键。

  解决方案：

          有些数据库产品不支持外键 MYISAM 那需要另外的解决方案

          1.声明约束  

          2.支持同步修改 外键支持级联更新， on update cascade,on delete restraict

          3.系统开销不一定会过度。外键约束确实需要一些开销，但是相比其他选择，显得还是更高效一点。

 通过使用约束来帮助数据库防止错误。

 

第六章 实体-属性-值  EAV

    目标：支持可变的属性

    反模式：使用泛型属性表

            issue(issue_id)  issueAttributes(issue_id,attr_name,attrname) 通过另外一张表来支持可扩展的属性，这两张表列少，新增属性不会造成影响，不需要增加列，不会有控制。但是有下列缺陷：

           1.查询属性 需要查询某个属性的值时，需要根据issueAttributes根据属性值查询 select issue_id,attri_value from issueAttributes where attr_name='date';查询操作啰嗦不清晰

           2.支持数据完整性，放弃很多传统数据库设计原有的好处。

           3. 无法声明强制属性。这种设计无法给某一列设置not null等限制。

           4.无法使用SQL的数据类型。比如日期列格式多变，导致数据格式比较乱。

           5. 无法确保引用完整性。无法只针对某列定义外键约束。如果在issueattribute表中设置外键引用，会应用到所有列上。

           6.无法配置属性名。attr_name可以会因为插入记录的不一致行，导致列名不同。

           7.重组列。竖表转化为横表方式转化成多列的查询。查询开销很大。

     解决方案：模型化子类型

          1.单表继承 为所有类型的所有属性都保留一列，大表。同时可以使用一列表示子类型，根据子类型的不同，有些列就不需要填写。不过每增加一个属性，则需要在原有表里增加新的属性列。

                     但是表的列的数量是有限制的，而且没有任何元信息来记录哪些属性属于哪个子类型。如果子类型很少，以及子类型特殊属性很少，你也需要使用active record模式来访问单表数据库时，单表继承模式是最佳选择。

          2.实体表继承。  为每个子类型创建一张单独的表。每个表都包含那些属于鸡肋的共有属性，而包含子类型特殊化的属性。好处在于让你能阻止在一行内存储一些和当前子类型无关的属性。同样，如果将一个新的属性增加到通用属性中，则需要为每个子表都增加一遍。如果不需要考虑子类型需要在所有表中查询，则可以使用视图来关联这些表。当你很少一次性查询所有子类型时，实体继承表设计时最好的选择。

         3.类表继承。创建一张基类表，包含所有子类型的公共属性。对于每个子类型，创建一张子表，通过外键于基类表关联。可以通过视图或者级联查询来一次性获取所有的记录，如果某个子类型不具有某个属性时，其值为空。当你经常要查询所有子类型时这个设计是最佳选择。引用这些公共列就行了。

        4.半结构化数据模型。序列化大字块。如果有很多子类型并且必须经常的增加新的属性支持，则可以使用一个BLOB列来存储xml或者json格式的子类型的特殊属性。这种方式很容易扩展。但是无法获取某个指定的属性查询，也无法对其中的属性进行聚合排序查询。

        5.如果你接收已经使用EAV设计的表，那么从数据库查询出来的数据需要写另外的应用代码来遍历结果数据进行处理。

 为元数据使用元数据。

  第七章：多态关联

        目标：引用多张父表  一个给定的评论只能外键关联一个bug表或者一个特性表。SQL不支持按行连接不同的表。foreigh key(issue_id) references bugs(issue_id) or freatureRequests(issue_id)

        反模式：使用双用途外键。

                1.定义多态关联。使用另外一个列issue_tyep取值为bugs或者featureRequests。但是这样的话就不能定义外键了。

               2.使用多态关联进行查询。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')

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

             1.反向引用。多态关联是多个反向关联。

             2.创建交叉表 分别为bugs和featureRequests表再创建一张表，里面有这个表的id和对应父表的id保存两个表的关系。

             3.创建公用的超级表。超级表只保存一个issue_id，其他bugs，featureRequests，comments表共同引用这个表的外键约束。

       在每个表与表的关系中，都有一个引用表和一个被引用表。