mysql - 使用跨 3 个表的简单联接优化 MySQL 查询

Question

我有以下数据库表:

users
user_id (INT) (PRIMARY), is_cool (TINY) (1 or 0)

INDEX ON is_cool

activity 
activity_id (INT) (PRIMARY), user_id (INT), item_id (INT), created_at (DATETIME)

INDEX on item_id
INDEX on user_id
INDEX on created_at

items
item_id (INT) (PRIMARY), item_name VARCHAR(255), item_parent_id INT()

INDEX ON item_parent_id

我正在尝试在这些选项卡上进行简单的联接，并且我通过以下查询获得了很长的查询时间(> 2秒):

SELECT i.item_name, a.activity_id, a.user_id, a.created_at
FROM activity as a
INNER JOIN item as i on a.item_id = i.item_id
INNER JOIN users as u on u.user_id = a.user_id
WHERE i.item_parent_id = 1 and u.is_cool = 1
ORDER by a.created_at DESC
LIMIT 0, 25

关于如何改进这个问题有什么建议吗？由于数据库的庞大性质，它似乎悬而未决。对于较小的记录，它工作得很好，但对于具有 100k 条记录的记录 - 它需要很长时间的过程。

Answer 1

这里的执行计划取决于数据库中的数据分布，但 MySQL 无论如何都不会在这里选择正确的索引。

问题

您基本上希望迭代按created_at排序且符合其他表中条件的所有事件。最有可能的是，MySQL 会更有选择性地处理条件 i.item_parent_id = 1 并开始从 item 表进行连接。也就是说，排序将在连接表中的字段上进行，如果连接从事件表中生成大部分行，则会导致文件排序和性能不佳。如果您向我们展示 EXPLAIN 的结果会更好。

解决方案

如果 activity JOIN item 的结果生成几行(例如，少于 1000 行)，我只需创建以下索引:您在 item_parent_id 上已有的索引、activity (item_id,created_at) 和用户 (user_id, is_cool) 的复合索引。

如果activity JOIN item的结果产生许多行(更有可能)，我会在activity上创建(item_id,created_at)， item 上的 (item_id, item_parent_id)，users 上的 (user_id, is_cool)，并添加 STRAIGHT_JOIN 选项查询:

SELECT STRAIGHT_JOIN i.item_name, a.activity_id, a.user_id, a.created_at
FROM activity as a
INNER JOIN item as i on a.item_id = i.item_id
INNER JOIN users as u on u.user_id = a.user_id
WHERE i.item_parent_id = 1 and u.is_cool = 1
ORDER by a.created_at DESC
LIMIT 0, 25;