這篇文章主要介紹了MySQL中一些優化straight_join技巧,作者通過用戶的實際案例分析,需要的朋友可以參考下

在oracle中可以指定的表連接的hint有很多：ordered hint 指示oracle按照from關鍵字后的表順序來進行連接;leading hint 指示查詢優化器使用指定的表作為連接的首表，即驅動表;use_nl hint指示查詢優化器使用nested loops方式連接指定表和其他行源，并且將強制指定表作為inner表。

在mysql中就有之對應的straight_join,由于mysql只支持nested loops的連接方式，所以這里的straight_join類似oracle中的use_nl hint。mysql優化器在處理多表的關聯的時候，很有可能會選擇錯誤的驅動表進行關聯，導致了關聯次數的增加，從而使得sql語句執行變得非常的緩慢，這個時候需要有經驗的DBA進行判斷，選擇正確的驅動表，這個時候straight_join就起了作用了，下面我們來看一看使用straight_join進行優化的案例：

1.用戶實例：spxxxxxx的一條sql執行非常的緩慢，sql如下：

1. 73871 | root | 127.0.0.1:49665 | user_app_test | Query | 500 | Sorting result | 
2. SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
3. FROM test_log a,USER b 
4. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
5. GROUP BY DATE(practicetime) 

2.查看執行計劃：

1. mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
2. FROM test_log a,USER b 
3. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
4. GROUP BY DATE(practicetime); 
5. mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
6. -> FROM test_log a,USER b 
7. -> WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
8. -> GROUP BY DATE(practicetime)/G; 
9. *************************** 1. row *************************** 
10. id: 1 
11. select_type: SIMPLE 
12. table: a 
13. type: ALL 
14. possible_keys: ix_test_log_userid 
15. key: NULL 
16. key_len: NULL 
17. ref: NULL 
18. rows: 416782 
19. Extra: Using filesort 
20. *************************** 2. row *************************** 
21. id: 1 
22. select_type: SIMPLE 
23. table: b 
24. type: eq_ref 
25. possible_keys: PRIMARY 
26. key: PRIMARY 
27. key_len: 96 
28. ref: user_app_testnew.a.userid 
29. rows: 1 
30. Extra: Using where 
31. 2 rows in set (0.00 sec) 

3.查看索引：

1. mysql> show index from test_log; 
2. +————–+————+————————-+————–+————-+———–+————-+———-++ 
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
4. +————–+————+————————-+————–+————-+———–+————-+———-++ 
5. | test_log | 0 | ix_test_log_unique_ | 1 | unitid | A | 20 | NULL | NULL | | BTREE | | 
6. | test_log | 0 | ix_test_log_unique_ | 2 | paperid | A | 20 | NULL | NULL | | BTREE | | 
7. | test_log | 0 | ix_test_log_unique_ | 3 | qtid | A | 20 | NULL | NULL | | BTREE | | 
8. | test_log | 0 | ix_test_log_unique_ | 4 | userid | A | 400670 | NULL | NULL | | BTREE | | 
9. | test_log | 0 | ix_test_log_unique_ | 5 | serial | A | 400670 | NULL | NULL | | BTREE | | 
10. | test_log | 1 | ix_test_log_unit | 1 | unitid | A | 519 | NULL | NULL | | BTREE | | 
11. | test_log | 1 | ix_test_log_unit | 2 | paperid | A | 2023 | NULL | NULL | | BTREE | | 
12. | test_log | 1 | ix_test_log_unit | 3 | qtid | A | 16694 | NULL | NULL | | BTREE | | 
13. | test_log | 1 | ix_test_log_serial | 1 | serial | A | 133556 | NULL | NULL | | BTREE | | 
14. | test_log | 1 | ix_test_log_userid | 1 | userid | A | 5892 | NULL | NULL | | BTREE | | 
15. +————–+————+————————-+————–+————-+———–+————-+———-+——–+——+——-+ 

4.調整索引，A表優化采用覆蓋索引：

1. mysql>alter table test_log drop index ix_test_log_userid,add index ix_test_log_userid(userid,practicetime) 

5.查看執行計劃：

1. mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
2. FROM test_log a,USER b 
3. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
4. GROUP BY DATE(practicetime)/G 
5. *************************** 1. row *************************** 
6. id: 1 
7. select_type: SIMPLE 
8. table: a 
9. type: index 
10. possible_keys: ix_test_log_userid 
11. key: ix_test_log_userid 
12. key_len: 105 
13. ref: NULL 
14. rows: 388451 
15. Extra: Using index; Using filesort 
16. *************************** 2. row *************************** 
17. id: 1 
18. select_type: SIMPLE 
19. table: b 
20. type: eq_ref 
21. possible_keys: PRIMARY 
22. key: PRIMARY 
23. key_len: 96 
24. ref: user_app_test.a.userid 
25. rows: 1 
26. Extra: Using where 
27. 2 rows in set (0.00 sec) 

調整后執行稍有效果，但是還不明顯，還沒有找到要害：

1. SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
2. FROM test_log a,USER b 
3. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
4. GROUP BY DATE(practicetime); 
5. ………………. 
6. 143 rows in set (1 min 12.62 sec) 

6.執行時間仍然需要很長，時間的消耗主要耗費在Using filesort中，參與排序的數據量有38W之多，所以需要轉換驅動表;嘗試采用user表做驅動表：使用straight_join強制連接順序：

1. mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
2. FROM USER b straight_join test_log a 
3. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
4. GROUP BY DATE(practicetime)/G; 
5. *************************** 1. row *************************** 
6. id: 1 
7. select_type: SIMPLE 
8. table: b 
9. type: ALL 
10. possible_keys: PRIMARY 
11. key: NULL 
12. key_len: NULL 
13. ref: NULL 
14. rows: 42806 
15. Extra: Using where; Using temporary; Using filesort 
16. *************************** 2. row *************************** 
17. id: 1 
18. select_type: SIMPLE 
19. table: a 
20. type: ref 
21. possible_keys: ix_test_log_userid 
22. key: ix_test_log_userid 
23. key_len: 96 
24. ref: user_app_test.b.userid 
25. rows: 38 
26. Extra: Using index 
27. 2 rows in set (0.00 sec) 

執行時間已經有了質的變化，降低到了2.56秒;

1. mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
2. FROM USER b straight_join test_log a 
3. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
4. GROUP BY DATE(practicetime); 
5. …….. 
6. 143 rows in set (2.56 sec) 

7.在分析執行計劃的第一步：Using where; Using temporary; Using filesort，user表其實也可以采用覆蓋索引來避免using where的出現，所以繼續調整索引：

1. mysql> show index from user; 
2. +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ 
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
4. +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ 
5. | user | 0 | PRIMARY | 1 | userid | A | 43412 | NULL | NULL | | BTREE | | 
6. | user | 0 | ix_user_email | 1 | email | A | 43412 | NULL | NULL | | BTREE | | 
7. | user | 1 | ix_user_username | 1 | username | A | 202 | NULL | NULL | | BTREE | | 
8. +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ 
9. 3 rows in set (0.01 sec) 
10.  
11. mysql>alter table user drop index ix_user_username,add index ix_user_username(username,isfree); 
12. Query OK, 42722 rows affected (0.73 sec) 
13. Records: 42722 Duplicates: 0 Warnings: 0 
14.  
15. mysql>explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
16. FROM USER b straight_join test_log a 
17. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
18. GROUP BY DATE(practicetime); 
19. *************************** 1. row *************************** 
20. id: 1 
21. select_type: SIMPLE 
22. table: b 
23. type: index 
24. possible_keys: PRIMARY 
25. key: ix_user_username 
26. key_len: 125 
27. ref: NULL 
28. rows: 42466 
29. Extra: Using where; Using index; Using temporary; Using filesort 
30. *************************** 2. row *************************** 
31. id: 1 
32. select_type: SIMPLE 
33. table: a 
34. type: ref 
35. possible_keys: ix_test_log_userid 
36. key: ix_test_log_userid 
37. key_len: 96 
38. ref: user_app_test.b.userid 
39. rows: 38 
40. Extra: Using index 
41. 2 rows in set (0.00 sec) 

8.執行時間降低到了1.43秒：

1. mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows 
2. FROM USER b straight_join test_log a 
3. WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 
4. GROUP BY DATE(practicetime); 
5. 。。。。。。。 
6. 143 rows in set (1.43 sec)