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

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.查看执行计划：

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.调整索引，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_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)

调整后执行稍有效果，但是还不明显，还没有找到要害：

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.执行时间仍然需要很长，时间的消耗主要耗费在using filesort中，参与排序的数据量有38w之多，所以需要转换驱动表；尝试采用user表做驱动表：使用straight_join强制连接顺序：

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)

执行时间已经有了质的变化，降低到了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.在分析执行计划的第一步：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: 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.执行时间降低到了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)