侧边栏壁纸
  • 累计撰写 247 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

内外连接查询的底层原理及如何使用索引进行性能优化

kaixindeken
2021-05-03 / 0 评论 / 1 点赞 / 190 阅读 / 5,472 字

连接查询的基本使用

MySQL 之所以被称之为关系型数据库,是因为数据表之间可以通过外键建立关联关系。实际项目开发中,数据库中的数据表很多都不是孤立存在的,比如在一个博客系统中:

  • 文章需要用户创建,因此文章表和用户表存在关联(多对一);
  • 用户可以给文章添加评论,文章表与评论表之间存在关联(一对多);
  • 一篇文章可以设置多个标签,而同一个标签可以分配给多篇文章,文章表与标签表之间存在关联(多对多)。

为了方便演示,我们这里创建一张用户表 users 和一张文章表 pages:

/*用户表*/
CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `email` varchar(191) NOT NULL,
  `password` varchar(60) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*文章表*/
CREATE TABLE `pages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `html` longtext NOT NULL,
  `markdown` longtext NOT NULL,
  `draft` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_by` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pages_created_by_index` (`created_by`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这里我们除了创建主键索引外,还分别为用户表的 email 列设置了唯一索引,为文章表的 created_by 列设置了一般索引,因为这两个字段在查询中会用到。

如果你想要在一次查询中同时获取文章及对应作者信息,就需要用到连接查询:

SELECT * FROM `pages` JOIN `users` ON users.id = pages.created_by WHERE pages.id = 9;

当然在实际项目编写 SQL 语句时,建议指定要查询的字段,降低磁盘 I/O,提升查询效率,我们还可以为数据表和查询字段设置别名:

SELECT p.id, p.name as title, p.created_at, u.id as uid, u.name as author 
FROM `pages` as p 
JOIN `users` as u ON u.id = p.created_by 
WHERE p.id = 9;

连接查询可以同时返回被连接的两张表的数据记录。

不同连接类型的区别

连接查询可以细分为内连接和外连接,外连接可以进一步细分为左(外)连接和右(外)连接,上面的示例对应的是内连接,省略了 INNER 关键字而已:

SELECT p.id, p.name as title, p.created_at, u.id as uid, u.name as author 
FROM `pages` as p 
INNER JOIN `users` as u ON u.id = p.created_by 
WHERE p.id = 9;

内连接与外连接的主要区别是:

  • 对于内连接而言,驱动表(JOIN 关键字前面的表,这里是 pages)按照查询条件匹配的结果集在被驱动表(JOIN 关键字后面的表,这里是 users)找不到对应的匹配记录,则对应的记录不会出现在最终结果集中;
  • 对于外连接而言,则没有这个约束,对于左连接而言,只要驱动表(LEFT JOIN 前面的表)按照查询条件匹配到结果,则对应记录就会出现在最终结果集中,如果被驱动表(LEFT JOIN 后面的表,可能有多个)匹配记录为空,则会以 NULL 填充;
  • 右连接与左连接相反,RIGHT JOIN 之后的表是驱动表,之前的表是被驱动表,不管被驱动表是否匹配到记录,只要驱动表匹配到记录,就会出现在最终结果集中,被驱动表为空的话也以 NULL 填充。

比如我们将上面的演示示例 SQL 改为左连接和右连接查询如下:

/*左连接(这个业务用左连接查询更合适)*/
SELECT p.id, p.name as title, p.created_at, u.id as uid, u.name as author 
FROM `pages` as p 
LEFT JOIN `users` as u ON u.id = p.created_by 
WHERE p.id = 9;

/*右连接(对应的业务逻辑变成了查询用户及其关联的所有文章)*/
SELECT p.id, p.name as title, p.created_at, u.id as uid, u.name as author 
FROM `pages` as p 
RIGHT JOIN `users` as u ON p.created_by = u.id
WHERE u.id = 1;

日常开发中,使用左连接的场景最多,即使是右连接,也会转化为左连接来实现,因为左连接更符合人类的思维模式,尤其是在 ORM 模式中,模型类之间的关联关系实现。

连接查询的底层原理

说到这里,我们有必要了解下存储引擎底层(InnoDB)连接查询是如何运作的,然后才知道如何基于索引对其进行优化。

不管是内连接还是外连接,都是先根据驱动表上设置的查询条件到驱动表匹配记录,然后再根据从驱动表匹配的记录逐个去被驱动表根据被驱动表设置的查询条件进行匹配,然后将匹配记录放入结果集,直到最后一条驱动表匹配记录在被驱动表完成匹配,将最终结果集返回给客户端。这种查询有一个形象的名字 —— 嵌套循环连接(Nested-Loop Join)。

所以,以两个连接表为例,整个连接查询过程涉及一次驱动表查询,和若干次被驱动表查询,被驱动表查询次数取决于驱动表匹配结果的行数,我们以上的左连接 SQL 查询为例:

SELECT p.id, p.name as title, p.created_at, u.id as uid, u.name as author 
FROM `pages` as p 
LEFT JOIN `users` as u ON u.id = p.created_by 
WHERE p.id = 9;

MySQL 底层首先根据 p.id = 9 整个查询条件去驱动表 pages 匹配符合条件的记录,这里由于 id 是主键索引,所以只会返回一条记录,然后根据返回的记录去被驱动表 users 进行匹配,对应的查询条件是 u.id = p.created_by,p.created_by 现在已经是一个确定值,即驱动表匹配结果中的 created_by 字段值,u.id 又是 users 表的主键索引,所以最终返回一条记录,总的数据库查询次数是 2。

上面是多对一关联,如果是一对多,则被驱动表会返回多条记录:

SELECT u.id as uid, u.name as author, p.id, p.name as title, p.created_at 
FROM `users` as u 
LEFT JOIN `pages` as p ON p.created_by = u.id
WHERE u.id = 1;

这个时候整体流程和上面的左连接查询一样,只是 p.created_by = 1 会匹配多条记录,但是总的查询次数仍然是 2,因为驱动表 users 只返回了一条记录,但是下面这条 SQL 查询则不然:

SELECT u.id as uid, u.name as author, p.id, p.name as title, p.created_at 
FROM `users` as u 
LEFT JOIN `pages` as p ON p.created_by = u.id
WHERE u.id IN (1, 10, 100);

由于 u.id IN (1, 10, 100) 在驱动表 users 匹配了 3 条记录,所以需要去被驱动表 pages 进行三次查询,根据 p.created_by = u.id 匹配记录,最后返回最终结果集,当然,你也可以在在被驱动表上设置多个查询条件进行进一步过滤:

SELECT u.id as uid, u.name as author, p.id, p.name as title, p.created_at 
FROM `users` as u 
LEFT JOIN `pages` as p ON p.created_by = u.id AND p.draft <> 0 
WHERE u.id IN (1, 10, 100);

这个时候,总的数据库查询次数就变成了 1 + 3 = 4 次了。

如果连接了第三张表、第四张表...原理还是这样,只是要查询的次数更多了,相应的查询性能也会降低,尤其是越后面的数据表,如果查询没有命中索引,进行多次全表扫描,性能可想而知有多么糟糕。

网上有一个连接查询底层执行流程的图片,可供参考:

1.jpeg

连接查询的索引优化

有了以上的知识储备,想必你对如何优化连接查询已经胸有成竹了:只要让每次数据库查询能命中索引,避免出现全表扫描即可。比如我们上面的示例中,对 created_by 这一连接条件字段设置了索引,如果这个字段没有设置索引,而连接查询条件 ON 子句中又没有其他索引命中,那么这个连接查询的性能在实际项目中将比单表查询性能成倍的降低,因为如果驱动表匹配了多条记录,将会到被驱动表进行多次数据库查询。

我们可以对连接查询运行 explain 语句查看执行计划:

1.jpeg

注:对于连接查询而言,虽然存在针对多张表的多次查询,但是只有一个 SELECT 关键字,因此虽然有两个执行计划,但是 id 值都是 1,我们可以根据执行计划的先后顺序判断那张表是驱动表,哪张表是被驱动表。

可以看到,用到了两个索引,第一个是执行驱动表等值匹配时用到的,因为是主键索引,所以类型是 const,即常量级别,第二个是去被驱动表进行匹配时用到的,由于是一个二级索引,所以对应类型是 ref。

如果连接查询没有命中索引,则会进行全表扫描:

1.jpeg

这是我们不希望看到的。这里还可以看到 explain 执行计划的 Extra 字段中包含了 Using join buffer,这个是什么意思呢?

原来,这是 MySQL 底层为了提升连接查询性能做的优化。为了避免连接查询要去被驱动表做多次查询,MySQL 提供了 join buffer 的概念,join buffer 是在执行连接查询前申请的一块固定大小的内存,MySQL 会把驱动表匹配到的结果集临时存放到这个 join buffer 中,然后去被驱动表进行匹配,每一条被驱动表的记录会一次性和 join buffer 中的多条驱动表记录做匹配,这个匹配的过程是在内存中完成的,所以可以显著减少被驱动表的磁盘 IO,进而提升连接查询的性能。

1

评论区