MySQL查询优化:如何优化投票计算？

Question

希望你过得很好。

我需要一些关于这个数据库的帮助：

​

​

这是一个存储选票的数据库。用户选择他们喜欢的音轨，然后投票给他们。他们可以投“赞成”或“反对”。像馅饼一样简单。但是，当涉及到计算数据时，它会变得毛茸茸的。

元数据

它是一个键值样式表，存储最常用的统计数据(只是缓存的类型)：

mysql> SELECT * FROM Meta;
+-------------+-------+
| Key         | Value |
+-------------+-------+
| TRACK_COUNT | 2620  |
| VOTE_COUNT  | 3821  |
| USER_COUNT  | 371   |
+-------------+-------+

投票

投票表本身控制着投票。这里唯一有趣的字段是Type，它的值意味着：

1. 0 -应用程序进行投票，用户使用UI对轨道进行投票。
2. 1 -输入投票(从外部服务)
3. 2 -合并投票。实际上和导入的投票一样，但实际上它做了一个注意，这个用户已经使用外部服务为这个轨道投票，现在他正在使用App重复自己。

跟踪

轨道是保存自己的总统计数据。喜欢、不喜欢、喜欢外部服务(LikesRP)、不喜欢外部服务(DislikesRP)、喜欢/不喜欢调整的数量。

应用程序

该应用程序需要获得以下选票：

1. 在过去的7天里5次投票最多的曲目
2. 在过去的7天里有5次投票结果
3. 5在过去7天中最多的投票轨道，其中的选票是从外部服务(Vote.Type = 1)进口。
4. 上个月有100首上座率最高的歌曲

要获得100次投票最多的跟踪，我使用以下查询：

SELECT
    T.Hash,
    T.Title,
    T.Artist,
    COALESCE(X.VotesTotal, 0) + T.LikesAdjust as VotesAdjusted
FROM (
    SELECT
        V.TrackHash,
        SUM(V.Vote) AS VotesTotal
    FROM
        Vote V
    WHERE
        V.CreatedAt > NOW() - INTERVAL 1 MONTH AND V.Vote = 'up'
    GROUP BY
        V.TrackHash
    ORDER BY
        VotesTotal DESC
) X
RIGHT JOIN Track T
    ON T.Hash = X.TrackHash
ORDER BY
    VotesAdjusted DESC
LIMIT 0, 100;

此查询工作正常，并执行调整(客户端希望调整列表中的跟踪位置)。几乎相同的查询用于获得5条最向上/向下的投票曲目。任务3的查询如下：

SELECT
    T.Hash,
    T.Title,
    T.Artist,
    COALESCE(X.VotesTotal, 1) as VotesTotal
FROM (
    SELECT
        V.TrackHash,
        SUM(V.Vote) AS VotesTotal
    FROM
        Vote V
    WHERE
        V.Type = '1' AND
        V.CreatedAt > NOW() - INTERVAL 1 WEEK AND
        V.Vote = 'up'
    GROUP BY
        V.TrackHash
    ORDER BY
        VotesTotal DESC
) X
RIGHT JOIN Track T
    ON T.Hash = X.TrackHash
ORDER BY
    VotesTotal DESC
LIMIT 0, 5;

问题是，第一个查询需要大约2秒的时间来执行，而我们的票数还不到4k。到年底，这个数字将达到20万张左右，这很可能会毁掉这个数据库。所以我正在想办法解决这个难题。

现在我开始思考这些问题：

1. 我把数据库设计搞错了吗？我是说，会更好吗？
2. 我把查询搞错了吗？
3. 还有什么我能改进的吗？

我做的第一件事就是缓存。但是，好的，这彻底解决了这个问题。但是我对与SQL相关的解决方案很好奇(总是倾向于完美)。

我想出的第二件事是将这些计算值放到Meta表中，并在投票过程中对它们进行更改。不过，我还没来得及试试看。顺便说一句，这值得吗？或者，企业级应用程序如何解决这些问题？

谢谢。

编辑

我真不敢相信我竟然忘了包括指数。下面是它们：

