使用左連接優化 MySQL 查詢

好的，首先我將提供一些有關數據庫結構的資訊

我有 3 個在此查詢中使用的索引表（儘管其中一個可能應該是弱實體，但這是我接手時的情況）

person
person_id (pk)

classes
class_id (pk)
course_id (fk)

learners_to_classes
id (pk)
person_id (fk)
class_id (fk)

個人表有大約 46,100 條記錄 learners_to_classes 有大約 51,100 條記錄，類有大約 1670 條記錄。

我正在處理的查詢是動態建構的。我正在開發一個人員搜尋功能，該功能將列出人員的記錄，加上一個指標，如果該人員已完成 2 個特定的課程類型，則基於許多參數。

我遇到的問題是，當提供的唯一參數是無（可能很少使用）或課程類型的組合已經完成時，查詢時間是多少。

例如。我想要一個將 aTrained 設置為 Yes 的人員列表將產生以下查詢：

SELECT 
   p . *,
   IF(sum(c.course_id = 2) > 0, 'Yes','No') as `cTrained`,
   IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as `aTrained`
FROM
   (`person` p)
LEFT JOIN
   (SELECT 
       `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
   FROM
       (`classes` c)
   INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 4) > 0
ORDER BY p.`firstname` asc

搜尋具有 cTrained=‘Yes’ 和 aTrained=‘Yes’ 的人會生成以下查詢：

SELECT 
   p . *,
   IF(sum(c.course_id = 2) > 0,'Yes','No') as `connectorTrained`,
   IF(sum(c.course_id = 4) > 0,'Yes','No') as `asistTrained`
FROM
   (`person` p)
       LEFT JOIN
   (SELECT 
       `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
   FROM
       (`classes` c)
   INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 2) > 0
   AND sum(c.course_id = 4) > 0
ORDER BY `firstname` asc

這些查詢如何執行超過 100 秒，我似乎想不出優化它們的方法。我希望這裡的人可能有一個想法

首先，擺脫子查詢。它不是必需的，它會干擾優化。其次，您不需要left join, 因為該having子句要求匹配。

SELECT p .*,
      IF(sum(c.course_id = 2) > 0, 'Yes', 'No') as cTrained,
      IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as aTrained
FROM person p JOIN
    learners_to_classes ltc
    ON p.person_id = ltc.person_id JOIN
    classes c
    ON ltc.class_id = c.class_id
GROUP BY p.person_id
HAVING sum(c.course_id = 4) > 0
ORDER BY p.firstname asc

對於此查詢，您需要明顯的索引： learners_to_classes(person_id, class_id)和classes(class_id). 第一個似乎不見了。

引用自：https://dba.stackexchange.com/questions/101993