MySQL查询优化-内部查询 [英] MySQL Query Optimization - inner queries

问题描述

这是整个查询...

SELECT s.*, (SELECT url FROM show_medias WHERE show_id = s.id AND is_primary = 1) AS media_url
FROM (shows As s)
WHERE `s`.`id` IN (
 SELECT DISTINCT st.show_id
 FROM show_time_schedules AS sts
 LEFT JOIN show_times AS st ON st.id = sts.show_time_id
 WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)
 )
AND `s`.`is_active` = 1
ORDER BY s.name asc 

如果...

SELECT url FROM show_medias WHERE show_id = s.id AND is_primary = 1
(0.0004 sec)

然后...

 SELECT DISTINCT st.show_id
 FROM show_time_schedules AS sts
 LEFT JOIN show_times AS st ON st.id = sts.show_time_id
 WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)
(0.0061 sec)

有明显的原因吗?...

Is there an obvious reason....

SELECT s.*, (inner query 1) AS media_url
FROM (shows As s)
WHERE `s`.`id` IN ( inner query 2 )
AND `s`.`is_active` = 1
ORDER BY s.name asc

正在服用5.7245 sec?

扩展扩展

id  select_type         table       type    possible_keys   key     key_len ref                     rows    filtered    Extra
1   PRIMARY             s           ALL     NULL            NULL    NULL    NULL                    151     100.00      Using where; Using filesort
3   DEPENDENT SUBQUERY  sts         ALL     NULL            NULL    NULL    NULL                    26290   100.00      Using where; Using temporary
3   DEPENDENT SUBQUERY  st          eq_ref  PRIMARY         PRIMARY 4       bvcdb.sts.show_time_id  1       100.00      Using where
2   DEPENDENT SUBQUERY  show_medias ALL     NULL            NULL    NULL    NULL                    159     100.00      Using where

推荐答案

您始终可以使用用EXPLAIN或EXPLAIN EXTENDED 来查看MySql对查询的作用

You can always use EXPLAIN or EXPLAIN EXTENDED to see what MySql is doing with a query

您还可以使用稍微不同的方式编写查询，是否尝试过以下方法?

You could also write your query a slightly different way, have you tried the following?

SELECT        s.*, 
              sm.url AS media_url 
FROM          shows AS s
INNER JOIN    show_medias AS sm ON s.id = SM.show_id
WHERE `s`.`id` IN ( 
                        SELECT DISTINCT st.show_id 
                        FROM show_time_schedules AS sts 
                        LEFT JOIN show_times AS st ON st.id = sts.show_time_id 
                        WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date) 
                        ) 
AND            `s`.`is_active` = 1 
AND            sm.is_primary = 1
ORDER BY       s.name asc 

看看这会产生什么效果会很有趣.我希望它会更快，因为目前，我认为MySql将为您拥有的每个节目运行内部查询1(这样一个查询将运行多次.联接应该更有效.)

It would be interesting to see what the effect of that is. I would expect it to be faster as, at the moment, I think MySql will be running inner query 1 for each show you have (so that one query will be run many times. A join should be more efficient.)

如果要在show_medias中没有所有行的所有节目，请用LEFT JOIN替换INNER JOIN.

Replace the INNER JOIN with a LEFT JOIN if you want all shows that don't have a row in show_medias.

我将很快查看您的扩展说明，我也想知道您是否想尝试以下方法?它将删除所有子查询:

I'll take a look at your EXPLAIN EXTENDED shortly, I also wonder if you want to try the following; it removes all of the subqueries:

SELECT        DISTINCT s.*,  
                       sm.url AS media_url  
FROM                   shows AS s 
INNER JOIN             show_medias AS sm ON s.id = SM.show_id
INNER JOIN             show_times AS st ON (s.id = st.show_id)
RIGHT JOIN             show_time_schedules AS sts ON (st.id = sts.show_time_id)

WHERE                  `s`.`is_active` = 1  
AND                    sm.is_primary = 1 
AND                    sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date)  
ORDER BY               s.name asc 

(也可以在上面看到扩展的解释-您可以将其添加到对此的评论中.)

(It would also be good to see the EXPLAIN EXTENDED on these - you could add it to the comments for this one).

进一步

在您的扩展说明上(关于如何阅读的良好开始这些在这里)

USING FILESORT和USING TEMPORARY都是关键指标.希望我建议的第二个查询应该删除任何TEMPORARY表(在子查询中).然后尝试关闭"ORDER BY"以查看是否有区别(到目前为止，我们可以将其添加到发现中:-)

The USING FILESORT and USING TEMPORARY are both key indicators. Hopefully, the second query I recommend should remove any TEMPORARY tables (in the subquery). Try then leaving the ORDER BY off to see if that makes a difference (and we can add that to the findings so far :-)

我还可以看到很多索引查找可能丢失了该查询；您所有的id列都是索引匹配的主要候选对象(通常使用索引警告).我还尝试添加这些索引，然后再次运行EXPLAIN EXTENDED来查看现在的区别(编辑，正如我们从您上面的评论中已经知道的一样！)

I can also see that the query is potentially missing out on a lot of index lookups; all of your id columns are prime candidates for index matches (with the usual index caveats). I'd also try adding those indexes and then running EXPLAIN EXTENDED again to see what the difference is now (EDIT as we already know from your comment above!)

这篇关于MySQL查询优化-内部查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！