ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【MySQL】性能优化之 straight_join

【MySQL】性能优化之 straight_join

原创 Linux操作系统 作者:杨奇龙 时间:2013-09-08 03:30:20 0 删除 编辑
   研究过或者熟悉oracle性能调优的朋友都知道oracle 提供很多hint 指定from 后的表的连接顺序,如use_hash  ordered ,leading 等,而MySQL 对表的连接只支持 nested loop Join, 提供的表连接驱动的hint 只有--straight_join(相当于Oracle里面的use_nl).其语法如下:
  select ..from  tab1  straiht_join  tab2 where ...
straight_join 实际上与内连接 inner join 表现完全一致,不同的是使用了 straight_join 后,tab1 会先于 tab2 载入。
   MySQL 在执行 inner join 的时候,会根据自己内部的优化规则来决定先载入 tab1 还是 tab2,如果您确认 MySQL 载入表的顺序并不是最优化的时候,就可以使用 straight_join 以替代 inner join,来强制MySQL 选择指定的执行顺序。
【现象】
生产环境中遇到一个例子,执行sql需要1.29s 已经超出业务方的要求,需要进行优化,sql 如下

select  d.instance_no,d.zone_id, d.region_no,d.user_id,d.cores,d.mem,d.disk, d.tx_pub , u.idkp , m.image_no, m.platform, m.image_size
from  user u, instance d , image m
where d.region_no = 'cn-cm9002' and
       u.user_id = d.user_id and
       d.image_id = m.image_id and
       d.status != 8 and 
       d.gmt_create <'2013-08-19 14:00:00';

hy@3309 03:09:09>explain select  d.instance_no,d.zone_id, d.region_no,d.user_id,d.cores,d.mem,d.disk, d.tx_pub , u.idkp , m.image_no, m.platform, m.image_size
    -> from  user u, instance d , image m
    -> where d.region_no = 'cn-cm9002' and
    ->        u.user_id = d.user_id and
    ->        d.image_id = m.image_id and
    ->        d.status != 8 and 
    ->        d.gmt_create <'2013-08-19 14:00:00';
+----+-------------+-------+--------+-------------------------------------+--------------------+---------+------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                       | key                | key_len | ref              | rows   | Extra       |
+----+-------------+-------+--------+-------------------------------------+--------------------+---------+------------------+--------+-------------+
|  1 | SIMPLE      | u     | index  | PRIMARY                             | idkp        | 98      | NULL             | 133002 | Using index |
|  1 | SIMPLE      | d     | ref    | image_id,ind_i_uid_hostname,user_id | ind_i_uid_hostname | 4       | hy.u.user_id  |      1 | Using where |
|  1 | SIMPLE      | m     | eq_ref | PRIMARY                             | PRIMARY            | 4       | hy.d.image_id |      1 |             |
+----+-------------+-------+--------+-------------------------------------+--------------------+---------+------------------+--------+-------------+
3 rows in set (0.00 sec)

【解决方法】
使用 straight_join 方式优化sql 执行的顺序 结果如下:
rac1@3309 15:01:55>explain select  d.instance_no,d.zone_id, d.region_no,d.user_id,d.cores,d.mem,d.disk, d.tx_pub , u.idkp , m.image_no, m.platform, m.image_size
    ->       from instance d straight_join  user u on u.user_id = d.user_id,  image m
    ->       where d.region_no = 'cn-cm9002' and
    ->             d.image_id = m.image_id and
    ->             d.status != 8 and 
    ->             d.gmt_create <'2013-08-19 14:00:00';
+----+-------------+-------+--------+-------------------------------------+---------+---------+------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                       | key     | key_len | ref              | rows   | Extra       |
+----+-------------+-------+--------+-------------------------------------+---------+---------+------------------+--------+-------------+
|  1 | SIMPLE      | d     | ALL    | image_id,ind_i_uid_hostname,user_id | NULL    | NULL    | NULL             | 316473 | Using where |
|  1 | SIMPLE      | m    | eq_ref | PRIMARY                             | PRIMARY | 4       | hy.d.image_id |      1 |             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                             | PRIMARY | 4       | hy.d.user_id  |      1 |             |
+----+-------------+-------+--------+-------------------------------------+---------+---------+------------------+--------+-------------+
3 rows in set (0.00 sec)

新的sql执行结果:
 

【问题分析】
上面的介绍中描述mysql的优化器只支持 nest loop ,对于多表连接会mysql优化器采用了简单的方式:选择结果集小的表作为驱动表。
instance表连接 user表有两种连接方式:
A 选择user 表作为驱动表 优化器扫描133002行
B 选择instance表作为驱动表 优化器扫描  316473行

因此 优化器选择了看起来正确的执行计划 以user表作为驱动表。但是 我们查看where条件,正确的应该是 通过instance 的region_no,status ,gmt_create 过滤得到instance的结果集,再来和user,image中的表进行关联。
而执行计划是扫描user表中全部的记录再去关联instance 表和image表,显然执行顺序有 偏差。因此加上straight_join hint 之后,强制优化器选择 instance为驱动表,按照正确的执行计划执行。
 
附上表的记录数:
hy@3309 01:11:17> select count(*) from user;
+----------+
| count(*) |
+----------+
|   134221 |
+----------+
1 row in set (0.02 sec)

hy@3309 01:19:44> select count(*) from instance;
+----------+
| count(*) |
+----------+
|   375732 |
+----------+
1 row in set (0.06 sec)

hy@3309 01:19:54> select count(*) from image;   
+----------+
| count(*) |
+----------+
|    18858 |
+----------+
1 row in set (0.00 sec)

139.JPG

039.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-772357/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7627798