注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

帅小伙的博客

希望能在这里交到更多的朋友

 
 
 

日志

 
 
 
 

mysql利用hint的一次调优  

2008-04-11 17:43:59|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

昨天,开发人员报告,说测试机上一个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进行了修改,让晦涩的语句变动更加简单、易读。

         

 

 

 

 

  评论这张
 
阅读(234)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018