色哟哟视频在线观看-色哟哟视频在线-色哟哟欧美15最新在线-色哟哟免费在线观看-国产l精品国产亚洲区在线观看-国产l精品国产亚洲区久久

0
  • 聊天消息
  • 系統消息
  • 評論與回復
登錄后你可以
  • 下載海量資料
  • 學習在線課程
  • 觀看技術視頻
  • 寫文章/發帖/加入社區
會員中心
創作中心

完善資料讓更多小伙伴認識你,還能領取20積分哦,立即完善>

3天內不再提示

MySQL join的語義學習

數據分析與開發 ? 來源:博客 ? 作者:Spongecaptain's ? 2021-10-22 16:41 ? 次閱讀

1. 數學基礎:笛卡爾乘積

笛卡爾乘積是一個數學概念:

笛卡爾乘積是指在數學中,兩個集合 X 和 Y 的笛卡爾積(Cartesian product),又稱直積,表示為 X × Y,第一個對象是 X 的成員而第二個對象是 Y 的所有可能有序對的其中一個成員。公式表示就是如下:

1X×Y = {(x,y)|x∈X,y∈Y}

案例:

1 2 3X = {1,2} Y = {a,b,c} X×Y = {(1,a),(1,b),(1,c),(2,a),(2,b),(2,c)}

如果對同一個數據庫的兩張表進行 join 操作,例如表 A 記錄 c~1,1~、c~1,2~、c~1,3~,表 B 有 c~2,1~ 以及 c~2,2~ 字段。

那么笛卡爾乘積的結果是:

c~1,1~+c~2,1~、c~1,1~+c~2,2~、c~1,2~+c~2,1~、c~1,2~+c~2,2~、c~1,3~+c~2,1~、c~1,3~+c~2,2~ 共 6 條記錄。其中 + 的含義是兩條記錄并做一條記錄。

2. join 的作用是什么?

join 是關系型數據庫在關系二字上的集中體現,其作用在于將兩張及以上表根據列中字段間的相關關系,將多表中的行融合在一起。

3. 不同的 join 類型的語義

join 類型語義

cross joinCross 即交叉,代表笛卡爾乘積中符號 ×,其也就是兩表的笛卡爾乘積結果

inner join語義上等效為從笛卡爾乘積中選出符合條件的交集記錄

left join語義上等效為從笛卡爾乘積中選出符合條件的交集記錄+左表剩余的所有記錄(把左表記錄作為基礎,依次添加右表字段,如果符合 ON 記錄,那么賦值為右表字段值,否則賦值為 NULL)

right join語義上等效為從笛卡爾乘積中選出符合條件的交集記錄+右表剩余的所有記錄

full joinMySQL 并不支持 full join,不過可以等效為相同條件的 left join 與 right 的 union

full join 補充說明,在 MySQL 中如下語句是一個典型的 Full join:

1 2 3select * from t1 left join t2 on t2.name = t1.name union select * from t1 right join t2 on t2.name = t1.name;

也可以用集合的語言來表示,如下圖所示:

9b396e4a-322f-11ec-82a8-dac502259ad0.png

在 SQL 實際上又把 inner join 稱為內連接,其余所有 join 類型都稱為外連接。因此 join 有等效別名關鍵字:

inner join:join

顯示(explicit) inner join 與隱式(implicit) inner join 性能上沒有區別。

left join:left outer join

right join:right outer join

cross join:cross outer join

full join:full outer join

LEFT JOIN 和 RIGHT JOIN沒什么差別,兩者的結果差異取決于左右表的放置順序。

4. 典型帶有 join 的 SQL 語法分析

典型帶有 join 的 SQL 語句如下所示:

1 2 3 4 5SELECT 《row_list》 FROM 《left_table》 《inner|left|right》 JOIN 《right_table》 ON 《join condition》 WHERE 《where_condition》

我們按照 SQL 語句的執行順序來對上述 SQL 語句進行說明:

