為什麼 FORCING INDEX 使這個查詢在較小的數據集上慢得多？
我在 Ubuntu 伺服器上使用 MySQL 5.6。SQL 涉及 2 個表，
collection_notes並且notes. 是一個關係表，用於連接和collection_notes之間的多對多關係collections``notes結構是這樣的：
collection_notes --------- Field Type Null Key Default Extra id int(11) NO PRI NULL auto_increment collection_id int(11) YES MUL NULL note_id int(11) YES MUL NULL created_at datetime NO NULL updated_at datetime NO NULL user_id int(11) YES MUL NULL notes --------- Field Type Null Key id int(11) NO PRI created_at datetime NO updated_at datetime NO notebook_id int(11) YES MUL shared tinyint(1) NO MUL slug varchar(100) NO UNI shared_at datetime YES MUL seq_in_nb int(11) YES note_type varchar(255) YES locked tinyint(1) NO locked_at datetime YES likes_count int(11) YES image_file_name varchar(255) YES generating_image int(11) YES image_generated_at int(11) NO commentable tinyint(1) YES last_stopped_share_at datetime YES bookmarks_count int(11) YES last_compiled_at int(11) NO first_shared_at datetime YES MUL title_image varchar(190) YES deleted_at datetime YES MUL comment_updated_at int(11) YES MUL
notes有更多的列。索引來自
collection_notes：Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type collection_notes 0 PRIMARY 1 id A 3874847 NULL NULL BTREE collection_notes 0 index_collection_notes_on_collection_id_and_note_id 1 collection_id A 138387 NULL NULL YES BTREE collection_notes 0 index_collection_notes_on_collection_id_and_note_id 2 note_id A 3874847 NULL NULL YES BTREE collection_notes 1 index_collection_notes_on_collection_id 1 collection_id A 184516 NULL NULL YES BTREE collection_notes 1 index_collection_notes_on_note_id 1 note_id A 3874847 NULL NULL YES BTREE collection_notes 1 index_collection_notes_on_user_id 1 user_id A 82443 NULL NULL YES BTREE索引來自
notes：Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type notes 0 PRIMARY 1 id A 5434512 NULL NULL BTREE notes 0 index_notes_on_slug 1 slug A 5434512 NULL NULL BTREE notes 1 index_notes_on_notebook_id 1 notebook_id A 5434512 NULL NULL YES BTREE notes 1 index_notes_on_shared_at 1 shared_at A 5434512 NULL NULL YES BTREE notes 1 index_notes_on_shared_and_locked 1 shared A 2 NULL NULL BTREE notes 1 index_notes_on_shared_and_locked 2 locked A 6 NULL NULL BTREE notes 1 index_notes_on_first_shared_at 1 first_shared_at A 5434512 NULL NULL YES BTREE notes 1 index_notes_on_notebook_id_and_seq_in_nb 1 notebook_id A 5434512 NULL NULL YES BTREE notes 1 index_notes_on_notebook_id_and_seq_in_nb 2 seq_in_nb A 5434512 NULL NULL YES BTREE notes 1 index_notes_on_deleted_at 1 deleted_at A 452876 NULL NULL YES BTREE notes 1 index_notes_on_comment_updated_at 1 comment_updated_at A 1358628 NULL NULL YES BTREE
notes表有約 600 萬行，其中約 300 萬個匹配項deleted_at IS NULL and shared = 1 and locked = 0
collection_notes表有約 400 萬行。SQL是：
SELECT `notes`.* FROM `notes` FORCE INDEX(`index_notes_on_comment_updated_at`) INNER JOIN `collection_notes` ON `notes`.`id` = `collection_notes`.`note_id` WHERE `notes`.`deleted_at` IS NULL AND `collection_notes`.`collection_id` = ? AND `notes`.`shared` = 1 AND `notes`.`locked` = 0 ORDER BY `notes`.`comment_updated_at` DESC LIMIT 15 OFFSET 0當
collection_id = 47這是一個比較大的集合，有超過54000條筆記，只需要30ms，返回15行，第一頁。（不強制這個索引index_notes_on_comment_updated_at，大約需要1秒，這就是我試圖優化它的原因)當
collection_id = 270014這是一個很小的集合時，只有兩個音符，並且需要101 秒！僅返回 2 行，這是該集合僅有的兩個註釋。（不 FORCING 此索引index_notes_on_comment_updated_at，需要 30ms，非常快）為什麼它的行為如此不同，當數據很小時它甚至更慢？
以下是解釋：
沒有FORCE INDEX的解釋
collection_id = 47：id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE collection_notes ref index_collection_notes_on_collection_id_and_note_id,index_collection_notes_on_collection_id,index_collection_notes_on_note_id index_collection_notes_on_collection_id_and_note_id 5 const 256478 Using where; Using index; Using temporary; Using filesort 1 SIMPLE notes eq_ref PRIMARY,index_notes_on_shared_and_locked,index_notes_on_deleted_at PRIMARY 4 maleskine.collection_notes.note_id 1 Using where沒有FORCE INDEX的解釋
collection_id = 270014：id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE collection_notes ref index_collection_notes_on_collection_id_and_note_id,index_collection_notes_on_collection_id,index_collection_notes_on_note_id index_collection_notes_on_collection_id_and_note_id 5 const 8 Using where; Using index; Using temporary; Using filesort 1 SIMPLE notes eq_ref PRIMARY,index_notes_on_shared_and_locked,index_notes_on_deleted_at PRIMARY 4 maleskine.collection_notes.note_id 1 Using where用FORCE INDEX 解釋
collection_id = 270014：id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE notes index NULL index_notes_on_comment_updated_at 5 NULL 15 Using where 1 SIMPLE collection_notes ref index_collection_notes_on_collection_id_and_note_id,index_collection_notes_on_collection_id,index_collection_notes_on_note_id index_collection_notes_on_collection_id_and_note_id 10 const,maleskine.notes.id 1 Using index用FORCE INDEX 解釋
collection_id = 47：id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE notes index NULL index_notes_on_comment_updated_at 5 NULL 15 Using where 1 SIMPLE collection_notes ref index_collection_notes_on_collection_id_and_note_id,index_collection_notes_on_collection_id,index_collection_notes_on_note_id index_collection_notes_on_collection_id_and_note_id 10 const,maleskine.notes.id 1 Using index我的問題是：
- 為什麼這個
FORCE INDEX(index_notes_on_comment_updated_at 會)加速查詢collection_id = 47（有 50000+ 條筆記）這麼多（1s -> 30ms），同時，讓查詢collection_id = 270014（只有 2 條筆記）這麼慢（30ms -> 101s）- 如果沒有
FORCE INDEX(index_notes_on_comment_updated_at)，我怎樣才能使原始查詢更快地用於大集合（如 collection_id = 47），現在它需要 1 秒才能完成。原始 SQL：
SELECT `notes`.* FROM `notes` INNER JOIN `collection_notes` ON `notes`.`id` = `collection_notes`.`note_id` WHERE `notes`.`deleted_at` IS NULL AND `collection_notes`.`collection_id` = ? AND `notes`.`shared` = 1 AND `notes`.`locked` = 0 ORDER BY `notes`.`comment_updated_at` DESC LIMIT 15 OFFSET 0
在您的情況下，索引
index_notes_on_comment_updated_at，包括單列 - comment_updated_at
在 JOIN 和/或 WHERE 部分中的查詢中無處使用：
SELECT `notes`.* FROM `notes` FORCE INDEX(`index_notes_on_comment_updated_at`) INNER JOIN `collection_notes` ON `notes`.`id` = `collection_notes`.`note_id` WHERE `notes`.`deleted_at` IS NULL AND `collection_notes`.`collection_id` = ? AND `notes`.`shared` = 1 AND `notes`.`locked` = 0 ORDER BY `notes`.`comment_updated_at` DESC LIMIT 15 OFFSET 0因此，強制 MySQL 使用此索引 - 與 FORCE 它使用 FULL SCAN 相同
‘shared’ 和 ’locked’ - 具有非常低的基數，
所以在這種情況下不要減少行數最好的選擇讓mysql使用預設鍵進行JOIN，行過濾器將是
`collection_notes`.`collection_id` =包含在您的結構中的 2 個索引中
加上索引中的更改順序 - index_collection_notes_on_collection_id_and_note_id for
(note_id, collection_id) 與您在查詢中使用的相同
添加所有計劃後編輯
讓我們尋找collection_id = 47：
原始查詢：
從 collection_notes 中選擇 256478 條記錄，而不是使用 where 在 notes 中查找；使用索引；使用臨時的；使用文件排序 使用 where
強制查詢：
使用 FORCED INDEX 過濾 15 個第一條記錄，而不是使用 where 使用索引進行查找
對於第二種情況： 使用強制索引它選擇索引
index_notes_on_comment_updated_at
並查找 note_id
為您的問題添加範例- 為什麼使用類似的計劃 FORCED INDEX 查詢更適合更大的集合：
按日期排序的數據（黃色列）和掃描 mysql 必須從綠色列中獲取第一個 NNN 記錄
我不嘗試解釋 - 索引是如何工作的，但在我們的例子中 - mysql 在第一個相等的記錄之後停止工作， 並且第一個記錄的數量大大少於正確記錄的總數
這是一個簡單的視覺化範例，真實數據可能有不同的頻率，但邏輯將是相同的 - 對於大數據集和少量相等數據，查詢可以（或不能）比更小的數據更快地工作。
還有許多其他參數可能會產生影響，但這是索引錯誤時可能發生的情況之一。
