SQL 反模式

所谓专家就是在一个很小的领域把所有错误都犯过的人。

逻辑数据库反模式

1.存储多值属性。

反模式：格式化的逗号分隔列表。增加插入，更新，删除复杂性，无法利用索引，通过逗号列表中的某一属性查询效率低，无法验证列表有效性，需要选择合适的分隔符，长度限制。

合理使用：在不需要获取单独项，仅展示列表时可以提高效率。

解决方案：创建交叉表：id1，id2。

2.分层存储和查询。

反模式：总依赖父节点。parentid。

合理使用：邻接表优势在于能快速定位直接父节点，容易插入新节点。通过WITH表达式也可以很好的递归。

解决方案：路径枚举、嵌套集、闭包表。

设计   表  查询子节点     查询树   插入   删除    引用完整性

邻接表  1       简单        困难    简单    简单      是

递归查询 1      简单         简单    简单    简单      是

路径枚举  1       简单       简单    简单    简单      否

嵌套集  1       困难         简单     困难     困难      否

闭包表   2       简单         简单    简单    简单      是

  

3.建立主键规范。

反模式：主键名叫id，并自动增长。冗余的键值，允许重复项，意义不明确，无法使用using关键字。

合理使用：框架原因需要id，或者自然键太长，长字符串索引开销较大。

解决方案：拥抱自然键和组合键

4.简化数据库架构。

反模式：无视约束，不添加约束。

解决方案：声明约束，唯一性，外键，检查等。可支持级联删除和更新。外键需要一些系统开销，但对于其他的方案，外键更高效：

不需要在更新和删除前执行select

在同步修改时不需要锁住整张表

不再需要定期监控孤立数据

5.支持可变属性。

6.引用多个父表。

反模式：使用双用途外键。即通过某个字段判断另以字段要关联的表。

合理使用：当程序框架中可以很好支持多态关联，并封装了风险，可以选择使用。

解决方案：

创建交叉表：

A id....

X1 id

X2

若要保证唯一性，可增加unique约束。

可双向查找。

 

创建公用超级表：

A : id

A1:id FK 。。。。。

A2:id FK 。。。。。

B：id，aid FK。。。。。。

查询时

SELECT FROM b left join A1 using (ID)

left join A2 using (ID)

WHERE......

合并跑到：

通过UNION

7.存储多值属性。

反模式：创建多个列。x1,x2,x3。

查询需搜索多列，添加删除更新不安全性，无法知道更新哪一列，可能某列没有值，无法保证唯一性，可能列不够用要增加列。

合理使用：当属性列固定，当属性列互相没有关系，相互独立。

解决方案：创建从属表

8.支持可扩展性。

反模式：克隆表、克隆列。

将将很长的表拆分成多张小表、将一个列拆分成多个子列。

不断产生新表，数据完整性，同步数据，确保唯一性，跨表查询，同步元数据，管理引用完整性，标识元数据分裂列。

解决方案：水平分区、垂直分区，使用关联表。

水平分区：物理拆分。

垂直分区：根据列拆分。

物理反模式

9.取整错误。

反模式：使用float类型。会舍入，不精确。

合理使用：当取值范围大于interger和numeric时就要使用float。

解决方案：使用numeric和decimal。尽量不要使用浮点数。

10.限定列的有效值。

反模式：在列定义上指定可选值。用check约束。

无法知道有哪些可选值。添加新值困难，无法删除老值，可移植性低下。

合理使用：候选值几乎不变时使用，可在代码中维护列表。

解决方案：在数据中指定值。创建一张表。

11.存储图片和大媒体。

反模式：假设你必须使用文件系统。文件不支持delete，不支持事务隔离，不支持回滚，不支持数据库备份，不支持sql访问权限。

合理使用：存在文件系统的好处：

数据库空间占用小，不包含文件的备份快，预览编辑图片方便。

解决方案：在需要时使用blob类型。

12.优化性能。

反模式：无规划的使用索引。不使用索引，索引不足；使用太多索引和无效索引；执行一些无法使用索引的查询。

解决方案：测量，解释，挑选，测试，优化，重建。

查询反模式：

13.辨别悬空值。

反模式：将NULL作为普通值。

解决方案：将NULL视为特殊值。

14.获取每组最大值。

反模式：引用非分组列。

解决方案：无歧义的使用列。

15.获取样本记录。

反模式：随机排序。使用rand（）表示整个过程无法利用索引。

合理使用：数据量较小，不会有性能问题时。

解决方案：没有具体的顺序。

专用方案：sql server：使用table-sample子句。

oracle 使用sample子句。

16.全文搜索。

反模式：模式匹配。like %a% 或正则表达式。无法从传统索引上受益。返回预料之外的结果。

合理使用：一些查询很少执行，不需要优化。

解决方案：使用正确的工具。

使用特殊的搜索引擎技术而不是sql，即数据库扩展的全文搜索。。讲结果保存起来以减少重复的搜索开销。

17.减少SQL查询数量。

反模式：使用一步操作解决复杂问题。

解决方案：分而治之。一步一个脚印，将复杂查询拆分，寻找union标记，通过代码生成sql语句如

SELECT 'UPDATE X SET X = ' u.id FROM u

18.减少输入。

反模式：捷径会让你迷失方向。破坏代码重构，使用通配符*增加开销，

解决方案：明确列出列名

应用开发反模式：

19.恢复或重置密码。

反模式：明文密码。

解决方案：先哈希 后存储。给哈希加料。重置密码而非返回密码。

20.编写SQL动态查询。

反模式：将未经验证的输入作为代码执行。

解决方案：不信任任何人。过滤输入内容，参数化动态内容，给输入的值加引号，将用户与代码隔离，找可靠的人帮你审查代码。

21.整理数据。

反模式：填充角落。看到分配的编号不连续，第一反应是填补其中的空缺。

解决方案：克服心理障碍。用rownumber定义行号，使用guid。

向对方解释行号不连续的原因，清楚的表明开销，使用自然键。

22.写更少的代码。

反模式：无米之炊。忽略了数据库的返回值，和程序代码混在一起阅读sql代码。

解决方案：关注返回值，查看程序生成的sql代码。

23.采用最佳实践。

反模式：将sql视为二等公民。

解决方案：建立一个质量至上的文化。

清晰的定义项目需求，并写成文档。设计并实现一个解决方案来满足需求。验证并测试解决方案是否符合需求。

数据库文档：

实体关系图。表、列视图。关系。触发器。存储过程。SQL安全。数据库基础设施。ORM。

24.简化MVC模型。

反模式：模型仅仅是活动记录。

解决方案：模型包含活动记录。