偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

MySQL LEFT JOIN 性能優(yōu)化策略

數(shù)據(jù)庫
隨著數(shù)據(jù)量的不斷增長以及業(yè)務(wù)邏輯的日益復(fù)雜,LEFT JOIN 的性能問題逐漸凸顯,本文深入探討了一系列針對 LEFT JOIN 的性能優(yōu)化策略。

連接查詢算是日常比較常用的數(shù)據(jù)庫關(guān)聯(lián)關(guān)鍵字涉及左外連接、右外連接、內(nèi)連接三種連接方式,本文將從MySQL 8.0的角度針對連接查詢和優(yōu)化進行深入解析,希望對你有幫助。

一、詳解MySQL left join

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

我們現(xiàn)在有一個驅(qū)動表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有一張關(guān)聯(lián)表,c_id記錄著與其關(guān)聯(lián)數(shù)據(jù)的id,并用available_balance記錄客戶余額,對應(yīng)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;

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

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

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

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

2. 講講join的原理

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

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

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

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

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

3. 能不能說說這個LEFT JOIN如何加索引

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

這一點我們也可以從執(zhí)行計劃看出,c表和cb表都走了全表掃描,且關(guān)聯(lián)查詢時被驅(qū)動表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)|

針對該執(zhí)行計劃,我們進行逐步的調(diào)優(yōu),針對驅(qū)動表c的查詢,因為用到了name字段,所以針對name添加一個索引:

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

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

所以我們針對被驅(qū)動表cb的c_id增加一個索引:

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

最終查詢耗時優(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 左右字段是否都需要加索引?為什么?

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

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

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

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

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

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

二、小結(jié)

在數(shù)據(jù)庫操作領(lǐng)域,MySQL 的 LEFT JOIN 無疑是一項極為重要的功能,它為我們提供了從多個表中獲取關(guān)聯(lián)數(shù)據(jù)的強大能力。然而,隨著數(shù)據(jù)量的不斷增長以及業(yè)務(wù)邏輯的日益復(fù)雜,LEFT JOIN 的性能問題逐漸凸顯,成為開發(fā)者和數(shù)據(jù)庫管理員需要重點關(guān)注的方面。

本文深入探討了一系列針對 LEFT JOIN 的性能優(yōu)化策略。

首先,我們詳細分析了合理設(shè)計表結(jié)構(gòu)對性能的巨大影響。通過確保表的主鍵、外鍵以及索引的正確設(shè)置,可以顯著減少數(shù)據(jù)庫在執(zhí)行 LEFT JOIN 操作時的搜索范圍,提高查詢效率。例如,為頻繁用于連接條件的列創(chuàng)建合適的索引,能夠讓數(shù)據(jù)庫快速定位到相關(guān)數(shù)據(jù),避免全表掃描帶來的性能損耗。 索引優(yōu)化方面,我們了解到復(fù)合索引的巧妙運用以及避免索引失效的重要性。

復(fù)合索引可以在多個列上創(chuàng)建單一索引結(jié)構(gòu),從而在多條件查詢時發(fā)揮重要作用。同時,要注意查詢語句的書寫方式,避免因不當(dāng)?shù)牟僮鞣蚝瘮?shù)使用導(dǎo)致索引失效,確保索引能夠在 LEFT JOIN 操作中充分發(fā)揮作用。 查詢語句的優(yōu)化也是關(guān)鍵環(huán)節(jié)。我們學(xué)會了通過簡化查詢邏輯、合理利用子查詢以及使用 STRAIGHT_JOIN 等方式來引導(dǎo)數(shù)據(jù)庫優(yōu)化器生成更高效的執(zhí)行計劃。這些優(yōu)化手段能夠幫助數(shù)據(jù)庫更好地理解我們的查詢意圖,合理分配資源,從而提升 LEFT JOIN 的執(zhí)行速度。

此外,數(shù)據(jù)庫的配置參數(shù)對 LEFT JOIN 性能也有著不可忽視的影響。通過調(diào)整諸如內(nèi)存分配、緩存大小等參數(shù),可以為數(shù)據(jù)庫的運行提供更有利的環(huán)境,進一步提升 LEFT JOIN 的執(zhí)行效率。

在實際應(yīng)用中,我們應(yīng)當(dāng)根據(jù)具體的業(yè)務(wù)場景和數(shù)據(jù)特點,綜合運用這些優(yōu)化策略。同時,持續(xù)進行性能測試和監(jiān)控,及時發(fā)現(xiàn)并解決性能瓶頸問題。只有這樣,我們才能在充分利用 LEFT JOIN 強大功能的同時,確保數(shù)據(jù)庫系統(tǒng)的高效穩(wěn)定運行,為業(yè)務(wù)的發(fā)展提供堅實的數(shù)據(jù)支持。希望本文所介紹的優(yōu)化策略能夠幫助讀者在處理 MySQL LEFT JOIN 相關(guān)問題時更加得心應(yīng)手,提升數(shù)據(jù)庫應(yīng)用的整體性能和質(zhì)量。

責(zé)任編輯:趙寧寧 來源: 寫代碼的SharkChili
相關(guān)推薦

2010-05-21 17:30:28

2023-05-10 10:30:02

性能優(yōu)化Tomcat

2009-09-16 17:07:00

linq實現(xiàn)Left

2009-09-08 09:45:23

App Engine性

2010-05-21 14:36:00

MySQL left

2010-05-18 14:14:03

MySQL關(guān)聯(lián)left

2021-07-16 23:01:03

SQL索引性能

2021-07-26 18:23:23

SQL策略優(yōu)化

2016-11-17 09:00:46

HBase優(yōu)化策略

2017-03-01 20:53:56

HBase實踐

2024-03-14 10:10:03

MySQL優(yōu)化事務(wù)

2020-10-19 19:45:58

MySQL數(shù)據(jù)庫優(yōu)化

2010-03-02 09:53:14

MySQL性能優(yōu)化

2009-04-20 08:51:50

MySQL查詢優(yōu)化數(shù)據(jù)庫

2020-03-23 15:15:57

MySQL性能優(yōu)化數(shù)據(jù)庫

2010-05-05 11:48:27

Oracle設(shè)計開發(fā)階

2017-08-14 09:05:50

SIOC存儲負載

2024-03-01 12:19:00

接口性能優(yōu)化

2024-09-04 14:28:20

Python代碼

2018-06-27 08:21:31

前端Web渲染
點贊
收藏

51CTO技術(shù)棧公眾號