如何加速（或优化）这个MySQL查询？

Question

如何加速（或优化）这个MySQL查询？

3

我正在用Python和MySQL构建一个视频推荐网站（类似于Pandora音乐的推荐），我的数据库中有三个表：

video - 存储视频信息，数据不会改变。列包括：

CREATE TABLE `video` (
    id int(11) NOT NULL AUTO_INCREMENT,
    website_id smallint(3) unsigned DEFAULT '0',
    rating_global varchar(128) DEFAULT '0',
    title varchar(256) DEFAULT NULL,
    thumb_url text,
PRIMARY KEY (`id`),
KEY `websites` (`website_id`),
KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=49362 DEFAULT CHARSET=utf8

video_tag - 与每个视频相关联的标签（属性）的表格。不会改变。

CREATE TABLE `video_tag` (
    id int(7) NOT NULL AUTO_INCREMENT,
    video_id mediumint(7) unsigned DEFAULT '0',
    tag_id mediumint(7) unsigned DEFAULT '0',
PRIMARY KEY (`id`),
KEY `video_id` (`video_id`),
KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=562456 DEFAULT CHARSET=utf8

用户评分 - 用户对每个标签的好坏评级表格。数据始终在变化。

CREATE TABLE `user_rating` (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id smallint(3) unsigned DEFAULT '0',
    tag_id int(5) unsigned DEFAULT '0',
    tag_rating float(10,5) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `video` (`tag_id`),
KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=447 DEFAULT CHARSET=utf8

根据用户的偏好，我希望对每个未观看的视频进行评分，并尝试预测他们最喜欢的内容。这导致了以下庞大的查询，对于50,000个视频需要约2秒钟才能完成：

SELECT video_tag.video_id, 
       (sum(user_rating.tag_rating) * video.rating_global) as score 

FROM video_tag 
JOIN user_rating ON user_rating.tag_id = video_tag.tag_id
JOIN video ON video.id = video_tag.video_id 

WHERE user_rating.user_id = 1 AND video.website_id = 2 
AND rating_global > 0 AND video_id NOT IN (1,2,3) GROUP BY video_id 
ORDER BY score DESC LIMIT 20

我急需提高效率，所以我正在寻求建议，希望了解最佳方向。有一些想法：

a) 重构我的数据库表结构（不确定如何）

b) 将更多的分组和聚合工作转移至Python中（还没有找到一个真正快速的连接三个表的方式）

c) 尝试将不变的表存储在内存中以尝试加快计算时间（早期的尝试还没有带来任何收益...）

您会推荐如何使其更有效率呢？

非常感谢！

--

根据评论的请求，EXPLAIN SELECT...显示：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  user_rating ref      video,user_id  user_id 3   const   88  Using where; Using temporary; Using filesort
1   SIMPLE  video_tag   ref      video_id,tag_id    tag_id  4   db.user_rating.tag_id   92  Using where
1   SIMPLE  video       eq_ref  PRIMARY,websites,id PRIMARY 4   db.video_tag.video_id   1   Using where

- thegreatt

1

这并没有太大的帮助，你应该包含适当的架构，因为架构将包括数据类型+索引类型/列。 - ajreal

这是否更符合您的想法？ - thegreatt

再加上，要包括您的执行计划说明（要求将所有内容都包含在内的目的是帮助人们理解您的问题，这对您有益）。 - ajreal

谢谢，但您能否解释一下吗？我不知道你所说的执行计划是什么意思。 - thegreatt

2

解释执行计划=EXPLAIN SELECT ...;（请参考此处 - http://dev.mysql.com/doc/refman/5.0/en/explain.html）。欢迎提问！（并且在下一个问题时请尽可能提供更多信息） - ajreal

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- georgepsarakis · Accepted Answer

将 *rating_global* 字段类型更改为数字类型（浮点数或整数），无需为 varchar 类型。个人建议将所有评分字段都更改为整数，我认为它们不需要为浮点数。
删除 id 上的 KEY，PRIMARY KEY 已经有索引了。video.id,rating_global,website_id
注意你的引用的整数长度（例如 video_id -> video.id），你可能会用完数字。这些大小应该相同。

我建议采用以下两步解决方案来替换你的查询：

CREATE TEMPORARY TABLE rating_stats ENGINE=MEMORY
SELECT video_id, SUM(tag_rating) AS tag_rating_sum 
FROM user_rating ur JOIN video_tag vt ON vt.id = ur.tag_id AND ur.user_id=1
GROUP BY video_id ORDER BY NULL

SELECT v.id, tag_rating_sum*rating_global AS score FROM video v 
JOIN rating_stats rs ON rs.video_id = v.id 
WHERE v.website_id=2 AND v.rating_global > 0 AND v.id NOT IN (1,2,3)
ORDER BY score DESC LIMIT 20

为了使后面的查询速度真正快，您可以在视频表中将website_id和rating_global字段合并到PRIMARY KEY中（也许只有website_id就足够了）。

您还可以使用另一个表格来存储这些统计数据，并根据用户登录/操作频率动态预计算。我猜您可以显示缓存数据而不是实时结果，两者之间应该没有太大区别。