有索引的查詢比沒有索引的要慢
我使用的是 MySQL 5.6，我的儲存引擎是 InnoDB。
我有一個包含 100 萬行的表，其中包含以下列：
- ID（主鍵）
- 名
- 姓
- foreign_key_id（外鍵，NOT NULL）
- foreign_key_id2（另一個外鍵，預設為 NULL）
這些行分隔在以下位置：
- 25%，foreign_key_id值為 1，foreign_key_id2為NULL
- 25%，foreign_key_id值為 1，foreign_key_id2 NOT NULL
- 25%，foreign_key_id值為 2，foreign_key_id2為NULL
- 25%，foreign_key_id值為 2，foreign_key_id2 NOT NULL
具有以下索引：
- 在foreign_key_id上索引foreign_key_idx
- 索引foreign_key_2_idx和foreign_key_id2
- 複合索引foreign_key_comp_idx on (foreign_key_idx, foreign_key_2_idx)
我執行以下查詢：
查詢 1 - 沒有索引：
SELECT * FROM table tbl IGNORE INDEX(foreign_key_idx, foreign_key_2_idx, foreign_key_comp_idx) WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL查詢 2 - 有索引（無復合索引）：
SELECT * FROM table tbl IGNORE INDEX(foreign_key_comp_idx) WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL查詢 3 - 使用複合索引（沒有其他索引）：
SELECT * FROM table tbl IGNORE INDEX(foreign_key_idx, foreign_key_2_idx) WHERE tbl.foreign_key_id = 1 AND tbl.foreign_key_id2 IS NOT NULL結果：
查詢 1（無索引）執行全表掃描並使用100 萬條記錄，總持續時間為0.37 秒。
查詢 2（索引，無復合索引）對foreign_key_idx 索引執行非唯一鍵查找，並使用 500K 記錄，總持續時間為0.6 秒。
查詢 3（僅限複合索引）對複合索引執行索引範圍掃描，並使用 480K 記錄，總持續時間為0.13 秒。
我真正不明白的是：為什麼查詢 2（有索引）總是比查詢 1（沒有索引）執行得慢？我真的很困，需要一些幫助……
我已經用不同數量的行測試了上面的查詢，比如 1k、10k、20k、50k、100k、200k、250k、500k、1M 等，總是具有相同的比率 (25%)，並且結果相同 (查詢 2 總是執行緩慢）
在此先感謝您，非常感謝您的任何意見！
編輯（2016 年 5 月 2 日）
顯示創建表命令：
CREATE TABLE `table` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `FirstName` varchar(255) NOT NULL, `LastName` varchar(255) NOT NULL, `foreign_key_id` int(11) NOT NULL, `foreign_key_id2` int(11) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `foreign_key_idx` (`foreign_key_id`), KEY `foreign_key_2_idx` (`foreign_key_id2`), KEY `foreign_key_comp_idx ` (`foreign_key_id`,`foreign_key_id2`), CONSTRAINT `foreign_key_idx` FOREIGN KEY (`foreign_key_id`) REFERENCES `table2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `foreign_key_2_idx` FOREIGN KEY (`foreign_key_id2`) REFERENCES `table3` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ) ENGINE=InnoDB AUTO_INCREMENT=1515998 DEFAULT CHARSET=latin1解釋計劃：
不確定是否重要，但 table2 有 20 條記錄，table3 也有 100 萬條記錄。
我的理解：該表包含 1M 行，其中 250k 由查詢返回。有 500k 行
foreign_key_id = 1和 500k 行af.foreign_key_id2 IS NOT NULL。使用全表掃描的查詢（實際上是對 InnoDB 中的 PRIMARY 鍵進行全索引掃描）將順序讀取所有 1M 行並檢查每一行的條件。
使用的查詢
foreign_key_idx（如果它使用’foreign_key_2_idx’應該是相同的）必須按“隨機”順序讀取500k行（假設行是隨機插入或分配ID）並檢查它們的其他條件。這意味著查詢通過它們的主鍵從表中讀取 500k 行——但這意味著它可能會訪問表中 100% 的數據頁。所以最後查詢讀取了一半的索引和所有的表 - 總共有更多的數據要經過，並且表是通過隨機搜尋訪問的，而不是順序訪問。這樣的查詢比全表掃描慢也就不足為奇了。通過的查詢將通過 ref 訪問方法
foreign_key_comp_idx找到索引的一部分，foreign_key_id = 1然後af.foreign_key_id2 IS NOT NULL通過範圍訪問獲取其滿足的部分 - 找到 250k 行，然後從表中讀取這些行。再次，它很有可能必須讀取超過 50% 的表頁，但它可能只是幸運的是它小於 100% 的數據分佈，而且它不必再次檢查條件因為他們得到了指數的保證。令我驚訝的是：通常的理解是，如果索引會導致獲取大約 20% 的表行，優化器甚至不會想要使用索引，而是更喜歡全表掃描，因為它通常更快。您的
IGNORE INDEX()提示不應該改變這一點，這就是FORCE INDEX()提示的用途（告訴優化器與建議的索引相比，表掃描的成本非常高）。但也許如果插入順序不是完全隨機的，統計數據和索引潛水會顯示（即使預期的行數沒有）。
我知道我在這裡聚會遲到了。
外鍵的基數是 2，這意味著您只有 2 個選項被索引 - 不是很有用。如果您對這些 FK 有 2000 種可能性，那麼索引將很有用。
每次插入，更新都需要更新索引，在這種情況下是沒有必要的——每個查詢都有一個額外的步驟——再次沒有必要。
將索引視為分組和排序，如果您願意的話，是一個指針..如果您只有兩個選項，您將被分組到大組中並且排序不多。
我不能說這就是它在 db 引擎中的工作方式，但這是我現在喜歡考慮索引的方式。
如果您有 2,000 個可能的 FK 值 - 這就是優勢在於可能的收縮（您正在查看的數據分段可能是 500 - 而不是 500,000）。
這是一個簡化的解釋 - 但您會注意到索引在插入時得到更新（它們在序列上工作得非常好，尤其是當您不在序列中間但在末尾插入時）。