## MySQL爭霸戰
### JOIN? IN? EXIST?
---
基本上正常情況下不需要特別處理這三者
因為資料的量級或複雜度沒到一定程度前,
就算效能差也是無感的,特別處理是沒意義
----
## 如果你說你要提前優化
## 那麼你加油!<!-- .element: class="fragment" data-fragment-index="0" -->:unamused:<!-- .element: class="fragment" data-fragment-index="1" -->
----
## 等到效能出現問題時
## 會有其他更有效的做法
----
### 像是設定 Index
### 用 Slow Query 來找戰犯 <!-- .element: class="fragment" data-fragment-index="0" -->
### 資料表重新規劃 <!-- .element: class="fragment" data-fragment-index="1" -->
### 最差都還有小朋友可以用 <!-- .element: class="fragment" data-fragment-index="2" -->
Note:
像是查看 slow qeruy 是不是有人亂下 sql
或者是 index 設定錯誤了
也可以對資料表重新規劃或拆表分表之類的,表格設計錯,再厲害的 SQL 都救不了
最差都還有砸錢升級機器
----
## 除了有感度的差別外
## 還有一個重要原因
## 就是可控性 <!-- .element: class="fragment" data-fragment-index="0" -->
----
## 你下的 SQL 不是 SQL
### MySQL 會嘗試優化你的 SQL <!-- .element: class="fragment" data-fragment-index="0" -->
---
## 你知道這代表什麼嗎?
### 代表我接下來要講的都是廢話 <!-- .element: class="fragment" data-fragment-index="0" -->
## :sob: <!-- .element: class="fragment" data-fragment-index="1" -->
Note:
不過當你所有能做的都做了,你也只能在乎這一點點的差異性了,所以走頭無路時你還是要回來進行優化,不過接下來要講的不一定是正確答案,因為每個 MySQL 版本不同都有可能導致 SQL優化器的差異性產出不一樣的結果,所以你必須針對你的版本去優化
---
## 接下來我們進入正題
### 如果你直接去問谷歌大神
### 他會給你一套標準答案
----
## Join > Exist > in
### 是對的,但也是不對 <!-- .element: class="fragment" data-fragment-index="0" -->
---
## 再講效能前我們先提一下
## 小表驅動大表<!-- .element: class="fragment" data-fragment-index="0" -->
----
## Nest Loop Join
```sql=1
select * from users join orders on orders.user_id = users.id
```
<!-- .element: class="fragment" data-fragment-index="0" -->
```jascript=+
for ( user in users ) {
for ( order in orders ) {
if (user.id == order.user_id) {
return order
}
}
}
```
<!-- .element: class="fragment" data-fragment-index="1" -->
----
## 但這樣不對啊!!!
#### 如果按照剛剛說的去執行
#### 不管左表大還是右表大
#### 查詢總次數都一樣啊
----
## 因為還差了一個 Index
### MySQL 是透過 BTree 實現<!-- .element: class="fragment" data-fragment-index="0" -->
![](https://i.imgur.com/Rnhr2lv.png)<!-- .element: class="fragment" data-fragment-index="0" -->
----
## 有了 BTree 後差在哪?
### Users 表沒變化,一樣全表搜尋 <!-- .element: class="fragment" data-fragment-index="0" -->
### Orders 表透過 Index 被優化了 <!-- .element: class="fragment" data-fragment-index="1" -->
Note:
到這邊我們已經把 Join 大概的實作講了一輪了,至於 Exist 實作原理差不多就不另外多敘述了
---
## 為什麼 IN 效能會墊底?
----
## 我們先思考一下
### 在其他語言使用 IN 的時候
Note:
像是 JS 的 indexOf 或是 PHP 的 in_array 又或是 Laravel 的 contains,都是把左邊的查詢值丟到右邊的陣列去跑回圈判斷存不存在
----
### 但事實上 MySQL 並沒有 IN
```sql=1
select * from users
where users.id in (select orders.user_id from orders)
```
<!-- .element: class="fragment" data-fragment-index="0" -->
按照其他語言的邏輯<!-- .element: class="fragment" data-fragment-index="1" -->
```sql=+
array = select user_id from orders
```
<!-- .element: class="fragment" data-fragment-index="1" -->
```javascript=+
for(user in users) {
for (item in array) {
if (user.id == item) {
return true
}
}
}
```
<!-- .element: class="fragment" data-fragment-index="1" -->
----
## 但如果這樣做的話
#### 不管是 Users 或 Orders 都不會用到 Index
#### 相當於兩張表都做了全表搜尋
----
## 所以 IN 更像一種語法糖
```sql=1
select * from users
where users.id in (select orders.user_id from orders)
```
<!-- .element: class="fragment" data-fragment-index="0" -->
```sql=+
array = select user_id from orders
//假設 array = (1,2,3)
```
<!-- .element: class="fragment" data-fragment-index="1" -->
```sql=+
select * from users where id in (1,2,3)
```
<!-- .element: class="fragment" data-fragment-index="2" -->
```sql=+
select * from users where id = 1 or id = 2 or id = 3
```
<!-- .element: class="fragment" data-fragment-index="3" -->
```sql=+
select * from user where id = 1
UNION
select * from user where id = 2
UNION
select * from user where id = 3
```
<!-- .element: class="fragment" data-fragment-index="4" -->
----
## 這樣做後有什麼差別?
### Users 表透過 Index 被優化了 <!-- .element: class="fragment" data-fragment-index="0" -->
---
## 講到這邊就會發現
### 其實 JOIN 或 EXIST 剛好跟 IN 相反
----
### 所以你會在網路上看到這樣的回答
### 根據左右表誰大來選擇用
## JOIN 或 EXIST 或 IN
----
### 至於這樣講對不對?還是老話一句
### 對,也不對<!-- .element: class="fragment" data-fragment-index="0" -->
因為你永遠也不知道 MySQL 替你做了什麼<!-- .element: class="fragment" data-fragment-index="1" -->
Note:
像是如果條件符合的話,在執行 IN 的時候,MySQL 會把 Subquery 查詢出來的陣列做成一張有索引的虛擬表,這樣就可以把 IN 直接變成左表驅動
https://dev.mysql.com/doc/refman/5.7/en/semijoins.html
----
### 不過有一點是可以確定的
### MySQL 版本不變,優化邏輯就不變
#### 所以只能整理出專案的所有業務邏輯
#### 並針對當下的 MySQL 版本去做最佳化
---
### 繞了一大圈,有講跟沒講一樣
### 是不是應證了我開頭說的
### 接下來都是廢話 :satisfied:
---
### 大部分情況 JOIN 真的比 IN 好
#### 因為就算是右表資料比較大<!-- .element: class="fragment" data-fragment-index="0" -->
#### 還是要經過語法轉換,最後在做 Union 一樣有時間成本<!-- .element: class="fragment" data-fragment-index="0" -->
---
### 那你知道 Laravel 全都用 IN 嗎?
Note:
eloquent 的 relation 都是使用 IN 去實作的,
關於為什麼不用 JOIN 我找不到官方的說法,
只有找到網路一些網友的見解。
----
### 網路有人說,如果用 JOIN
### 那麼 Colums 就會被混淆
<br>
#### 但我覺得這個透過 Builder 加上 select 就可以解決<!-- .element: class="fragment" data-fragment-index="0" -->
----
### 又有人說,這樣會污染 Model
<br>
#### 但我還是覺得在 Column 沒有混淆時<!-- .element: class="fragment" data-fragment-index="0" -->
#### 分別封裝成兩個或多個 Model 沒有難度<!-- .element: class="fragment" data-fragment-index="0" -->
----
### 哪是為了 Lazy Loading 嗎?
#### 懶加載很好,但是寫 API 用不到...<!-- .element: class="fragment" data-fragment-index="0" -->
----
## 那麼 ORM 還剩下什麼?
<span>好寫?<!-- .element: class="fragment" data-fragment-index="0" --></span> <span>又漂亮?<!-- .element: class="fragment" data-fragment-index="1" --></span> <span>又好維護?<!-- .element: class="fragment" data-fragment-index="2" --></span>
----
### 但這些值得你用效能來換嗎?
Note:
值不值得只能靠自己判斷,因為我自己也沒有答案,一開始寫 Laravel 覺得 ORM 很棒所以不會特別管 SQL 的部分,但在上家公司的時候,因為業務需求跟資料量的關係,所以終於遇到了 ORM 的效能貧頸,所以只能自己寫 SQL。
<br>不過現在在跑跑腿這邊,不管是業務邏輯還是資料量都還能接受直接 ORM 處理,所以在這邊我又回頭寫 ORM,所以值不值得需要看你情境。
----
## 不過這邊撇開其他問題
#### 單純看 Laravel 使用 IN 真的問題很大嗎?
---
## IN 有沒有優點?
<span>資料不重複<!-- .element: class="fragment" data-fragment-index="0" --></span>
<span>Array 越小越高效<!-- .element: class="fragment" data-fragment-index="1" --></span>
<span>搭配 Query Cache 降低對不常變動的表重複搜尋<!-- .element: class="fragment" data-fragment-index="2" --></span>
Note:
Query Cache 在新版的 MySQL 8.0 已經被拋棄了,原因是因為如果當你資料量越來越大,在 Cache 表裡面慢慢搜尋反爾會變成一種時間消耗,而且 Cache 只能逐字比對所以當你 Cache 量越來越多,他會越來越慢
----
### 那除了這些還有嗎?
### 還記得我們一開始說的<!-- .element: class="fragment" data-fragment-index="0" -->
### 你的 SQL 不是 SQL 嗎?<!-- .element: class="fragment" data-fragment-index="0" -->
---
## 我們還有一位神隊友
## SQL 優化器<!-- .element: class="fragment" data-fragment-index="0" -->
----
#### 接下來我們試著了解優化器做了什麼
<br/>
```sql=1
select * from users
where users.id in (select user_id from orders)
```
Note:
事實上這段 SQL 不會像我們之前那樣說的,先查詢 Subquery 後在轉換成 OR 最後再轉成 Where 並用 UNION 串接起來,如果這樣就太小看優化氣了
----
## 那麼他做了什麼?
<div>
#### 第一步驟會先還原真正的 SQL
```sql
select * from users
where users.id in (select user_id from orders)
```
#### 會被轉換成下面這樣
```sql
/* select#1 */select users.id, users.name from users
where users.id in (/* select#2 */select user_id from orders)
```
</div><!-- .element: class="fragment" data-fragment-index="0" -->
Note:
像我們剛剛講的,如果有使用 Query Cache 的話,select#2 並不會真的去查詢,而是返回之前的 Cache 資料,但現在是以沒有開啟 Query Cache 功能的模式解釋。
----
#### 第二步驟他會去掃描你的語意
```json=1
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": []
}
}
```
Note:
semi-join 是一種新的 JOIN,他跟一般的 JOIN 差在,他不會回傳所有資料,他只會找到一筆符合的就回傳左表資料
----
#### 然後如果你沒有特別指示的話
```json=1
{
"transformations_to_nested_joins": {
"transformations": [
"semijoin"
],
"expanded_query": "/* select#1 */ select `users`.`id` AS `id`,`users`.`last_track_id` AS `last_track_id`,`users`.`line_id` AS `line_id`,`users`.`line_bot_id` AS `line_bot_id`,`users`.`account` AS `account`,`users`.`name` AS `name`,`users`.`phone` AS `phone`,`users`.`email` AS `email`,`users`.`gender` AS `gender`,`users`.`is_verify` AS `is_verify`,`users`.`is_active` AS `is_active`,`users`.`state` AS `state`,`users`.`password` AS `password`,`users`.`remember_token` AS `remember_token`,`users`.`noticed_at` AS `noticed_at`,`users`.`created_at` AS `created_at`,`users`.`updated_at` AS `updated_at` from `users` semi join (`orders`) where (1 and (`users`.`id` = `orders`.`user_id`))"
}
}
```
#### 上面太多資訊,我們直接看下面<!-- .element: class="fragment" data-fragment-index="0" -->
```sql=1
select ... from `users` semi join (`orders`)
where (1 and (`users`.`id` = `orders`.`user_id`))
```
<!-- .element: class="fragment" data-fragment-index="0" -->
Note:
SQL 優化器有很多自己的優化策略,基本上你在下 SQL 的時候是可以指定使用或禁止使用特定策略。
<br/>轉換後會發現多一個 1,那是因為原本的 Subquery 沒有 Where 這時預設就會是 where 1。
----
#### 第三步驟他會去檢查你的 Where 條件
```json=1
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(1 and (`users`.`id` = `orders`.`user_id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and multiple equal(`users`.`id`, `orders`.`user_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and multiple equal(`users`.`id`, `orders`.`user_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(`users`.`id`, `orders`.`user_id`)"
}
]
}
}
```
----
#### equality_propagation(等式優化)
```json=1
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and multiple equal(`users`.`id`, `orders`.`user_id`))"
}
```
<div>
```javascript=+
a = b and b = c
```
等同
```javascript=+
a = b = c
```
</div><!-- .element: class="fragment" data-fragment-index="0" -->
Note:
優化器認為 A、B、C 三個變數都是一樣的,所以會把他們放在一起後面一起判斷
----
#### constant_propagation(常數優化)
```json=1
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and multiple equal(`users`.`id`, `orders`.`user_id`))"
}
```
<div>
```javascript=1
a = 5 and b > a
```
等同
```javascript=+
a = 5 and b > 5
```
</div><!-- .element: class="fragment" data-fragment-index="0" -->
----
#### trivial_condition_removal(條件優化)
```json=1
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(`users`.`id`, `orders`.`user_id`)"
}
```
---
### 優化器後面還做了很多事情
#### 像是 Index 優化
#### 關聯表 Index 優化
#### 各種表格掃描成本分析
#### 最後才找出最適合的執行方式
---
#### 但因為時間關係,
#### 所以今天就先這樣了
#### 如果有興趣,下次我們可以更深入了解
---
### 最後如果你當真要走這條不歸路
#### 請關注這三個關鍵字
#### Explain、Profile、optimizer_trace
{"metaMigratedAt":"2023-06-15T08:19:16.438Z","metaMigratedFrom":"YAML","title":"MySQL爭霸戰 JOIN? IN? EXIST?","breaks":true,"slideOptions":"{\"progress\":true,\"theme\":\"solarized\",\"transition\":\"concave\",\"spotlight\":{\"enabled\":false}}","contributors":"[{\"id\":\"6cafde5d-0be9-4081-bb7a-2b953ef414d1\",\"add\":11472,\"del\":639}]"}