主页 / dba / 问题 / 177622
Accepted
user8012596
Asked: 2017-06-30 09:55:05 +0800 CST

innodb 上大表的自动增量与复合主键

  • 772

我与 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;

一切看起来都不错，但最近我在谷歌上搜索了一些相关主题，发现一些讨论https://www.quora.com/Is-it-a-bad-idea-to-have-a-primary-key-on- 3-or-more-columns反对复合主键，并且更愿意使用自动增量主要用于插入目的。有人可以对此进行更多说明以维护主键的复合键或返回自动增量吗？

mysql index

2 个回答

  1. Best Answer

    复合键没有任何问题。但是，您必须考虑如何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 （而且我还没有找到一个实际案例，这还不够大）。

    这是否有意义，很大程度上取决于您的特定情况。

    • 8

  2. joanolo 有一些好的观点，有些观点我不同意......

    • 从 5.6.4 开始，DATETIME并且TIMESTAMP没有小数秒，每个占用 5 个字节。（所以有问题的 PK 总共是 9 个字节。）
    • 数据的碎片化并没有那么糟糕。而且，如果它允许对其他操作进行重大改进，那么它可能是值得的。（见下文。）BTree 天生就会稳定到大约 69% 满。（一个块分裂将一个 100% 的完整块变成两个 50% 的完整块，然后它们都逐渐重新填充。）
    • 在or键中使用DATETIME(or TIMESTAMP)是危险的——如果两个条目同时发生怎么办？（这个问题取决于应用程序；例如，测量卡车的位置不需要在一秒钟内读取两个 gps 读数。）PRIMARYUNIQUE
    • 关于 PKs 的链接谈论“胖”PKs。有问题的 PK 只有 9 个字节——并不是很胖。所以这个链接只是轻微相关。此外，仅当您至少有 2 个不包括胖列的二级索引时，胖度才适用。
    • 该表威胁要溢出 4-byte INT，下一个选择AUTO_INCREMENT是 8-byte BIGINT；与 9 个字节没有太大区别。
    • MEDIUMINT是 3 个字节 ( vehicleID)。
    • 我很确定InnoDB 结构中的字段没有“对齐”。InnoDB 的设计使得文件在所有硬件架构中都兼容。
    • MySQL要求PK 是唯一的。如果退出 alarmTypeID 会删除唯一性，请不要这样做！

    具体...

    ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`), -- 1+3+5 = 0 bytes
ADD KEY `gDateTime` (`gDateTime`),                       -- 5 + 1+3 = 9
ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`);   -- 2+3+5 + 1 = 11

    我说 PK 为 0 字节，因为它包含在其余列中。辅助键的数字是辅助键列的大小 + 额外的 PK 列。（当然，索​​引中有很大的开销，因此这些数字不能用于计算 BTree 的最终大小。您可能需要 3 倍的软糖因子。）

    一个SELECT与

    WHERE alarmTypeID = constant
  AND vehicleID = constant
  AND gDateTime ... (some range)

    alarmTypeID( , vehicleID, gDateTime) 比 ( gDateTime, alarmTypeID, )处理得更好vehicleID。如果这是一个常见的查询，我认为它胜过避免碎片化的愿望。

    PRIMARY KEY( alarmTypeID, vehicleID, gDateTime) 避免了辅助键和数据之间的反弹。

    PRIMARY KEY( gDateTime, alarmTypeID, vehicleID) 不能使用警报器或车辆，并且必须越过警报器和不感兴趣的车辆。或者使用辅助键，导致来回弹跳。无论哪种情况，都慢得多。（经验法则：当数据未缓存时，旋转磁盘的速度要慢 10 倍。）

    • 4

相关问题