mysql group by using filesort优化

1. 原join 连接语句

   SELECT 
   
   SUM(video_flowers.number) AS num,
   
   video_flowers.flower_id,
   
   flowers.title,
   
   flowers.image
   
   FROM
   
   `video_flowers`
   
       JOIN
   
   `flowers` ON `video_flowers`.`flower_id` = `flowers`.`id`
   
       JOIN
   
   `video_posts` ON `video_flowers`.`video_post_id` = `video_posts`.`id`
   
   WHERE
   
   `video_posts`.`user_id` = 36
   
   GROUP BY `video_flowers`.`flower_id`

可以优化成

SELECT 

    vf.num, flowers.title, flowers.image

FROM

    `flowers`


    <span class="hljs-keyword">join</span></br>
(<span class="hljs-keyword">SELECT</span> </br>
    <span class="hljs-keyword">SUM</span>(video_flowers.<span class="hljs-built_in">number</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">num</span>, video_flowers.flower_id, video_flowers.video_post_id
<span class="hljs-keyword">FROM</span></br>
    video_flowers </br>
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-string">`video_flowers`</span>.<span class="hljs-string">`flower_id`</span>) <span class="hljs-keyword">AS</span> vf <span class="hljs-keyword">ON</span> <span class="hljs-string">`vf`</span>.<span class="hljs-string">`flower_id`</span> = <span class="hljs-string">`flowers`</span>.<span class="hljs-string">`id`</span></br>
<span class="hljs-keyword">join</span> <span class="hljs-string">`video_posts`</span> <span class="hljs-keyword">on</span> <span class="hljs-string">`video_posts`</span>.<span class="hljs-string">`id`</span> = vf.<span class="hljs-string">`video_post_id`</span></br>
<span class="hljs-keyword">where</span> video_posts.user_id = <span class="hljs-number">36</span>;</code></pre>

这样就没有using filesort 和using temporary