mysql查询优化--临时表和文件排序(Using temporary; Using filesort问题解决)

先看一段sql:

  1. <span style="font-size:18px;">SELECT  
  2.     *  
  3. FROM  
  4.     rank_user AS rankUser  
  5. LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
  6. LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
  7. LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
  8. LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
  9. ORDER BY  
  10.      rankUser.create_time DESC  
  11. LIMIT 10 OFFSET 0</span>  



介绍一下这段sql的表的构成:一张主表:rank_user;两张跟rank_user直接关联(多张表通过同一字段最好是主键进行关联)的表:rank_user_level ,rank_user_login_stat ;两张跟rank_user非直接关联的表:rank_product ,rank_product_fee 。这段sql看似简单,但是执行时间却很长,我们来看一下执行计划:


执行时间1.45s,可以看到,这段不仅仅扫描全表,而且使用了临时表,进行了文件排序。

为了找到原因,我们把排序去掉看一下:

  1. SELECT  
  2.     *  
  3. FROM  
  4.     rank_user AS rankUser  
  5. LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
  6. LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
  7. LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
  8. LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
  9. -- ORDER BY  
  10.     -- rankUser.create_time DESC  
  11. LIMIT 10 OFFSET 0  



执行时间0.015s,扫描行数67452,果然是排序惹的祸。但是仅仅是排序惹的祸吗?别忘了这里有两张非直接关联的表,这样的查询,如果有查询条件或者排序分组的时候往往都需要创建临时表(这个没有办法,想想也知道)。为了验证这个观点,我们把两张非直接关联的表去掉看一下:

  1. SELECT  
  2.     *  
  3. FROM  
  4.     rank_user AS rankUser  
  5. LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
  6. -- LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
  7. -- LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
  8. LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
  9. ORDER BY  
  10.     rankUser.create_time DESC  
  11. LIMIT 10 OFFSET 0  

执行时间0.003s,扫描行数10,屌爆了有木有,mysql多表直接关联在没有其他筛选条件的情况下,查询速度大大提升,而且排序可以使用create_time这个索引,直接取到前十条。


到了这里,我想大家应该已经明白第一条sql查询时间很长的原因了:多表非直接关联的前提下还要排序。mysql查询往往最需要优化的地方就是临时表和文件排序了。这里总结一下教训:


1.mysql查询存在直接关联和非直接关联的问题,这两种查询效率差别很大;

2.mysql排序尽量使用索引;

3.mysql多表关联left join其他表的时候,如果以其他表的字段作为查询条件都会产生临时表;

4.mysql在非直接关联的基础上进行排序会很慢,需要进行优化;



知道了问题,我们就好优化了,这里我给出了两种方案:

第一种(子查询,适合子查询部分不作为查询条件):

  1. SELECT  
  2.             rankUser.id, rankUser.qq, rankUser.phone, rankUser.regip, rankUser.channel, rankUser.create_time, rankUser.qudao_key, rankUser.qq_openid, rankUser.wechat_openid,  
  3.             userLevel.recommend_count,userLevel.end_time,userLevel.new_level,userLevel.`level`,userLevel.new_recommend_count,userLevel.`is_limited`,  
  4.             (case when userLevel.new_level > 1 then 1 else 0 end) is_official_user,  
  5.             (select product_name from rank_product where level_id = userLevel.new_level) product_name,  
  6.             (select period from rank_product_fee where fee_id = userLevel.fee_id) period,  
  7.             userLoginInfo.last_login, userLoginInfo.login_count, userLoginInfo.login_seconds  
  8.         FROM rank_user AS rankUser  
  9.         LEFT JOIN rank_user_level as userLevel on userLevel.user_id=rankUser.id  
  10.         LEFT JOIN rank_user_login_stat as userLoginInfo ON rankUser.id = userLoginInfo.user_id  
  11. ORDER BY  
  12.     rankUser.create_time DESC  
  13. LIMIT 10 OFFSET 0  


第二种(非直接关联转变成直接关联,这个要根据业务来定,我这里rank_product和rank_product_fee两张表只是为了查询rank_user_level表中的产品和产品费用信息,而rank_user_level是一张直接关联的表,故这里可以先将这三张表进行合并,然后再和rank_user表进行联合查询):

  1. SELECT  
  2.     *  
  3. FROM  
  4.     rank_user AS rankUser  
  5. LEFT JOIN (  
  6.         select   
  7.         l.*,p.product_name,f.period   
  8.         from   
  9.         rank_user_level l,rank_product p,rank_product_fee f  
  10.         where   
  11.         l.new_level = p.level_id   
  12.         and l.fee_id = f.fee_id  
  13. AS userLevel ON rankUser.id = userLevel.user_id  
  14. LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
  15. ORDER BY  
  16.     rankUser.create_time DESC  
  17. LIMIT 10 OFFSET 0  



posted @ 2018-06-08 16:40  星朝  阅读(11676)  评论(2编辑  收藏  举报