MySQL 使用 WHERE BETWEEN 時,查詢命中所有分區
這可能只是我誤解了 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 個案例。