gpt4 book ai didi

mysql - 如何加速具有多个连接的 Group By 语句?

转载 作者:可可西里 更新时间:2023-11-01 08:31:07 25 4
gpt4 key购买 nike

我在尝试加速查询时遇到了问题,该查询仅需要大约 11 秒就处理 200 万行。 Here is a link to my sqlfiddle .这是我要运行的语句和我的 EXPLAIN 语句。

查询:

SELECT crawl.pk Pk,domains.domain Domain, 
CONCAT(schemes.scheme, "://", domains.domain, remainders.remainder) Uri,
crawl.redirect Redirect FROM crawl
LEFT JOIN dates ON crawl.date_crawled=dates.pk
LEFT JOIN schemes ON crawl.scheme=schemes.pk
LEFT JOIN domains ON crawl.domain=domains.pk
LEFT JOIN remainders ON crawl.remainder=remainders.pk
WHERE (dates.date < CURDATE() - INTERVAL 30 DAY)
AND crawl.redirect=0
GROUP BY crawl.domain
ORDER BY crawl.date_crawled ASC
LIMIT 50

解释:

+----+-------------+------------+--------+-----------------------+-----------------------+---------+----------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-----------------------+-----------------------+---------+----------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | dates | ALL | PRIMARY,date | NULL | NULL | NULL | 7 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | crawl | ref | date_crawled_redirect | date_crawled_redirect | 8 | mytable.dates.pk,const | 408644 | |
| 1 | SIMPLE | schemes | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.scheme | 1 | |
| 1 | SIMPLE | domains | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.domain | 1 | |
| 1 | SIMPLE | remainders | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.remainder | 1 | |
+----+-------------+------------+--------+-----------------------+-----------------------+---------+----------------------------+--------+----------------------------------------------+
5 rows in set (2.26 sec)

编辑#1:根据评论,我已经用 Joins 替换了 Left Joins 并通过 join 移动了日期过滤器。遗憾的是,这并没有减少查询时间。

SELECT crawl.pk Pk,domains.domain Domain, CONCAT(schemes.scheme, "://", domains.domain, remainders.remainder) Uri, crawl.redirect Redirect
FROM crawl
JOIN schemes ON crawl.scheme=schemes.pk
JOIN domains ON crawl.domain=domains.pk
JOIN remainders ON crawl.remainder=remainders.pk
JOIN dates ON crawl.date_crawled=dates.pk AND dates.date < CURDATE() - INTERVAL 30 DAY
WHERE crawl.redirect=0
GROUP BY crawl.domain
ORDER BY crawl.date_crawled ASC
LIMIT 50

编辑#2:我更新的解释:

+----+-------------+------------+--------+---------------------------------------------------------+-----------------------+---------+----------------------------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------------------------------------------------+-----------------------+---------+----------------------------+--------+-----------------------------------------------------------+
| 1 | SIMPLE | dates | range | PRIMARY,date,date_pk,dateBtreeIdx,pk | date_pk | 3 | NULL | 4 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | crawl | ref | domain_remainder,remainder,scheme,date_crawled_redirect | date_crawled_redirect | 8 | mytable.dates.pk,const | 408644 | |
| 1 | SIMPLE | schemes | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | domains | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.domain | 1 | |
| 1 | SIMPLE | remainders | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.remainder | 1 | |
+----+-------------+------------+--------+---------------------------------------------------------+-----------------------+---------+----------------------------+--------+-----------------------------------------------------------+

编辑 #3

+----+--------------------+------------+-----------------+------------------------------------------+---------+---------+----------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-----------------+------------------------------------------+---------+---------+----------------------------+---------+---------------------------------+
| 1 | PRIMARY | schemes | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using temporary; Using filesort |
| 1 | PRIMARY | crawl | ref | domain_remainder,remainder,scheme,domain | scheme | 4 | mytable.schemes.pk | 1448223 | Using where |
| 1 | PRIMARY | domains | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.domain | 1 | |
| 1 | PRIMARY | remainders | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.remainder | 1 | |
| 2 | DEPENDENT SUBQUERY | dates | unique_subquery | PRIMARY,date,date_pk,dateBtreeIdx,pk | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+------------+-----------------+------------------------------------------+---------+---------+----------------------------+---------+---------------------------------+
5 rows in set (0.04 sec)

