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

最新推荐文章于 2024-07-24 20:02:00 发布

那些花儿.

最新推荐文章于 2024-07-24 20:02:00 发布

阅读量6.4k

点赞数 2

文章标签： mysql优化

先看一段sql：

SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
     rankUser.create_time DESC  
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，可以看到，这段不仅仅扫描全表，而且使用了临时表，进行了文件排序。

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

SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
-- ORDER BY  
    -- rankUser.create_time DESC  
LIMIT 10 OFFSET 0

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

SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN rank_user_level AS userLevel ON rankUser.id = userLevel.user_id  
-- LEFT JOIN rank_product AS product ON userLevel.new_level = product.level_id  
-- LEFT JOIN rank_product_fee AS fee ON userLevel.fee_id = fee.fee_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
    rankUser.create_time DESC  
LIMIT 10 OFFSET 0

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

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

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

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

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

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

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

第一种（子查询，适合子查询部分不作为查询条件）：

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

在这里插入图片描述
第二种（非直接关联转变成直接关联，这个要根据业务来定，我这里rank_product和rank_product_fee两张表只是为了查询rank_user_level表中的产品和产品费用信息，而rank_user_level是一张直接关联的表，故这里可以先将这三张表进行合并，然后再和rank_user表进行联合查询）：

SELECT  
    *  
FROM  
    rank_user AS rankUser  
LEFT JOIN (  
        select   
        l.*,p.product_name,f.period   
        from   
        rank_user_level l,rank_product p,rank_product_fee f  
        where   
        l.new_level = p.level_id   
        and l.fee_id = f.fee_id  
) AS userLevel ON rankUser.id = userLevel.user_id  
LEFT JOIN rank_user_login_stat AS userLoginInfo ON rankUser.id = userLoginInfo.user_id  
ORDER BY  
    rankUser.create_time DESC  
LIMIT 10 OFFSET 0

在这里插入图片描述