MySQL：加入未使用的表時的索引（性能優化問題）

我需要優化以下查詢：

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE blogposts.deleted = 0
AND blogposts.title LIKE '%{de}%'
AND blogposts.visible = 1
AND blogposts.date_published <= NOW()
ORDER BY blogposts.date_created DESC
LIMIT 0 , 50

EXPLAIN SELECT 給我以下結果：

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE articles ALL blogpost_id NULL NULL NULL 6915 Using temporary; Using filesort
1 SIMPLE blogposts eq_ref PRIMARY PRIMARY 4 articles.blogpost_id 1 Using where

為什麼它先是文章，然後是博文？是因為博文有更多條目嗎？以及如何改進查詢以便articlepost 可以使用索引？

更新： 在 blogposts.date_created 上設置了索引。刪除 blogposts.title LIKE 條件和 date_published <= NOW() 不會做任何事情。

當我刪除“ articles.id AS articleid ”時，它可以在文章上使用 blogpost_id 索引……對我來說聽起來很奇怪，有人知道為什麼嗎？（因為我真的需要它..）

新的解釋如下所示：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  articles    index blogpost_id blogpost_id    4    NULL    6915    Using index; Using temporary; Using filesort
1   SIMPLE  blogposts   eq_ref  PRIMARY PRIMARY 4   articles.blogpost_id    1   Using where

我仔細查看了查詢，您也許可以重新設計它。這就是我的意思：

查詢的 LIMIT 0,50 部分似乎最後在查詢中忙。

您可以通過執行以下操作來改進查詢的佈局：

步驟 1) 創建一個內聯查詢以僅收集鍵。在這種情況下，部落格文章的 id。

步驟 2) 對帶鍵的內聯查詢施加任何 WHERE、ORDER BY 和 GROUP BY 子句。

步驟 3) 將 LIMIT 子句作為進行內聯查詢的最後一步。

第 4 步）如果您需要來自 blogpost 的其他列作為 blostpost 臨時表，則將內聯查詢與 blogpost 表連接起來

步驟 5) 將這個新的 blogpost temptable 加入到文章表中。

步驟 1-3 旨在創建一個恰好有 50 行並包含博文 ID 的臨時表。然後，最後執行所有 JOIN。

將這些步驟應用於您的原始查詢後，您應該擁有：

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM
(
 SELECT B.*
 FROM
 (
   SELECT id FROM blogposts
   WHERE date_published <= NOW()
   AND deleted = 0 AND visible = 1
   AND title LIKE '%{de}%'
   ORDER BY date_created DESC
   LIMIT 0,50
 ) A
 INNER JOIN blogposts B USING (id)
) blogposts
INNER JOIN articles
ON blogposts.id = articles.blogpost_id;

由於您編輯了問題並聲明您將刪除 LIKE，現在您的查詢應該看起來更像這樣：

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM
(
 SELECT B.*
 FROM
 (
   SELECT id FROM blogposts
   WHERE date_published <= NOW()
   AND deleted = 0 AND visible = 1
   ORDER BY date_created DESC
   LIMIT 0,50
 ) A
 INNER JOIN blogposts B USING (id)
) blogposts
INNER JOIN articles
ON blogposts.id = articles.blogpost_id;

在片語中

$$ things omitted $$，如果除了鍵之外您不需要部落格文章中的任何內容，那麼您的查詢應該如下所示：

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM
(
 SELECT id FROM blogposts
 WHERE date_published <= NOW()
 AND deleted = 0 AND visible = 1
 ORDER BY date_created DESC
 LIMIT 0,50
) blogposts
INNER JOIN articles
ON blogposts.id = articles.blogpost_id;

警告

確保您建構的索引涉及已刪除、可見和 date_created 列，如下所示：

ALTER TABLE blogposts ADD INDEX deleted_visible_date_created (deleted,visible,date_created);

試一試 ！！！

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