MySQL中一些優(yōu)化straight_join技巧

更新時(shí)間：2015年05月07日 17:10:17 作者：羅龍九

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

在oracle中可以指定的表連接的hint有很多：ordered hint 指示oracle按照from關(guān)鍵字后的表順序來進(jìn)行連接；leading hint 指示查詢優(yōu)化器使用指定的表作為連接的首表，即驅(qū)動(dòng)表；use_nl hint指示查詢優(yōu)化器使用nested loops方式連接指定表和其他行源，并且將強(qiáng)制指定表作為inner表。
在mysql中就有之對應(yīng)的straight_join,由于mysql只支持nested loops的連接方式，所以這里的straight_join類似oracle中的use_nl hint。mysql優(yōu)化器在處理多表的關(guān)聯(lián)的時(shí)候，很有可能會(huì)選擇錯(cuò)誤的驅(qū)動(dòng)表進(jìn)行關(guān)聯(lián)，導(dǎo)致了關(guān)聯(lián)次數(shù)的增加，從而使得sql語句執(zhí)行變得非常的緩慢，這個(gè)時(shí)候需要有經(jīng)驗(yàn)的DBA進(jìn)行判斷，選擇正確的驅(qū)動(dòng)表，這個(gè)時(shí)候straight_join就起了作用了，下面我們來看一看使用straight_join進(jìn)行優(yōu)化的案例：

1．用戶實(shí)例：spxxxxxx的一條sql執(zhí)行非常的緩慢，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_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í)行計(jì)劃：

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> 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.調(diào)整索引，A表優(yōu)化采用覆蓋索引：

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

5.查看執(zhí)行計(jì)劃：

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)

調(diào)整后執(zhí)行稍有效果，但是還不明顯，還沒有找到要害：

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í)行時(shí)間仍然需要很長，時(shí)間的消耗主要耗費(fèi)在Using filesort中，參與排序的數(shù)據(jù)量有38W之多，所以需要轉(zhuǎn)換驅(qū)動(dòng)表；嘗試采用user表做驅(qū)動(dòng)表：使用straight_join強(qiáng)制連接順序：

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í)行時(shí)間已經(jīng)有了質(zhì)的變化，降低到了2.56秒；

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í)行計(jì)劃的第一步：Using where; Using temporary; Using filesort，user表其實(shí)也可以采用覆蓋索引來避免using where的出現(xiàn)，所以繼續(xù)調(diào)整索引：

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: 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í)行時(shí)間降低到了1.43秒：

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)

您可能感興趣的文章: