试试这个

SELECT 
    a.source AS referrer, 
    COUNT(a.id) AS frequency, 
    SUM(t.sales) AS sales
FROM (Select id, source From analytics Where user_id = 52094) a
LEFT JOIN (Select analytics, case when status = 'COMPLETED' Then 1 else 0 end as sales 
           From transactions) t ON a.id = t.analytics
GROUP BY a.source 
ORDER BY frequency DESC 
LIMIT 10

我之所以提出这个建议，是因为您说过“它们是海量表”，但是这个sql只使用很少的列。在这种情况下，如果我们只使用带有require列的内联视图，那就很好了
注意：记忆在这里也将发挥重要作用。所以在决定内联视图之前先确认内存

我会尝试子查询：

SELECT a.source AS referrer, 
       COUNT(*) AS frequency,
       SUM((SELECT COUNT(*) FROM transactions t 
        WHERE a.id = t.analytics AND t.status = 'COMPLETED')) AS sales
FROM analytics a
WHERE a.user_id = 52094 
GROUP BY a.source
ORDER BY frequency DESC 
LIMIT 10;

加上与@gordon的答案完全相同的索引：分析（用户id，id，来源）和事务（分析，状态）。

你能试试下面的方法吗

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(sales) AS sales
FROM analytics
LEFT JOIN(
	SELECT transactions.Analytics, (CASE WHEN transactions.status = 'COMPLETED' THEN 1 ELSE 0 END) AS sales
	FROM analytics INNER JOIN transactions ON analytics.id = transactions.analytics
) Tra
ON analytics.id = Tra.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10

我将创建以下索引（b树索引）：

analytics(user_id, source, id) 
transactions(analytics, status)

这与戈登的建议不同。
索引中列的顺序很重要。
您可以按特定的 analytics.user_id ，因此此字段必须是索引中的第一个字段。然后你分组 analytics.source . 避免按 source 这应该是索引的下一个字段。你也可以参考 analytics.id ，因此最好将此字段作为索引的一部分，放在最后。mysql能够只读取索引而不接触表吗？我不知道，但它很容易测试。
索引打开 transactions 必须从 analytics ，因为它将用于 JOIN . 我们还需要 status .

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales
FROM analytics
LEFT JOIN transactions ON analytics.id = transactions.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10

我假设 predicate user\u id=52094用于说明目的，在应用程序中，所选的user\u id是一个变量。
我还假设酸性在这里不是很重要。
（1） 因此，我将使用一个实用表维护两个副本表，其中只包含必需的字段（类似于vladimir上面建议的索引）。

CREATE TABLE mv_anal (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `source` varchar(45),
  PRIMARY KEY (`id`)
);

