嵌套的MariaDB查询速度慢

f45qwnt8 于 2022-11-08 发布在其他

关注(0)|答案(2)|浏览(158)

我遇到了一个查询性能问题，我在表中有2100万条记录，而我在这里查看的两个表各有800万条记录;但我做了一个查询，在我看来，不是很好，但这是我知道的唯一方法。
这个查询需要65秒，我需要在1秒内完成它，我认为如果我没有所有的SELECT查询，这是可能的，但再次，我不知道如何用我的SQL知识做它。
数据库服务器版本为MariaDB 10.6。

SELECT
pa.`slug`,
(
    SELECT 
    SUM(`impressions`) 
    FROM `rh_pages_gsc_country` 
    WHERE `page_id` = pa.`page_id` 
    AND `country` = 'aus'
    AND `date_id` IN 
        (
            SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
        )
) as au_impressions,
(
    SELECT 
    SUM(`clicks`) 
    FROM `rh_pages_gsc_country` 
    WHERE `page_id` = pa.`page_id` 
    AND `country` = 'aus'
    AND `date_id` IN 
        (
            SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
        )
) as au_clicks,
(
    SELECT 
    COUNT(`keywords_id`) 
    FROM `rh_pages_gsc_keywords` 
    WHERE `page_id` = pa.`page_id`
    AND `date_id` IN 
        (
            SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
        )
) as keywords,
(
    SELECT 
    AVG(`position`) 
    FROM `rh_pages_gsc_keywords` 
    WHERE `page_id` = pa.`page_id`
    AND `date_id` IN 
        (
            SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
        )
) as avg_pos,
(
    SELECT 
    AVG(`ctr`) 
    FROM `rh_pages_gsc_keywords` 
    WHERE `page_id` = pa.`page_id`
    AND `date_id` IN 
        (
            SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
        )
) as avg_ctr
FROM `rh_pages` pa
WHERE pa.`site_id` = 13
ORDER BY au_impressions DESC, keywords DESC, slug DESC

如果有人能提供帮助，我认为这里不需要表结构，因为它基本上显示在查询中，但这里有一张约束和表类型的照片。

任何能提供帮助的人都将不胜感激。

mariadb

来源：https://stackoverflow.com/questions/73874744/nested-mariadb-query-slow

2条答案

按热度按时间

qlckcl4x1#

您的查询正在聚合（汇总）两个不同明细表（rh_pages_gsc_country和rh_pages_gsc_keywords）中的行，并对特定日期范围执行此操作。并且它具有大量的correlated subqueries。
提高性能的第一步是

将相关子查询转换为独立子查询，然后联接它们。
为每个从表编写一个子查询，而不是为需要汇总的每个列编写一个子查询。

你提到你一直在纠结这个问题。我希望你能从这个答案中学到这样一个概念：如果您能够得到给出相同结果的独立子查询，然后将它们连接在一起，则通常可以重构掉相关子查询。如果您在SELECT子句中提到了子查询--SELECT ... (SELECT whatever) whatever ...--则您可能有机会进行这种重构。
开始吧。首先你需要一个日期范围的子查询。你已经有了这个，只是重复了一下。

SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()

接下来需要一个rh_pages_gsc_country的子查询，它是对现有查询的修改，我们将在一个子查询中获取两个SUM。

SELECT SUM(`impressions`) impressions,
             SUM(`clicks`) clicks, 
             page_id, date_id
        FROM `rh_pages_gsc_country` 
       WHERE `country` = 'aus'
      GROUP BY page_id, date_id

这个子查询生成一个虚拟表，其中page_id和date_id的每种组合正好对应一行，包含展示次数和点击次数。
接下来，让我们将子查询连接到一个主查询中。这将生成结果集中的一些列。

