索引是否必須覆蓋所有選定的列才能用於 ORDER BY？
在 SO，最近有人問為什麼 ORDER BY 不使用索引？
這種情況涉及 MySQL 中的一個簡單 InnoDB 表，該表包含三列和 10k 行。其中一列，一個整數，被索引了——並且 OP 試圖檢索他在該列上排序的整個表：
SELECT * FROM person ORDER BY age他附加
EXPLAIN的輸出顯示這個查詢是用 afilesort（而不是索引）解決的，並詢問為什麼會這樣。儘管提示
FORCE INDEX FOR ORDER BY (age)導致使用索引，但有人回答（帶有其他人的支持評論/贊成票）索引僅用於從索引中讀取所有選定列時的排序（即通常Using index在Extra列中表示EXPLAIN輸出）。後來給出的解釋是遍歷索引然後從表中獲取列會導致隨機 I/O，MySQL 認為這比filesort.這似乎與
ORDER BY優化的手冊章節背道而馳，它不僅傳達了一種強烈的印象，即ORDER BY從索引中滿足比執行額外的排序更可取（實際上，filesort它是快速排序和合併排序的組合，因此 必須有一個下限; 雖然按順序遍歷索引並尋找表格應該是 - 所以這很有意義），但它也忽略了提到這個所謂的“優化”，同時還說明：Ω(*n*log *n*)``O(*n*)以下查詢使用索引來解析
ORDER BY元件：SELECT * FROM t1 ORDER BY ***key_part1***,***key_part2***,... ;根據我的閱讀，在這種情況下正是這種情況（但在沒有明確提示的情況下沒有使用索引）。
我的問題是：
為了讓 MySQL 選擇使用索引，是否確實需要對所有選定的列進行索引？
- 如果是這樣，這在哪裡記錄（如果有的話）？
- 如果不是，這裡發生了什麼？
改編自Denis對 SO 上另一個問題的回答（經許可） ：
由於查詢將獲取所有記錄（或幾乎所有記錄），因此您通常最好完全不使用索引。這樣做的原因是，讀取索引實際上要花一些錢。
當您要查找整個表格時，按順序讀取表格並在記憶體中對其行進行排序可能是您最便宜的計劃。如果您只需要幾行並且大多數都將匹配 where 子句，那麼選擇最小的索引就可以了。
要了解原因，請想像所涉及的磁碟 I/O。
假設您想要整個表沒有索引。為此，您讀取 data_page1、data_page2、data_page3 等，依次訪問涉及的各個磁碟頁，直到到達表的末尾。然後，您排序並返回。
如果您想要前 5 行沒有索引，您可以像以前一樣順序讀取整個表，同時對前 5 行進行堆排序。誠然，對於少數幾行，這需要大量的閱讀和排序。
現在假設您希望整個表都有一個索引。為此，您依次讀取 index_page1、index_page2 等。然後，這會導致您以完全隨機的順序（排序的行出現在數據中的順序）訪問，比如說，data_page3，然後是 data_page1，然後是 data_page3，然後是 data_page2，等等。所涉及的 IO 使得按順序讀取整個混亂並在記憶體中對抓包進行分類變得更便宜。
相反，如果您只想要索引表的前 5 行，則使用索引成為正確的策略。在最壞的情況下，您在記憶體中載入 5 個數據頁並繼續前進。
順便說一句，一個好的 SQL 查詢計劃器將根據數據的碎片程度來決定是否使用索引。如果按順序獲取行意味著在表格中來回縮放，那麼優秀的計劃者可能會認為不值得使用索引。相反，如果使用相同的索引對錶進行集群，則可以保證行是有序的，從而增加了它被使用的可能性。
但是，如果你將同一個查詢與另一個表連接起來，並且另一個表有一個非常有選擇性的 where 子句可以使用一個小索引，那麼規劃器可能會決定實際上更好，例如獲取標記為的行的所有 ID
foo，散列加入表，並在記憶體中對它們進行堆排序。
為了讓 MySQL 選擇使用索引，是否確實需要對所有選定的列進行索引？
這是一個載入的問題，因為有一些因素決定了索引是否值得使用。
因素 #1
對於任何給定的索引，關鍵人群是什麼？換句話說，索引中記錄的所有元組的基數（不同計數）是多少？
因素 #2
你用的是什麼儲存引擎？是否可以從索引訪問所有需要的列？
下一步是什麼 ？？？
讓我們舉一個簡單的例子：一個包含兩個值（男性和女性）的表
讓我們創建這樣一個表並測試索引使用情況
USE test DROP TABLE IF EXISTS mf; CREATE TABLE mf ( id int not null auto_increment, gender char(1), primary key (id), key (gender) ) ENGINE=InnODB; INSERT INTO mf (gender) VALUES ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'), ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M'); ANALYZE TABLE mf; EXPLAIN SELECT gender FROM mf WHERE gender='F'; EXPLAIN SELECT gender FROM mf WHERE gender='M'; EXPLAIN SELECT id FROM mf WHERE gender='F'; EXPLAIN SELECT id FROM mf WHERE gender='M';測試 InnoDB
mysql> USE test Database changed mysql> DROP TABLE IF EXISTS mf; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE mf -> ( -> id int not null auto_increment, -> gender char(1), -> primary key (id), -> key (gender) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO mf (gender) VALUES -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), -> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'), -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), -> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M'); Query OK, 40 rows affected (0.06 sec) Records: 40 Duplicates: 0 Warnings: 0 mysql> ANALYZE TABLE mf; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.mf | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT id FROM mf WHERE gender='F'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT id FROM mf WHERE gender='M'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql>測試 MyISAM
mysql> USE test Database changed mysql> DROP TABLE IF EXISTS mf; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE mf -> ( -> id int not null auto_increment, -> gender char(1), -> primary key (id), -> key (gender) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO mf (gender) VALUES -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), -> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'), -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'), -> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M'); Query OK, 40 rows affected (0.00 sec) Records: 40 Duplicates: 0 Warnings: 0 mysql> ANALYZE TABLE mf; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.mf | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 36 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT id FROM mf WHERE gender='F'; +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT id FROM mf WHERE gender='M'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | mf | ALL | gender | NULL | NULL | NULL | 40 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql>InnoDB 分析
當數據作為 InnoDB 載入時，請注意所有四個
EXPLAIN計劃都使用了gender索引。第三個和第四個EXPLAIN計劃使用gender索引，即使請求的數據是id. 為什麼？因為id在PRIMARY KEY並且所有二級索引都有引用指針返回PRIMARY KEY（通過gen_clust_index）。MyISAM 分析
當數據作為 MyISAM 載入時，請注意前三個
EXPLAIN計劃使用gender索引。在第四個EXPLAIN計劃中，查詢優化器決定根本不使用索引。它選擇了全表掃描。為什麼？不管 DBMS 是什麼，查詢優化器都按照一個非常簡單的經驗法則進行操作：如果一個索引被篩選為用於執行查找的候選者，並且查詢優化器計算出它必須查找超過總數量的 5%表中的行：
- 如果所有需要檢索的列都在選定的索引中，則完成全索引掃描
- 否則進行全表掃描
結論
如果您沒有適當的覆蓋索引，或者任何給定元組的鍵數量超過表的 5%，則必鬚髮生六件事：
- 意識到您必須分析查詢
- 從這些查詢中查找所有
WHERE、GROUP BY和 ORDER BY` 子句- 按此順序制定索引
WHERE具有靜態值的子句列GROUP BY列ORDER BY列我過去曾寫過這 5% 的經驗法則：
May 07, 2012：MySQL EXPLAIN 不顯示 FULLTEXT 的“使用索引”Mar 22, 2012: MySQL 為什麼選擇這個執行計劃？Mar 09, 2012:未使用索引Jan 18, 2012: MySQL 狀態變數 Handler_read_rnd_next 增長了很多Dec 27, 2011: MySQL - ALTER TABLE for InnoDB 的最快方法Jul 29, 2011: MySQL 查詢優化 : 索引和分頁Jul 12, 2011：儘管沒有索引/鍵，但在更改一個 WHERE 欄位時，MySQL 查詢非常慢更新 2012-11-14 13:05 EDT
我回顧了你的問題和原來的 SO 文章。然後，我想到了我
Analysis for InnoDB之前提到的我。與person表相吻合。為什麼？對於兩個表
mf和person
- 儲存引擎是 InnoDB
- 主鍵是
id- 表訪問是通過二級索引
- 如果 table 是 MyISAM，我們會看到一個完全不同的
EXPLAIN計劃現在，查看來自 SO question 的查詢：
select * from person order by age\G。由於沒有WHERE子句，您明確要求進行全表掃描。表的預設排序順序是id(PRIMARY KEY) 因為它的 auto_increment 和gen_clust_index （又名聚集索引）是按內部 rowid 排序的。當您按索引排序時，請記住 InnoDB 二級索引將 rowid 附加到每個索引條目。這產生了每次對全行訪問的內部需求。
ORDER BY如果您忽略有關如何組織 InnoDB 索引的這些事實，則在 InnoDB 表上設置可能是一項相當艱鉅的任務。回到那個 SO 查詢，因為您明確要求進行全表掃描，恕我直言，MySQL 查詢優化器做了正確的事情（或者至少，選擇了阻力最小的路徑）。當涉及到 InnoDB 和 SO 查詢時，執行全表掃描然後進行一些
filesort操作要比通過 gen_clust_index 對每個二級索引條目執行全索引掃描和行查找要容易得多。我不提倡使用索引提示，因為它忽略了解釋計劃。儘管如此，如果你真的比 InnoDB 更了解你的數據，你將不得不求助於索引提示，尤其是對於沒有
WHERE子句的查詢。更新 2012-11-14 14:21 EDT
根據《Understanding MySQL Internals 》一書
第 202 頁第 7 段說：
數據儲存在稱為聚集索引的特殊結構中，它是一個 B 樹，主鍵作為鍵值，數據部分中的實際記錄（而不是指針）。因此，每個 InnoDB 表都必須有一個主鍵。如果未提供，則添加一個使用者通常不可見的特殊行 ID 列作為主鍵。輔助鍵將儲存標識記錄的主鍵的值。B-tree 程式碼可以在innobase/btr/btr0btr.c中找到。
這就是為什麼我前面說過：執行全表掃描然後進行一些文件排序，而不是通過 gen_clust_index 對每個二級索引條目執行全索引掃描和行查找要容易得多。InnoDB 每次都會進行雙索引查找。這聽起來有點殘酷，但這只是事實。再次，考慮到缺少
WHERE子句。這本身就是 MySQL 查詢優化器進行全表掃描的提示。