mysql> SHOW INDEXES IN Vote;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Vote  |          0 | UNIQUE_UserId_TrackHash |            1 | UserId      | A         |         890 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          0 | UNIQUE_UserId_TrackHash |            2 | TrackHash   | A         |        4450 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          1 | INDEX_TrackHash         |            1 | TrackHash   | A         |        4450 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          1 | INDEX_CreatedAt         |            1 | CreatedAt   | A         |        1483 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          1 | UserId                  |            1 | UserId      | A         |        1483 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> SHOW INDEXES IN Track;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Track |          0 | PRIMARY        |            1 | Hash        | A         |        2678 |     NULL | NULL   |      | BTREE      |         |
| Track |          1 | INDEX_Likes    |            1 | Likes       | A         |          66 |     NULL | NULL   |      | BTREE      |         |
| Track |          1 | INDEX_Dislikes |            1 | Dislikes    | A         |          27 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 1

这是一个非常主观的问题，因为它在很大程度上取决于您的确切需求和性能测试，这里没有人能够对您的数据进行测试。但我可以回答您的问题，并添加一些可能对您有用的通用解决方案：

，我是不是把数据库设计搞错了？我是说，会更好吗？

不是的。这是OLTP的理想设计。

，我把查询搞错了吗？

否(尽管子查询中的ORDER BY是多余的)。查询的性能在很大程度上取决于Vote表上的索引，因为所查询的主要列将在本部分中：

SELECT  V.TrackHash, SUM(V.Vote) AS VotesTotal
FROM    Vote V
WHERE   V.CreatedAt > NOW() - INTERVAL 1 MONTH AND V.Vote = 'up'
GROUP BY V.TrackHash

我建议使用两个索引，一个在TrackHash上，一个在CreatedAt、Vote和Type上(这作为3个单独的索引可能表现得更好，值得用两种方法进行测试)。200 k行不是那么多数据，所以使用正确的索引，在过去的一个月中查询数据不需要太长时间。

还有什么我可以改进的吗？

这在很大程度上是一个平衡的行为，它确实取决于您的确切要求，以最好的方式进行。有三种主要的方法可以解决这个问题。

1.您的当前方法(每次查询选票表)

如前所述，我认为这种方法对于您的应用程序应该是可伸缩的。优点是它不需要任何维护，并且发送给应用程序的所有数据都是最新的和准确的。缺点是性能，插入数据(由于更新索引)和选择数据可能需要更长的时间。这将是我更喜欢的方法。

2. OLAP方法

这将涉及维护一个汇总表，例如：

CREATE TABLE VoteArchive
(       TrackHash           CHAR(40) NOT NULL,
        CreatedDate         DATE NOT NULL,
        AppMadeUpVotes      INT NOT NULL,
        AppMadeDownVotes    INT NOT NULL,
        ImportedUpVotes     INT NOT NULL,
        ImportedDownVotes   INT NOT NULL,
        MergedUpVotes       INT NOT NULL,
        MergedDownVotes     INT NOT NULL,
    PRIMARY KEY (CreatedDate, TrackHash)
);

这可以通过运行一个简单的查询来每晚填充。

INSERT VoteArchive
SELECT  TrackHash,
        DATE(CreatedAt),
        COUNT(CASE WHEN Vote = 'Up' AND Type = 0 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Down' AND Type = 0 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Up' AND Type = 1 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Down' AND Type = 1 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Up' AND Type = 2 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Down' AND Type = 2 THEN 1 END)
FROM    Votes
WHERE   CreatedAt > DATE(CURRENT_TIMESTAMP)
GROUP BY TrackHash, DATE(CreatedAt);

然后，您可以使用这个表来代替您的实时数据。它的优点是日期是聚集索引的一部分，因此任何受日期限制的查询都应该非常快。这样做的缺点是，如果您查询此表，直到最后一次填充该表时，才能获得准确的统计数据，那么您将得到更快的查询。这也是维护查询的额外工作。但是，如果我能够查询实时数据，这将是我的第二选择。

3.在投票期间更新统计数据

我包括这是为了完整，但恳请你不要使用这个方法。您可以在应用程序层或通过触发器实现这一点，尽管它允许查询最新的数据而不必查询“产品”表，但它是开放的错误，而且我从来没有遇到过任何真正提倡这种方法的人。对于每一次投票，您都需要执行insert/update逻辑，该逻辑应该将一个非常快速的insert查询转换为一个更长的进程，这取决于您如何进行维护，这是一个机会(尽管并发问题非常小)。

4.上述的组合

您可以始终拥有与您的选票表格式相同的两个表，以及解决方案2中列出的一个表，其中一个表仅用于存储今天的选票，另一个用于保存历史选票，并且仍然可以维护一个汇总表，然后您可以将今天的数据与汇总表结合起来，以获得最新的结果，而无需查询大量数据。同样，这是额外的维护，更有可能出错。