Mysql

為什麼 FORCING INDEX 使這個查詢在較小的數據集上慢得多?

  • December 16, 2016

我在 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

我的問題是:

  1. 為什麼這個FORCE INDEX(index_notes_on_comment_updated_at 會)加速查詢collection_id = 47(有 50000+ 條筆記)這麼多(1s -> 30ms),同時,讓查詢collection_id = 270014(只有 2 條筆記)這麼慢(30ms -> 101s)
  2. 如果沒有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 在第一個相等的記錄之後停止工作, 並且第一個記錄的數量大大少於正確記錄的總數

這是一個簡單的視覺化範例,真實數據可能有不同的頻率,但邏輯將是相同的 - 對於大數據集和少量相等數據,查詢可以(或不能)比更小的數據更快地工作。

還有許多其他參數可能會產生影響,但這是索引錯誤時可能發生的情況之一。

引用自:https://dba.stackexchange.com/questions/158171