mysql關聯查詢如何優化

158次閱讀

共計 4323 個字符，預計需要花費 11 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 mysql 關聯查詢如何優化，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

mysql 中任何關聯查詢都是 nest loop（嵌套循環）操作，nest loop 是在驅動表中取出一條數據，然后從被驅動表中逐行比較，把符合規則的放入結果集中，然后再取下一行，依次循環，驅動表每返回一行，被驅動表就要掃描一次。
針對 nest loop 關聯機制需要從下面幾個方面著手優化：
1、減少 nest loop 循環次數，使用小結果集做驅動表，驅動大結果集。
2、被驅動表每次循環都要被掃描，所以要求關聯鍵上一定要有索引，而且選擇性要好。
3、如果第二條無法滿足，可以通過調 join_buffer_size 來設置 join buffer 的大小，不過還是建議添加索引而不是純粹的加大 join_buffer_size

接下來通過下面的實驗來了解 mysql 的 nest loop
實驗環境：Percona server5.6.27 大表 bill、小表 user，表上均有索引
mysql select count(*) from bill;
+———-+
| count(*) |
+———-+
| 1966789 |
+———-+

mysql select count(*) from user_tmp;
+———-+
| count(*) |
+———-+
| 36317 |
+———-+
一、執行計劃：
mysql explain select a.user_id,b.loan_info_id from bill b left JOIN user_tmp a on a.user_id=b.user_id;
+—-+————-+——-+——–+—————+———+———+—————+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+—————+———+————-+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1912096 | NULL |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index |
+—-+————-+——-+——–+—————+———+———+—————+———+————-+

左連接左表不管有多大總是驅動表，右表總是被驅動表

內連接，mysql 的優化器會根據統計信息自動選擇小表 user_tmp 做驅動表，大家可以看到 rows 列值和我們剛開始統計的行數不一致，是因為統計信息和實際是有差異，所以有時候統計信息的不準確會導致執行計劃不是最優的。內連接可以用 STRAIGHT_JOIN 按照順序執行，即指定左表為驅動表

二、執行效率（關聯鍵都有索引）：
當小表是驅動表的時候
mysql select a.user_id,b.loan_info_id from bill b INNER JOIN user_tmp a on a.user_id=b.user_id;
這里結果集有幾萬條，省略
耗時：0.202s
使用 STRAIGHT_JOIN 強制大表是驅動表的時候
mysql select STRAIGHT_JOIN a.user_id,b.loan_info_id from bill b INNER JOIN user_tmp a on a.user_id=b.user_id
耗時：5.260s
由于兩張表的相差幾十倍，兩種執行計劃的效率也是顯而易見的
注：如果大表的關聯鍵索引選擇性比較差（如重復數據多等），每次循環掃太多了，不如讓大表做驅動表，上述實驗是在大表的索引選擇性好的情況下得出的結果

以上是“mysql 關聯查詢如何優化”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！

正文完