注意事項:下面的說法僅僅從 MySQL 執行語義上進行說明,實際上 MySQL 在內存中不會建立 vt1、vt2、vt3 表。

FROM:MySQL 中 FROM 子句總是第一個被執行的,FROM 的作用是對 join 涉及的多個表進行笛卡爾乘積 vt1 表,結果有 m*n 行(m 為左表的行數,n 為右表的行數);

ON:新建一張 vt2 表,并根據 ON 的條件篩選 vt1 表,符合條件的行加入到 vt2 中;

ON 只有對 Cross join 不是必須的。

JOIN:如果是 left join 或者 right join,那么就需要添加外部行,如果是 inner join 就不需要添加外部行。添加外部行以 left join 為例,首先遍歷左表的每一行,其中不在 vt2 中的行會被添加到 vt2 中,不屬于左表的字段會被置為 NULL,最終形成 vt3;

WHERE:對 vt3 表按照條件進行過濾,滿足條件的行被輸出到 vt4;

SELECT:從 vt4 中取出指定的字段到 vt5;

ON 與 WHERE 的區別是什么?

ON 與 WHERE 在使用 inner join 時,無論是在結果上還是在性能上都沒有區別。

從結果上看,inner join 中無論條件寫在 ON 還是 WHERE 后,結果相同。在使用 left/left join 時,結果有區別。例如,在 left join 中對 ON 后不符合條件的左表中的行還是會被納入到結果中,但是卻可以被 WHERE 后的條件過濾掉。

從效率的角度上看,雖然很多中文資源認為有所區別,但實際上沒區別,可以參考:SQL JOIN - WHERE clause vs. ON clause,查詢優化器會避免寫法的不同導致執行效率的不同。

5. join 性能優化

5.1 join 可以跨庫嗎?

MySQL 可以利用 FEDERATED 引擎等方式實現跨庫 join,但查詢效率實際上并不高。通常認為 MySQL join 操作指的同數據庫的多表 join。

5.2 join 內部執行過程與索引

在單表查詢中,我們通常會強調兩點:

WHERE 后的字段是否可以走索引,如果不行,那么將直接走簇集索引,進行全表掃描,效率很差;

SELECT 后的字段是否可以走覆蓋索引,如果不行,那么則需要回表到簇集索引;

但在 join 多表問題中,索引不僅僅需要考慮上述兩個問題。

MySQL 中的 join 操作并不會在內存中構造臨時表,第四節中的說法只是方便從語義上進行理解。join 具體如何執行取決于查詢優化器的選擇。

MySQL 支持如下三種 join 操作(以兩張表 join 為例):

nested loop join:利用嵌套 for 循環對兩張表中的每一行數據進行兩兩比較。需要遍歷第一張表 n 行,每一行都需要進行時間復雜度為 O(n) 的非索引查詢,因此總的比較的時間復雜度為 O(n^2^)

block nested loop join:對 nested loop join 的優化,利用對第一張表的行進行查詢緩存,這樣內層 for 循環中第二張表的每一條行數據一次性與第一張表的多條行數據進行比較,減少了對內表的比較次數。需要遍歷第一張表 n 行,每 k 行都需要進行時間復雜度為 O(n) 的非索引查詢,因此總的比較的時間復雜度為 O(n^2^/k),k 為常數。

index nested loop join:從第一張表讀一行,然后在第二張表的索引中查找這個數據,索引是 B+ 樹索引。需要遍歷第一張表 n 行,每一行都需要進行時間復雜度為 O(logn) 的非索引查詢,因此總的比較的時間復雜度為 O(nlogn)。

batched key access join:其也是利用對外循環表的字段進行緩存,減少對內循環表的訪問次數。比較次數得到一定減少,但是比較的時間復雜度還是為 O(nlogn/k),k 為常數。

可見,join 操作的性能非常取決于第二張表是否基于索引進行查詢。不過,為什么不要求第一張表也使用索引?

