服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|

服务器之家 - 数据库 - Mysql - MySQL中一些优化straight_join技巧

MySQL中一些优化straight_join技巧

2020-05-05 16:49罗龙九 Mysql

这篇文章主要介绍了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如下：

?
1
2
3
4
5
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.查看执行计划：

?
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
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.查看索引：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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表优化采用覆盖索引：

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

5.查看执行计划：

?
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
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)

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

?
1
2
3
4
5
6
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强制连接顺序：

?
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
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秒；

?
1
2
3
4
5
6
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的出现，所以继续调整索引：

?
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
36
37
38
39
40
41
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秒：

?
1
2
3
4
5
6
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)
  • mysql
  • 优化
  • straight_join

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1862 　 1045 　 1406 　 1005 　 1130 　 10061 　 1067 　 2003 　 10048 　 1577 　 1665 　 1786 　 1175 　 2006 　 2013 　 1036 　 1146 　 1366 　 2002 　 1615 　 SQL文件 　 表优化 　 straight_join 　 winxp 　 拒绝服务漏洞 　 免安装版 　
    © 2019-2020 服务器之家 版权所有 www.zzvips.com 关于我们联系我们版权申明网站地图