MySQL中一些最佳化straight_join技巧_Mysql

在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如下：

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

2.查看執行計畫：

mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM test_log a,USER bWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows-> FROM test_log a,USER b-> WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4-> GROUP BY DATE(practicetime)\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: atype: ALLpossible_keys: ix_test_log_useridkey: NULLkey_len: NULLref: NULLrows: 416782Extra: Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: btype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 96ref: user_app_testnew.a.useridrows: 1Extra: Using where2 rows in set (0.00 sec)

3.查看索引：

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

4.調整索引，A表最佳化採用覆蓋索引：

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

5.查看執行計畫：

mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM test_log a,USER bWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime)\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: atype: indexpossible_keys: ix_test_log_useridkey: ix_test_log_useridkey_len: 105ref: NULLrows: 388451Extra: Using index; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: btype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 96ref: user_app_test.a.useridrows: 1Extra: Using where2 rows in set (0.00 sec)

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

SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM test_log a,USER bWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);……………….143 rows in set (1 min 12.62 sec)

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

mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime)\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: btype: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 42806Extra: Using where; Using temporary; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: atype: refpossible_keys: ix_test_log_useridkey: ix_test_log_useridkey_len: 96ref: user_app_test.b.useridrows: 38Extra: Using index2 rows in set (0.00 sec)

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

mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);……..143 rows in set (2.56 sec)

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

mysql> show index from user;+——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+| user |     0 | PRIMARY     |      1 | userid   | A     |    43412 |   NULL | NULL  |   | BTREE   |     || user |     0 | ix_user_email  |      1 | email    | A     |    43412 |   NULL | NULL  |   | BTREE   |     || user |     1 | ix_user_username |      1 | username  | A     |     202 |   NULL | NULL  |   | BTREE   |     |+——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+3 rows in set (0.01 sec)mysql>alter table user drop index ix_user_username,add index ix_user_username(username,isfree);Query OK, 42722 rows affected (0.73 sec)Records: 42722 Duplicates: 0 Warnings: 0mysql>explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);*************************** 1. row ***************************id: 1select_type: SIMPLEtable: btype: indexpossible_keys: PRIMARYkey: ix_user_usernamekey_len: 125ref: NULLrows: 42466Extra: Using where; Using index; Using temporary; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: atype: refpossible_keys: ix_test_log_useridkey: ix_test_log_useridkey_len: 96ref: user_app_test.b.useridrows: 38Extra: Using index2 rows in set (0.00 sec)

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

mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);。。。。。。。143 rows in set (1.43 sec)