實際上,第一張表被稱為驅動表,亦可稱之為基表,MySQL 總是要遍歷該表的所有行,每一行都去第二張表中進行匹配查詢。遍歷可以不建立索引,走簇集索引即可,而查詢操作則需要依賴于二級索引。

那么,MySQL 如何決定將哪一張表作為驅動表呢?

MySQL 選擇驅動表的原則是:在對最終結果集沒影響的前提下,優先選擇結果集最少的那張表作為驅動表。原因在于驅動表的行數決定了在非驅動表中進行查詢的次數,驅動表行數越少,進行查詢的次數越少。

如果是 left join,那么基表通常是 left join 左側表,right join 的基表通常為 right join 右側表。

因此,我們要非常注意非驅動表的索引,在 ON 以及 WHERE 后的字段都應該被索引覆蓋。

5.3 join 與數據庫范式

數據庫范式有若干條[4],定義偏于學術性,但核心思路是簡潔明了的:數據庫范式目的是使結構更合理,消除存儲異常,使數據冗余盡量小,便于插入、刪除和更新。

join 操作的原因就在于多表之間有關系并且多個表之間數據幾乎沒有冗余。

舉一個例子,我們有三個表:

student(id,name)

class(id,description)

student_class(student_id,class_id)

如果要查詢一個學生對應的班級描述,那么就需要對上述三標進行 join,join 的性能問題可能會使我們產生擔心。

為此,我們可以故意破壞范式,制造出一張存在冗余的“大表”:

student_class_full(student_id, class_id, name, description)

你會發現,class 的 description 可能存儲在兩個表中(student_class_full 與 class),這不符合范式,并且為寫操作帶來了一致性問題以及寫性能下降。另一方面,我們不再需要使用 join 來完成查詢,讀性能得到提高。

可見,在一些場景下,我們可以選擇破壞數據庫范式,避免使用 join 來提高讀性能。代價是不同表之間出現的字段冗余、寫性能下降,寫操作出現多表間的一致性問題。

5.4 join 來代替子查詢

join 比子查詢在空間復雜度上要低,因此很多人建議利用 join 來代替子查詢:

子查詢:執行子查詢時,MYSQL 需要創建臨時表,查詢完畢后再刪除這些臨時表,所以,子查詢的速度會受到一定的影響,這里多了一個創建和銷毀臨時表的過程。

join:正如 5.3 小節所述,join 走嵌套查詢。小表驅動大表,通過索引字段進行關聯。

6. 是否應當使用 join?

阿里巴巴在 Java 開發手冊中建議[8]:超過三個表禁止 join。需要 join 的字段,數據類型保持絕對一致。

可見,阿里巴巴的意思是可以用 join,但是不要超過3張表。

(1)為什么 join 表的個數不能太多?

雖然我們可以利用索引來優化查詢,但是如果是 k 張 n 行的數據庫進行 join 查詢,最壞的情況下時間復雜度為 O(n*(logn)^k-1^),因此 join 表的數量應當得到控制。

例如,我們假設每一張表的行數為 1000,000 行,那么時間復雜度有:

join 表的數量(k)時間復雜度

220*1000,000

3400*1000,000

48000*1000,000

kO(n*(logn)^k-1^)

(2)為什么可以使用 join?

很多場景下 join 是最優選擇。例如兩張表各有 10W 條數據,我們的確可以利用 service 層,分兩步向兩個數據庫索要對應的行數據,然后在 service 層完成數據行的關聯與過濾。但是 2*10 W 行數據有很大的網絡傳輸壓力,并且會對 service 層所在的服務器內存有一定壓力。而 join 在 mysql server 處實際可能僅僅會得到 100 條符合要求的記錄,那么對比起來,在 service 層的額外開銷更難以接受。

當然,分庫的 join 避免不了網絡傳輸的額外開銷(排除一機多庫)。

SUMMARY

基于笛卡爾乘積,我們能夠方便地從語義上理解 MySQL 各種 join 語義;

