MySQL為什麼選擇這個執行計劃？

我有兩個疑問，

select some_other_column 
from `table` 
order by primary_index_column asc 
limit 4000000, 10;

和

select some_other_column 
from `table` 
order by secondary_index_column asc 
limit 4000000, 10;

兩者都返回 10 行；第一個需要 2.74 秒，第二個需要 7.07 秒。 some_other_column不是任何索引的一部分。 primary_index_column是主鍵列；secondary_index_column具有 b 樹索引和 200 的基數（根據 MySQL）。

結果如下explain：

mysql> explain select some_other_column from `table` order by primary_index_column limit 4000000, 10;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | table   | index | NULL          | PRIMARY | 4       | NULL | 4000010 |       |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+

mysql> explain select some_other_column from `table` order by secondary_index_column limit 4000000, 10;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | table   | ALL  | NULL          | NULL | NULL    | NULL | 4642945 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

為什麼 MySQL 為第二個查詢選擇特定的執行計劃？ 我不明白為什麼它可以將索引用於第一個查詢，但不能用於第二個查詢。

InnoDB 中的索引列始終具有附加到它的gen_clust_index（又名聚集索引）的附加鍵。這將被第一個查詢遍歷以按索引的順序到達第 4000000 行。由於它是唯一被請求的列，因此無需訪問該表。

第二個查詢必須將表中的非索引列與索引列一起收集到臨時表中。然後在將非索引列顯示為 SELECT 輸出之前對臨時表進行排序。

注意另一個對比

• 表數為 4636881
• 第一個查詢的 EXPLAIN 計劃遍歷了 4000010 個 indexed_column 鍵。無需讀取最後的 636871 個鍵。
• 第二個查詢的 EXPLAIN 計劃遍歷了按 indexed_column 排序的 4636881 行。對於從表中提取非索引列的每一行，都會查找索引列（已按索引排序）並隨之而來。tmp 表按索引列排序，然後 mysqld 忽略前 4000000 行，留下 10 行。僅針對 10 行的表和索引之間的所有互動都是瓶頸。

常見的事情

在這兩種情況下，查詢都會指定要遍歷的行數。由於表中的行數為 4636881，因此我們應該很容易期待完整掃描。當 MySQL 查詢優化器決定在哪裡執行完整掃描時，對比變得明顯。

• 第一個查詢僅在 SELECT 列表和 WHERE 子句中引用索引列。MySQL 查詢優化器選擇執行全索引掃描，而無需聯繫表，因為所需的一切都在索引中。
• 第二個查詢引用 WHERE 子句中的索引列。但是，它必須伸出表來檢索相應的非索引列。MySQL Query Optmizer 被查詢告知它不能使用索引，因為它預期讀取的行數。作為任何 RDBMS 的經驗法則，如果必須讀取超過 5% 的表來完成查詢，MySQL 查詢優化器只會將索引“置於匯流排之下”並進行全表掃描。

算一下，這是 MySQL 查詢優化器計算的結果：

• 4636881 的 5% 是 231844
• 第二個查詢被命令讀取 4000000 行，遠高於 231844
• MySQL 查詢優化器意識到表（因為非索引列）和索引（因為索引列）之間會有太多的互動來獲取所需的數據。它決定只讀取表（因為索引列和非索引列都位於表中）而不是在它們之間來回反彈。

老實說，根據表的行數、表的目前索引以及查詢規定的行數，MySQL 查詢優化器做出了正確的決定。

推薦

創建此索引

ALTER TABLE `table` ADD INDEX mynewndx (indexed_column,some_other_column);

並且您的第二個查詢將永遠不會再觸及表格。當 MySQL 查詢優化器看到這個新索引時，它的行為會完全不同。

根據關於優化查詢的MySQL 文件order by，

在某些情況下，MySQL 無法使用索引來解析 ORDER BY

$$ … $$這些案例包括：

• $$ … $$
• 使用的表索引類型不按順序儲存行。例如，這適用於 MEMORY 表中的 HASH 索引。

我對 InnoDB 的理解是，行是按照主鍵順序儲存的。因此，它們對於任何二級索引都是無序的。

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