今天在线上发现一个慢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 wj WHERE o.wj_linkid = wj.
id
and u.user_id = wj.user_id AND 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 wj WHERE o.wj_linkid = wj.
id
and u.user_id = wj.user_id AND 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
on
o.wj_linkid = wj.id, eload_users
AS
u
WHERE
u.user_id = wj.user_id
AND
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
on
o.wj_linkid = wj.id, eload_users
AS
u
WHERE
u.user_id = wj.user_id
AND
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来确定连接顺序。
版权声明:原创作品,如需转载,请注明出处。否则将追究法律责任
本文转自 emma_cql 51CTO博客,原文链接:http://blog.51cto.com/chenql/1902445