我想做的是加快这个查询的速度。目前它的速度大约慢了15秒以上。r.email和v.send上都有索引，还有r.id、r.viewed以及r.saved。EXPLAIN显示两个键都在使用中，但我看到它使用的是Using where; Using temporary; Using filesort。
有什么改进这个查询的想法吗？
SELECT r.id, r.top, r.email
FROM request as r
WHERE r.viewed = 0 AND r.saved = 0 AND r.email NOT IN (
SELECT v.sent FROM viewing as v WHERE v.sent = r.email
)
GROUP BY r.email
ORDER BY r.top desc, r.date
LIMIT 100;
MySQL在IN子句中为多个记录使用临时表。尝试
SELECT r.id, r.top, r.email
FROM request as r
left outer join viewing v on v.sent = r.email
WHERE r.viewed = 0 AND r.saved = 0 AND v.sent is null
GROUP BY r.email
ORDER BY r.top desc, r.date
LIMIT 100;
使用LEFT JOIN而不是NOT IN：为什么。。？参见此处
SELECT r.id, r.top, r.email
FROM request as r
LEFT JOIN viewing v
ON r.email= v.sent
WHERE r.viewed = 0 AND
r.saved = 0 AND
v.sent IS NULL
GROUP BY r.email
ORDER BY r.top DESC, r.date
LIMIT 100;
为了获得最佳性能，请考虑在表上添加以下索引，最好包括索引：
ALTER TABLE request ADD INDEX ix1 (email, viewed, saved, top, date);
ALTER TABLE viewing ADD INDEX ix1 (sent);
SELECT
r.id,
r.top,
r.email
FROM
request r
LEFT OUTER JOIN
viewing v
ON
v.sent = r.email
WHERE
r.viewed = 0
AND
r.saved = 0
AND
v.sent IS NULL
GROUP BY
r.email
ORDER BY
r.top DESC, r.date
LIMIT 100
显然MySQL存在子查询性能问题。
幸运的是，有一些方法可以解决这个问题。具体来说，将子查询转换为联接，或者在特定情况下转换为排除联接。