nodejs/Sequelize/MySQL——基础、联表及优化

3,087 阅读14分钟

本文你将看到:

  • MySQL、SQL、ORM、Sequelize 在概念上的关系
  • Sequelize 的用法
  • Sequelize 中联表关系的概念、用法、原理、区别
  • 如何优化数据库查询

1 概念

MySQL

大部分人对 MySQL、SQL 是有了解的,毕竟教科书里都写着。 MySQL 是一种典型的关系型数据库嘛,怎么叫关系呢?

简单说,关系型数据库是由多张能互相联接的二维行列表格组成的数据库。

这里两个点:::二维行列表::(体现在个体表数据结构上)、::互相连接::(体现在表关系和库引擎特点上)。

相较于 MongoDB 这种 NoSQL,二维行列表带来的优势在于:

  • 数据结构严谨带来的可靠:每一列的数据类型甚至大小都被模型定义,一切稳稳当当
  • 「平层」带来的理解便利:每一条数据都是「平层」的,毕竟自由嵌套读起来真的太南了

SQL

既然关系型数据库是一个统一的标准,那只要各家都按标准实现,剩下的事就可以统一了,比如对数据库的访问。

SQL 就是干这个的,它只是一句字符串,可以理解为一个命令,在关系型数据库上做任何操作,但考虑关系设计已经把复杂的事情简单化,SQL 无非就做这么几件事:

  • 对库、表本身,以及表之间的关系进行定义
  • 在一张表内增删改查
  • 借助表间关系,一次性联合访问多张表中数据
  • 访问一些基础的运算函数

总之这样一些拼起来,理论上你就能「只用一句SQL」为所欲为了。 了解更多:按我的知识水平,只能读 《菜鸟教程》

ORM 和 Sequlize

但 SQL 是远远不够的,因为字符串本身没有任何约束可言,你可能想查一个数据,结果一手抖打错把库删了,就只能跑路了。另外在代码里写一堆字符串着实丑陋。

所以出来一种叫 ORM 的东西,什么是 ORM 呢?字面上意思是::「对象关系映射」::,有点绕。

其实就是把数据库表映射成语言对象;然后暴露出一堆方法用来查库,ORM 负责把方法调用转成 SQL;因为表中的记录就是 key - value 形式,所以查询到的返回结果通常也是个对象,方便使用数据。这样对数据库访问的便捷性和稳定性都得到了提高。

          方法          SQL 
业务逻辑 <------> ORM <------> 数据库 
         数据对象        数据 

然而 ORM 只是一个解决方案。在右侧,不受数据库类型限制,只要是遵循 SQL 的关系型数据库都得到支持;在左侧,不受语言类型限制,各家都有相对成熟的实现方案,甚至会根据语言特性增加一些语言层面的优化支持。

在 nodejs 中,「Sequlizejs」可能是最出类拔萃的 ORM 实现。植根于 nodejs,Sequlizejs 完美支持 Promise 式调用,进一步你可以走 async/await,和业务代码紧密粘合;如果上了 ts,从模型定义带来的类型提醒能让调用更省心。

官方文档在这里:《Sequelize V5》


2 基础用法

表/模型的定义

前面提到,ORM 的第一步就是要建立对象到数据表的映射,在 Sequlize 里是这样的,比如我们关联一个 station 的表

const Model = sequlize.define('station', { 
	id: { 
		field: 'id', 
		type: Sequelize.INTEGER, 
		allowNull: false, 
		primaryKey: true, 
		autoIncrement: true, 
	}, 
	store_id: Sequelize.STRING(20), 
	name: Sequelize.STRING(20), 
	type: Sequelize.TINYINT, 
	status: Sequelize.TINYINT, 
	ip: Sequelize.STRING(20), 
	plate_no: Sequelize.STRING(20), 
	related_work_order_id: Sequelize.BIGINT, 
}); 

可见在定义过程中,数据类型是从 Sequelize 静态属性上引用的。这些类型能覆盖数据库里的类型,但命名并非对应的,具体参考:lib/data-types.js

你也可以通过 define 的第三个参数做一些自定义,这些配置会被合并到 Sequlize 构造函数的 define 字段中,用来定义模型和数据表的关联行为,比如「自动更新表中的 update_at、create_at」。参考 Model | Sequelize 中的 options

但是模型归模型,是给 ORM 用的,数据库里的表还是要自己去建的。通过客户端或如下这种建表 SQL:

