我该如何加速（或拆分）这个MySQL查询？

Question

我正在用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

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

根据用户的喜好，我想给每个未观看的视频打分，并预测他们最可能喜欢哪个视频。这导致我写了一个非常复杂的查询，处理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

Answer 1

• 把 *rating_global* 这个字段的类型改成数字类型，可以是浮点数（float）或者整数（integer），没必要用字符型（varchar）。我个人建议把所有评分字段都改成整数，因为我觉得用浮点数没什么必要。

• 去掉 id 上的索引，因为主键（PRIMARY KEY）已经有索引了。视频的 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

为了让后面的查询速度更快，你可以在 video 表的主键中加入 website_id 和 rating_global 字段（可能只加 website_id 就够了）。

你还可以使用另一个表来存储这些统计数据，并根据用户的登录或操作频率动态计算。我猜你可以显示缓存的数据，而不是实时结果，这样差别不大。