昨天，开发人员报告，说测试机上一个sql突然变得很慢，sql如下：
select order0_.id as x0_0_
from order_tab order0_,
order_record_tab orderrecor1_,
order_factory_tab orderfacto2_,
 relate_order_tab relateorde3_
where (order0_.id=orderrecor1_.order_id )
          and(orderrecor1_.id=orderfacto2_.order_record_id )
          and(relateorde3_.order_id=order0_.id )
          and(orderfacto2_.id in(174028 , 174029))
          and((relateorde3_.relate_internet_order_id is not null ))
          and(relateorde3_.relate_internet_order_id not in
          (select order4_.internet_order_id
             from order_tab order4_,
                     order_record_tab orderrecor5_,
                     order_factory_tab orderfacto6_,
                     relate_order_tab relateorde7_
              where (order4_.id=orderrecor5_.order_id )
                       and(orderrecor5_.id=orderfacto6_.order_record_id )
                       and(relateorde7_.order_id=order4_.id )
                       and(orderfacto6_.id in(174028 , 174029))
                       and((relateorde7_.relate_internet_order_id is not null )
                )
              )
           );
   此sql故障时的执行计划如下：
+----+--------------------+--------------+-------------+------------------------------------------------------------------------------+-----------------------------+---------+----------------------------------------+------+-------------+
| id | select_type        | table        | type        | possible_keys                                                                | key                         | key_len | ref                                    | rows | Extra       |
+----+--------------------+--------------+-------------+------------------------------------------------------------------------------+-----------------------------+---------+----------------------------------------+------+-------------+
|  1 | PRIMARY            | orderfacto2_ | range       | PRIMARY,ORDER_RECORD_ID                                                      | PRIMARY                     | 4       | NULL                                   |    2 | Using where |
|  1 | PRIMARY            | orderrecor1_ | eq_ref      | PRIMARY,ORDER_ID                                                             | PRIMARY                     | 4       | pointcard.orderfacto2_.ORDER_RECORD_ID |    1 |             |
|  1 | PRIMARY            | order0_      | eq_ref      | PRIMARY                                                                      | PRIMARY                     | 4       | pointcard.orderrecor1_.ORDER_ID        |    1 | Using index |
|  1 | PRIMARY            | relateorde3_ | ref         | ORDER_ID,relate_order_tab_relate_internet_order_id,relate_order_tab_order_id | relate_order_tab_order_id   | 4       | pointcard.orderrecor1_.ORDER_ID        |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | order4_      | ref_or_null | PRIMARY,idx_internet_order_id,order_tab_internet_order_id                    | order_tab_internet_order_id | 5       | func                                   |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | relateorde7_ | ref         | ORDER_ID,relate_order_tab_relate_internet_order_id,relate_order_tab_order_id | relate_order_tab_order_id   | 4       | pointcard.order4_.ID                   |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | orderrecor5_ | ref         | PRIMARY,ORDER_ID                                                             | ORDER_ID                    | 4       | pointcard.order4_.ID                   |    1 |             |
|  2 | DEPENDENT SUBQUERY | orderfacto6_ | ref         | PRIMARY,ORDER_RECORD_ID                                                      | ORDER_RECORD_ID             | 4       | pointcard.orderrecor5_.ID              |    1 | Using where |
+----+--------------------+--------------+-------------+------------------------------------------------------------------------------+-----------------------------+---------+----------------------------------------+------+-------------+
很显然，这是一个用hibernate生成的sql语句，执行计划显示格式比较乱。
其中，在主查询和子查询中所涉及的4个表记录数如下：
mysql> select count(1) from  order_tab order0_;
+----------+
| count(1) |
+----------+
| 30737230 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from  order_record_tab orderrecor1_;
+----------+
| count(1) |
+----------+
| 31298527 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from  order_factory_tab orderfacto2_;
+----------+
| count(1) |
+----------+
|   155895 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from  relate_order_tab relateorde3_;
+----------+
| count(1) |
+----------+
|     9397 |
+----------+
1 row in set (0.00 sec)
 
