在MySQL 5中优化多层联接中父表上的不同

Question

我有以下表:在MySQL 5数据库中：

• projects - 1,000行
• tasks (FKed to projects) - 10,000行
• task_tags (FKed to tasks) -35万行
• task_comments (FKed to tasks) - 750,000行

并希望获得任何任务在标记或注释中都有“紧急”一词的项目列表，即

select DISTINCT p.*
from projects p
join tasks t on p.id = t.project_id
left join task_tags tt on t.id = tt.task_id
left join task_comments tc on t.id = tc.task_id
where tt.value = 'urgent' OR tc.text = 'urgent'

如果没有DISTINCT，查询将在0.1秒内执行(所使用的索引看起来是合理的)。可视化执行计划(表为projects、tasks、task_tags和task_comments按从左到右的顺序排列)：

添加DISTINCT会使查询严重不符合性能(按分钟的顺序)，在上一个嵌套循环之后添加不同的内容，因此我认为需要MySQL对所有结果行进行排序，然后去重复它们。

虽然这是正确的，但这并不是最好的选择--毕竟，一旦我们知道一个项目符合标准，就没有必要检查该项目的任何其他任务，因为这不是select distinct p.*, t.id --但我不知道如何鼓励MySQL更明智地规划这个项目，因为我不知道我想要什么样的计划。

我试过一些类似的东西

select distinct project_id
from tasks
where id in (
    select task_id from task_tags where value = 'urgent'
    union
    select task_id from task_comments where text = 'urgent'
)

作为另一种选择，尽管我认为它稍微快了一点，但我们也需要几分钟的时间，而且我没有其他很好的想法(除了在MySQL 5中没有像持久化视图那样的东西，但是在现阶段升级DB不是一个实际的选择)。

对于我可以尝试什么(甚至下一步在哪里调查)，有什么想法吗？数据库模型是相当固定的，但我当然可以添加索引并乐于查看选项。

Answer 1

让我们把它往内翻，这样我们就可以看到它是从正确的地方开始的。优化者不会为我们做这件事。

1. 从每一张可能说“紧急”的表开始
2. UNION他们。(UNION DISTINCT比UNION ALL稍慢，但您可能会得到两个重复行。你自己决定。)
3. 加入到tasks以获得project_id
4. 最后，将需要的几行扩展到projects中。(请注意，在找出大多数行不需要之前，这两种公式都需要获取所有的p。)

从OR转换到UNION是个好主意，但IN ( SELECT ... )不是一个有效的构造。

SELECT p.*
    FROM (
         SELECT t.project_id
            FROM task_comments tc
            JOIN tasks t  ON t.id = tc.task_id
            WHERE tc.text = 'urgent'  -- see Note
         ) UNION DISTINCT (
         SELECT t.project_id
            FROM task_tags tt
            JOIN tasks t  ON t.id = tt.task_id
            WHERE tt.value = 'urgent'
         ) AS x
    JOIN projects p  ON p.id = x.project_id

那就需要

tc:  INDEX(text, task_id)  -- see Note
t:   (I assume you have PRIMARY KEY(id))
tt:  INDEX(value, task_id)
p:   (I assume you have PRIMARY KEY(id))

注意:也许你真的想在tc.text的任何地方检查“紧急”？如果是这样的话，优化它的最好方法是

tc:  FULLTEXT(text)

然后切换到

WHERE MATCH(tc.text) AGAINST ('+urgent' IN BOOLEAN MODE)