我每周运行一次以下查询，但它现在需要 22 小时才能运行!该报告的目的是汇总广告展示位置和日期的展示和转化数据，因此我查询的主表没有主键，因为可能有多个事件具有相同的日期/展示位置。

主数据集有大约 40 万条记录，因此运行此报告的时间不会超过几分钟。

表的描述是:

tbl_ads(400,000 条记录)

day_est     DATE (index)
conv_day_est    DATE (index)
placement_id    INT (index)
adunit_id   INT (index)
cost_type   VARCHAR(20)
cost_value  DECIMAL(10,2)
adserving_cost  DECIMAL(10,2)
conversion1 INT
estimated_spend DECIMAL(10,2)
clicks      INT
impressions INT
publisher_clicks    INT
publisher_impressions   INT
publisher_spend DECIMAL (10,2)
source VARCHAR(30)

ma​​p_external_id(75,000 条记录)

placement_id    INT
adunit_id   INT
external_id VARCHAR (50)
primary key(placement_id,adunit_id,external_id)

SQL 查询

SELECT A.day_est,A.placement_id,A.placement_name,A.adunit_id,A.adunit_name,A.imp,A.clk, C.ads_cost, C.ads_spend, B.conversion1, B.conversion2,B.ID_Matched, C.pub_imps, C.pub_clicks, C.pub_spend, COALESCE(A.cost_type,B.cost_type) as cost_type, COALESCE(A.cost_value,B.cost_value) as cost_value, D.external_id
FROM (SELECT day_est, placement_id,adunit_id,placement_name,adunit_name,cost_type,cost_value,
    SUM(impressions) as imp, SUM(clicks) as clk
    FROM tbl_ads
    WHERE source='delivery'
    GROUP BY 1,2,3 ) as A LEFT JOIN
(
    SELECT conv_day_est, placement_id,adunit_id, cost_type,cost_value, SUM(conversion1) as conversion1,
    SUM(conversion2) as conversion2,SUM(id_match) as ID_Matched
    FROM tbl_ads
    WHERE source='attribution'
    GROUP BY 1,2,3
) as B on A.day_est=B.conv_day_est AND A.placement_id=B.placement_id AND A.adunit_id=B.adunit_id
LEFT JOIN
(
    SELECT day_est,placement_id,adunit_id,SUM(adserving_cost) as ads_cost, SUM(estimated_spend) as ads_spend,sum(publisher_clicks) as pub_clicks,sum(publisher_impressions) as pub_imps,sum(publisher_spend) as pub_spend
    FROM tbl_ads
    GROUP BY 1,2,3 ) as C on A.day_est=C.day_est AND A.placement_id=C.placement_id AND A.adunit_id=C.adunit_id
LEFT JOIN
(
    SELECT placement_id,adunit_id,external_id
    FROM map_external_id
) as D on A.placement_id=D.placement_id AND A.adunit_id=D.adunit_id
INTO OUTFILE '/tmp/weekly_report.csv';

解释的结果:

+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+----------------+
| id | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows   | Extra          |
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+----------------+
|  1 | PRIMARY     | <derived2>         | ALL   | NULL          | NULL    | NULL    | NULL | 136518 |                |
|  1 | PRIMARY     | <derived3>         | ALL   | NULL          | NULL    | NULL    | NULL |   5180 |                |
|  1 | PRIMARY     | <derived4>         | ALL   | NULL          | NULL    | NULL    | NULL | 198190 |                |
|  1 | PRIMARY     | <derived5>         | ALL   | NULL          | NULL    | NULL    | NULL |  23766 |                |
|  5 | DERIVED     | map_external_id    | index | NULL          | PRIMARY | 55      | NULL |  20797 | Using index    |
|  4 | DERIVED     | tbl_ads            | index | NULL          | PIndex  | 13      | NULL | 318400 |                |
|  3 | DERIVED     | tbl_ads            | ALL   | NULL          | NULL    | NULL    | NULL | 318400 | Using filesort |
|  2 | DERIVED     | tbl_ads            | index | NULL          | PIndex  | 13      | NULL | 318400 | Using where    |
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+----------------+

最佳答案

更多的是推测性答案，但我认为 22 小时也不太不现实..

首先要做的事情...您不需要最后一个子查询，只需声明

LEFT JOIN map_external_id as D on A.placement_id=D.placement_id AND A.adunit_id=D.adunit_id

其次，在第一个和第二个子查询中，您的 WHERE 语句中有字段 source 并且该字段未在您的表方案中列出。明明可能是 or enum 或 string 类型，它有索引吗？我有一个包含 1'000'000 左右条目的表，其中缺少索引导致一个简单查询的处理时间为 30 秒(无法相信将查询放入登录过程的人)。

中间的无关问题，最终结果集大小是多少？

第三，我的假设是，通过运行聚合子查询，mysql 实际上创建了没有任何索引的临时表——这是不好的。 您是否查看过单个子查询的结果集？典型的集合大小是多少？根据您的陈述和我对您的典型数据的猜测，我认为聚合实际上只会略微减少集合大小(除了 WHERE 语句)。所以让我按照子查询的顺序猜测:200'000、100'000、200'000

然后每个子查询与下一个子查询在三个可能没有索引的字段上连接。第一次加入的最坏情况:200'000 * 100'000 = 20'000'000'000 次比较。从我 30 秒查询 1'000'000 条记录的经验来看，这使它成为 20'000 * 30 = 600'000 秒 =+- 166 小时。显然这太多了，也许缺少一个数字，也许是 20 秒而不是 30 秒，结果集可能不同，最坏的情况不是平均情况 - 但你得到了图像。

然后我的解决方法是尝试创建额外的表来替换您的聚合子查询。从您的查询来看，您可以每天更新它，因为我猜您只是在展示等方面插入行，因此您可以逐步添加聚合数据。然后，您将大型查询转换为以下两个步骤

1. 更新您的汇总表
2. 做最后的转储。

显然应该对聚合表进行有意义的索引。我认为这应该会将最终查询缩短到几秒钟。

关于mysql - 为什么我的 SQL 查询这么慢？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21741813/