查询加入数以百万计的记录是缓慢的，请帮助我优化

Question

以下是我的查询：

SELECT SQL_BUFFER_RESULT SQL_BIG_RESULT users.id, users.email, 
        COUNT(av.user_id) AS article_views_count,
        COUNT(af.id) AS article_favorites_count,
        COUNT(lc.user_id) AS link_clicks_count,
        COUNT(ai.user_id) AS ad_impressions_count,
        COUNT(ac.user_id) AS ad_clicks_count
          FROM users
            LEFT JOIN article_views AS av     ON (av.user_id = users.id AND av.created_at >= '2012-11-28 00:00:00' AND av.created_at <= '2012-11-30 23:59:59')
            LEFT JOIN article_favorites AS af ON (af.user_id = users.id AND af.created_at >= '2012-11-28 00:00:00' AND af.created_at <= '2012-11-30 23:59:59')
            LEFT JOIN link_clicks AS lc       ON (lc.user_id = users.id AND lc.created_at >= '2012-11-28 00:00:00' AND lc.created_at <= '2012-11-30 23:59:59')
            LEFT JOIN ad_impressions AS ai    ON (ai.user_id = users.id AND ai.created_at >= '2012-11-28 00:00:00' AND ai.created_at <= '2012-11-30 23:59:59')
            LEFT JOIN ad_clicks AS ac         ON (ac.user_id = users.id AND ac.created_at >= '2012-11-28 00:00:00' AND ac.created_at <= '2012-11-30 23:59:59')
          GROUP BY users.id
          HAVING (article_views_count + article_favorites_count + link_clicks_count + ad_impressions_count + ad_clicks_count) > 0

一些统计数据可以给你提供背景：

1. 用户: 1,474,348行
2. article_views: 32 603 637行
3. article_favorites: 10 199行
4. link_clicks: 4 258 901行
5. ad_impressions: 66,758,573行
6. ad_clicks: 324,125行

加入的每个表在user_id和created_at上都有一个复合索引(按这个顺序)。

我们正在运行MySQL5，每个表都是MyISAM引擎。

下面是对查询的解释：https://gist.github.com/4197482

目标是只返回用户有任何活动(查看，喜爱，点击，印象，广告点击)在这段时间内。

有什么办法优化这个坏男孩吗？

Answer 1

您的查询似乎是一个基于大量数据进行分析的查询(因为它包含一个聚合函数和一个GROUP子句)。

要提高此类查询的性能，您可以创建一个物化视图结果，然后使用以下一些想法进行连接：

CREATE TABLE my_view AS SELECT ... FROM ... JOIN ...

这样做，下一个查询将更高效，因为MySQL只需计算聚合。

然后，您只需实现一种刷新表的策略(例如，通过时间戳)。

另一种解决方案是在DBMS中导入数据，而DBMS是在这种查询(面向列的数据库)上高效构建的。例如，InfiniDB是一种基于MySQL的开源数据库管理系统，其存储引擎为分析性查询优化。

Answer 2

尝试使用每个表将查询拆分到INNER JOIN，并将它们与UNION组合起来。喜欢

SELECT users.id, users.email, COUNT(av.user_id) AS article_views_count
FROM users
JOIN article_views AS av ON (av.user_id = users.id AND av.created_at >= '2012-11-28 00:00:00' AND av.created_at <= '2012-11-30 23:59:59')
GROUP BY users.id, users.email

UNION

....