《Mysql實例MySQL中一些優(yōu)化straight_join技巧》要點：
本文介紹了Mysql實例MySQL中一些優(yōu)化straight_join技巧，希望對您有用。如果有疑問，可以聯(lián)系我們。
在oracle中可以指定的表連接的hint有很多：ordered hint 指示oracle按照from關鍵字后的表順序來進行連接；leading hint 指示查詢優(yōu)化器使用指定的表作為連接的首表,即驅動表；use_nl hint指示查詢優(yōu)化器使用nested loops方式連接指定表和其他行源,并且將強制指定表作為inner表.
在mysql中就有之對應的straight_join,由于mysql只支持nested loops的連接方式,所以這里的straight_join類似oracle中的use_nl hint.mysql優(yōu)化器在處理多表的關聯(lián)的時候,很有可能會選擇錯誤的驅動表進行關聯(lián),導致了關聯(lián)次數(shù)的增加,從而使得sql語句執(zhí)行變得非常的緩慢,這個時候需要有經(jīng)驗的DBA進行判斷,選擇正確的驅動表,這個時候straight_join就起了作用了,下面我們來看一看使用straight_join進行優(yōu)化的案例：MYSQL入門
1．用戶實例：spxxxxxx的一條sql執(zhí)行非常的緩慢,sql如下：
MYSQL入門
73871 | root | 127.0.0.1:49665 | user_app_test | Query | 500 | Sorting result | 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)
2.查看執(zhí)行計劃：
MYSQL入門
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); 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: 1 select_type: SIMPLE table: a type: ALL possible_keys: ix_test_log_userid key: NULL key_len: NULL ref: NULL rows: 416782 Extra: Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 96 ref: user_app_testnew.a.userid rows: 1 Extra: Using where 2 rows in set (0.00 sec)
3.查看索引：
MYSQL入門
mysql> show index from test_log; +――――C+――――+――――――――-+――――C+――――-+―――C+――――-+―――-++ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +――――C+――――+――――――――-+――――C+――――-+―――C+――――-+―――-++ | 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 | | +――――C+――――+――――――――-+――――C+――――-+―――C+――――-+―――-+――C+――+――-+
4.調整索引,A表優(yōu)化采用覆蓋索引：
MYSQL入門
mysql>alter table test_log drop index ix_test_log_userid,add index ix_test_log_userid(userid,practicetime)
5.查看執(zhí)行計劃：
MYSQL入門
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: 1 select_type: SIMPLE table: a type: index possible_keys: ix_test_log_userid key: ix_test_log_userid key_len: 105 ref: NULL rows: 388451 Extra: Using index; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 96 ref: user_app_test.a.userid rows: 1 Extra: Using where 2 rows in set (0.00 sec)
調整后執(zhí)行稍有效果,但是還不明顯,還沒有找到要害：
MYSQL入門
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); ………………. 143 rows in set (1 min 12.62 sec)
6.執(zhí)行時間仍然需要很長,時間的消耗主要耗費在Using filesort中,參與排序的數(shù)據(jù)量有38W之多,所以需要轉換驅動表；嘗試采用user表做驅動表：使用straight_join強制連接順序：
MYSQL入門
mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime)\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 42806 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: ix_test_log_userid key: ix_test_log_userid key_len: 96 ref: user_app_test.b.userid rows: 38 Extra: Using index 2 rows in set (0.00 sec)
執(zhí)行時間已經(jīng)有了質的變化,降低到了2.56秒；
MYSQL入門
mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime); …….. 143 rows in set (2.56 sec)
7.在分析執(zhí)行計劃的第一步：Using where; Using temporary; Using filesort,user表其實也可以采用覆蓋索引來避免using where的出現(xiàn),所以繼續(xù)調整索引：
MYSQL入門
mysql> show index from user; +――-+――――+――――――+――――C+――――-+―――C+――――-+―――-+――C+――+――――+―――+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +――-+――――+――――――+――――C+――――-+―――C+――――-+―――-+――C+――+――――+―――+ | 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 | | +――-+――――+――――――+――――C+――――-+―――C+――――-+―――-+――C+――+――――+―――+ 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: 0 mysql>explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime); *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: index possible_keys: PRIMARY key: ix_user_username key_len: 125 ref: NULL rows: 42466 Extra: Using where; Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: ix_test_log_userid key: ix_test_log_userid key_len: 96 ref: user_app_test.b.userid rows: 38 Extra: Using index 2 rows in set (0.00 sec)
8.執(zhí)行時間降低到了1.43秒：
MYSQL入門
mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rows FROM USER b straight_join test_log a WHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4 GROUP BY DATE(practicetime); ....... 143 rows in set (1.43 sec)
維易PHP培訓學院每天發(fā)布《Mysql實例MySQL中一些優(yōu)化straight_join技巧》等實戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/12622.html
