Mysql

MySQL 使用 WHERE BETWEEN 時,查詢命中所有分區

  • December 7, 2018

這可能只是我誤解了 MySQL 分區的工作原理,但我有一個表定義為:

 `ID` int(11) NOT NULL,
 `target_id` int(11) NOT NULL,
 `created_at` datetime NOT NULL,

 PRIMARY KEY (`ID`, `created_at`),
 KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
 KEY `index_on_created_at` (`created_at`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY HASH(MONTH(`created_at`))
PARTITIONS 12

如果我使用單個created_at時間戳查詢數據:

select * from my_table where `created_at`= '2018-12-00 05:00:00' AND target_id in (6,7,8);

然後解釋知道只命中 1 個分區。這是對的。


但是,如果我查詢一個範圍:

select * from my_table where `created_at` BETWEEN '2018-12-00 05:00:00' AND '2019-01-04 04:59:59' AND target_id in (6,7,8);

解釋現在觸及所有分區。這是一個已知的限制,還是我只是做錯了什麼?


邊注; 從其他“分區”問題來看,我希望有幾個“分區無助於正確索引”的響應……我只是為自己嘗試並收集一些指標,以便我們找到最適合我們需求的解決方案. (也對錶進行分區,即使它隨後檢查所有分區,與擁有 1 個具有相同索引的整體表相比,查詢時間仍然減少了一半;2.3 秒對 0.8 秒)


編輯1:

我確實確保兩個查詢都命中index_on_created_at索引。唯一的區別是索引上的“非唯一鍵查找”與“索引範圍掃描”。

您剛剛找到了許多原因之一,這些原因**PARTITIONing大多是無用的。**

即使使用第一個 SELECT,也沒有比使用具有合適索引的非分區表所能獲得的性能優勢。j 特別是:

INDEX(target_id, created_at)  -- in this order

第二 SELECT展示瞭如何PARTITION BY HASH處理一個範圍——通過查看所有分區。它別無選擇,只能這樣做。是的,是的,有了更多的聰明才智,它可能會注意到該日期範圍應該只有兩個月。即便如此,使用合適索引的非分區會執行得更快、更簡單。

關於索引的其他評論:

KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
KEY `index_on_created_at` (`created_at`)

有兩個問題:

  • INDEX(a,b), INDEX(a)– 你也可以扔掉後者,因為前者可以達到它的目的。
  • 直到 MySQL 8.0,在定義DESC中被忽略。INDEX(由於您沒有ORDER BY依賴於它的 an,因此不會造成任何傷害。)

如果您的目標是了解分區可以/不能做什麼,我推薦我的部落格

即使它隨後檢查所有分區,與擁有 1 個具有相同索引的整體表相比,查詢時間仍然減少了一半

我對此表示異議。您是否在關閉查詢記憶體的情況下執行?您是否將每個測試執行了兩次——以避免 buffer_pool_ 記憶體差異?實際上,關鍵可能是“具有相同的索引”。 幾乎總是,在分區和非分區之間切換時,需要修改索引。

  • 分區時,稍後將分區鍵放在需要它的每個索引中。或者,如果分區修剪足夠,則將其排除在外。
  • 未分區時,非範圍列應位於索引的早期。
  • 未分區時,範圍列(例如日期範圍)應在索引中延遲。

為什麼即使是點查詢也不能通過分區加快速度。請注意,這兩種方法的工作量似乎都差不多:

  • 非分區:使用的 BTree 索引是 3 級深。Hop-skip-jump,那裡有你想要的那一排。
  • 分區:首先修剪到所需的分區(“躍點”)。比如說,BTree 的深度只有 2 級(“跳跳”)。

啊。也許您可以發現分區的第 5 個案例。

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