编辑#4:

+----+-------------+------------+--------+--------------------------------------+-------------------------+---------+----------------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------------------------+-------------------------+---------+----------------------------+---------+-----------------------------------------------------------+
| 1 | SIMPLE | dates | range | PRIMARY,date,date_pk,dateBtreeIdx,pk | date_pk | 3 | NULL | 4 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | schemes | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using join buffer |
| 1 | SIMPLE | crawl | ref | scheme_domain_remainder | scheme_domain_remainder | 4 | mytable.schemes.pk | 1455517 | Using where |
| 1 | SIMPLE | domains | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.domain | 1 | |
| 1 | SIMPLE | remainders | eq_ref | PRIMARY | PRIMARY | 4 | mytable.crawl.remainder | 1 | |
+----+-------------+------------+--------+--------------------------------------+-------------------------+---------+----------------------------+---------+-----------------------------------------------------------+
5 rows in set (0.04 sec)

编辑 #5

SELECT urls.pk PK, domains.domain Domain, CONCAT(schemes.scheme, "://", domains.domain, remainders.remainder) Uri, urls.redirect Redirect, urls.date_crawled DC FROM
(SELECT * FROM (
SELECT * FROM crawl as urls ORDER BY date_crawled ASC
) AS tmp GROUP BY tmp.domain ) as urls
JOIN schemes ON urls.scheme=schemes.pk
JOIN domains ON urls.domain=domains.pk
JOIN remainders ON urls.remainder=remainders.pk
JOIN dates ON urls.date_crawled=dates.pk AND dates.date < CURDATE() - INTERVAL 30 DAY
WHERE urls.redirect=0
ORDER BY urls.date_crawled ASC
LIMIT 50

最佳答案

您手头有一个近乎最优的查询。唯一的问题是表 dates 中的非最佳索引。正如您在 EXPLAIN 输出中看到的那样,MySQL 无法使用表 dates 中的任何索引,因此它被用作第一个表。这会为您的表 crawl 生成一个半优化的执行计划,其中包含大量要访问的行。

要改善这一点,您应该在 dates.date 列上添加一个 BTREE 索引:

ALTER TABLE dates ADD INDEX dateBtreeIdx USING BTREE (date)

BTREE 索引用于范围条件。在您的情况下,“低于”see here .

基于此,您也可以尝试将连接字段 Dates.pk 添加到索引中。这可能会进一步加快您的查询速度,但取决于您的数据。

编辑

现在 MySQL 可以在 date.dates(type = RANGE 和 rows = 4)上使用索引。您看不到加速,因为现在优化器不会在 schemes 中使用 PRIMARY KEY...

但最大的性能问题在于 crawl。使用 IN 查询尝试不同的方法:

SELECT 
crawl.pk Pk, domains.domain Domain,
CONCAT(schemes.scheme, "://", domains.domain, remainders.remainder) Uri,
crawl.redirect Redirect
FROM
crawl, schemes, domains, remainders
WHERE
crawl.scheme=schemes.pk
AND crawl.domain=domains.pk
AND crawl.remainder=remainders.pk

AND crawl.date_crawled IN (SELECT pk FROM dates WHERE (dates.date < CURDATE() - INTERVAL 30 DAY))
AND crawl.redirect=0

GROUP BY
crawl.domain
ORDER BY
crawl.date_crawled ASC
LIMIT 50

编辑 #2

SELECT 
urls.pk PK, domains.domain Domain,
CONCAT(schemes.scheme, "://", domains.domain, remainders.remainder) Uri,
urls.redirect Redirect,
urls.date_crawled DC
FROM
(SELECT pk, redirect, date_crawled FROM crawl GROUP BY `domain` ) as urls
JOIN schemes ON urls.scheme=schemes.pk
JOIN domains ON urls.`domain`=domains.pk
JOIN remainders ON urls.remainder=remainders.pk
JOIN dates ON urls.date_crawled=dates.pk AND dates.date < CURDATE() - INTERVAL 30 DAY
WHERE
urls.redirect=0
ORDER BY urls.date_crawled ASC
LIMIT 50

关于mysql - 如何加速具有多个连接的 Group By 语句?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26823804/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com