数据库设计准则(第一、第二、第三范式说明)

I、关系数据库设计范式介绍

1.1 第一范式(1NF)无重复的列

所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息。简而言之,第一范式就是无重复的列。

说明:在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

1.2 第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。例如员工信息表中加上了员工编号(emp_id)列,因为每个员工的员工编号是惟一的,因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。

第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。

1.3 第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]

满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。

II、范式应用实例剖析

下面以一个学校的学生系统为例分析说明,这几个范式的应用。首先第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统(DBMS)中,傻瓜也不可能做出不符合第一范式的数据库,因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此,你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。

首先我们确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息,说关心的问题有如下几个方面。

  • 学生有那些基本信息

  • 学生选了那些课,成绩是什么

  • 每个课的学分是多少

  • 学生属于那个系,系的基本信息是什么。

2.1 第二范式(2NF)实例分析

首先我们考虑,把所有这些信息放到一个表中(学号,学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话)下面存在如下的依赖关系。

(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话)
(课程名称) → (学分)
(学号,课程)→ (学科成绩)

2.1.1 问题分析

因此不满足第二范式的要求,会产生如下问题

  • 数据冗余: 同一门课程由n个学生选修,”学分”就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

  • 更新异常:
    1) 若调整了某门课程的学分,数据表中所有行的”学分”值都要更新,否则会出现同一门课程学分不同的情况。
    2)假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有”学号”关键字,课程名称和学分也无法记录入数据库。

  • 删除异常 : 假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

2.1.2 解决方案

把选课关系表SelectCourse改为如下三个表:

  • 学生:Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话);

  • 课程:Course(课程名称, 学分);

  • 选课关系:SelectCourse(学号, 课程名称, 成绩)。

2.2 第三范式(3NF)实例分析

接着看上面的学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字”学号”,因为存在如下决定关系:

(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话)

但是还存在下面的决定关系

(学号) → (所在学院)→(学院地点, 学院电话)

即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。

它也会存在数据冗余、更新异常、插入异常和删除异常的情况。 (數據的更新,刪除異常這里就不分析了,可以參照2.1.1進行分析)

根据第三范式把学生关系表分为如下两个表就可以滿足第三范式了:

  • 学生:(学号, 姓名, 年龄, 性别,系别);
  • 系别:(系别, 系办地址、系办电话)。

总结

上面的数据库表就是符合I,II,III范式的,消除了数据冗余、更新异常、插入异常和删除异常。

MySQL删除大量数据的一些建议

生产环境,往往需要更新/删除大量的数据,由于很可能消耗太多的IO资源,对于生产繁忙的系统,需要小心,以避免对生产环境造成影响。
删除大量数据还有一些副作用,比如主从延时、数据文件无法收缩、锁表等。

以下是一些要指引和规则:

1、批量删除,这样往往可以工作得更快,你可能需要在每次批量删除前sleep一段时间,控制删除的频率,这样的目的是减少对生产系统的IO冲击,把符合平均分布,避免从库滞后太多;

2、可以考虑分区表技术,我很少用分区表,但删除一个分区,显然比删除大量数据简单方便的多,这也是分区表清理/归档数据的优势所在;

3、按照主键的序列分批分批,或者基于时间分批分批,你总可以找到一种方式批量删除,如果实在没有批量删除的方式,可能你的表结构设计得不好;

4、基于硬件的性能,每批删除的记录数,可以选择几百到几千到几万的数据量,但不要太大,MySQL很难同时处理好大事务和随机小事务;

5、如果要删除大部分数据,那么可以考虑的方式是,创建一个新表,insert要保留的数据,然后切换表;

6、对于大表(InnoDB)删除大量数据,如果是一个很大的事务,中止删除数据的操作,可能需要几倍的时间用于回滚,导致严重的IO瓶颈,而批量删除可以让我们的回滚恢复得快得多。

7、需要留意空间的释放,选择独立表空间会更有利于释放空间。

MySQL出现Waiting for table metadata lock的原因以及解决方法

MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

造成alter table产生Waiting for table metadata lock的原因其实很简单,一般是以下几个简单的场景:

场景一:长事物运行,阻塞DDL,继而阻塞所有同表的后续操作

通过show processlist可以看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。

这是最基本的一种情形,这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中(见下图),在after create步骤会获取metadata 独占锁,当进行到altering table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl的表现,并不会像之前在整个alter table过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
处理方法: kill 掉 DDL所在的session.

