亲宝软件园·资讯

展开

SQL关系模型梳理

Ezail_xdu 人气:0

关系模型

关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表,可以把它们看作很多Excel表。

表的每一行称为记录(Record),记录是一个逻辑意义上的数据。

表的每一列称为字段(Column),同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型(整型、浮点型、字符串、日期等),以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0,同样的,一个字符串型字段为NULL也不表示它的值为空串''

通常情况下,字段应该避免允许为NULL。不允许为NULL可以简化查询条件,加快查询速度,也利于应用程序读取数据后无需判断是否为NULL。

和Excel表有所不同的是,关系数据库的表和表之间需要建立“一对多”,“多对一”和“一对一”的关系,这样才能够按照应用程序的逻辑来组织和存储数据。

在关系数据库中,关系是通过主键和外键来维护的。

主键

例如,假设我们把name字段作为主键,那么通过名字小明或小红就能唯一确定一条记录。但是,这么设定,就没法存储同名的同学了,因为插入相同主键的两条记录是不被允许的。

对主键的要求,最关键的一点是:记录一旦插入到表中,主键最好不要再修改,因为主键是用来唯一定位记录的,修改了主键,会造成一系列的影响。

由于主键的作用十分重要,如何选取主键会对业务开发产生重要影响。如果我们以学生的SFZ号作为主键,似乎能唯一定位记录。然而,SFZ号也是一种业务场景,如果SFZ号升位了,或者需要变更,作为主键,不得不修改的时候,就会对业务产生严重影响。

所以,选取主键的一个基本原则是:不使用任何业务相关的字段作为主键。

因此,SFZ号、手机号、邮箱地址这些看上去可以唯一的字段,均不可用作主键。

作为主键最好是完全业务无关的字段,我们一般把这个字段命名为id

外键

一对多

当我们用主键唯一标识记录时,我们就可以在students表中确定任意一个学生的记录:

id name other columns…
1 小明
2 小红

我们还可以在classes表中确定任意一个班级记录:

id name other columns…
1 一班
2 二班

但是我们如何确定students表的一条记录,例如,id=1的小明,属于哪个班级呢?

由于一个班级可以有多个学生,在关系模型中,这两个表的关系可以称为“一对多”,即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系,我们需要在students表中加入一列class_id,让它的值与classes表的某条记录相对应:

id class_id name other columns…
1 1 小明
2 1 小红
5 2 小白

这样,我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

例如:

小明的class_id1,因此,对应的classes表的记录是id=1的一班;小红的class_id1,因此,对应的classes表的记录是id=1的一班;小白的class_id2,因此,对应的classes表的记录是id=2的二班。

students表中,通过class_id的字段,可以把数据与另一张表关联起来,这种列称为外键。

外键并不是通过列名实现的,而是通过定义外键约束实现的:

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes(id)

其中,外键约束的名称fk_class_id可以任意,FOREIGN KEY (class_id)指定了class_id作为外键,REFERENCES classes (id)指定了这个外键将关联到classes表的id列(即classes表的主键)。

通过定义外键约束,关系数据库可以保证无法插入无效的数据。**即如果classes表不存在id=99的记录,students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能,大部分互联网应用程序为了追求速度,并不设置外键约束,而是仅靠应用程序自身来保证逻辑的正确性。这种情况下,class_id仅仅是一个普通的列,只是它起到了外键的作用而已。

要删除一个外键约束,也是通过ALTER TABLE实现的:

ALTER TABLE students
DROP FOREIGN KEY fk_class_id

注意:删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

多对多

通过一个表的外键关联到另一个表,我们可以定义出一对多关系。有些时候,还需要定义“多对多”关系。例如,一个老师可以对应多个班级,一个班级也可以对应多个老师,因此,班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的,即通过一个中间表,关联两个一对多关系,就形成了多对多关系

一对一

一对一关系是指,一个表的记录对应到另一个表的唯一一个记录。

细心的话会发现,既然是一对一关系,那为啥不给students表增加一个mobile列,这样就能合二为一了?

如果业务允许,完全可以把两个表合为一个表。但是,有些时候,如果某个学生没有手机号,那么,contacts表就不存在对应的记录。实际上,一对一关系准确地说,是contacts表一对一对应students表。

还有一些应用会把一个大表拆成两个一对一的表,目的是把经常读取和不经常读取的字段分开,以获得更高的性能。例如,把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles,大部分时候,只需要查询user_info表,并不需要查询user_profiles表,这样就提高了查询速度。

总结:关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束,也可以不设置约束,仅依靠应用程序的逻辑来保证。

索引

索引的概念与用法

在关系数据库中,如果有上万甚至上亿条记录,在查找记录的时候,想要获得非常快的速度,就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。**通过使用索引,可以让数据库系统不必扫描整个表,而是直接定位到符合条件的记录,这样就大大加快了查询速度。

例如,对于students表:

id class_id name gender score
1 1 小明 M 90
2 1 小红 F 95
3 1 小军 M 88

如果要经常根据score列进行查询,就可以对score列创建索引:

ALTER TABLE students
ADD INDEX idx_score(score);

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score,使用列score的索引。

索引名称是任意的,索引如果有多列,可以在括号里依次写上。

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。**反过来,如果记录的列存在大量相同的值,例如gender列,大约一半的记录值是M,另一半是F,因此,对该列创建索引就没有意义。

可以对一张表创建多个索引。索引的优点是提高了查询效率,缺点是在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。

对于主键,关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的,因为主键会保证绝对唯一。

唯一索引

在设计关系数据表的时候,看上去唯一的列,例如SFZ号、邮箱地址等,因为他们具有业务含义,因此不宜作为主键。

但是,这些列根据业务要求,又具有唯一性约束:即不能出现两条记录存储了同一个SFZ号。这个时候,就可以给该列添加一个唯一索引。例如,我们假设students表的name不能重复:

ALTER TABLE students
ADD UNIQUE INDEX uni_name(name);

通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引:

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下,name列没有索引,但仍然具有唯一性保证。

无论是否创建索引,对于用户和应用程序来说,使用关系数据库不会有任何区别。

这里的意思是说,当我们在数据库中查询时,如果有相应的索引可用,数据库系统就会自动使用索引来提高查询效率,如果没有索引,查询也能正常执行,只是速度会变慢。

因此,索引可以在使用数据库的过程中逐步优化。

Tips

1.通过对数据库表创建索引,可以提高查询速度。但索引越多,插入和更新的速度越慢。

2.索引加得不好,查询不会变快,甚至会变慢。

3.通过创建唯一索引,可以保证某一列的值具有唯一性。

4.数据库索引对于用户和应用程序来说都是透明的。

加载全部内容

相关教程
猜你喜欢
用户评论