MySQL爭霸戰 JOIN？ IN？ EXIST?

## MySQL爭霸戰 ### JOIN？ IN？ EXIST? --- 基本上正常情況下不需要特別處理這三者因為資料的量級或複雜度沒到一定程度前，就算效能差也是無感的，特別處理是沒意義 ---- ## 如果你說你要提前優化 ## 那麼你加油！:unamused: ---- ## 等到效能出現問題時 ## 會有其他更有效的做法 ---- ### 像是設定 Index ### 用 Slow Query 來找戰犯  ### 資料表重新規劃  ### 最差都還有小朋友可以用  Note: 像是查看 slow qeruy 是不是有人亂下 sql 或者是 index 設定錯誤了也可以對資料表重新規劃或拆表分表之類的，表格設計錯，再厲害的 SQL 都救不了最差都還有砸錢升級機器 ---- ## 除了有感度的差別外 ## 還有一個重要原因 ## 就是可控性  ---- ## 你下的 SQL 不是 SQL ### MySQL 會嘗試優化你的 SQL  --- ## 你知道這代表什麼嗎？ ### 代表我接下來要講的都是廢話  ## :sob:  Note: 不過當你所有能做的都做了，你也只能在乎這一點點的差異性了，所以走頭無路時你還是要回來進行優化，不過接下來要講的不一定是正確答案，因為每個 MySQL 版本不同都有可能導致 SQL優化器的差異性產出不一樣的結果，所以你必須針對你的版本去優化 --- ## 接下來我們進入正題 ### 如果你直接去問谷歌大神 ### 他會給你一套標準答案 ---- ## Join > Exist > in ### 是對的，但也是不對  --- ## 再講效能前我們先提一下 ## 小表驅動大表 ---- ## Nest Loop Join ```sql=1 select * from users join orders on orders.user_id = users.id ```  ```jascript=+ for ( user in users ) { for ( order in orders ) { if (user.id == order.user_id) { return order } } } ```  ---- ## 但這樣不對啊！！！ #### 如果按照剛剛說的去執行 #### 不管左表大還是右表大 #### 查詢總次數都一樣啊 ---- ## 因為還差了一個 Index ### MySQL 是透過 BTree 實現 ![](https://i.imgur.com/Rnhr2lv.png) ---- ## 有了 BTree 後差在哪？ ### Users 表沒變化，一樣全表搜尋  ### Orders 表透過 Index 被優化了  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) ```  按照其他語言的邏輯 ```sql=+ array = select user_id from orders ```  ```javascript=+ for(user in users) { for (item in array) { if (user.id == item) { return true } } } ```  ---- ## 但如果這樣做的話 #### 不管是 Users 或 Orders 都不會用到 Index #### 相當於兩張表都做了全表搜尋 ---- ## 所以 IN 更像一種語法糖 ```sql=1 select * from users where users.id in (select orders.user_id from orders) ```  ```sql=+ array = select user_id from orders //假設 array = (1,2,3) ```  ```sql=+ select * from users where id in (1,2,3) ```  ```sql=+ select * from users where id = 1 or id = 2 or id = 3 ```  ```sql=+ select * from user where id = 1 UNION select * from user where id = 2 UNION select * from user where id = 3 ```  ---- ## 這樣做後有什麼差別？ ### Users 表透過 Index 被優化了  --- ## 講到這邊就會發現 ### 其實 JOIN 或 EXIST 剛好跟 IN 相反 ---- ### 所以你會在網路上看到這樣的回答 ### 根據左右表誰大來選擇用 ## JOIN 或 EXIST 或 IN ---- ### 至於這樣講對不對？還是老話一句 ### 對，也不對 因為你永遠也不知道 MySQL 替你做了什麼 Note: 像是如果條件符合的話，在執行 IN 的時候，MySQL 會把 Subquery 查詢出來的陣列做成一張有索引的虛擬表，這樣就可以把 IN 直接變成左表驅動 https://dev.mysql.com/doc/refman/5.7/en/semijoins.html ---- ### 不過有一點是可以確定的 ### MySQL 版本不變，優化邏輯就不變 #### 所以只能整理出專案的所有業務邏輯 #### 並針對當下的 MySQL 版本去做最佳化 --- ### 繞了一大圈，有講跟沒講一樣 ### 是不是應證了我開頭說的 ### 接下來都是廢話 :satisfied: --- ### 大部分情況 JOIN 真的比 IN 好 #### 因為就算是右表資料比較大 #### 還是要經過語法轉換，最後在做 Union 一樣有時間成本 --- ### 那你知道 Laravel 全都用 IN 嗎？ Note: eloquent 的 relation 都是使用 IN 去實作的，關於為什麼不用 JOIN 我找不到官方的說法，只有找到網路一些網友的見解。 ---- ### 網路有人說，如果用 JOIN ### 那麼 Colums 就會被混淆 <br> #### 但我覺得這個透過 Builder 加上 select 就可以解決 ---- ### 又有人說，這樣會污染 Model <br> #### 但我還是覺得在 Column 沒有混淆時 #### 分別封裝成兩個或多個 Model 沒有難度 ---- ### 哪是為了 Lazy Loading 嗎？ #### 懶加載很好，但是寫 API 用不到... ---- ## 那麼 ORM 還剩下什麼？ <span>好寫?</span> <span>又漂亮？</span> <span>又好維護？</span> ---- ### 但這些值得你用效能來換嗎？ Note: 值不值得只能靠自己判斷，因為我自己也沒有答案，一開始寫 Laravel 覺得 ORM 很棒所以不會特別管 SQL 的部分，但在上家公司的時候，因為業務需求跟資料量的關係，所以終於遇到了 ORM 的效能貧頸，所以只能自己寫 SQL。 <br>不過現在在跑跑腿這邊，不管是業務邏輯還是資料量都還能接受直接 ORM 處理，所以在這邊我又回頭寫 ORM，所以值不值得需要看你情境。 ---- ## 不過這邊撇開其他問題 #### 單純看 Laravel 使用 IN 真的問題很大嗎？ --- ## IN 有沒有優點？ <span>資料不重複</span> <span>Array 越小越高效</span> <span>搭配 Query Cache 降低對不常變動的表重複搜尋</span> Note: Query Cache 在新版的 MySQL 8.0 已經被拋棄了，原因是因為如果當你資料量越來越大，在 Cache 表裡面慢慢搜尋反爾會變成一種時間消耗，而且 Cache 只能逐字比對所以當你 Cache 量越來越多，他會越來越慢 ---- ### 那除了這些還有嗎？ ### 還記得我們一開始說的 ### 你的 SQL 不是 SQL 嗎？ --- ## 我們還有一位神隊友 ## SQL 優化器 ---- #### 接下來我們試著了解優化器做了什麼 <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> 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`))" } } ``` #### 上面太多資訊，我們直接看下面 ```sql=1 select ... from `users` semi join (`orders`) where (1 and (`users`.`id` = `orders`.`user_id`)) ```  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> 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> ---- #### trivial_condition_removal（條件優化） ```json=1 { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(`users`.`id`, `orders`.`user_id`)" } ``` --- ### 優化器後面還做了很多事情 #### 像是 Index 優化 #### 關聯表 Index 優化 #### 各種表格掃描成本分析 #### 最後才找出最適合的執行方式 --- #### 但因為時間關係， #### 所以今天就先這樣了 #### 如果有興趣，下次我們可以更深入了解 --- ### 最後如果你當真要走這條不歸路 #### 請關注這三個關鍵字 #### Explain、Profile、optimizer_trace