為什麼 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 在第一個相等的記錄之後停止工作, 並且第一個記錄的數量大大少於正確記錄的總數
這是一個簡單的視覺化範例,真實數據可能有不同的頻率,但邏輯將是相同的 - 對於大數據集和少量相等數據,查詢可以(或不能)比更小的數據更快地工作。
還有許多其他參數可能會產生影響,但這是索引錯誤時可能發生的情況之一。