场景二:未提交事物,阻塞DDL,继而阻塞所有同表的后续操作

通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在 information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁。

处理方法:通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉,让其回滚。

场景三:

通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下:

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

处理方法:通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.

总之,alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的),在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

深入了解 Token 认证的来龙去脉

不久前,我在在前后端分离实践中提到了基于 Token 的认证,现在我们稍稍深入一些。

通常情况下,我们在讨论某个技术的时候,都是从问题开始。那么第一个问题:

为什么要用 Token?

而要回答这个问题很简单——因为它能解决问题!

可以解决哪些问题呢?

  1. Token 完全由应用管理,所以它可以避开同源策略
  2. Token 可以避免 CSRF 攻击
  3. Token 可以是无状态的,可以在多个服务间共享

Token 是在服务端产生的。如果前端使用用户名/密码向服务端请求认证,服务端认证成功,那么在服务端会返回 Token 给前端。前端可以在每次请求的时候带上 Token 证明自己的合法地位。如果这个 Token 在服务端持久化(比如存入数据库),那它就是一个永久的身份令牌。

于是,又一个问题产生了:需要为 Token 设置有效期吗?

需要设置有效期吗?

对于这个问题,我们不妨先看两个例子。一个例子是登录密码,一般要求定期改变密码,以防止泄漏,所以密码是有有效期的;另一个例子是安全证书。SSL 安全证书都有有效期,目的是为了解决吊销的问题,对于这个问题的详细情况,来看看知乎的回答。所以无论是从安全的角度考虑,还是从吊销的角度考虑,Token 都需要设有效期。

那么有效期多长合适呢?

只能说,根据系统的安全需要,尽可能的短,但也不能短得离谱——想像一下手机的自动熄屏时间,如果设置为 10 秒钟无操作自动熄屏,再次点亮需要输入密码,会不会疯?如果你觉得不会,那就亲自试一试,设置成可以设置的最短时间,坚持一周就好(不排除有人适应这个时间,毕竟手机厂商也是有用户体验研究的)。

然后新问题产生了,如果用户在正常操作的过程中,Token 过期失效了,要求用户重新登录……用户体验岂不是很糟糕?

为了解决在操作过程不能让用户感到 Token 失效这个问题,有一种方案是在服务器端保存 Token 状态,用户每次操作都会自动刷新(推迟) Token 的过期时间——Session 就是采用这种策略来保持用户登录状态的。然而仍然存在这样一个问题,在前后端分离、单页 App 这些情况下,每秒种可能发起很多次请求,每次都去刷新过期时间会产生非常大的代价。如果 Token 的过期时间被持久化到数据库或文件,代价就更大了。所以通常为了提升效率,减少消耗,会把 Token 的过期时保存在缓存或者内存中。

还有另一种方案,使用 Refresh Token,它可以避免频繁的读写操作。这种方案中,服务端不需要刷新 Token 的过期时间,一旦 Token 过期,就反馈给前端,前端使用 Refresh Token 申请一个全新 Token 继续使用。这种方案中,服务端只需要在客户端请求更新 Token 的时候对 Refresh Token 的有效性进行一次检查,大大减少了更新有效期的操作,也就避免了频繁读写。当然 Refresh Token 也是有有效期的,但是这个有效期就可以长一点了,比如,以天为单位的时间。

时序图表示

使用 Token 和 Refresh Token 的时序图如下:

1)登录

2)业务请求

3)Token 过期,刷新 Token

上面的时序图中并未提到 Refresh Token 过期怎么办。不过很显然,Refresh Token 既然已经过期,就该要求用户重新登录了。

当然还可以把这个机制设计得更复杂一些,比如,Refresh Token 每次使用的时候,都更新它的过期时间,直到与它的创建时间相比,已经超过了非常长的一段时间(比如三个月),这等于是在相当长一段时间内允许 Refresh Token 自动续期。

到目前为止,Token 都是有状态的,即在服务端需要保存并记录相关属性。那说好的无状态呢,怎么实现?

无状态 Token

