我正在用 Python 和 MySQL 构建一个视频推荐网站(想想音乐视频的潘多拉)。我的数据库中有三个表:

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

user_rating - 用户给每个标签的好坏评级表。数据总是在变化。

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

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

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) 将不变的表存储在内存中以尝试加快计算时间(早期的修补尚未产生任何 yield ..)

您建议如何提高效率？

谢谢!!

--

根据评论中的请求，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

最佳答案

• 将 *rating_global* 的字段类型更改为数字类型( float 或整数)，无需为 varchar。就我个人而言，我会将所有评分字段更改为整数，我发现它们不需要是 float 。

• 将 KEY 放到 id 上，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

为了使后一个查询执行得非常快，您可以将 PRIMARY KEY 合并到 video 表字段 website_id 和 rating_global 中(也许只有 website_id 就足够了)。

您还可以使用包含这些统计信息的另一个表，并根据用户登录/操作频率进行动态预计算。我猜你可以显示缓存数据而不是显示实时结果，应该没有太大区别。

关于python - 我如何加速(或分解)这个 MySQL 查询？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7411028/