Home > database, sql优化 > mysql sql优化之straight_join

mysql sql优化之straight_join

January 17th, 2013 Leave a comment Go to comments

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

Categories: database, sql优化 Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.