通过大量连接优化 MySQL 查询

我目前运行一个网站，该网站在列表中跟踪最新的分数和评级。该列表有数千个经常更新的条目，并且该列表应该可以按这些分数和评级列进行排序。

目前获取这些数据的 Mysql 看起来像（大致）：

SELECT e.*, SUM(sa.amount) AS score, AVG(ra.rating) AS rating
FROM entries e 
LEFT JOIN score_adjustments sa ON sa.entry_id = e.id
    HAVING sa.created BETWEEN ... AND ... 
LEFT JOIN rating_adjustments ra ON ra.entry_id = e.id
    HAVING ra.rating > 0 
ORDER BY score 
LIMIT 0, 10

表格所在位置（简化）：

entries:
    id: INT(11) PRIMARY
    ...other data...

score_adjustments:
    id: INT(11), PRIMARY
    entry_id: INT(11), INDEX, FOREIGN KEY (entries.id)
    created: DATETIME
    amount: INT(4)

rating_adjustments:
    id: INT(11), PRIMARY
    entry_id: INT(11), INDEX, FOREIGN KEY (entries.id)
    rating: DOUBLE

大约有300,000score_adjustments条目数量每天增加约 5,000 个。这rating_adjustments大约是1/4。

现在，我不是 DBA 专家，但我猜想打电话SUM() and AVG()一直都不是一件好事——尤其是当sa and ra包含数十万条记录 - 对吗？

我已经对查询进行了缓存，但我希望查询本身能够快速 - 但仍尽可能保持最新。我想知道是否有人可以分享任何解决方案来优化像这样的繁重的联接/聚合查询？如有必要，我愿意进行结构性改变。

EDIT 1

添加了有关查询的更多信息。

你的数据很糟糕聚集的.

InnoDB 将存储具有“接近”的 PK 物理上靠近的行。由于您的子表使用代理 PK，因此它们的行将随机存储。当需要对“主”表中的给定行进行计算时，DBMS 必须跳过所有位置以从子表中收集相关行。

尝试使用更“自然”的键，而不是代理键，并将父级的 PK 置于前沿，类似于：

score_adjustments:
    entry_id: INT(11), FOREIGN KEY (entries.id)
    created: DATETIME
    amount: INT(4)
    PRIMARY KEY (entry_id, created)

rating_adjustments:
    entry_id: INT(11), FOREIGN KEY (entries.id)
    rating_no: INT(11)
    rating: DOUBLE
    PRIMARY KEY (entry_id, rating_no)

注意：这假设created的分辨率足够好并且rating_no添加了多个评级以允许每个评级entry_id。这只是一个例子 - 您可以根据您的需要改变 PK。

这将“强制”属于同一行entry_id物理上靠近存储，因此只需对 PK/聚类键进行范围扫描即可计算 SUM 或 AVG，并且只需很少的 I/O。

或者（例如，如果您使用不支持集群的 MyISAM），cover带索引的查询，因此在查询期间根本不会触及子表。

最重要的是，您可以对设计进行反规范化，并将当前结果缓存在父表中：

• 将 SUM(score_adjustments.amount) 存储为物理字段，并在每次插入、更新或删除行时通过触发器调整它score_adjustments.
• 将 SUM(评级调整.评级) 存储为“S”andCOUNT（评级调整.评级）为“C”。当添加一行时rating_adjustments，将其添加到 S 并递增 C。在运行时计算 S/C 以获得平均值。类似地处理更新和删除。