CREATE TABLE mv_trans (
  `id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `analytics` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE util (
  last_updated_anal int (11) NOT NULL,
  last_updated_trans int (11) NOT NULL
);

INSERT INTO util (0, 0);

这里的好处是，我们将读取原始表的相对较小的投影—希望os级和db级缓存可以工作，它们不是从较慢的辅助存储中读取，而是从较快的ram中读取。这是一个很大的收获。
下面是我如何更新这两个表（下面是由cron运行的事务）：

-- TRANSACTION STARTS -- 

INSERT INTO mv_trans 
SELECT id, IF (status = 'COMPLETE', 1, 0) AS status, analysis 
FROM transactions JOIN util
ON util.last_updated_trans <= transactions.id

UPDATE util
SET last_updated_trans = sub.m
FROM (SELECT MAX (id) AS m FROM mv_trans) sub;

-- TRANSACTION COMMITS -- 

-- similar transaction for mv_anal.

（2） 现在，我将处理选择性以减少顺序扫描时间。我将不得不建立一个b-树索引上的用户id，源和id（在这个序列中）的mv\ anal。
注意：以上可以通过在分析表上创建索引来实现，但是构建这样一个索引需要读取包含6000万行的大表。我的方法要求索引构建只读取非常细的表。因此，我们可以更频繁地重建btree（因为表是append only，所以可以解决倾斜问题）。
这就是我如何确保在查询和反扭曲btree问题时实现高选择性的方法。
（3） 在postgresql中，with子查询总是具体化的。我也希望mysql也是这样。因此，作为优化的最后一英里：

WITH sub_anal AS (
  SELECT user_id, source AS referrer, COUNT (id) AS frequency
  FROM mv_anal
  WHERE user_id = 52094
  GROUP BY user_id, source
  ORDER BY COUNT (id) DESC
  LIMIT 10
)
SELECT sa.referrer, sa.frequency, SUM (status) AS sales
FROM sub_anal AS sa 
JOIN mv_anal anal 
ON sa.referrer = anal.source AND sa.user_id = anal.user_id
JOIN mv_trans AS trans
ON anal.id = trans.analytics

试试下面，让我知道这是否有帮助。

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales
FROM (SELECT * FROM analytics where user_id = 52094) analytics
LEFT JOIN (SELECT analytics, status from transactions where analytics = 52094) transactions ON analytics.id = transactions.analytics
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10

对于此查询：

SELECT a.source AS referrer, 
       COUNT(*) AS frequency, 
       SUM( t.status = 'COMPLETED' ) AS sales
FROM analytics a LEFT JOIN
     transactions t
     ON a.id = t.analytics
WHERE a.user_id = 52094 
GROUP BY a.source 
ORDER BY frequency DESC 
LIMIT 10 ;

你想要索引吗 analytics(user_id, id, source) 以及 transactions(analytics, status) .

此查询可能会连接数百万个 analytics 记录 transactions 记录并计算数百万条记录的总和（包括状态检查）。如果我们能先申请 LIMIT 10 然后进行连接和求和，可以加快查询速度。不幸的是，我们需要 analytics.id 对于连接，在应用 GROUP BY . 但也许吧 analytics.source 不管怎样，都有足够的选择性来提升查询。
因此，我的想法是计算频率，受它们的限制，返回 analytics.source 以及 frequency 并使用此结果筛选 analytics 在主查询中，它将对希望大大减少的记录数执行其余的连接和计算。
最小子查询（注意：无连接，无求和，返回10条记录）：

SELECT
    source,
    COUNT(id) AS frequency
FROM analytics
WHERE user_id = 52094
GROUP BY source
ORDER BY frequency DESC 
LIMIT 10

使用上述查询作为子查询的完整查询 x :

SELECT
    x.source AS referrer,
    x.frequency,
    SUM(IF(t.status = 'COMPLETED', 1, 0)) AS sales
FROM
    (<subquery here>) x
    INNER JOIN analytics a
       ON x.source = a.source  -- This reduces the number of records
    LEFT JOIN transactions t
       ON a.id = t.analytics
WHERE a.user_id = 52094      -- We could have several users per source
GROUP BY x.source, x.frequency
ORDER BY x.frequency DESC

如果这不能产生预期的性能提升，这可能是由于mysql以意外的顺序应用了连接。正如这里解释的“有没有办法强制执行mysql的执行顺序？”，您可以将join替换为 STRAIGHT_JOIN 在这种情况下。

这个问题肯定受到了很多关注，所以我相信所有显而易见的解决方案都已经尝试过了。我没看到什么能解决问题 LEFT JOIN 不过，在查询中。
我注意到了 LEFT JOIN 语句通常会强制查询计划器进行哈希连接，这种连接对于少量结果来说速度很快，但是对于大量结果来说速度非常慢。正如@rick james的回答所指出的，因为原始查询中的连接在identity字段上 analytics.id ，这将生成大量结果。哈希连接将产生糟糕的性能结果。下面的建议在没有任何模式或处理更改的情况下解决了这个问题。
因为聚合是通过 analytics.source ，我将尝试一个查询，该查询为frequency by source和sales by source创建单独的聚合，并将左连接推迟到聚合完成之后。t型

我在你的查询中发现的唯一问题是

GROUP BY analytics.source 
ORDER BY frequency DESC

因为这个查询正在使用临时表进行文件排序。
避免这种情况的一种方法是创建另一个表，如

CREATE TABLE `analytics_aggr` (
  `source` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `frequency` int(10) DEFAULT NULL,
  `sales` int(10) DEFAULT NULL,
  KEY `sales` (`sales`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`

使用下面的查询将数据插入分析\u aggr

insert into analytics_aggr SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales
    FROM analytics
    LEFT JOIN transactions ON analytics.id = transactions.analytics
    WHERE analytics.user_id = 52094 
    GROUP BY analytics.source 
    ORDER BY null

现在您可以使用

select * from analytics_aggr order by sales desc

首先是一些分析。。。

SELECT  a.source AS referrer,
        COUNT(*) AS frequency,  -- See question below
        SUM(t.status = 'COMPLETED') AS sales
    FROM  analytics AS a
    LEFT JOIN  transactions AS t  ON a.id = t.analytics AS a
    WHERE  a.user_id = 52094
    GROUP BY  a.source
    ORDER BY  frequency DESC
    LIMIT  10

如果Map来自 a 至 t 是“一对多”，那么你需要考虑 COUNT 以及 SUM 具有正确的值或膨胀值。从目前的情况来看，它们是“膨胀的”。这个 JOIN 在聚合之前发生，因此您要计算事务数和完成的事务数。我想这是需要的。
注意：通常的模式是 COUNT(*) ; 说 COUNT(x) 意味着检查 x 因为存在 NULL . 我怀疑不需要支票？
此索引处理 WHERE 是“掩护”：

analytics:  INDEX(user_id, source, id)   -- user_id first

 transactions:  INDEX(analytics, status)  -- in this order

这个 GROUP BY 可能需要也可能不需要“排序”。这个 ORDER BY ，不同于 GROUP BY ，肯定需要排序。并且需要对整个分组行进行排序；这条路没有捷径 LIMIT .
通常，摘要表是面向日期的。也就是说 PRIMARY KEY 包括“日期”和一些其他维度。也许，按日期和用户id键入会有意义吗？平均用户每天有多少笔交易？如果至少有10个，那么让我们考虑一个汇总表。另外，重要的是不要 UPDATEing 或者 DELETEing 旧唱片。更多
我可能会的

user_id ...,
source ...,
dy DATE ...,
status ...,
freq      MEDIUMINT UNSIGNED NOT NULL,
status_ct MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY(user_id, status, source, dy)

然后查询变成

SELECT  source AS referrer,
        SUM(freq) AS frequency,
        SUM(status_ct) AS completed_sales
    FROM  Summary
    WHERE  user_id = 52094
      AND  status = 'COMPLETED'
    GROUP BY source
    ORDER BY  frequency DESC
    LIMIT  10

速度来自许多因素
较小的表（要查看的行数较少）
不 JOIN 更有用的索引
（它仍然需要额外的排序。）
即使没有汇总表，也可能会有一些加速。。。
table有多大？“innodb\u buffer\u pool\u size”有多大？ Normalizing 一些既庞大又重复的字符串可能会使该表不受i/o限制。
这太可怕了： KEY (transaction_id(191)) ; 请参阅这里的5种方法来修复它。
ip地址不需要255字节，也不需要 utf8mb4_unicode_ci . (39）和ascii就足够了。

聚会迟到了。我认为您需要将一个索引加载到mysql的缓存中。nlj可能在扼杀表演。我是这么看的：
这条路
你的问题很简单。它有两个表，“路径”非常清晰：
优化器应该计划读取 analytics 先上桌。
优化器应该计划读取 transactions 第二张table。这是因为你正在使用 LEFT OUTER JOIN . 关于这个问题没有太多讨论。
此外，还有 analytics 表是6000万行，最好的路径应该在这个表上尽快筛选行。
通道
路径清除后，需要决定是使用索引访问还是表访问。两者各有利弊。但是，您希望改进 SELECT 性能：
您应该选择索引访问。
避免混合访问。因此，您应该不惜一切代价避免任何表访问（获取）。翻译：将所有参与的列放在索引中。
过滤
再说一次，您希望 SELECT . 因此：
您应该在索引级别而不是表级别执行过滤。
行聚合
过滤之后，下一步是按 GROUP BY analytics.source . 这可以通过放置 source 列作为索引中的第一列。
路径、访问、筛选和聚合的最佳索引
考虑到以上所有因素，您应该将所有提到的列包含到索引中。以下指标应能提高响应时间：

create index ix1_analytics on analytics (user_id, source, id);

create index ix2_transactions on transactions (analytics, status);

这些索引实现了上面描述的“路径”、“访问”和“过滤”策略。
索引缓存
最后——这很关键——将辅助索引加载到mysql的内存缓存中。mysql正在执行一个nlj（嵌套循环连接）——mysql行话中的“ref”——需要随机访问第二个循环近20万次。
不幸的是，我不知道如何将索引加载到mysql的缓存中。使用 FORCE 可以工作，如：

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales
FROM analytics
LEFT JOIN transactions FORCE index (ix2_transactions)
  ON analytics.id = transactions.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10

确保有足够的缓存空间。下面是一个简短的问题/答案：如何确定mysql索引是否完全适合内存
祝你好运！哦，把结果贴出来。

我将尝试从两个表中分离查询。因为你只需要前10名 source s、 我会先得到它们，然后从 transactions 这个 sales 列：

SELECT  source as referrer
        ,frequency
        ,(select count(*) 
          from   transactions t  
          where  t.analytics in (select distinct id 
                                 from   analytics 
                                 where  user_id = 52094
                                        and source = by_frequency.source) 
                 and status = 'completed'
         ) as sales
from    (SELECT analytics.source
                ,count(*) as frequency
        from    analytics 
        where   analytics.user_id = 52094
        group by analytics.source
        order by frequency desc
        limit 10
        ) by_frequency

如果不使用 distinct