MySQL:具有许多连接的慢查询

Question

我有一个包含大约 100 数百个 Mysql 表的数据库。所有表都在 INNODB 下。
我们公司为我们的用户提供语法检查和翻译服务。

这是简化的数据库模式

database schema

每个用户都可以通过搜索引擎通过关键字搜索自己的项目。每个查询都使用全文搜索。

我还创建了一个 View :

create view searches as 
Select
  project_contents.content_markdown,
  project_contents.content_jp_markdown,
  project_contents.remarks_markdown,
  project_corrections.correction_markdown,
  project_corrections.comment_markdown,
  project_messages.message_from_markdown,
  project_messages.message_to_markdown,
  projects.id,
  projects.user_id,
  projects.project_category_id,
  projects.uuid,
  projects.teacher_id,
  projects.title,
  projects.is_deleted,
  projects.submit_date,
  projects.receipt_date,
  projects.correct_date,
  project_questions.question_markdown,
  project_questions.answer_markdown,
  project_rates.content_markdown As rate_content_markdown,
  teachers.username,
  project_categories.parent_id,
  project_additional_writings.append_markdown
From
  project_contents 
    INNER JOIN projects
        ON project_contents.project_id = projects.id
    INNER JOIN project_messages
        ON project_messages.project_id = projects.id 
    INNER JOIN project_categories
        ON projects.project_category_id = project_categories.id
    LEFT JOIN project_questions
        ON project_questions.project_id = projects.id 
    LEFT JOIN project_rates
        ON project_rates.project_id = projects.id
    LEFT JOIN project_corrections
        ON project_corrections.project_content_id = project_contents.id 
    LEFT JOIN project_additional_writings
        ON project_additional_writings.project_id = projects.id
    LEFT JOIN teachers
        ON projects.teacher_id = teachers.id

这里是查询

SELECT   `search`.`id`,
         `search`.`uuid`,
         `search`.`user_id`,
         `search`.`parent_id`,
         `search`.`project_category_id`,
         `search`.`teacher_id`,
         `search`.`username`,
         `search`.`is_deleted`,
         `search`.`title`,
         `search`.`submit_date`,
         `search`.`receipt_date`,
         `search`.`correct_date`,
         `search`.`content_jp_markdown`,
         `search`.`remarks_markdown`,
         `search`.`content_markdown`,
         `search`.`correction_markdown`,
         `search`.`comment_markdown`,
         `search`.`question_markdown`,
         `search`.`answer_markdown`,
         `search`.`append_markdown`,
         `search`.`rate_content_markdown`,
         `search`.`message_from_markdown`,
         `search`.`message_to_markdown`,
         `search`.`memo_markdown`,
         (match(`search`.`title`) against('"famous blogger"'                 IN boolean mode)) AS `search__score_title`,
         (match(`search`.`content_markdown`) against('"famous blogger"'      IN boolean mode)) AS `search__score_content_markdown`,
         (match(`search`.`content_jp_markdown`) against('"famous blogger"'   IN boolean mode)) AS `search__score_content_jp_markdown`,
         (match(`search`.`remarks_markdown`) against('"famous blogger"'      IN boolean mode)) AS `search__score_remarks_markdown`,
         (match(`search`.`correction_markdown`) against('"famous blogger"'   IN boolean mode)) AS `search__score_correction_markdown`,
         (match(`search`.`comment_markdown`) against('"famous blogger"'      IN boolean mode)) AS `search__score_comment_markdown`,
         (match(`search`.`message_from_markdown`) against('"famous blogger"' IN boolean mode)) AS `search__score_message_from_markdown`,
         (match(`search`.`message_to_markdown`) against('"famous blogger"'   IN boolean mode)) AS `search__score_message_to_markdown`,
         (match(`search`.`question_markdown`) against('"famous blogger"'     IN boolean mode)) AS `search__score_question_markdown`,
         (match(`search`.`answer_markdown`) against('"famous blogger"'       IN boolean mode)) AS `search__score_answer_markdown`,
         (match(`search`.`append_markdown`) against('"famous blogger"'       IN boolean mode)) AS `search__score_append_markdown`,
         (match(`search`.`rate_content_markdown`) against('"famous blogger"' IN boolean mode)) AS `search__score_rate_content_markdown`,
         (match(`search`.`memo_markdown`) against('"famous blogger"'         IN boolean mode)) AS `search__score_memo_markdown`
FROM     `idiy_v3`.`searches`  AS `search`
WHERE    `search`.`is_deleted` = '0'
AND      `search`.`parent_id` = 1
AND      `search`.`user_id` = 217
AND      ((`search`.`username` LIKE '%famous blogger%')
         OR (match(`search`.`title`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`content_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`content_jp_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`remarks_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`correction_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`comment_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`message_from_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`message_to_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`question_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`answer_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`append_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`rate_content_markdown`) against('\"famous blogger\"' IN boolean mode))
         OR (match(`search`.`memo_markdown`) against('\"famous blogger\"' IN boolean mode)))
GROUP BY `search`.`id`
ORDER BY `search`.`submit_date` DESC limit 5

默认情况下，每个查询需要 15 到 25 秒(取决于用户的项目数量、关键字等)。

当我从 project_corrections 表中删除列时，查询不到 10 秒。

为什么会有这么大的差异？
如何优化我的查询？

Answer 1

添加一个新表，其中包含副本 的用户名和所有标记并连接到一个TEXT 列中。在该列上有一个 FULLTEXT 索引。您的 SELECT 将针对该列执行单个 MATCH，然后 JOIN 到其他表以获取详细信息。

一次 MATCH 的速度大约是进行 13 次 MATCHes 的 13 倍。而且 OR 很难优化。