?1. 關(guān)聯(lián)查詢案例介紹

我們現(xiàn)在有一個(gè)驅(qū)動(dòng)表customer，它存儲(chǔ)客戶id、姓名以及出生日期，默認(rèn)情況下id是主鍵，沒有任何索引，對(duì)此我們給出DDL語(yǔ)句：

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有一張關(guān)聯(lián)表，c_id記錄著與其關(guān)聯(lián)數(shù)據(jù)的id，并用available_balance記錄客戶余額，對(duì)應(yīng)DDL如下，可以看到此時(shí)我們沒有添加任何索引：

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;

假設(shè)此時(shí)數(shù)據(jù)庫(kù)大約有2000w的數(shù)據(jù)，我們希望查出姓名為if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t的用戶的出生日期和可用余額，如果沒有記錄余額則設(shè)置為null，對(duì)應(yīng)我們給出這樣一條SQL：

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

最終查詢結(jié)果如下，耗時(shí)大約是1s多一些，對(duì)于用戶而言超過200ms的延遲都是有感知的，所以針對(duì)這個(gè)查詢我們需要進(jìn)行相應(yīng)的優(yōu)化，對(duì)此筆者以市面上常見的面經(jīng)為出發(fā)點(diǎn)，逐步拆解并解決這道問題：

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

2. 講講join的原理

join底層關(guān)聯(lián)本質(zhì)上都是基于驅(qū)動(dòng)表(上面的c表)的結(jié)果到被驅(qū)動(dòng)表(上面的cb表)進(jìn)行循環(huán)掃描定位，這里筆者以MySQL5.7、MySQL 8兩個(gè)版本對(duì)join連接的幾種類型進(jìn)行介紹：

(1) Simple Nested-Loop Join：這也就是我們上文中兩張關(guān)聯(lián)表沒有加索引關(guān)聯(lián)查詢，得到所有驅(qū)動(dòng)表c的數(shù)據(jù)后，直接給cb表走全表掃描定位匹配，極端情況下要查詢count(c)*count(cb)次，也就是我們傳說中的時(shí)間復(fù)雜度為O(n^2):

(2) Index Nested-Loop Join：這就是join左右字段都加索引后的查詢，這意味著驅(qū)動(dòng)表的選擇不在于我們自身，而是由MySQL優(yōu)化器決定，當(dāng)驅(qū)動(dòng)表的結(jié)果交給被驅(qū)動(dòng)表時(shí)，被驅(qū)動(dòng)表直接通過索引定位到關(guān)聯(lián)數(shù)據(jù)并阻塞。

(3) Block Nested-Loop Join：沒有索引列的情況都會(huì)選擇該算法而不優(yōu)先考慮Simple Nested-Loop Join，Block Nested-Loop Join相比Simple Nested-Loop Join多了一個(gè)中間操作，它會(huì)將驅(qū)動(dòng)表查詢結(jié)果緩存到j(luò)oin buffer，與被驅(qū)動(dòng)表關(guān)聯(lián)時(shí)會(huì)進(jìn)行批量?jī)?nèi)存關(guān)聯(lián)與合并。

(4) HashJoin：這是8.0.18及其之后的版本對(duì)于關(guān)聯(lián)查詢的優(yōu)化，其原理是針對(duì)驅(qū)動(dòng)表join字段進(jìn)行哈希運(yùn)算生成結(jié)果集存入內(nèi)存中，然后掃描被驅(qū)動(dòng)表并直接通過哈希運(yùn)算定位到驅(qū)動(dòng)表是否存在關(guān)聯(lián)的值已完成結(jié)果合并。當(dāng)然如果驅(qū)動(dòng)表數(shù)據(jù)量大的話，驅(qū)動(dòng)表部分?jǐn)?shù)據(jù)還會(huì)利用磁盤進(jìn)行分片，生成臨時(shí)文件，然后被驅(qū)動(dòng)表同樣是通過哈希運(yùn)算定位到磁盤分片編號(hào)進(jìn)行物理磁盤IO獲取關(guān)聯(lián)結(jié)果。

3. 能不能說說這個(gè)LEFT JOIN如何加索引

