文章
问答
冒泡
Sql反模式-如何应对各种数据库常见编程错误

前言

在我工作的过程中,不知为何,我常常与数据库打交道更多一点,最近在新公司当中.我依然看 到大量的数据库反模式,此篇就来细数哪些数据库当中的错误使用案例,本人能力有限,解决 方案并不能尽善尽美,但即便如此也是前车之鉴,后车之师,有则改之,无则加勉,安全生产,警 钟长鸣。

那么有哪些反模式

乱穿马路

目的: 存储多值属性

如,一篇博客可以拥有多个作者,且在数据结构上应该符合1NF.

反模式:格式化的逗号分割列表

下面就是一张不符合1NF的表格,用逗号分割作者列表,由于account_id类型为VarChar, 导致了引发长度不够,非作者ID,分隔符错误,查询统计十分困难,性能极为低下,等等一系列 的问题

post_id title account_id
1 name 1,3
2 pika 1,2,3
3 haha 4,5,6

解决方案:创建一张交叉表

多值属性建议使用交叉表来存储, 有利于crud,避免不可信赖的用户输入.这里可能大家基本 遇不到,因为绝大多数的ORM中多对多关系模型基本解决了这个问题.

post_id account_id
1 1
1 3
2 1
2 2
2 4

我的经历

这里除了这种典型的反模式外,我在工作当中还遇到用json字符串来存储多值属性,这里的道 理也是如此,看似解决了部分问题,可依然有如下的问题:破坏数据关系,性能低下等等.当然, 在Mysql新版本和Postgersql中,支持了Json字段类型.但使用这些字段情况下应当注意.防止 在错误的基础上堆积更多的高层建筑,联系千丝万缕,牵一发而动全身,一不小心就轰然倒塌.若 重构符合规范,其代价不亚于重写一个.避免一声长叹而姑息处理.

单纯的树

老版本的Mysql不支持With语句,CTE递归查询.我们绝大多数使用"邻接表"的数据结构来处理,但 是这种模式递归查询实现困难,当然,现代绝大多数数据库包括Mysql都已经之窗而CTE递归 查询.已经可以很优雅的解决绝大多数树型数据结构的经典问题

反模式: 总是依赖父节点

一颗重视依赖父节点的树

