前面我们了解了些单表SQL的索引设计及调优技巧。但在日常开发中,除了单表的SQL外,还有更为复杂的多表join查询和子查询语句。这就需要在多表中创建索引,难度也提升了不少。很多开发人员下意识地认为 JOIN 会降低 SQL 的性能效率,所以就将一条多表 SQL 拆成单表的一条条查询,但这样反而会影响 SQL 执行的效率。究其原因,在于开发人员不了解 JOIN 的实现过程。
下面,我们就来一起看一下join的工作原理以及实现算法和应用场景,从而可以开心的使用join语句来连接查询数据。
一、join连接算法
MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联:
-
Nested Loop Join;
-
Hash Join。
通常认为,在 OLTP 业务中,因为查询数据量较小、语句相对简单,大多使用索引连接表之间的数据。这种情况下,优化器大多会用 Nested Loop Join 算法;而 OLAP 业务中的查询数据量较大,关联表的数量非常多,所以用 Hash Join 算法,直接扫描全表效率会更高。
注意,这里仅讨论最新的 MySQL 8.0 版本中 JOIN 连接的算法,同时也推荐在生产环境时优先用 MySQL 8.0。
接下来我们来看一下这两个算法:
1、Nested Loop Join
Nested Loop Join 之间的表关联是使用索引进行匹配的,假设表 R 和 S 进行连接,其算法伪代码大致如下:
for each row r in R with matching condition:
lookup index idx_s on S where index_key = r
if