上文提到查詢耗時(shí)為1s多，針對(duì)索引添加我們優(yōu)先使用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為例該查詢首先查詢驅(qū)動(dòng)表c，它會(huì)基于where條件進(jìn)行全表掃描獲取數(shù)據(jù)，基于查詢結(jié)果緩存到hash join buffer再到關(guān)聯(lián)表即被驅(qū)動(dòng)表的聚簇索引進(jìn)行全表掃描匹配結(jié)果：

這一點(diǎn)我們也可以從執(zhí)行計(jì)劃看出，c表和cb表都走了全表掃描，且關(guān)聯(lián)查詢時(shí)被驅(qū)動(dòng)表cb用到MySQL 8的hash join關(guān)聯(lián)，這種關(guān)聯(lián)方式本質(zhì)上就說

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)|

針對(duì)該執(zhí)行計(jì)劃，我們進(jìn)行逐步的調(diào)優(yōu)，針對(duì)驅(qū)動(dòng)表c的查詢，因?yàn)橛玫搅薾ame字段，所以針對(duì)name添加一個(gè)索引：

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

經(jīng)過調(diào)整之后，查詢耗時(shí)提升為0.739s，查看執(zhí)行計(jì)劃，可以看到針對(duì)驅(qū)動(dòng)表的慢查詢已經(jīng)走索引了，現(xiàn)在問題就是出在被驅(qū)動(dòng)表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)|

所以我們針對(duì)被驅(qū)動(dòng)表cb的c_id增加一個(gè)索引:

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

最終查詢耗時(shí)優(yōu)化為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 左右字段是否都需要加索引？為什么？

回答這個(gè)問題，我們首先需要了解左外連接的工作機(jī)制，它本質(zhì)上就是基于驅(qū)動(dòng)表(也就是上文的c表)的id與被驅(qū)動(dòng)表cb進(jìn)行鏈接，如果cb沒有數(shù)據(jù)則結(jié)果顯示null：

這也就意味著left join左邊的字段是基于where條件的查詢結(jié)果篩選出來的數(shù)據(jù)，然后遍歷并與被驅(qū)動(dòng)表cb進(jìn)行關(guān)聯(lián)，所以如果left join左邊（也就是我們驅(qū)動(dòng)表c的id）如果不作為查詢條件的情況下，可以不加索引，當(dāng)然我們本次關(guān)聯(lián)的id本身就是主鍵，所以這個(gè)問題就沒有討論的必要了。

對(duì)于left join的右邊，它是作為被驅(qū)動(dòng)表(也就是我們的cb表)的關(guān)聯(lián)查詢條件，從執(zhí)行計(jì)劃就可以看出如果沒添加索引，它會(huì)基于驅(qū)動(dòng)表c給的關(guān)聯(lián)條件id進(jìn)行全表掃描以找到符合條件的數(shù)據(jù)，所以為了提升被驅(qū)動(dòng)表cb的檢索速度，關(guān)聯(lián)條件c_id是需要增加索引的。

5. 你覺得針對(duì)聯(lián)表查詢還有那些優(yōu)化技巧

除了上述優(yōu)化技巧，針對(duì)關(guān)聯(lián)查詢我們可以從表結(jié)構(gòu)設(shè)計(jì)以及SQL查詢層面考慮優(yōu)化：

• 如果業(yè)務(wù)上允許的話，可以考慮將關(guān)聯(lián)的字段冗余一份到驅(qū)動(dòng)表上，直接避免關(guān)聯(lián)查詢開銷。
• 如果驅(qū)動(dòng)表和被驅(qū)動(dòng)都具備篩選能力(即關(guān)聯(lián)的表都可以通過where查詢到需要的數(shù)據(jù))，可以考慮用數(shù)據(jù)量小的表作為驅(qū)動(dòng)表，采用小表驅(qū)大表的方式完成關(guān)聯(lián)查詢。
• 非必要不采取left join或者right join，盡可能在關(guān)聯(lián)條件上加索引，然后通過inner join讓MySQL優(yōu)化器幫我們選擇驅(qū)動(dòng)表并完成數(shù)據(jù)檢索。

轉(zhuǎn)自https://juejin.cn/post/7459769651342622771