SELECT pa.slug, country.impressions, country.clicks
  FROM rh_pages pa 
  JOIN (
           SELECT SUM(`impressions`) impressions,
                  SUM(`clicks`) clicks, 
                  page_id, date_id
             FROM `rh_pages_gsc_country` 
            WHERE `country` = 'aus'  -- constant for country code
            GROUP BY page_id, date_id
      ) country ON  country.page_id = pa.page_id
  JOIN (
           SELECT `date_id` 
            FROM `rh_pages_gsc_dates` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
       ) dates ON dates.date_id = country.date_id
 WHERE pa.site_id = 13             -- constant for page id
 ORDER BY country.impressions DESC

这将遍历rh_pages_gsc_dates和rh_pages_gsc_country的行 * 仅一次 *，以满足您的查询。因此，速度更快。
最后，让我们对rh_pages_gsc_keywords表的摘要执行同样的操作。

SELECT pa.slug, country.impressions, country.clicks,
       keywords.keywords, keywords.avg_pos, keywords.avg_ctr
  FROM rh_pages pa 
  JOIN (
           SELECT SUM(`impressions`) impressions,
                  SUM(`clicks`) clicks, 
                  page_id, date_id
             FROM `rh_pages_gsc_country` 
            WHERE `country` = 'aus'  -- constant for country code
            GROUP BY page_id, date_id
      ) country ON  country.page_id = pa.page_id
  JOIN (
           SELECT SUM(`keywords_id`) keywords,
                  AVG(`position`) position,
                  AVG(`ctr`) avg_ctr, 
                  page_id, date_id
             FROM `rh_pages_gsc_keywords` 
            GROUP BY page_id, date_id
       ) keywords ON keywords.page_id = pa.page_id
  JOIN (
           SELECT `date_id` 
            FROM `rh_pages_gsc_keywords` 
            WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
       ) dates ON dates.date_id = country.date_id
              AND dates.date_id = keywords.date_id
 WHERE pa.site_id = 13             -- constant for page id
 ORDER BY impressions DESC, keywords DESC, slug DESC

这几乎肯定会比您现有的更快。如果足够快，那就太好了。如果不够快，请不要犹豫，提出另一个问题以寻求帮助，并将其标记为query-optimization。我们需要查看您的表定义、索引定义和EXPLAIN的输出。在提出后续问题之前，请先read this。

我没有，重复一遍，没有，调试过任何东西，这取决于你 *

赞(0）回复(0）举报 2022-11-08

xj3cbfub2#

不要规范化任何在“范围扫描”中经常使用的列，例如date。下面的代码非常慢：

AND  `date_id` IN (
                SELECT  `date_id`
                    FROM  `rh_pages_gsc_dates`
                    WHERE  `date` BETWEEN NOW() - INTERVAL 12 MONTH
                                      AND NOW() )

BIGINT（8字节）指向DATE（5字节）也会占用额外的空间。
一旦将date移动到各个表中，子查询就会简化，例如

SELECT  AVG(`position`)
            FROM  `rh_pages_gsc_keywords`
            WHERE  `page_id` = pa.`page_id`
              AND  `date_id` IN (
                SELECT  `date_id`
                    FROM  `rh_pages_gsc_dates`
                    WHERE  `date` BETWEEN NOW() - INTERVAL 12 MONTH
                                      AND NOW() )

变成了

SELECT  AVG(`position`)
            FROM  `rh_pages_gsc_keywords`
            WHERE  `page_id` = pa.`page_id`
              AND  `date` >= NOW() - INTERVAL 12 MONTH

我假设“NOW”之后没有任何内容被存储。

AND  `date`  < NOW()

每个表可能都需要一个新索引，例如

INDEX(page_id, date)  -- in that order

(Yes，其他人的“加入”建议是一个很好的建议。它基本上与我的建议上下正交。）
进行这些更改后，如果性能不够好，我们可以讨论Summary Tables

赞(0）回复(0）举报 2022-11-08

我来回答

嵌套的MariaDB查询速度慢

2条答案

相关问题

热门标签

最新问答