mysql sql优化之straight

  • 时间:
  • 浏览:1
  • 来源:彩神3D_神彩3D官方

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: 422006 Extra: Using where; Using temporary; Using filesort

1.用户实例:spxxxxxx的第根小sql执行非常的缓慢,sql如下:

73871 | root            | 127.0.0.1:49665     | user_app_test   | Query       |     2000 | 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)

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)

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)

调整后执行稍有效果,而且还不明显,还没有 找到要害:

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)

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 ***************************

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

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

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         |      200670 |     NULL | NULL   |      | BTREE      |         |

| test_log |          0 | ix_test_log_unique_ |            5 | serial      | A         |      200670 |     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      |         |

+————–+————+————————-+————–+————-+———–+————-+———-+——–+——+——-+

在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进行优化的案例: