Index

Mariadb 沒有使用正確的索引

  • July 6, 2020

小提琴:https ://www.db-fiddle.com/f/m4vsq4ERyBhiBNdZqALMVP/0

我有兩個索引,一個 forsome_other_id和一個 for created_at , some_other_id

選擇查詢使用生產伺服器上的第一個索引(~23M 行),而它使用我的開發機器上的第二個索引(1 行)。

伺服器說明:

*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: symbol_details
        type: ref
possible_keys: symbol_details_some_other_id_index,symbol_details_created_at_some_other_id_index
         key: symbol_details_some_other_id_index
     key_len: 8
         ref: const
        rows: 24152
       Extra: Using where

開發說明:

          id: 1
 select_type: SIMPLE
       table: symbol_details
        type: range
possible_keys: symbol_details_some_other_index,symbol_details_created_at_some_other_id_index
         key: symbol_details_created_at_some_other_id_index
     key_len: 5
         ref: NULL
        rows: 1
       Extra: Using where; Using index

同樣在小提琴中,它沒有使用第二個索引,而顯然對created_at.

是因為行數嗎?

我想在生產中使用這樣的查詢,但是這樣太慢了。像這樣的單個查詢會返回約 23M 行中的約 100 行,我認為第二個索引會涵蓋此查詢,但它似乎以某種方式沒有。

關於如何使用索引的任何想法,或為此查詢創建另一個索引,或修改查詢以使用索引。

最好的解決方案是不觸及查詢,因為它是由 ORM 生成的。在使用 ORM 時在查詢中使用提示並不完全乾淨,但如果它是唯一的選擇是可行的。

謝謝!

開發 Mariadb 版本 10.4.13 和生產版本是 10.4.12(在 docker btw 中執行)。

編輯:

第一個索引正在其他地方使用,它需要在那裡(索引 on some_other_id)。也許我可以將兩個索引合併到on中?IDK還沒有。

EDIT2 為什麼我選擇有兩個索引:

由於some_other_id的基數是 ~70k 並且created_at是 ~400k 我選擇有第二個索引首先過濾created_at。現在我在想我這樣想是否正確。

select  count(*)
   from  symbol_details
   where  tsetmc_id = 41974758296041288
     and  created_at >  '2020-06-20'
     and  created_at <  '2020-06-31';

最佳索引,按此順序

INDEX(tsetmc_id, created_at)

當您從範圍值開始時,它無法超過“id”。簡單的規則是:將=列放在索引定義的首位。更多:http: //mysql.rjweb.org/doc.php/index_cookbook_mysql

補充說明

  • 優化器可能會決定表掃描比使用INDEX. 這通常發生在需要觸摸超過大約 20% 的表格時。(使用索引意味著在索引的 BTree 和數據的 BTree 之間來回跳轉。)如果它避開索引,請不要擔心;這可能是一個明智的決定。
  • 當涉及“範圍”時,索引中列的順序至關重要。一旦超過“範圍”,優化器將停止考慮列。

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