前言
在我工作的过程中,不知为何,我常常与数据库打交道更多一点,最近在新公司当中.我依然看 到大量的数据库反模式,此篇就来细数哪些数据库当中的错误使用案例,本人能力有限,解决 方案并不能尽善尽美,但即便如此也是前车之鉴,后车之师,有则改之,无则加勉,安全生产,警 钟长鸣。
那么有哪些反模式
乱穿马路
目的: 存储多值属性
如,一篇博客可以拥有多个作者,且在数据结构上应该符合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的,但是却忘了,还是软件工程中的铁则--没有银弹,具体问题要具体 分析。