MySQL 在加入另一個表時不使用索引

我有兩個表，第一個表包含 CMS 中的所有文章/部落格文章。其中一些文章也可能出現在雜誌中，在這種情況下，它們與包含雜誌特定資訊的另一個表具有外鍵關係。

這是這兩個表的 create table 語法的簡化版本，其中刪除了一些非必要的行：

CREATE TABLE `base_article` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date_published` datetime DEFAULT NULL,
 `title` varchar(255) NOT NULL,
 `description` text,
 `content` longtext,
 `is_published` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `base_article_date_published` (`date_published`),
 KEY `base_article_is_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mag_article` (
   `basearticle_ptr_id` int(11) NOT NULL,
   `issue_slug` varchar(8) DEFAULT NULL,
   `rubric` varchar(75) DEFAULT NULL,
   PRIMARY KEY (`basearticle_ptr_id`),
   KEY `mag_article_issue_slug` (`issue_slug`),
   CONSTRAINT `basearticle_ptr_id_refs_id` FOREIGN KEY (`basearticle_ptr_id`) REFERENCES `base_article` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CMS 總共包含大約 250,000 篇文章，我編寫了一個簡單的Python 腳本，如果他們想在本地複制此問題，可以使用該腳本使用範例數據填充測試數據庫。

如果我從這些表中選擇一個，MySQL 選擇合適的索引或快速檢索文章沒有問題。但是，當兩個表在一個簡單的查詢中連接在一起時，例如：

SELECT * FROM `base_article` 
INNER JOIN `mag_article` ON (`mag_article`.`basearticle_ptr_id` = `base_article`.`id`)
WHERE is_published = 1
ORDER BY `base_article`.`date_published` DESC
LIMIT 30

MySQL 未能選擇適當的索引，性能下降。這是相關的解釋擴展（執行時間超過一秒）：

+----+-------------+--------------+--------+-----------------------------------+---------+---------+----------------------------------------+-------+----------+---------------------------------+
| id | select_type |    table     |  type  |           possible_keys           |   key   | key_len |                  ref                   | rows  | filtered |              Extra              |
+----+-------------+--------------+--------+-----------------------------------+---------+---------+----------------------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | mag_article  | ALL    | PRIMARY                           | NULL    | NULL    | NULL                                   | 23830 | 100.00   | Using temporary; Using filesort |
|  1 | SIMPLE      | base_article | eq_ref | PRIMARY,base_article_is_published | PRIMARY | 4       | my_test.mag_article.basearticle_ptr_id |     1 | 100.00   | Using where                     |
+----+-------------+--------------+--------+-----------------------------------+---------+---------+----------------------------------------+-------+----------+---------------------------------+

• 編輯 9 月 30 日：我可以WHERE從此查詢中刪除子句，但EXPLAIN看起來仍然一樣，查詢仍然很慢。

一種可能的解決方案是強制索引。執行相同的查詢FORCE INDEX (base_articel_date_published)會導致在大約 1.6 毫秒內執行的查詢。

+----+-------------+--------------+--------+---------------+-----------------------------+---------+-------------------------+------+-----------+-------------+
| id | select_type |    table     |  type  | possible_keys |             key             | key_len |           ref           | rows | filtered  |    Extra    |
+----+-------------+--------------+--------+---------------+-----------------------------+---------+-------------------------+------+-----------+-------------+
|  1 | SIMPLE      | base_article | index  | NULL          | base_article_date_published |       9 | NULL                    |   30 | 833396.69 | Using where |
|  1 | SIMPLE      | mag_article  | eq_ref | PRIMARY       | PRIMARY                     |       4 | my_test.base_article.id |    1 | 100.00    |             |
+----+-------------+--------------+--------+---------------+-----------------------------+---------+-------------------------+------+-----------+-------------+

如果可以避免，我寧願不必在此查詢上強制建立索引，原因有幾個。最值得注意的是，可以通過多種方式過濾/修改此基本查詢（例如按 過濾issue_slug），之後base_article_date_published可能不再是使用的最佳索引。

任何人都可以提出提高此查詢性能的策略嗎？

怎麼樣，這應該消除對“使用臨時；使用文件排序”的需要，因為數據已經在正確的排序中。

您需要知道為什麼 MySQL 需要“使用臨時；使用文件排序”來消除這種需要。

有關消除需要的說明，請參見第二個 sqlfriddle

SELECT
     *
   FROM base_article

   STRAIGHT_JOIN 
     mag_article
   ON
     (mag_article.basearticle_ptr_id = base_article.id)

   WHERE
     base_article.is_published = 1

   ORDER BY
     base_article.date_published DESC

見http://sqlfiddle.com/#!2/302710/2

效果很好，我前段時間也需要這個用於國家/城市表，請參閱此處的範例數據http://sqlfiddle.com/#!2/b34870/41

編輯後，如果 base_article.is_published = 1 總是返回 1 條記錄，如您解釋的那樣，您可能還想分析此答案，INNER JOIN 傳遞的表可能會提供更好的性能，如以下答案中的查詢

https://stackoverflow.com/questions/18738483/mysql-slow-query-using-filesort/18774937#18774937

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