如果我们把所有状态信息都附加在 Token 上,服务器就可以不保存。但是服务端仍然需要认证 Token 有效。不过只要服务端能确认是自己签发的 Token,而且其信息未被改动过,那就可以认为 Token 有效——“签名”可以作此保证。平时常说的签名都存在一方签发,另一方验证的情况,所以要使用非对称加密算法。但是在这里,签发和验证都是同一方,所以对称加密算法就能达到要求,而对称算法比非对称算法要快得多(可达数十倍差距)。更进一步思考,对称加密算法除了加密,还带有还原加密内容的功能,而这一功能在对 Token 签名时并无必要——既然不需要解密,摘要(散列)算法就会更快。可以指定密码的散列算法,自然是 HMAC。

上面说了这么多,还需要自己去实现吗?不用!JWT 已经定义了详细的规范,而且有各种语言的若干实现。

不过在使用无状态 Token 的时候在服务端会有一些变化,服务端虽然不保存有效的 Token 了,却需要保存未到期却已注销的 Token。如果一个 Token 未到期就被用户主动注销,那么服务器需要保存这个被注销的 Token,以便下次收到使用这个仍在有效期内的 Token 时判其无效。有没有感到一点沮丧?

在前端可控的情况下(比如前端和服务端在同一个项目组内),可以协商:前端一但注销成功,就丢掉本地保存(比如保存在内存、LocalStorage 等)的 Token 和 Refresh Token。基于这样的约定,服务器就可以假设收到的 Token 一定是没注销的(因为注销之后前端就不会再使用了)。

如果前端不可控的情况,仍然可以进行上面的假设,但是这种情况下,需要尽量缩短 Token 的有效期,而且必须在用户主动注销的情况下让 Refresh Token 无效。这个操作存在一定的安全漏洞,因为用户会认为已经注销了,实际上在较短的一段时间内并没有注销。如果应用设计中,这点漏洞并不会造成什么损失,那采用这种策略就是可行的。

在使用无状态 Token 的时候,有两点需要注意:

  1. Refresh Token 有效时间较长,所以它应该在服务器端有状态,以增强安全性,确保用户注销时可控
  2. 应该考虑使用二次认证来增强敏感操作的安全性

到此,关于 Token 的话题似乎差不多了——然而并没有,上面说的只是认证服务和业务服务集成在一起的情况,如果是分离的情况呢?

分离认证服务

当 Token 无状态之后,单点登录就变得容易了。前端拿到一个有效的 Token,它就可以在任何同一体系的服务上认证通过——只要它们使用同样的密钥和算法来认证 Token 的有效性。就样这样:

当然,如果 Token 过期了,前端仍然需要去认证服务更新 Token:

可见,虽然认证和业务分离了,实际即并没产生多大的差异。当然,这是建立在认证服务器信任业务服务器的前提下,因为认证服务器产生 Token 的密钥和业务服务器认证 Token 的密钥和算法相同。换句话说,业务服务器同样可以创建有效的 Token。

如果业务服务器不能被信任,该怎么办?

不受信的业务服务器

遇到不受信的业务服务器时,很容易想到的办法是使用不同的密钥。认证服务器使用密钥1签发,业务服务器使用密钥2验证——这是典型非对称加密签名的应用场景。认证服务器自己使用私钥对 Token 签名,公开公钥。信任这个认证服务器的业务服务器保存公钥,用于验证签名。幸好,JWT 不仅可以使用 HMAC 签名,也可以使用 RSA(一种非对称加密算法)签名。

不过,当业务服务器已经不受信任的时候,多个业务服务器之间使用相同的 Token 对用户来说是不安全的。因为任何一个服务器拿到 Token 都可以仿冒用户去另一个服务器处理业务……悲剧随时可能发生。

为了防止这种情况发生,就需要在认证服务器产生 Token 的时候,把使用该 Token 的业务服务器的信息记录在 Token 中,这样当另一个业务服务器拿到这个 Token 的时候,发现它并不是自己应该验证的 Token,就可以直接拒绝。

现在,认证服务器不信任业务服务器,业务服务器相互也不信任,但前端是信任这些服务器的——如果前端不信任,就不会拿 Token 去请求验证。那么为什么会信任?可能是因为这些是同一家公司或者同一个项目中提供的若干服务构成的服务体系。

但是,前端信任不代表用户信任。如果 Token 不没有携带用户隐私(比如姓名),那么用户不会关心信任问题。但如果 Token 含有用户隐私的时候,用户得关心信任问题了。这时候认证服务就不得不再啰嗦一些,当用户请求 Token 的时候,问上一句,你真的要授权给某某某业务服务吗?而这个“某某某”,用户怎么知道它是不是真的“某某某”呢?用户当然不知道,甚至认证服务也不知道,因为公钥已经公开了,任何一个业务都可以声明自己是“某某某”。

