如何改进SQL查询w/联接？

Question

我使用的是MySQL 5.5。我有三张桌子:新闻，NewsSubTopic，SubTopic。我想知道为什么这个查询很慢：

SELECT
  DISTINCT COUNT(news0_.id) as col_0_0_ 
FROM
  news news0_
INNER JOIN
  news_sub_topic subtopics1_ 
ON
  news0_.id = subtopics1_.news_sub_topics_id
INNER JOIN
  sub_topic subtopic2_ 
ON
  subtopics1_.sub_topic_id = subtopic2_.id 
WHERE
  (subtopic2_.id IN (55 , 134 , 135 , 52 , 53 , 32 , 54))
  AND news0_.type = 'api';

我已经在news.type中放置了索引键。可以在NewsSubTopic中添加密钥吗？

下面是一些统计数据: News表有1,088,126条条目，NewsSubTopic有823,247条条目，SubTopic有168。

注意，该查询是由Grails (或Hibernate)生成的。请解释一下如何调试查询，并理解它为什么慢(3-5秒)吗？还有其他方法来改进它(我可以提供您需要的其他信息)。

Answer 1

您不需要DISTINCT、COUNT的参数或对sub_topic的引用

SELECT  COUNT(*)
FROM    news_sub_topic ns
JOIN    news n
ON      n.id = ns.news_sub_topics_id
WHERE   ns.sub_topic_id IN (55 , 134 , 135 , 52 , 53 , 32 , 54)
        AND n.type = 'api'

在news_sub_topic (sub_topic_id, news_sub_topics_id)上创建复合索引

请注意，DISTINCT COUNT(…)和COUNT(DISTINCT …)是不同的东西。如果您需要后者，请使用以下命令：

SELECT  COUNT(DISTINCT n.id)
FROM    news_sub_topic ns
JOIN    news n
ON      n.id = ns.news_sub_topics_id
WHERE   ns.sub_topic_id IN (55 , 134 , 135 , 52 , 53 , 32 , 54)
        AND n.type = 'api'

你能解释一下你想用你的查询做什么吗？

Answer 2

尽管已经说过了，但您的查询可能不是萨盖博。您将需要为连接条件创建索引，以使查询尽可能减少对news_sub_topics_id的敏感性。

也要确保使用非常有选择性的索引(按选择性排列索引字段(VerySelective、LessSelective、.)。

Answer 3

我不明白你为什么要这么做

        ...
        ON
        news0_.id = subtopics1_.news_sub_topics_id

NEWS.id不是它的PK吗?在加入主题时，不会有另一个像NEWS.topicid这样的专栏吗？

您可以试着首先筛选出以下几个子主题：

            select distinct newssubtopicid from newssubtopic as NS
            inner join subtopic ST on NS.subtopicid = ST.id
            and ST.id in (55 , 134 , 135 , 52 , 53 , 32 , 54)

ST.id是它的PK，不是吗？应该在NS.subtopicid上有一个索引。以上查询需要多长时间？

此时，您知道需要哪些新闻副主题来提取相关的新闻项目：

          select count(NEWS.id) from NEWS
          inner join 
          (
            select distinct newssubtopicid from newssubtopic as NS
            inner join subtopic ST on NS.subtopicid = ST.id
            and ST.id in (55 , 134 , 135 , 52 , 53 , 32 , 54)
          ) as T
          on NEWS.topicid = T.newssubtopicid