innodb 上大表的自動增量與復合主鍵

我與 Rick James 就此進行了相當長時間的討論，我們提出了使用複合鍵來替換 int 限制接近 20 億的自動增量 pk 的想法。我的表將在幾個月內輕鬆達到這個限制，因為我們每月都會擷取接近幾億的數據。下面是我的桌子的樣子。關鍵表是gdata所以我使用 3 個欄位來合成主表PRIMARY KEY (alarmTypeID,vehicleID,gDateTime)。然後我有另一個表稱為警報表。兩者之間的聯繫是一對多的。這意味著其中的一個數據gdata可以有零個或多個alarms與之相關。它們之間的聯繫是vehicleID和gDateTime。

CREATE TABLE `gdata` ( 
   `alarmTypeID` tinyint(4) NOT NULL DEFAULT '0', 
   `fleetID` smallint(11) NOT NULL, 
   `fleetGroupID` smallint(11) DEFAULT NULL, 
   `fleetSubGroupID` smallint(11) DEFAULT NULL, 
   `deviceID` mediumint(11) NOT NULL, 
   `vehicleID` mediumint(11) NOT NULL, 
   `gDateTime` datetime NOT NULL, 
   `insertDateTime` datetime NOT NULL, 
   `latitude` float NOT NULL, 
   `longitude` float NOT NULL, 
   `speed` smallint(11) NOT NULL
    -- (see full text) 
) ;
ALTER TABLE `gdata` 
   ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`), 
   ADD KEY `gDateTime` (`gDateTime`), 
   ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`); 
COMMIT; 

這是報警表

CREATE TABLE `alarm` (
   `alarmTypeID` tinyint(4) NOT NULL, 
   `vehicleID` mediumint(9) NOT NULL, 
   `gDateTime` datetime NOT NULL, 
   `insertDateTime` datetime NOT NULL, 
   `alarmValue` varchar(5) NOT NULL, 
   `readWeb` enum('n','y') NOT NULL DEFAULT 'n', 
   `readWebDateTime` datetime NOT NULL, 
   `readMobile` enum('n','y') NOT NULL DEFAULT 'n', 
   `readMobileDateTim` datetime NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

ALTER TABLE `alarm` 
   ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`); 
COMMIT;

一切看起來都不錯，但最近我在Google上搜尋了一些相關主題，發現一些討論https://www.quora.com/Is-it-a-bad-idea-to-have-a-primary-key-on- 3-or-more-columns反對複合主鍵，並且更願意使用自動增量主要用於插入目的。有人可以對此進行更多說明以維護主鍵的複合鍵或返回自動增量嗎？

複合鍵沒有任何問題。但是，您必須考慮如何InnoDB儲存數據。

引用上面的連結文件：

每個 InnoDB 表中的數據被分成頁。構成每個表的頁面排列在稱為 B 樹索引的樹資料結構中。表數據和二級索引都使用這種類型的結構。代表整個表的 B 樹索引稱為聚集索引，它是根據主鍵列組織的。索引資料結構的節點包含該行中所有列的值（對於聚集索引）或索引列和主鍵列（對於二級索引）。

也就是說，InnoDB 會根據你的PRIMARY KEY. 如果您插入的數據的 PK 增加，則不會出現頁面碎片。總是會發生這種情況AUTO_INCREMENT。如果您按時間順序插入數據（即gDateTime始終單調遞增），請將構成 PK 的列的順序更改為：

PRIMARY KEY (`gDateTime`, `alarmTypeID`, `vehicleID`)

… 將具有相同的優勢，即不必“在其他行中間放置一個新行”（這意味著 B-tree 不會因每個插入而碎片化）。

但是：如果您從其他（相關）表中引用此表，則必須在引用表中始終儲存 PK ( gDateTime, alarmTypeID, vehicleID)。這意味著您每次節省 7 或 8 個字節的儲存空間。複合 PK 將使用 2 + 1 + 8 = 11 個字節的資訊（可能由於對齊而使用 12 個字節）；而INT UNSIGNED AUTO_INCREMENT, 您將在引用表中僅使用 4 個字節。您的 PK 限制為 2^32 個不同的值。如果您需要超過 2^32 的值，您將需要BIGINT AUTO_INCREMENT，這會給您 2^64 （而且我還沒有找到一個實際案例，這還不夠大）。

這是否有意義，很大程度上取決於您的特定情況。

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