按照sql的执行计划，主查询中先对orderfacto2_做范围扫描（range），然后和 orderrecor1_、order0_分别做等值连接（eq_ref），最后再和 relateorde3_做ref连接，这个过程是没有问题的。而子查询和主查询是完全系统的，但是子查询显然和主查询的执行计划不同，这点令人疑惑。在 生产机上此语句的执行计划如下：
+----+--------------------+--------------+--------+------------------------------------------------------------------------------+---------------------------+---------+----------------------------------------+------+-------------+
| id | select_type        | table        | type   | possible_keys                                                                | key                       | key_len | ref                                    | rows | Extra       |
+----+--------------------+--------------+--------+------------------------------------------------------------------------------+---------------------------+---------+----------------------------------------+------+-------------+
|  1 | PRIMARY            | orderfacto2_ | range  | PRIMARY,ORDER_RECORD_ID                                                      | PRIMARY                   | 4       | NULL                                   |    2 | Using where |
|  1 | PRIMARY            | orderrecor1_ | eq_ref | PRIMARY,ORDER_ID                                                             | PRIMARY                   | 4       | pointcard.orderfacto2_.ORDER_RECORD_ID |    1 |             |
|  1 | PRIMARY            | order0_      | eq_ref | PRIMARY                                                                      | PRIMARY                   | 4       | pointcard.orderrecor1_.ORDER_ID        |    1 | Using index |
|  1 | PRIMARY            | relateorde3_ | ref    | ORDER_ID,relate_order_tab_relate_internet_order_id,relate_order_tab_order_id | relate_order_tab_order_id | 4       | pointcard.orderrecor1_.ORDER_ID        |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | orderfacto6_ | range  | PRIMARY,ORDER_RECORD_ID                                                      | PRIMARY                   | 4       | NULL                                   |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | orderrecor5_ | eq_ref | PRIMARY,ORDER_ID                                                             | PRIMARY                   | 4       | pointcard.orderfacto6_.ORDER_RECORD_ID |    1 |             |
|  2 | DEPENDENT SUBQUERY | order4_      | eq_ref | PRIMARY,idx_internet_order_id,order_tab_internet_order_id                    | PRIMARY                   | 4       | pointcard.orderrecor5_.ORDER_ID        |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | relateorde7_ | ref    | ORDER_ID,relate_order_tab_relate_internet_order_id,relate_order_tab_order_id | relate_order_tab_order_id | 4       | pointcard.order4_.ID                   |    1 | Using where |
+----+--------------------+--------------+--------+------------------------------------------------------------------------------+---------------------------+---------+----------------------------------------+------+-------------+
8 rows in set (0.03 sec)
 
和我们意料的一样，子查询的执行计划和主查询完全相同，速度也很快。怀疑测试机上的表数据统计信息是否没有更新，但是由于analyze时间很长，此步骤暂时不能做，想到的解决办法如下：
1、使用straight join按照主查询的连接查询进行连接；
2、让开发人员修改应用，将查询分为两步，先做子查询，得到结果后再做主查询；
3、和2想法类似，不过使用临时表存储子查询结果，再做主查询。
显然，方法1是解决问题最快的方法，我们改写后的sql如下：
select order0_.id as x0_0_
from order_tab order0_,
order_record_tab orderrecor1_,
order_factory_tab orderfacto2_,
 relate_order_tab relateorde3_
where (order0_.id=orderrecor1_.order_id )
          and(orderrecor1_.id=orderfacto2_.order_record_id )
          and(relateorde3_.order_id=order0_.id )
          and(orderfacto2_.id in(174028 , 174029))
          and((relateorde3_.relate_internet_order_id is not null ))
          and(relateorde3_.relate_internet_order_id not in
(select order4_.internet_order_id
from
order_factory_tab orderfacto6_
STRAIGHT_JOIN
order_record_tab orderrecor5_
STRAIGHT_JOIN
order_tab order4_
STRAIGHT_JOIN
relate_order_tab relateorde7_
where (order4_.id=orderrecor5_.order_id )and(orderrecor5_.id=orderfacto6_.order_record_id )and(relateorde7_.order_id=order4_.id )and(orderfacto6_.id in(174028 , 174029))and((relateorde7_.relate_internet_order_id is not null ))));
执行计划变为：
+----+--------------------+--------------+--------+------------------------------------------------------------------------------+---------------------------+---------+----------------------------------------+------+-------------+
| id | select_type        | table        | type   | possible_keys                                                                | key                       | key_len | ref                                    | rows | Extra       |
+----+--------------------+--------------+--------+------------------------------------------------------------------------------+---------------------------+---------+----------------------------------------+------+-------------+
|  1 | PRIMARY            | orderfacto2_ | range  | PRIMARY,ORDER_RECORD_ID                                                      | PRIMARY                   | 4       | NULL                                   |    2 | Using where |
|  1 | PRIMARY            | orderrecor1_ | eq_ref | PRIMARY,ORDER_ID                                                             | PRIMARY                   | 4       | pointcard.orderfacto2_.ORDER_RECORD_ID |    1 |             |
|  1 | PRIMARY            | order0_      | eq_ref | PRIMARY                                                                      | PRIMARY                   | 4       | pointcard.orderrecor1_.ORDER_ID        |    1 | Using index |
|  1 | PRIMARY            | relateorde3_ | ref    | ORDER_ID,relate_order_tab_relate_internet_order_id,relate_order_tab_order_id | relate_order_tab_order_id | 4       | pointcard.orderrecor1_.ORDER_ID        |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | orderfacto6_ | range  | PRIMARY,ORDER_RECORD_ID                                                      | PRIMARY                   | 4       | NULL                                   |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | orderrecor5_ | eq_ref | PRIMARY,ORDER_ID                                                             | PRIMARY                   | 4       | pointcard.orderfacto6_.ORDER_RECORD_ID |    1 |             |
|  2 | DEPENDENT SUBQUERY | order4_      | eq_ref | PRIMARY,idx_internet_order_id,order_tab_internet_order_id                    | PRIMARY                   | 4       | pointcard.orderrecor5_.ORDER_ID        |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | relateorde7_ | ref    | ORDER_ID,relate_order_tab_relate_internet_order_id,relate_order_tab_order_id | relate_order_tab_order_id | 4       | pointcard.order4_.ID                   |    1 | Using where |
+----+--------------------+--------------+--------+------------------------------------------------------------------------------+---------------------------+---------+----------------------------------------+------+-------------+
8 rows in set (0.00 sec)
速度也随之变得飞快。
不过，最后应用还是按方法2进行了修改，让晦涩的语句变动更加简单、易读。