Mysql多表关联查询优化--straight_jion
今天在线上发现一个慢sql:1
2
3
4
5
6
7
SELECT COUNT(o.order_sn) FROM eload_order_info AS o,eload_users AS u ,eload_wj_link AS wjWHERE o.wj_linkid = wj.idand u.user_id = wj.user_idAND o.pay_time >= '1488297600' AND o.pay_time <= '1488383940' ;
+-------------------+
| COUNT(o.order_sn) |
+-------------------+
| 1432 |
+-------------------+
1 row in set (8.03 sec)
我们来看一下执行计划：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
explain SELECT COUNT(o.order_sn) FROM eload_order_info AS o,eload_users AS u ,eload_wj_link AS wjWHERE o.wj_linkid = wj.idand u.user_id = wj.user_idAND o.pay_time >= '1488297600' AND o.pay_time <= '1488383940' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wj
type: index
possible_keys: PRIMARY,user_id
key: user_id
key_len: 4
ref: NULL
rows: 401244
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: o
type: ref
possible_keys: wj_linkid,wj_linkid_2
key: wj_linkid
key_len: 4
ref: rosewholesale_db.wj.id
rows: 14
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rosewholesale_db.wj.user_id
rows: 1
Extra: Using where; Using index
3 rows in set (0.00 sec)
我们来看一下这三个表的行数：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
>select count(*) from eload_order_info;
+----------+
| count(*) |
+----------+
|3258824 |
+----------+
1 row in set (2.42 sec)
>select count(*) from eload_users;
+----------+
| count(*) |
+----------+
|6505146 |
+----------+
1 row in set (3.00 sec)
>select count(*) from eload_wj_link;
+----------+
| count(*) |
+----------+
| 400153 |
+----------+
1 row in set (0.09 sec)
从执行计划我们可以看到mysql选择了eload_wj_link做为驱动表，如果我们指定eload_order_info作为驱动表的话可以pay_time的范围来过滤结果集。这时我们可以通过straight_jion来指定表连接的顺序。
对上面的sql进行改写后我们来看一下运行时间：
1
2
3
4
5
6
7
>SELECT COUNT(o.order_sn) FROM eload_order_info AS o straight_join eload_wj_link AS wj ono.wj_linkid = wj.id, eload_users AS u WHERE u.user_id = wj.user_idAND o.pay_time >= '1488297600' AND o.pay_time <= '1488383940' ;
+-------------------+
| COUNT(o.order_sn) |
+-------------------+
| 1512 |
+-------------------+
1 row in set (2.18 sec)
速度从原来的8.02s提升到2.18s,这个改善还是挺大的，我们再来看一下改造后的语句的执行计划：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
>explain SELECT COUNT(o.order_sn) FROM eload_order_info AS o straight_join eload_wj_link AS wj ono.wj_linkid = wj.id, eload_users AS u WHERE u.user_id = wj.user_idAND o.pay_time >= '1488297600' AND o.pay_time <= '1488383940' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: o
type: ALL
possible_keys: wj_linkid,wj_linkid_2
key: NULL
key_len: NULL
ref: NULL
rows: 3351498
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: wj
type: eq_ref
possible_keys: PRIMARY,user_id
key: PRIMARY
key_len: 4
ref: rosewholesale_db.o.wj_linkid
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rosewholesale_db.wj.user_id
rows: 1
Extra: Using where; Using index
3 rows in set (0.00 sec)
虽然扫描的行数多了，但是实际执行速度快了很多。当涉及到多表连接时如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
页:
[1]