第 4 節從語義上說明了典型帶有 join 的 SQL 語法的執行過程,但是注意其內部并不會建立多個虛擬表;

第 5 節分析了 join 操作的內部機制:join 基于小表驅動大表地進行嵌套查詢,被驅動表是否能夠走索引進行查詢將決定整個 join 語句的執行效率;

第 6 節分析了 join 使用建議,并給出其時間復雜度模型,解釋了阿里巴巴建議 join 表數量不應當超過 3 張的原因;

作者:spongecaptain

https://spongecaptain.cool/post/mysql/joininmysql/

責任編輯:haq

聲明:本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人,不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用,如有內容侵權或者其他違規問題,請聯系本站處理。 舉報投訴
  • MySQL
    +關注

    關注

    1

    文章

    804

    瀏覽量

    26542
  • Join
    +關注

    關注

    0

    文章

    9

    瀏覽量

    3258

原文標題:MySQL join 學習

文章出處:【微信號:DBDevs,微信公眾號:數據分析與開發】歡迎添加關注!文章轉載請注明出處。

收藏 人收藏

    評論

    相關推薦

    MySQL還能跟上PostgreSQL的步伐嗎

    Percona 的老板 Peter Zaitsev最近發表一篇博客,討論了MySQL是否還能跟上PostgreSQL的腳步。Percona 作為MySQL 生態扛旗者,Percona 開發了知名
    的頭像 發表于 11-18 10:16 ?200次閱讀
    <b class='flag-5'>MySQL</b>還能跟上PostgreSQL的步伐嗎

    詳解MySQL多實例部署

    詳解MySQL多實例部署
    的頭像 發表于 11-11 11:10 ?227次閱讀

    MySQL編碼機制原理

    前言 一位讀者在本地部署 MySQL 測試環境時碰到一個問題,我覺得挺有代表性的,所以寫篇文章介紹一下,看完相信你會對 MySQL 的編碼機制有最本質的了解,本文的目錄結構如下 讀者問題簡介
    的頭像 發表于 11-09 11:01 ?224次閱讀

    適用于MySQL的dbForge架構比較

    dbForge Schema Compare for MySQL 是一種工具,用于輕松有效地比較和部署 MySQL 數據庫結構和腳本文件夾差異。該工具提供了 MySQL 數據庫架構中所有差異的全面視圖。
    的頭像 發表于 10-28 09:41 ?195次閱讀
    適用于<b class='flag-5'>MySQL</b>的dbForge架構比較

    MySQL知識點匯總

    大家好,這部分被稱為DQL部分,是每個學習MySQL必須要學會的部分,下面就讓我來介紹MySQL中的其他部分。
    的頭像 發表于 08-05 15:27 ?396次閱讀
    <b class='flag-5'>MySQL</b>知識點匯總

    圖像語義分割的實用性是什么

    圖像語義分割是一種重要的計算機視覺任務,它旨在將圖像中的每個像素分配到相應的語義類別中。這項技術在許多領域都有廣泛的應用,如自動駕駛、醫學圖像分析、機器人導航等。 一、圖像語義分割的基本原理 1.1
    的頭像 發表于 07-17 09:56 ?415次閱讀

    圖像分割與語義分割中的CNN模型綜述

    圖像分割與語義分割是計算機視覺領域的重要任務,旨在將圖像劃分為多個具有特定語義含義的區域或對象。卷積神經網絡(CNN)作為深度學習的一種核心模型,在圖像分割與語義分割中發揮著至關重要的
    的頭像 發表于 07-09 11:51 ?841次閱讀

    【大語言模型:原理與工程實踐】大語言模型的基礎技術

    語義學習的起點是研究如何將詞轉化為向量表示,這一直是自然語言處理領域的研究熱點。詞表示方法主要分為三種:詞的獨熱表示、詞的分布式表示和基于預訓練的詞嵌入表示。 詞的獨熱表示:構建包含所有詞的詞表V,獨
    發表于 05-05 12:17

    MySQL的整體邏輯架構

    支持多種存儲引擎是眾所周知的MySQL特性,也是MySQL架構的關鍵優勢之一。如果能夠理解MySQL Server與存儲引擎之間是怎樣通過API交互的,將大大有利于理解MySQL的核心
    的頭像 發表于 04-30 11:14 ?449次閱讀
    <b class='flag-5'>MySQL</b>的整體邏輯架構

    MySQL聯表查詢優化

    使用顯示連接left join(right join,inner join),盡量避免隱式連接(where逗號連接表 .... and .... and ...)這類寫法,假設三張表每張表有一千
    的頭像 發表于 04-24 12:33 ?579次閱讀
    <b class='flag-5'>MySQL</b>聯表查詢優化

    MySQL忘記root密碼解決方案

    mysql登錄密碼為password()算法加密,解密成本太高,以下為通用方案; 原理:mysql提供了特殊啟動方式,即跳過權限表驗證,啟動后,登錄不需要提供密碼; 登錄后,即可修改mysql數據庫的user表,重置
    的頭像 發表于 04-23 16:08 ?693次閱讀

    三星正考慮為Bixby添加生成式AI功能

    Choi強調:“鑒于新型生成式AI與大語義學習(LLM)技術的出現,我們有必要去重新定位Bixby搭載這類科技以實現智能升級。
    的頭像 發表于 04-01 15:01 ?610次閱讀

    MySQL密碼忘記了怎么辦?MySQL密碼快速重置方法步驟命令示例!

    MySQL密碼忘記了怎么辦?MySQL密碼快速重置方法步驟命令示例! MySQL是一種常用的關系型數據庫管理系統,如果你忘記了MySQL的密碼,不必擔心,可以通過一些簡單的步驟來快速重
    的頭像 發表于 01-12 16:06 ?743次閱讀

    如何使用Golang連接MySQL

    首先我們來看如何使用Golang連接MySQL
    的頭像 發表于 01-08 09:42 ?3356次閱讀
    如何使用Golang連接<b class='flag-5'>MySQL</b>

    mysql密碼忘了怎么重置

    mysql密碼忘了怎么重置? MySQL是一種開源的關系型數據庫管理系統,密碼用于保護數據庫的安全性和保密性。如果你忘記了MySQL的密碼,可以通過以下幾種方法進行重置。 方法一:使用MySQ
    的頭像 發表于 12-27 16:51 ?6670次閱讀
    主站蜘蛛池模板: 国产乱码卡二卡三卡4W| 国产69精品久久久久无码麻豆| 阿娇和冠希13分钟在线观看| 第七色 夜夜撸| 精品一品国产午夜福利视频 | 国产人妻久久久精品麻豆| 精品久久久无码21P发布| 欧美亚洲精品真实在线| 午夜在线观看免费完整直播网页| 在线高清视频不卡无码| voyeurhit农村夫妻偷拍| 果冻传媒2021在线观看| 嗯啊好爽视频| 我的漂亮朋友在线观看全集免费| 亚洲三级在线观看| 国产成人综合高清在线观看| 美女脱了内裤张开腿让男人爽| 新妺妺窝人体色WWW| china18一19 第一次| 精品区2区3区4区产品乱码9| 人与人特黄一级| 34g污奶绵uk甩奶| 九九黄色大片| 少妇高潮A片特黄久久精品网| 91九色网址| 久久久精品久久久久久| 亚洲 日韩 国产 制服 在线| 苍井空小公主qvod| 内地同志男16china16| 亚洲无码小格式| 果冻传媒在线完整免费播放| 色偷偷91综合久久噜噜| yellow日本动漫免费观看| 美女搞鸡网站| 2021久久精品免费观看| 久久婷婷国产五月综合色啪最新| 校花娇喘呻吟校长陈若雪视频| 俄罗斯mm| 午夜福利试看120秒体验区| 国产成人免费视频| 兽交白浆喷水高潮|