為簡單的 MySQL 查詢優化 ORDER BY

我正在嘗試優化這個非常簡單的查詢，現在它讓我悲傷了一天:(

看起來很簡單，我需要從 2 個表中選擇一個 JOIN，並獲得按特定順序排序的前 X 個結果。這是查詢：

SELECT * FROM `po` 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10

但是，它執行得非常慢（半秒到 2 秒）。以下是表結構：

CREATE TABLE `po` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `supplier_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB AUTO_INCREMENT=457790 DEFAULT CHARSET=latin1

CREATE TABLE `po_suppliers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9386 DEFAULT CHARSET=latin1

執行 EXPLAIN 揭示了這個問題：

+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref          | rows | Extra                                        |
+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | index | PRIMARY       | PRIMARY     | 4       | NULL         |  480 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | po    | ref   | supplier_id   | supplier_id | 4       | sergiis.s.id |    1 | Using index                                  |
+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+

你能幫我弄清楚如何優化它以更快地執行嗎？我在我排序的列上有索引。我在加入的列上有索引。如果我刪除 JOIN - 這太快了。如果我刪除 ORDER BY，它會非常快。為什麼我得到這個可怕的臨時 + 文件排序？

你發布的EXPLAIN SELECT絕對看起來違反直覺。

如果您的查詢包含在內WHERE s.id = ...，那麼您看到的查詢計劃可能更有意義，但我假設您沒有。

看起來優化器被以下事實分散了注意力表格的閱讀順序應與其選擇的順序相反。

這裡有兩種選擇。

– 使用該STRAIGHT_JOIN 指令堅持優化器僅按列出的順序處理表：

SELECT STRAIGHT_JOIN * FROM `po` 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

– 使用FORCE KEY 索引提示指示優化器優先選擇表的主鍵po：

SELECT * FROM `po` FORCE KEY (PRIMARY) 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

第一個選項可能是更好的選擇，因為FORCE KEY儘管有名稱，但它仍然只是優化器可以選擇忽略的“提示”，而STRAIGHT_JOIN確實確實強制優化器按照它們的順序加入表’重新上市。

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