Mysql

MySQL分區比較大的DB Table

  • February 19, 2021

我的表結構類似於以下 -

CREATE TABLE `ProductCatalog` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `SerialNumber` varchar(20) DEFAULT NULL,
 `BasePrice` decimal(10,2) DEFAULT NULL,
 `BatchCode` tinyint(3) unsigned DEFAULT NULL,
 `Type` varchar(5) DEFAULT NULL,
 `ItemCode` varchar(5) DEFAULT NULL,
 `ArrivalDate` datetime DEFAULT NULL,
 `InsertTimestamp` int(10) unsigned NOT NULL,
 `BrandID` tinyint(3) unsigned DEFAULT NULL,
 `Model` varchar(10) NOT NULL DEFAULT 'RX209',
 `Description` text,
 PRIMARY KEY (`id`),
 KEY 'idx_ic_sn_ad' (`ItemCode`, `SerialNumber`, `ArrivalDate`),
 KEY 'idx_sn_ad' (`SerialNumber`, `ArrivalDate`, `ItemCode`, `BasePrice`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

該表中大約有 6 億行，並且正在快速增長。每天大約有 50 萬條記錄被插入，有時更多，有時更少。所以在一天中的某個時間段（早上 6 點到晚上 8 點）會有大量的寫入活動

以下是我針對此表執行的查詢

SELECT * 
FROM ProductCatalog  
WHERE SerialNumber='1234567890' 
 AND ItemCode!="ABCD" 
ORDER BY id DESC LIMIT 1;
   
SELECT BasePrice 
FROM ProductCatalog 
WHERE SerialNumber='123456789' 
 AND ItemCode!="ABCD" 
 and ItemCode!="PQRS" 
 AND ItemCode!="MNOP" 
ORDER BY ID Desc LIMIT 1
   
SELECT * 
FROM ProductCatalog 
WHERE SerialNumber='123456789' AND ItemCode='ABCD'
 AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')  
ORDER BY ArrivalDate ASC

SELECT BatchCode
FROM ProductCatalog 
WHERE SerialNumber='123456789' 
 AND ItemCode!="ABCD" 
 and ItemCode!="PQRS" 
 AND ItemCode!="MNOP" 
ORDER BY ID Desc LIMIT 1

上面的查詢和表索引已按照我之前的問題中的建議進行了優化，這裡 MySQL 表正確索引以進行性能優化

問題

由於ProductCatalog表越來越大，而且上面提到的第三個查詢經常使用，我正在考慮對這個表進行分區。為此，我正在考慮使用ArrivalDate列進行範圍分區。以下是我提出的查詢。

ALTER TABLE ProductCatalog PARTITION BY RANGE (TO_DAYS(ArrivalDate)) (
PARTITION p11 VALUES LESS THAN (TO_DAYS('2011-01-01')),
PARTITION p12 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p13 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p14 VALUES LESS THAN (TO_DAYS('2014-01-01')),
PARTITION p15 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION p16 VALUES LESS THAN (TO_DAYS('2016-01-01')),
PARTITION p17 VALUES LESS THAN (TO_DAYS('2017-01-01')),
PARTITION p18 VALUES LESS THAN (TO_DAYS('2018-01-01')),
PARTITION p19 VALUES LESS THAN (TO_DAYS('2019-01-01')),
PARTITION p20 VALUES LESS THAN (TO_DAYS('2020-01-01')),
PARTITION p21 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p22 VALUES LESS THAN MAXVALUE);

每年晚些時候我都會像這樣重新組織分區

ALTER TABLE ProductCatalog 
REORGANIZE PARTITION p22 INTO (
   PARTITION p22 VALUES LESS THAN (TO_DAYS('2022-01-01')),
   PARTITION p23 VALUES LESS THAN (TO_DAYS('2023-01-01')),
   PARTITION p24 VALUES LESS THAN MAXVALUE
);

問題

每年平均插入大約 2 億行。所以考慮到這一點 -

  1. 這種分區對我現有的表結構和索引的性能有什麼好處。
  2. 這種分區對我上面指定的選擇語句有什麼好處嗎？
  3. 這種分區對每時每刻都進行的大量插入有什麼好處嗎？
  4. 我知道我們可以刪除特定分區，但是在刪除之前是否可以輕鬆地將特定分區存檔到另一個存檔數據庫？
  5. 還有其他更好的主意，以在數據庫中獲得最大可能的讀寫性能嗎？
  6. id int(10)當數據快速增加時，我是否需要擔心列？
  7. 此分區將對ArrivalDate在條件中使用no 的其他三個查詢產生什麼影響WHERE？

因此，請記下您的問題清單：

這種分區對我現有的表結構和索引的性能有什麼好處。

不！ 如果你總是查詢日期，也許。

這種分區對我上面指定的選擇語句有什麼好處嗎？

只有你的第三個查詢，那是可能的。

此分區將對 WHERE 條件中未使用 ArrivalDate 的其他三個查詢產生什麼影響？

這取決於二級索引是否也被分區。但通常可能會有更多成本，因為您必須查詢可能包含行的每個分區。分區也破壞了 MySQL 中的許多特性，因此不應該輕率地進行。

這種分區對每時每刻都進行的大量插入有什麼好處嗎？

不，您仍然根據無意義的行標識符進行附加。一切都將停留在最後一頁。

我知道我們可以刪除特定分區，但是在刪除之前是否可以輕鬆地將特定分區存檔到另一個存檔數據庫？

我不確定 MySQL - 在其他數據庫中移動/刪除分區相對簡單。

當數據快速增加時，我需要擔心列 id int(10) 嗎？

是的！您要麼現在解決問題，要麼必須使用更大、同樣無意義的行標識符重建表，從而進一步解決您的問題。

還有其他更好的主意，以在數據庫中獲得最大可能的讀寫性能嗎？

是的。它被稱為定義主鍵，並在其上進行集群。

現在你有一大堆雜亂無章的行。我將其稱為堆，但人們將其保留為某種類型的雜亂無章的行，這些行向使用者隱藏行指針，而不是將其聲明為主鍵。

您的行根據它們的大致插入順序位於表中。為了更有效地定位這些行，您可以在頂部添加一個索引，以便找到它們隱藏的位置，但即使使用索引，您也可能每行讀取一頁或整個表，具體取決於數據的分佈方式。請在此處查看我的答案以獲得非常基本的說明：使用包含列的索引進行大量更新來節省性能

因此，如果(SerialNumber, ArrivalDate, ItemCode)是唯一的，從您的問題中並不完全明顯，但讓我們假設它是（如果需要額外的列來定義唯一性，我們可以稍後進行調整）。如果我們這樣定義您的表：

CREATE TABLE `ProductCatalog` (
 `SerialNumber`    varchar(20)  DEFAULT NULL,
 `ItemCode`        varchar(5) NOT NULL,
 `ArrivalDate`     datetime NOT NULL,
 `BasePrice`       decimal(10,2) DEFAULT NULL,
 `BatchCode`       tinyint(3) unsigned DEFAULT NULL,
 `Type`            varchar(5) DEFAULT NULL,
 `InsertTimestamp` int(10) unsigned NOT NULL,
 `BrandID`         tinyint(3) unsigned DEFAULT NULL,
 `Model`           varchar(10) NOT NULL DEFAULT 'RX209',
 `Description`     text,
 CONSTRAINT PK_ProductCatalog PRIMARY KEY (SerialNumber, ItemCode, ArrivalDate)
)

發生三件事：

  1. 我們節省了空間，因為我們沒有在每一行上都添加不必要的行指針
  2. 我們保證唯一性
  3. 現在（大部分）根據 對行進行分組(SerialNumber, ItemCode, ArrivalDate)。

#3 的後果是您送出的所有查詢都將遍歷一棵 b-tree，並且通常不超過 1-2 頁來滿足查詢。

是的，這可能會導致頁面拆分（碎片化），但頁面拆分的影響通常小於額外索引（也是碎片化）和更多讀取 I/O 的成本。如果它變得非常糟糕（您將像其他任何事情一樣監控它），您可以重建表（聚集索引）。

其他一般性意見：

  1. 不要將時間戳儲存為DATETIME/以外的任何內容TIMESTAMP。數據類型的存在是有原因的，如果您必須在插入時進行轉換，那比在讀取時進行轉換要好。
  2. 如果 、 或 中的一個或多個Brand依賴Model於Description，ItemCode則該數據應儲存在其自己的表中。使您的主桌更加緊湊。
  3. 如果SerialNumber和ItemCode是固定長度，你可能最好使用CHAR(<the max length>)vs VARCHAR。

您說沒有列組合是“唯一的”？

bbaird 正確地建議PRIMARY KEY (SerialNumber, ItemCode, ArrivalDate)幫助提高性能，但由於缺乏獨特性而不起作用？

在這種情況下，請執行以下操作：

PRIMARY KEY (SerialNumber, ItemCode, ArrivalDate, ID)
INDEX(ID)

這給你

  • 分群有益於您的所有查詢，因為您總是在過濾SerialNumber
  • PKID中的任何位置都可以確保它是獨一無二的。
  • 保持 AUTO_INCREMENT 快樂，因為ID它位於某個索引的開頭。
  • 佔用幾乎完全相同的磁碟空間
 PRIMARY KEY(ID),
 INDEX(SerialNumber, ItemCode, ArrivalDate)
  • 一個輕微的缺點是任何進一步的輔助鍵都將在其中包含 PK 的副本，因此當 PK 更大時更大。
  • PK(ID) 按時間順序對數據進行排序，這對某些查詢有一些好處。所有新行都位於數據的“末尾”。PK（其他東西）以不同的方式排列數據，從而導致插入在多個位置進入。然而，SELECTs 的好處可能應該大於壞處。

PARTITIONing未針對這些查詢指示。如果您將基於 清除“舊”數據ArrivalDate，那麼您的分區（和 bbaird 的評論）確實適用。更多：http: //mysql.rjweb.org/doc.php/partitionmaint

SUBPARTITIONing沒用（根據我的經驗）。

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

相關問答

Mysql

是否可以創建一個 MySQL 分區，然後通過另一列創建一個子分區？

  • July 26, 2022
檢視問答
Mysql

表分區有什麼幫助？

  • April 27, 2021
檢視問答
Mysql

加快大型數據庫查詢

  • November 4, 2020
檢視問答
Mysql

我應該在多租戶系統中按租戶 ID 通常對錶進行分區嗎？

  • September 11, 2020
檢視問答
Mysql

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

  • December 7, 2018
檢視問答
Mysql

在分區表中搜尋很慢

  • October 27, 2016
檢視問答