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如下：

2.查看执行计划：

12345678910111213141516171819202122232425262728293031 mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM test_log a,USER bWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP 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: 1select_type: SIMPLEtable: atype: ALLpossible_keys: ix_test_log_useridkey: NULLkey_len: NULLref: NULLrows: 416782Extra: Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: btype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 96ref: user_app_testnew.a.useridrows: 1Extra: Using where2 rows in set (0.00 sec)

3.查看索引：

123456789101112131415 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.查看执行计划：

123456789101112131415161718192021222324252627 mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM test_log a,USER bWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime)\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: atype: indexpossible_keys: ix_test_log_useridkey: ix_test_log_useridkey_len: 105ref: NULLrows: 388451Extra: Using index; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: btype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 96ref: user_app_test.a.useridrows: 1Extra: Using where2 rows in set (0.00 sec)

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

123456 SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM test_log a,USER bWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);……………….143 rows in set (1 min 12.62 sec)

6.执行时间仍然需要很长，时间的消耗主要耗费在Using filesort中，参与排序的数据量有38W之多，所以需要转换驱动表；尝试采用user表做驱动表：使用straight_join强制连接顺序：

123456789101112131415161718192021222324252627 mysql> explain SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime)\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: btype: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 42806Extra: Using where; Using temporary; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: atype: refpossible_keys: ix_test_log_useridkey: ix_test_log_useridkey_len: 96ref: user_app_test.b.useridrows: 38Extra: Using index2 rows in set (0.00 sec)

执行时间已经有了质的变化，降低到了2.56秒；

123456 mysql>SELECT DATE(practicetime) date_time,COUNT(DISTINCT a.userid) people_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);……..143 rows in set (2.56 sec)

7.在分析执行计划的第一步：Using where; Using temporary; Using filesort，user表其实也可以采用覆盖索引来避免using where的出现，所以继续调整索引：

1234567891011121314151617181920212223242526272829303132333435363738394041 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_rowsFROM USER b straight_join test_log aWHERE a.userid=b.userid AND b.isfree=0 AND LENGTH(b.username)>4GROUP BY DATE(practicetime);*************************** 1. row ***************************id: 1select_type: SIMPLEtable: btype: indexpossible_keys: PRIMARYkey: ix_user_usernamekey_len: 125ref: NULLrows: 42466Extra: Using where; Using index; Using temporary; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: atype: refpossible_keys: ix_test_log_useridkey: ix_test_log_useridkey_len: 96ref: user_app_test.b.useridrows: 38Extra: Using index2 rows in set (0.00 sec)

8.执行时间降低到了1.43秒：