在與 PARTITION BY RANGE + LIMIT 相同的列上使用 ORDER BY 進行高效分區修剪？

我已經在 MariaDB（10.4.5，目前為 RC）中使用 InnoDB 設置了一個表，使用按其值僅遞增的列進行分區，並且始終在末尾插入新數據。對於這種情況，分區對於加快某些查詢並在快速驅動器上保持新/活動分區和在慢速旋轉磁碟上保持舊/存檔分區是有意義的。對於插入加速，它工作得很好！（類似於 TimescaleDb 方法，但沒有時間也沒有 PostgreSQL。）

在同一列上按範圍選擇也可以正常工作；它只會開始讀取指定範圍的分區（索引）。到目前為止一切都很酷。

現在，我也有在該列上沒有子句的查詢，但按該列降序排列（即新數據優先），加上一個限制，通常只會命中一個或兩個最新分區（快速，記憶體指數）。但是，無論指定的順序是什麼，似乎 MySQL/MariaDB 都會從頭到尾打開分區。真的有那麼笨嗎？此外，我真的找不到其他人有這個問題，這讓我有點擔心。（有時這意味著我錯過了一些非常明顯的東西。）

為了在這裡更具體 - 為了測試，我有下表：

CREATE TABLE `mytable` (
 `user_id` bigint(20) unsigned NOT NULL,
 `my_id` bigint(20) unsigned NOT NULL,
 `data` varbinary(123) DEFAULT NULL,
 PRIMARY KEY (`user_id`,`my_id`),
 UNIQUE KEY `my_id_idx` (`my_id`)  -- I was hoping this one could help me
) ENGINE=InnoDB ROW_FORMAT=COMPACT
PARTITION BY RANGE (`my_id`)
(PARTITION `p0` VALUES LESS THAN (10000000) ENGINE = InnoDB,
PARTITION `p10M` VALUES LESS THAN (20000000) ENGINE = InnoDB,
PARTITION `p20M` VALUES LESS THAN (30000000) ENGINE = InnoDB,
PARTITION `p30M` VALUES LESS THAN (40000000) ENGINE = InnoDB,
[...]
) 

我執行如下查詢：

SELECT 
   user_id,
   my_id,
   LENGTH(data) AS data_len
FROM
   mytable
   -- tried to optimize with index hints:
   -- USE INDEX FOR ORDER BY (MY_ID_IDX)
   -- USE INDEX FOR ORDER BY (PRIMARY)
   -- USE INDEX FOR ORDER BY (MY_IDX, PRIMARY)
WHERE
   user_id = 1234567
ORDER BY my_id DESC
LIMIT 10;

我發現它首先開始尋找所有數據，user_id = 1234567首先顯示旋轉磁碟上的大量 I/O 負載，然後最終進入快速儲存以獲取完整集，然後切斷最後LIMIT 10一行……都在快速儲存，所以我們浪費了幾分鐘的時間！唔。

我的數據太大，我們無法將所有索引都放入記憶體 - 我們依賴磁碟上“足夠”的索引來記憶體在儲存層上。但是，即使所有索引都適合記憶體，數據也必須來自磁碟，並且一些使用者在這裡有大量數據（> 10M 行），這樣在記憶體中進行這種排序效率很低。 所以我希望找到一種方法讓 MariaDB 查找最後 LIMIT 行，然後停止閱讀。

作為一個人，你會首先開始查看最後一個分區，因為它ORDER BY my_id DESC和最新的分區包含它的最高值。但是，我如何告訴 MySQL/MariaDB 這樣做呢？

explain partitions結果（對於上面列出的所有 USE INDEX 變體都是相同的）：

 select_type: SIMPLE
       table: mytable
  partitions: p0M,p10M,p20M,p30M, ... (~ hundred here)
        type: ref
possible_keys: PRIMARY
         key: PRIMARY
     key_len: 8
         ref: const
        rows: 9999999 (worst-case)
       Extra: Using where

事實上，與我的預期相反，如果使用 first-to-new 分區*按升序進行查詢，它的性能甚至不會更好。*它仍然會請求所有分區的所有索引，然後發現它只需要一個…

我聽說過一些關於 MySQL 未來版本中分區的全域索引的資訊，但我懷疑它是否真的會在此處提供幫助，因為它的大小很大……而且它已經通過我的分區佈局得到了暗示案子。我發現的關於“分區修剪”的資訊似乎與讀取順序無關；僅關於查詢中的子句。

任何幫助表示讚賞。:-)

較新的分區將被動態創建，並且在特定分區上給出提示是不可行的。我的情況是“最新”分區很快，“舊”是“慢”，“最舊”是“超慢”——假設沒有記憶體在儲存層上，因為太多了。此外，我在一台單獨的機器上使用代理（SPIDER），它應該為客戶端提供一個單一的查詢介面，不需要知道後端的分區佈局，所以我更喜歡一種方法來實現它’自動的’。

恭喜。我認為您發現了一種情況，即分區不能與非分區一樣快。

WHERE user_id = 1234567
ORDER BY my_id DESC
LIMIT 10;

需要按此INDEX(user_id, my_id)順序進行，無需分區。因此，它將觸及 10 行並退出。

使用您擁有的分區，它必須檢查每個分區，收集在每個分區中找到的行，對它們進行排序，然後在第 10 個停止。

“分區不是性能靈丹妙藥”。

您還有其他可以從中PARTITION BY RANGE受益的疑問嗎？如果是這樣，您可能需要權衡取捨。即，一些查詢執行得更快，一些執行得更慢。

一般來說，如果“使用者”的數量相當有限，並且您不斷地為每個使用者插入新行，那麼每個使用者有一個“熱點”就可以了。

這導致

PRIMARY KEY(user_id, my_id)

以my_id某種方式獨一無二。它不必被聲明UNIQUE。如果是AUTO_INREMENT，那麼這工作正常：

my_id INT AUTO_INCREMENT,
PRIMARY KEY(user_id, my_id)  -- to cluster by user
INDEX(my_id)   -- to keep AUTO_INCREMENT happy

這樣，大多數這樣的查詢都非常有效：

WHERE user_id = 12345
 AND ((other stuff))

buffer_pool 中的記憶體比 SSD 與 HDD 更重要。並且觸摸的塊數對性能很重要。

每個INSERTs使用者需要一個塊。最終，會有一個區塊分裂。但隨後，它又回到了一個活動塊（“熱點”）。

SELECTs，即使所需的塊不在 buffer_pool 中，由於WHERE user_id=...導致所需的行位於非常少的塊中，它也往往是有效的。SELECT ... LIMIT 10對於您提到的情況尤其如此。

塊被記憶體。 全都 INDEXes沒有。有問題的查詢將只查看非分區佈局中的 1 個（可能是 2 個）塊。該指數的其餘部分將根據活動來來去去。

10M 行是“大”；10 億行是“巨大的”。MySQL 和 MariaDB 的全域索引可能需要幾年的時間；不要屏住呼吸。

的價值是innodb_buffer_pool_size多少？多少記憶體？

引用自：https://dba.stackexchange.com/questions/240768