MySQL中一些优化straight_join技巧
在oracle中可以指定的表连接的hint有很多：orderedhint指示oracle按照from关键字后的表顺序来进行连接；leadinghint指示查询优化器使用指定的表作为连接的首表，即驱动表；use_nlhint指示查询优化器使用nestedloops方式连接指定表和其他行源，并且将强制指定表作为inner表。
在mysql中就有之对应的straight_join,由于mysql只支持nestedloops的连接方式，所以这里的straight_join类似oracle中的use_nlhint。mysql优化器在处理多表的关联的时候，很有可能会选择错误的驱动表进行关联，导致了关联次数的增加，从而使得sql语句执行变得非常的缓慢，这个时候需要有经验的DBA进行判断，选择正确的驱动表，这个时候straight_join就起了作用了，下面我们来看一看使用straight_join进行优化的案例：
1．用户实例：spxxxxxx的一条sql执行非常的缓慢，sql如下：
73871|root|127.0.0.1:49665|user_app_test|Query|500|Sortingresult| SELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMtest_loga,USERb WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(practicetime)
2.查看执行计划：
mysql>explainSELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMtest_loga,USERb WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(practicetime); mysql>explainSELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows ->FROMtest_loga,USERb ->WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 ->GROUPBYDATE(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:Usingfilesort ***************************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:Usingwhere 2rowsinset(0.00sec)
3.查看索引：
mysql>showindexfromtest_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>altertabletest_logdropindexix_test_log_userid,addindexix_test_log_userid(userid,practicetime)
5.查看执行计划：
mysql>explainSELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMtest_loga,USERb WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(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:Usingindex;Usingfilesort ***************************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:Usingwhere 2rowsinset(0.00sec)
调整后执行稍有效果，但是还不明显，还没有找到要害：
SELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMtest_loga,USERb WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(practicetime); ………………. 143rowsinset(1min12.62sec)
6.执行时间仍然需要很长，时间的消耗主要耗费在Usingfilesort中，参与排序的数据量有38W之多，所以需要转换驱动表；尝试采用user表做驱动表：使用straight_join强制连接顺序：
mysql>explainSELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMUSERbstraight_jointest_loga WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(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:Usingwhere;Usingtemporary;Usingfilesort ***************************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:Usingindex 2rowsinset(0.00sec)
执行时间已经有了质的变化，降低到了2.56秒；
mysql>SELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMUSERbstraight_jointest_loga WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(practicetime); …….. 143rowsinset(2.56sec)
7.在分析执行计划的第一步：Usingwhere;Usingtemporary;Usingfilesort，user表其实也可以采用覆盖索引来避免usingwhere的出现，所以继续调整索引：
mysql>showindexfromuser; +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ |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|| +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ 3rowsinset(0.01sec) mysql>altertableuserdropindexix_user_username,addindexix_user_username(username,isfree); QueryOK,42722rowsaffected(0.73sec) Records:42722Duplicates:0Warnings:0 mysql>explainSELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMUSERbstraight_jointest_loga WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(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:Usingwhere;Usingindex;Usingtemporary;Usingfilesort ***************************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:Usingindex 2rowsinset(0.00sec)
8.执行时间降低到了1.43秒：
mysql>SELECTDATE(practicetime)date_time,COUNT(DISTINCTa.userid)people_rows FROMUSERbstraight_jointest_loga WHEREa.userid=b.useridANDb.isfree=0ANDLENGTH(b.username)>4 GROUPBYDATE(practicetime); 。。。。。。。 143rowsinset(1.43sec)