​1. 关联查询案例介绍

我们现在有一个驱动表customer，它存储客户id、姓名以及出生日期，默认情况下id是主键，没有任何索引，对此我们给出DDL语句：

CREATE TABLE `customer` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

customer有一张关联表，c_id记录着与其关联数据的id，并用available_balance记录客户余额，对应DDL如下，可以看到此时我们没有添加任何索引：

CREATE TABLE `customer_balances` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `c_id` bigint NOT NULL,
  `available_balance` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1863126107830751234 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

假设此时数据库大约有2000w的数据，我们希望查出姓名为if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t的用户的出生日期和可用余额，如果没有记录余额则设置为null，对应我们给出这样一条SQL：

SELECT name,birthday from customer c 
left join customer_balances cb on c.id =cb.c_id
 WHERE name='if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t';

最终查询结果如下，耗时大约是1s多一些，对于用户而言超过200ms的延迟都是有感知的，所以针对这个查询我们需要进行相应的优化，对此笔者以市面上常见的面经为出发点，逐步拆解并解决这道问题：

name                                              |birthday           |available_balance|
--------------------------------------------------+-------------------+-----------------+
if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t|2024-12-01 11:02:35|         25853253|

2. 讲讲join的原理

join底层关联本质上都是基于驱动表(上面的c表)的结果到被驱动表(上面的cb表)进行循环扫描定位，这里笔者以MySQL5.7、MySQL 8两个版本对join连接的几种类型进行介绍：

(1) Simple Nested-Loop Join：这也就是我们上文中两张关联表没有加索引关联查询，得到所有驱动表c的数据后，直接给cb表走全表扫描定位匹配，极端情况下要查询count(c)*count(cb)次，也就是我们传说中的时间复杂度为O(n^2):

(2) Index Nested-Loop Join：这就是join左右字段都加索引后的查询，这意味着驱动表的选择不在于我们自身，而是由MySQL优化器决定，当驱动表的结果交给被驱动表时，被驱动表直接通过索引定位到关联数据并阻塞。

(3) Block Nested-Loop Join：没有索引列的情况都会选择该算法而不优先考虑Simple Nested-Loop Join，Block Nested-Loop Join相比Simple Nested-Loop Join多了一个中间操作，它会将驱动表查询结果缓存到join buffer，与被驱动表关联时会进行批量内存关联与合并。

(4) HashJoin：这是8.0.18及其之后的版本对于关联查询的优化，其原理是针对驱动表join字段进行哈希运算生成结果集存入内存中，然后扫描被驱动表并直接通过哈希运算定位到驱动表是否存在关联的值已完成结果合并。当然如果驱动表数据量大的话，驱动表部分数据还会利用磁盘进行分片，生成临时文件，然后被驱动表同样是通过哈希运算定位到磁盘分片编号进行物理磁盘IO获取关联结果。

3. 能不能说说这个LEFT JOIN如何加索引

上文提到查询耗时为1s多，针对索引添加我们优先使用explain 来分析一下SQL的查询过程：

explain SELECT c.name,c.birthday,cb.available_balance 
from customer c 
left join customer_balances cb on c.id =cb.c_id 
WHERE name='if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t';

以我们的SQL为例该查询首先查询驱动表c，它会基于where条件进行全表扫描获取数据，基于查询结果缓存到hash join buffer再到关联表即被驱动表的聚簇索引进行全表扫描匹配结果：

这一点我们也可以从执行计划看出，c表和cb表都走了全表扫描，且关联查询时被驱动表cb用到MySQL 8的hash join关联，这种关联方式本质上就说

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra                                     |
--+-----------+-----+----------+----+-------------+---+-------+---+-------+--------+------------------------------------------+
 1|SIMPLE     |c    |          |ALL |             |   |       |   |3079319|    10.0|Using where                               |
 1|SIMPLE     |cb   |          |ALL |             |   |       |   |3447555|   100.0|Using where; Using join buffer (hash join)|

针对该执行计划，我们进行逐步的调优，针对驱动表c的查询，因为用到了name字段，所以针对name添加一个索引：

ALTER TABLE db.customer DROP INDEX customer_name_IDX;
CREATE INDEX customer_name_IDX USING BTREE ON db.customer (name);

经过调整之后，查询耗时提升为0.739s，查看执行计划，可以看到针对驱动表的慢查询已经走索引了，现在问题就是出在被驱动表cb还是走全表扫描：

id|select_type|table|partitions|type|possible_keys    |key              |key_len|ref  |rows   |filtered|Extra                                     |
--+-----------+-----+----------+----+-----------------+-----------------+-------+-----+-------+--------+------------------------------------------+
 1|SIMPLE     |c    |          |ref |customer_name_IDX|customer_name_IDX|403    |const|      1|   100.0|                                          |
 1|SIMPLE     |cb   |          |ALL |                 |                 |       |     |4566577|   100.0|Using where; Using join buffer (hash join)|

所以我们针对被驱动表cb的c_id增加一个索引:

CREATE INDEX customer_balances_c_id_IDX USING BTREE ON db.customer_balances (c_id);

最终查询耗时优化为0.001s，

id|select_type|table|partitions|type|possible_keys             |key                       |key_len|ref    |rows|filtered|Extra|
--+-----------+-----+----------+----+--------------------------+--------------------------+-------+-------+----+--------+-----+
 1|SIMPLE     |c    |          |ref |customer_name_IDX         |customer_name_IDX         |403    |const  |   1|   100.0|     |
 1|SIMPLE     |cb   |          |ref |customer_balances_c_id_IDX|customer_balances_c_id_IDX|8      |db.c.id|   1|   100.0|     |

4. left  join on 左右字段是否都需要加索引？为什么？

回答这个问题，我们首先需要了解左外连接的工作机制，它本质上就是基于驱动表(也就是上文的c表)的id与被驱动表cb进行链接，如果cb没有数据则结果显示null：

这也就意味着left join左边的字段是基于where条件的查询结果筛选出来的数据，然后遍历并与被驱动表cb进行关联，所以如果left join左边（也就是我们驱动表c的id）如果不作为查询条件的情况下，可以不加索引，当然我们本次关联的id本身就是主键，所以这个问题就没有讨论的必要了。

对于left join的右边，它是作为被驱动表(也就是我们的cb表)的关联查询条件，从执行计划就可以看出如果没添加索引，它会基于驱动表c给的关联条件id进行全表扫描以找到符合条件的数据，所以为了提升被驱动表cb的检索速度，关联条件c_id是需要增加索引的。

5. 你觉得针对联表查询还有那些优化技巧

除了上述优化技巧，针对关联查询我们可以从表结构设计以及SQL查询层面考虑优化：

• 如果业务上允许的话，可以考虑将关联的字段冗余一份到驱动表上，直接避免关联查询开销。
• 如果驱动表和被驱动都具备筛选能力(即关联的表都可以通过where查询到需要的数据)，可以考虑用数据量小的表作为驱动表，采用小表驱大表的方式完成关联查询。
• 非必要不采取left join或者right join，尽可能在关联条件上加索引，然后通过inner join让MySQL优化器帮我们选择驱动表并完成数据检索。

转自https://juejin.cn/post/7459769651342622771