1、what's this?
|-- 2、emmm?
|  `-- 3、No
`-- 4、ahaha?
  |-- 5、ok
  `-- 6、yes
    `-- 7、i can fix it
      `-- 8、awsome

解决方案:邻接表+地柜查询/修改过的前序遍历(MPTT)

  • CTE:
    WITH RECURSIVE cte AS (
    SELECT * FROM Comments WHERE comment_id = 4  -- 条件:评论id = 4
    UNION                     -- 递归:父评论id为此评论id的评论
    SELECT c.* FROM Comments c JOIN cte t WHERE c.parent_id = t.comment_id
    )
    SELECT * FROM cte;
  • Mptt: 可以参考这篇文章:[http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/]

    感悟

    曾经公司树型模型使用外键链接,实际上本质上也是临近表模型,但是问题出就出在了查询上 面,曾经的同事在客户端地滚查询树,导致了与数据库多次IO,使得查询极其缓慢,我在这上面 优化了近半个月,才搞定.所以这里一定多注意

主键,我们需要唯一

反模式:以不变应万变

-- bug_id、product_id没有唯一,当然可以出现重复值啦
CREATE TABLE BugProducts (
 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 Bugs(product_id)
);

解决方案: 裁减设计

CREATE TABLE BugProducts (
 bug_id    BIGINT UNSIGNED NOT NULL,
 product_id  BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY (bug_id, product_id),         -- 放大招,此处应有掌声
 FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
 FOREIGN KEY (product_id) REFERENCES Bugs(product_id)
);

感悟

  • 尽量使用逻辑主键
  • 使用业务主键考虑好业务的不可变性,比如:手机号,一旦用户更换手机号,不用我多说了吧
  • 主键不能交给用户修改
  • 除了交叉表,尽量不使用业务主键
  • 分布式时,不要使用自增ID作为主键,应采用UUID(Mongodb的ObjectID)

不用钥匙的入口

目的: 简化数据库架构(FK)

有时候,人们听到有毒性的东西,就恐惧,但是抛开剂量就谈毒性就是耍流氓.网上盛传的阿 里巴巴开发手册说了一句不要使用外键.有些人就奉为圣经.不提前提条件和场景,单说需要 说着不需要,都是伪科学.

  • 反对派认为,外键同步更新和删除麻烦来拒绝使用外键,站得住脚吗?难道不知道外键有级 联更新吗?还有,人家阿里是分布式的,表都不在一个库里,你一个小型应用,单机mysql也 把这个当圣经.

反模式: 无视约束

  • 使用应用程序保证数据的完整性
  • 不用外键时数据管理简单,操作方便,性能高(呵呵)

解决方案:声明约束

  • 由数据库自身保证数据一致性
  • 有主键的可以增加ER图的可读性
  • 外键一定程度上说明了业务逻辑,会使设计周到及具体

感悟

安全还是性能,这是要取舍的,注重安全使用外键,适用于小型和注重安全的应用,注重性能则 抛弃外键,但需要更好的检查用程序来控制约束,适用于大多数可允许数据冗余和容错的大中 型应用

认贼作父

目的:引用多个父表

比如一篇文章,评论表(comments)可能被(作者表)和一些其他表同时用到.当你看清楚问的根 源时,解决方案将变得异常简单,多态关联就是一个反向关联

反模式: 使用双用途外键

  • 例如 评论表中一个type字段说明是来自哪张表的,就像一个娃于有两个爹

    解决方案

  • 使用交叉表

感悟

认清谁是你爹,就这么简单,现在公司我见到的设计就是这样,比如附件表,总以为附件从 属于文章,附件从属于广告主,附件从属于后台管理,重度耦合,一个东西被存储多次,臃 肿不堪,无法去重.所以认清谁是你爹很重要

多列属性

目的: 存储多值属性

给一个文章打标签

反模式

创建多个tag列,你能确定一篇文章只有短短几个tag?

post_id title tag1 tag2 tag3
1 hahha 技术 null null
### 解决方案
- 创建重属表
- 使用BitMap存储tag更好

精度!

目的: 使用小数取代整数

FLOAT在数据库引擎当中不是一个非精确值

反模式:使用FLOAT类型

不用说了吧,Float不是一个精确的值,应该很多人知道吧,JavaScript Number类型没把你坑 够吗?

解决方案: 使用DECIMAL类型

天天新花样

目的:限定列的有效值(ENUM)

当一个Status表取值必须有效,但是Status取值不是固定的,有可能添加新的Status值

反模式: 在列上使用ENUM类型

这个是群里面一个人提出的神经病设计,当你动态更改Status值的时候,就要去求DBA更改数 据结构,这不是有病吗?

解决方案:在数据中指定值

CREATE TABLE Status (
 status VARCHAR(20) PRIMARY KEY
);

感悟:慎用ENUM

大多数SQL引擎并不支持枚举类型,除非固化不动,尽量慎用。增删改的时候需要专 业人员使用DDL(数据库定义语言),而不是普通用户可以使用的DML(数据操纵语言)

对未知的恐惧

目的: 辨别悬空值

获取全名的时候,将中间名声明为了NULL,导致NULL的全名也成了NULL

SELECT CONCAT(first_name, middle_initial, last_name) FROM Accounts;

反模式

  • 将所有列一律声明为NOT NULL
  • 列中的每一个值都必须存在且有意义的时候,却用了NULL

解决方案: 将NULL视为特殊值

这就不用说了吧,比如常见有个让叫NULL导致了系统崩溃,这都是一个梗了, 值必须存在且有 意义的时候,请务必声明为NOT NULL,可结合DEFAULT赋予默认值。

感悟: To be or not to be, Null or Not Null.

JavaScript: true, false, null, undefined, Nan, Infinity

可怜人的搜索引擎

通过关键字搜索字符串, 通过字符串搜索全文

反模式

使用LIKE性能和效率都非常低,大量数据全文检索的情况下速度堪忧

SELECT * FROM posts WHERE post LIKE '%crash';

解决方案

  • 使用全文索引,Mysql新版本就有,Postgersql自带
  • 使用Lucene/ElasticSearch第三方工具

面条查询

目标: 减少SQL查询数量

如何正确、简单、高效完成复杂的SQL查询。

反模式

SQL开发人员常常仅用一句SQL查询完成一个复杂问题。

解决方案

常用的复杂的SQL查询,其实可以创建视图(VIEW)。

魔豆,魔豆

目标:简化MVC模型(Design pattern)

低耦合、高内聚。清晰的领会模型的意义。

反模式:模型仅仅是活动记录

一个领域模型(Model)对应一个数据库表(Table),导致大量代码别复制粘贴, 而不能很好的被复用。高耦合,低内聚,业务逻辑混乱。 其实这是对Active Record理解的问题,也属于设计模式问题, 一个领域模型对应一个具体表还一个业务逻辑模型的问题。

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

原著推荐《领域驱动设计:软件核心复杂性应对之道》,模型设计围绕程序逻辑,而不是数据库层面,将模型和表解耦,业务逻辑与实体表分层。

感悟:运用之道,存乎一心

  • 思维: SQL语法只是基础手段, 思维方式才是革命纲领 ORM,其实就是表和模型之间关联的手段,在《宫本行茂的程序世界》中说道,数据要抽象, 而Model就是Table数据的抽象,但是很多时候,我发现有两个极端,一,极度依赖ORM的, 二,极度依赖SQL的,但是却忘了,还是软件工程中的铁则--没有银弹,具体问题要具体 分析。

关于作者

雷米Remy
获得点赞
文章被阅读