如何優化此查詢以避免下面描述的全表掃描？
我有一個緩慢的查詢，大約需要 15 秒才能回傳。
讓我們把這部分排除在外——我已經確認所有索引都在那里。
當我運行時EXPLAIN，它顯示在表上運行了 FULL TABLE 掃描crosswalk（fromQuestionCategoryJoinID沒有使用索引，即使我試圖強制） - 如果我洗掉任何一個欄位和OR，則使用索引并且查詢完成以毫秒為單位。
SELECT c.id, c.name, GROUP_CONCAT(DISTINCT tags.externalDisplayID SEPARATOR ', ') AS tags
FROM checklist c
LEFT JOIN questionchecklistjoin qcheckj on qcheckj.checklistID = c.id
LEFT JOIN questioncategoryjoin qcatj ON qcatj.questionID = qcheckj.questionID
LEFT JOIN questioncategoryjoin qcatjsub on qcatjsub.parentQuestionID = qcatj.questionID
LEFT JOIN crosswalk cw on (cw.fromQuestionCategoryJoinID = qcatj.id OR cw.fromQuestionCategoryJoinID = qcatjsub.id)
-- index used if I remove OR, eg.: LEFT JOIN crosswalk cw on (cw.fromQuestionCategoryJoinID = qcatj.id)
LEFT JOIN questioncategoryjoin qcj1 on qcj1.id = cw.toQuestionCategoryJoinID
LEFT JOIN question tags on tags.id = qcj1.questionID
GROUP BY c.id
ORDER BY c.name, tags.externalDisplayID;
uj5u.com熱心網友回復：
將查詢拆分為每個部分的兩個查詢OR。然后將它們與UNION.
SELECT id, name, GROUP_CONCAT(DISTINCT externalDisplayID SEPARATOR ', ') AS tags
FROM (
SELECT c.id, c.name, tags.externalDisplayID
FROM checklist c
LEFT JOIN questionchecklistjoin qcheckj on qcheckj.checklistID = c.id
LEFT JOIN questioncategoryjoin qcatj ON qcatj.questionID = qcheckj.questionID
LEFT JOIN crosswalk cw on cw.fromQuestionCategoryJoinID = qcatj.id
LEFT JOIN questioncategoryjoin qcj1 on qcj1.id = cw.toQuestionCategoryJoinID
LEFT JOIN question tags on tags.id = qcj1.questionID
UNION ALL
SELECT c.id, c.name, tags.externalDisplayID
FROM checklist c
LEFT JOIN questionchecklistjoin qcheckj on qcheckj.checklistID = c.id
LEFT JOIN questioncategoryjoin qcatj ON qcatj.questionID = qcheckj.questionID
LEFT JOIN questioncategoryjoin qcatjsub on qcatjsub.parentQuestionID = qcatj.questionID
LEFT JOIN crosswalk cw on cw.fromQuestionCategoryJoinID = qcatjsub.id
LEFT JOIN questioncategoryjoin qcj1 on qcj1.id = cw.toQuestionCategoryJoinID
LEFT JOIN question tags on tags.id = qcj1.questionID
) AS x
GROUP BY x.id
ORDER BY x.name
此外，包含externalDisplayIDin沒有意義ORDER BY，因為這將按其值從組中的隨機行中排序。如果這是你想要的，你可以ORDER BY externalDisplayID加入GROUP_CONCAT()引數。
uj5u.com熱心網友回復：
這里還有第二個低效問題。我稱之為“爆炸-內爆”。首先一堆JOINs（可能）擴展中間表中GROUP BY c.id的行數，然后將行數折疊回您開始時的行數（每行輸出一行checkpoint）。
在嘗試提供幫助之前，請回答：
- 是否
LEFT真的需要？ - 每個表有多少行？（特別是在
cw） - 你能擺脫
DISTINCT嗎？
可以通過延遲JOINs to qcj1and標簽until after theUNION來改進 Barmar 的答案：
SELECT ...
FROM ( SELECT ...
FROM first few tables
UNION ALL
SELECT ...
FROM first few tables
) AS u
[LEFT] JOIN qcj1
[LEFT] JOIN tags
GROUP BY ...
ORDER BY ...
另一個優化（再次建立在 Barmar 的基礎上）
GROUP BY x.id
ORDER BY x.name
-->
GROUP BY x.name, x.id
ORDER BY x.name, x.id
當GROUP BY和ORDER BY中的專案“相同”時，它們可以在單個操作中完成，從而（至少）節省一個排序。
x.name, x.id是確定性的，其中 asx.name可能會name以不同的順序放置兩行相同的行，這取決于（可能）取決于月相。
這些索引可能有幫助：
qcheckj: INDEX(checklistID, questionID)
qcatj: INDEX(questionID, id)
qcatjsub: INDEX(parentQuestionID, id)
cw: INDEX(fromQuestionCategoryJoinID, toQuestionCategoryJoinID)
uj5u.com熱心網友回復：
試試這個方法：
SELECT c.id, c.name, GROUP_CONCAT(DISTINCT tags.externalDisplayID
SEPARATOR ', ') AS tags
FROM checklist c
LEFT JOIN questionchecklistjoin qcheckj on qcheckj.checklistID = c.id
LEFT JOIN questioncategoryjoin qcatj ON qcatj.questionID =
qcheckj.questionID
LEFT JOIN questioncategoryjoin qcatjsub on qcatjsub.parentQuestionID =
qcatj.questionID
LEFT JOIN crosswalk cw on
(cw.fromQuestionCategoryJoinID,cw.fromQuestionCategoryJoinID) =
(qcatj.id,qcatjsub.id)
LEFT JOIN crosswalk cw on (cw.fromQuestionCategoryJoinID = qcatj.id)
LEFT JOIN questioncategoryjoin qcj1 on qcj1.id =
cw.toQuestionCategoryJoinID
LEFT JOIN question tags on tags.id = qcj1.questionID
GROUP BY c.id
ORDER BY c.name, tags.externalDisplayID;
這將洗掉 OR 并實作相同的 .
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/399391.html
標籤：mysql