mysql 多表查询语句优化

思路:

1、explain定位执行效率差的SQL找出问题所在

2、对症下药

解决方法:

1、left join on 

on的字段建外键、索引

2、left join改成join

示例:

select c.* from hotel_info_original c 
left join hotel_info_collection h 
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id 
where h.hotel_id is null 

一般情况下参与联合查询的两张表都会一大一小,如果是join,在没有其他过滤条件的情况下MySQL会选择小表作为驱动表,但是left join一般用作大表去join小表,而left join本身的特性决定了MySQL会用大表去做驱动表,这样下来效率就差了不少

如果我把上面那个sql改成 

select c.* from hotel_info_original c 
join hotel_info_collection h 
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id 

很明显,MySQL选择了小表作为驱动表,再配合(hotel_id,hotel_type)上的索引瞬间降低了好多个数量级。
一个关于left join 的通用法则,即:如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句。 

翻看了一遍MySQL官方文档,关于优化查询的部分,看到了这样一句:这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。我感觉我找到了问题所在,于是我将original和 collection表的(hotel_type,hotel_id)的encoding和collation(决定字符比较的规则)全部改成统一的utf8_general_ci,然后再次运行第一条sql的查询计划,得到如下结果: 



    连接类型已经由index优化到了ref,如果将hotel_type申明为not null可以优化到eq_ref,不过这里影响不大了,优化后这条sql能在0.01ms内运行完。 

     那么如何优化left join: 
1、条件中尽量能够过滤一些行将驱动表变得小一点,用小表去驱动大表 

2、右表的条件列一定要加上索引(主键、唯一索引、前缀索引等),最好能够使type达到range及以上(ref,eq_ref,const,system) 

3、无视以上两点,一般不要用left join!

 

 

  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

We_Join

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值