为了得到用户的信任,认证服务就不得不帮助用户来甄别业务服务。所以,认证服器决定不公开公钥,而是要求业务服务先申请注册并通过审核。只有通过审核的业务服务器才能得到认证服务为它创建的,仅供它使用的公钥。如果该业务服务泄漏公钥带来风险,由该业务服务自行承担。现在认证服务可以清楚的告诉用户,“某某某”服务是什么了。如果用户还是不够信任,认证服务甚至可以问,某某某业务服务需要请求 A、B、C 三项个人数据,其中 A 是必须的,不然它不工作,是否允许授权?如果你授权,我就把你授权的几项数据加密放在 Token 中……

废话了这么多,有没有似曾相识……对了,这类似开放式 API 的认证过程。开发式 API 多采用 OAuth 认证,而关于 OAuth 的探讨资源非常丰富,这里就不深究了。

转自:开源中国-边城

架构师需要具备的几项技能

好的架构师,一定是好的程序员

  • 没写过N年代码,何来指点江山?

  • 没做过N年架构实践,何以指导避坑?

  • 只有框架,没有细节,何以服众?

bad case:项目失败了,架构师点评技术团队“能力差”

bad case :项目失败了,技术团队点评架构师“只会忽悠”

跨域知识

  • 如果只是一个方向能力强,顶多算技术高手

  • 除了一个/几个方向的专家,对端,站点,服务,数据等多方面均要有所了解

  • 对研发,测试,运维,安全均要有所了解

  • 除了技术,对业务,产品,项目管理均要有所了解

画外音:10年专于一个方向,好还是不好,值得讨论?

“把问题讲清楚”是一项很重要的能力

  • 懂得把复杂的事物进行抽象,简单化

  • 懂得把抽象的事物进行形象,具体化

  • 懂得把形象的事物落到白板上,画出来

  • 懂得把白板上的事物讲出来

bad case:有些架构师,自己懂,但讲不出来,挺遗憾。

落地能力,沟通能力,执行力

  • 光讲清楚还不够,能落地实现,才是真功夫

  • 同样是技术人,沟通切忌颐指气使,发号施令

  • 别把高大上的名词天天挂在嘴上,落地了什么,才是价值

画外音:最近这些名词听得多不?区块链,云计算,Saas。一定要小心,任何脱离业务的架构设计,都是耍流氓。

见过纸上谈兵的大拿么?


转自:
微信公众号-架构师之路

Laravel Eloquent 强制MySql使用索引

为什么需要强制索引?

数据库没有使用我们设想的索引进行sql查询,导致查询特别慢。

mysql强制索引查询语句

laravel中实现强制索引查询


作者:caijinlin
链接:https://www.jianshu.com/p/eee9b1a98cc2
來源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

Laravel Eloquent 查询慢

在做一个后台统计项目时遇到一个很奇怪的问题,使用 Laravel 框架的 Eloquent 查询数据时,非常慢。该表的数据量也不算太大,几十万条数据而已。使用 MySQL 命令行工具查询,同样的 SQL 语句只需要几十毫秒就可以完成查询。

通过反复对比 Laravel 生成的 SQL 和手写的 SQL,并没有发现任何异常,同样的 SQL 语句直接使用 PDO 查询速度也很快,但是在 Laravel 框架中执行就非常慢。在 Google,StackOverflow 等网站也有人提出了同样的问题,但是并没有得到有效的解决方案。

Laravel 框架的 Eloquent 最终也是调用的 PDO,所以通过反复思考还是认为在 PDO 的配置上出现了问题,但是使用 Laravel 的 PDO 配置,直接使用原生 PDO 代码查询却并不慢。

最终通过 Debug 发现问题出在了 Laravel 的数据库配置文件上,在官方文档中数据库的配置文件如下:

在我们平时创建数据表时,collation通常选择utf8_general_ci,但是此处的配置却是utf8_unicode_ci,导致通过 Laravel 执行同样的查询时,索引不能正常使用。解决方案,将配置文件中的collation修改为 null,如下所示:

这样 Laravel 连接数据库查询时会使用数据库的默认配置。再刷新一下页面,查询速度果然快了很多,与命令行执行查询的速度基本一样。问题终于解决。

关于collation配置造成查询慢的原因,等有时间再另写文章分析。