CREATE TABLE `station` ( 
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, 
`store_id` varchar(20) NOT NULL DEFAULT '', 
`name` varchar(20) NOT NULL DEFAULT '', 
`type` tinyint(4) NOT NULL DEFAULT '0', 
`status` tinyint(4) NOT NULL DEFAULT '0', 
`ip` varchar(20) NOT NULL DEFAULT '', 
`related_work_order_id` bigint(20) NOT NULL DEFAULT '0', 
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`plate_no` varchar(20) NOT NULL DEFAULT '', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='工位表'; 

基础的CURD

Sequlize 对象提供丰富的 api,诸如:

  • findOne、findAll……
  • create、upsert……
  • aggregate、max……

API 的调用应该不用赘述,文档 /lib/model.js~Model 里都安排的明明白白。这里主要看下当我们调用一个基础 api,Sequlize 转成了什么,对理解 ORM 和 SQL 的对应关系很有帮助。

一个例子:findAll

当没给 attributes,Sequelize 默认会把模型中的定义拿出来做 attributes,这样比 Select * 节约数据库操作成本和传输带宽。

最简单的,当我执行一个

Station.findAll() 

Sequlize 转成的 SQL 是这样的

SELECT 
	`id`, 
	`store_id`, 
	`name`, 
	`type`, 
	`status`, 
	`ip`, 
	`plate_no` 
FROM 
	`station` AS `station`; 

我们可以简单加一些条件:

Station.findAll({ 
	attributes: [ 'ip' ], 
	where: { 
		status: 1, 
	}, 
order: [ 
[ 'name', 'ASC' ], 
], 
limit: 10, 
offset: 5, 
}); 

SQL 这样的(还是很清晰的)

SELECT `ip` FROM `station` AS `station` WHERE `station`.`status` = 1 ORDER BY `station`.`name` ASC LIMIT 5, 10; 

第二个例子:findOrCreate

有些高级 API 会::触发数据库事务::

事情通常不会这么简单,比如当我调

Station.findOrCreate({ 
	where: { 
		id: 1, 
	}, 
	defaults: { 
		name: 'haha', 
	}, 
}); 

你知道的,在一句 SQL 中是不能实现的,于是 sequlize 开了事务,做「查 -> 判断 -> 增」

START TRANSACTION; 
SELECT `id`, `store_id`, `name`, `type`, `status`, `ip`, `plate_no` FROM `station` AS `station` WHERE `station`.`id` = 2; 
INSERT INTO `station` (`id`,`name`) VALUES (2,`haha`); 
COMMIT; 

3 联表查询

3.1 为什么要联表

前面我们有了个 Station 表,现在多了个 Car 表,通过 station_id 记录 Car 所在的 Station,那我要查 Station 列表以及它们包含的 Car 怎么办。

先 find Station,再用 where - station_id - in 查 Car,最后写逻辑遍历 Station 把 Car 挨个塞进去,是可行的。但一方面多一次查询会增加数据库资源消耗,另一方面自己也多了处理逻辑。

所以我们需要用到「关系型数据库」所擅长的「表间关系」,一次完成上述查询和数据合并。

3.2 联表关系

在 Sequlize 中,联表关系需要在模型 associate 方法中标记,通常为这种格式:

File.belongsTo(User, {...option}); 

查的时候用 include

File.findOne({
	include: [{ model: User }],
}); 

同模型定义本身一样,这个标记不会对数据库进行任何操作,只是在 ORM 层建立模型之间的关系。这种关系会在我们调用联表查询时,转换成「JOIN」这样的联表 SQL 语句。

查的操作总是「include」,但是否标记以及采用哪个标记方法,决定了后面查询时是否可用联表查询,以及查询的 SQL 和查询结果的组织方式。

首先明确几个概念,在一个标记行为中

  • 两种模型
    • 源模型:需要标记和其他模型关系的模型,就是执行联表查询的模型 (上面的 File)
    • 目标模型:被标记关系的模型,本身不因此次标记获得联表查询能力 (上面的 User)
  • 四种关联键
    • foreignKey:外键,用来关联外部模型,::一个模型有了外键,对关联的模型来说就是唯一了::
    • targetKey
    • sourceKey
    • otherKey:当一个 foreignKey 不够用时的替代品

表之间的关系通常包括:一对一、一对多、多对多。

3.3 一对一关系(belongsTo / hasOne)

【Sequelize】一对一关系文档

【误区】这里我之前有个误区,以为「一对一」是像夫妻关系一样「双向唯一」的,其实不然。我们的关系声明只能从源模型单向发起,也就是说「一对一」关系也是单向的,只能保证「源模型记录对应一条目标模型记录」,反过来不保证。 就像「儿子.hasOne(爸爸)」,不保证「爸爸」只有一个儿子。

一对一关系可用 belongsTo、hasOne 两种方式标记。

1 belongsTo

File(源模型)中有一个 creator_id标记自己::所属::的 User(目标模型)。这里 User 不见得只有一个 File,但一个 File 只能关联到一个 User 上。

File.BelongsTo(User, { 
	foreignKey: 'creator_id',	// 如果不定义这个,也会自动定义为「目标模型名 + 目标模型主键名」即 user_id
	targetKey: 'id',	// 目标模型的关联键,默认主键,通常省略
}); 
// 这里 creator_id 位于源模型 File 上 

2 hasOne

条件还是 File 模型中有一个 creator_id标记自己所属的 User。这时如果反过来把 User 当源模型,在 User 侧,假设一个 User 只有一个 File,我们要从 User 拿 File:

User.HasOne(File, { 
	foreignKey: 'creator_id',	// 如果不定义这个,也会自动定义为「源模型名 + 源模型主键名」即 user_id
	sourceKey: 'id',	// 源模型的关联键,默认主键,通常省略
}
// 这里 creator_id 位于目标模型 File 上 

hasOne 把 foreignKey 反转到源模型上,所以在 targetKey、sourceKey 用默认值的前提下(通常也是这样的),foreignKey 的位置可以决定用 BelongsTo 还是 HasOne 方便。

3 BelongsTo 和 HasOne

BelongsTo 和 HasOne 都可以定义「一对一」关系,借助三种 key 二者理论上可以互相替代。

事实上转换后的 SQL 也是一样的::LEFT JOIN::

# File.BelongsTo(User)
SELECT `FileClass`.`id`, `user`.`id` AS `user.id`
FROM `file` `FileClass`
	LEFT JOIN `user` ON `FileClass`.`creator_id ` = `user`.`id`
# User.HasOne(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM `user` `UserClass`
	LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `

但区别在「概念」层面,「属于」和「有一个」是两回事,哪个模型相对于另一个模型真的是「唯一归属」的,那么这个模型就该拥有一个 foreignKey。

3.4 一对多(hasMany)

::hasMany 可以理解为「多选版的 hasOne」,::和 hasOne 一样,这里要求::「目标模型」对「源模型」是唯一归属的::

还是上面的场景,File 模型中有一个 creator_id标记自己创建的 User。这里我们从 User 拿所有他创建的 File。

User.HasMany(File, { 
	foreignKey: 'creator_id',	// 如果不定义这个,也会自动定义为「源模型名 + 源模型主键名」即 user_id
	sourceKey: 'id',	// 源模型的关联键,默认主键,通常省略
}
// 这里 creator_id 位于目标模型 File 上 

和 hasOne 的深层区别

实际上::在 findAll 下,SQL 和「一对一」的一样::(也就是说 ORM 也无法限制 LEFT JOIN 进来的数量,所谓一对一只是「全 JOIN 进来选一个」)。

# User.HasMany(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM `user` `UserClass`
	LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `

::findOne 就不同了::,如果你用 hasOne,SQL 只需要全局给个LIMIT 1,意味着「源模型和 JOIN 进来的目标模型我都只要一条」

# findOne: User.HasOne(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM `user` `UserClass`
	LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `
LIMIT 1;

但如果你标记 hasMany,且用 findOne 查,是在说「源模型要一条,但要它关联的 N 条目标模型」。这时,全局给LIMIT 1就会把目标模型查询结果误杀到 1 个了,所以先 LIMIT 自身查询获得「源模型一条」,再 LEFT JOIN获得「它关联的 N 条目标模型」

# findOne: User.HasMany(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM (
	SELECT `UserClass`.`id`,
	FROM `user` `UserClass`
	LIMIT 1
) `UserClass`
	LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `

3.5 多对多关系(belongsToMany)

有时候,::「目标模型」和「源模型」互不唯一::。一个 Group(文件夹)可能拥有多个 User,一个 User 也可能拥有多个 Group,这就直接导致::「给任何一个模型添加 foreignKey 都不合理」。::

我们需要「中间模型」做红娘,维护「目标模型」和「源模型」的关系。中间模型有两个 foreignKey(一个被 otherKey 代替),对「目标模型」和「源模型」都是唯一的。

User.BelongsToMany(Group, { 
	through: GroupUser,		// 
	foreignKey: 'group_id',	// 如果不定义这个,也会自动定义为「目标模型名 + 目标模型主键名」即 user_id
	otherKey: 'user_id',
}

findOne 时,SQL 是这样的:先把 GroupUser INNER JOIN Group,再把结果LEFT JOIN到 User 上

# findOne(User): User.BelongsToMany(Group)
SELECT `UserClass`.`id`, `group`.`id` AS `group.id`
FROM (
	SELECT `UserClass`.`id`,
	FROM `user` `UserClass`
	LIMIT 1
) `UserClass`
	LEFT JOIN (`group_user` `group->GroupUser`
		INNER JOIN `group` ON `group`.`id` = `group->GroupUser`.`group_id`)
	ON `UserModel`.`id` = `group->GroupUser`.`user_id`;

3.6 几种 JOIN

  • left outer join(等价于 left join,因为默认是 outer):以左侧表行为准,合并并返回左侧出现的行;当没有右侧关联记录,返回行,右侧加进来的字段置空
  • right join:和 left 相反,以右侧表行为准
  • inner join:只有 join 时,左右都匹配到值才返回,相当于取交集
  • full join:两侧任意一个匹配到就返回,相当于取并集

在 include 中,如果配置了required: true,SQL 就会从LEFT JOIN变为INNER JOIN,剔除没有关联记录的行


4 数据库查询的优化

前面提到的东西仅仅做到「能用」,事实上,业务查询场景很可能比较复杂,如果随意写,DBA 会打上门的。

4.1 慢查询、全表扫描和索引

在数据库界,人们常常提到「慢查询」,指的是查询时长超过指定时长的查询。慢查询的危害在于不仅本次查询的请求时间变长,还会较长时间的占用系统资源,对其他查询造成影响或者干脆撑挂数据库。

而「慢查询」最常见的罪魁祸首就是「全表扫描」,指的是数据库引擎为了找到某条记录,对全表进行逐个搜索,直到搜索到这条记录。想象下,如果你有上亿条数据,而要查的数据碰巧比较靠后,这个得查到什么时候?(复杂度在 O(n)) 那怎么样才不「全表扫描」呢?

举个例子,当你用主键查一条记录的时候,就不会全表扫描。

File.findByPk(123); 

因为 MySQL 默认给主键列加了「索引」。

::「索引」厉害在哪?MySQL 为这一列建立了一个 btree::(不同数据库的实现是不一样的,但 btree 是主流)。这样查“id 为 318 的 Station”只需要从根节点沿着找下去,类似这个意思:

3xx --> 31x --> 318 

4.2 给其他列加索引

那么如果我查普通列呢?也可以通过索引提升查询效率。

File.findOne({ 
	where: { 
		name: 'station1' 
	} 
}) 

你也可以为这个列手动增加一个索引:

create index index_name on file(name); 

但这个索引的实现是和主键索引有区别的,它不直接查到数据记录,而是把 btree 建立在主键 id 上,现在查一个「name 为 station1 的记录」的过程变成类似这种:

开始 
--> name: sta... --> name: statio --> name: station1 
--> 拿到 station1 的 id: 816 
--> id: 8xx --> id: 81x --> id: 816 
--> 拿到 816 的数据 

如果嫌这个路径长,还有更近一步的,对于常查的列,比如 File 的 name 和 author,可以建立「覆盖索引」:

create index index_name_and_address on file(name, author); 

这时候如果我只根据 name 查 author:

File.findOne({ 
	where: { 
		name: 'station1' 
	}, 
	attributes: ['author'] 
}) 

因为索引里已经存了 address,就不需要再去访问源数据了:

开始 
--> name: sta... --> name: statio --> name: station1 
--> 拿到 station1 的 address: xxx 

索引越多越好吗?

然而索引并不是越多越好,索引虽然提升了查询的效率,缺牺牲了插入、删除的效率。想象下以前只要把新数据堆到表上就行,现在还要修改索引,更麻烦的是索引是个平衡树,很多场景需要对整个树进行调整。(主键为什么默认是自增的?我猜也是为了减少插入数据时树操作的成本)

所以我们一般考虑在常用来「where」或者「order」的列上加索引。

4.3 查询语句优化

前面说的给常用列增加索引可以提升查询效率,让查询尽量走「btree」而不是「全表扫描」。 但前提是别上来就 select *,而是要用 attributes 只摘取你要的列:

where: { 
	attributes: ['id', 'name'] 
} 

但并不是所有的查询都会走「btree」,不优秀的 sql 仍然会触发全表扫描,产生慢查询,应该尽量避免。

当你 where 一个列时,MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。

放到 Sequelize 里就是:

Sequelize.Op.gt|gte|lt|lte|eq|between|in ... 

比如,能用 in 尽量别用 not in

// 不好 
status: { 
	[Op.notIn]: [ 3, 4, 5, 6 ], 
}, 
// 好 
status: { 
	[Op.in]: [ 1, 2 ], 
}, 

具体的网上一搜「避免全表扫描」一大把,就不展开了。


5 总结

  • MySQL 通过 SQL 操作,ORM 基于业务编程语言进一步抽象操作,最终转为 SQL。Sequelize 是 node 上的一种 ORM
  • 介绍了 Sequelize 的模型建立、查询语法
  • 联表有三种关系,通过四种标记关联,讨论了这些关联中的概念、用法、原理、区别
  • 索引对数据库优化意义很大,同时也要在语句上避免不走索引