希望你做得很好.
我需要一点这个数据库的帮助:
这是一个存储投票的数据库.用户选择他们喜欢的音轨,然后投票给他们.他们可以“投票”或“投票”投票.非常简单.但是,当谈到计算统计数据时,它变得毛茸茸.
元
它是一个键值样式表,存储最常用的统计信息(只是排序缓存):
mysql> SELECT * FROM Meta;
+-------------+-------+
| Key | Value |
+-------------+-------+
| TRACK_COUNT | 2620 |
| VOTE_COUNT | 3821 |
| USER_COUNT | 371 |
+-------------+-------+
投票
投票表持有投票本身.这里唯一有趣的字段是Type,其值意味着:
> 0 – 应用程序制作投票,用户使用UI为该曲目投票
> 1 – 导入投票(来自外部服务)
> 2 – 合并投票.实际上与导入的投票相同,但它实际上已经注意到,该用户已经使用外部服务投票支持此曲目,现在他正在重复使用该应用程序.
跟踪
该轨道保留了自己的总统计数据.喜欢,不喜欢外来服务(LikesRP),不喜欢外部服务(DislikesRP),喜欢/不喜欢调整.
应用
该应用程序需要获得投票:
在过去7天内,有5首最多投票的曲目
在过去的7天里,> 5首最受欢迎的曲目
在过去7天内投票最多的5首曲目,投票从外部服务中导入(Vote.Type = 1)
上个月有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万票,这很可能会杀死这个数据库.所以我正在弄清楚如何解决这个难题.
现在我回答这些问题:
>我的数据库设计是否错误?我的意思是,它会更好吗?
>我的查询错了吗?
>还有什么我可以提高的吗?
我做的第一件事就是缓存.但是,好的,这大大解决了这个问题.但我对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 | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+