MySql - 大型表 IP 配对的更好架构？

Question

我正在尝试管理一些互联网日志。我本质上是捕获哪些 IP 正在与其他 IP 进行联系并对此进行报告。

问题是有大量的闲聊，我不确定是否可以使我的架构变得更好。

我的表架构:

CREATE TABLE `IpChatter` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `SourceIp` bigint(20) NULL,
  `DestinationIp` bigint(20) NULL,
  `SourcePort` int(11) NULL,
  `DestinationPort` int(11) NULL,
  `FKToSomeTableWithExtraMetaDataId` bigint(20) NOT NULL,
  CONSTRAINT `PK_IpChatter` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;


CREATE INDEX `IX_IpChatter_FKToSomeTableWithExtraMetaDataId` ON `IpChatter`  (`FKToSomeTableWithExtraMetaDataId`) using HASH;
CREATE INDEX `IX_IpChatter_Main_Query_SourceIp` ON `IpChatter`        (`SourceIp`);
CREATE INDEX `IX_IpChatter_Main_Query_DestinationIp` ON `IpChatter`   (`DestinationIp`);
CREATE INDEX `IX_IpChatter_Main_Query_SourcePort` ON `IpChatter`      (`SourcePort`);
CREATE INDEX `IX_IpChatter_Main_Query_DestinationPort` ON `IpChatter` (`DestinationPort`);


ALTER TABLE `IpChatter` ADD CONSTRAINT `FK_IpChatter_FKToSomeTableWithExtraMetaData` 
FOREIGN KEY (`FKToSomeTableWithExtraMetaDataId`) REFERENCES `FKToSomeTableWithExtraMetaData` (`Id`)
ON DELETE CASCADE;

现在我有 2mill 行数据，并在大约 4 秒内提取我需要的数据。然而，这是使用相对较少的测试数据得出的。我想象最终产品中的数据大小会增加 30 倍。因此，在最终产品中，4 秒肯定意味着 2 分钟。有没有更好的方法可以标准化这些数据，或者我遇到了瓶颈而无能为力？另外，我选择的索引没问题吗？

Answer 1

没关系，我已经明白了。我想我只需要输入问题来帮助我想出解决方案。

因此，在查看我的数据后，我注意到很多配对都是重复的，但在不同的 FKToSomeTableWithExtraMetaDataId 值下。

So 告诉我，我可以通过创建一个具有不同配对的 SourceIp,DestinationIp,SourcePort,DestinationPort` 来标准化数据。然后创建一个查找表以将该表与 ToSomeTableWithExtraMetaData 表连接起来。

这将我的原始 IP 数据减少了 1700%!这将在搜索一系列 IP 时显着提高性能，而且现在需要遍历的行数要少得多。另外，借助查找表，我可以更加灵活地查询。

CREATE TABLE `IpChatter` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `SourceIp` bigint(20) NULL,
  `DestinationIp` bigint(20) NULL,
  `SourcePort` int(11) NULL,
  `DestinationPort` int(11) NULL,
  `FKToSomeLookupTableId` bigint(20) NOT NULL,
  CONSTRAINT `PK_IpChatter` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;


CREATE INDEX `IX_IpChatter_FKToSomeLookupTableId` ON `IpChatter`  (`FKToSomeLookupTableId`) using HASH;
CREATE INDEX `IX_IpChatter_Main_Query_SourceIp` ON `IpChatter`        (`SourceIp`);
CREATE INDEX `IX_IpChatter_Main_Query_DestinationIp` ON `IpChatter`   (`DestinationIp`);
CREATE INDEX `IX_IpChatter_Main_Query_SourcePort` ON `IpChatter`      (`SourcePort`);
CREATE INDEX `IX_IpChatter_Main_Query_DestinationPort` ON `IpChatter` (`DestinationPort`);


ALTER TABLE `IpChatter` ADD CONSTRAINT `FK_IpChatter_FKToSomeLookupTable` 
FOREIGN KEY (`FKToSomeLookupTableId`) REFERENCES `FKToSomeLookupTable` (`Id`)
ON DELETE CASCADE;


CREATE TABLE `FKToSomeLookupTable` (
  `FKToSomeTableWithExtraMetaDataId` bigint(20) NOT NULL,
  `IpChatterId` bigint(20) NOT NULL,
  CONSTRAINT `PK_FKToSomeLookupTable` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;

CREATE INDEX `IX_IpChatter_FKToSomeTableWithExtraMetaDataId` ON `FKToSomeLookupTable`  (`FKToSomeTableWithExtraMetaDataId`) using HASH;
CREATE INDEX `IX_IpChatter_IpChatterId` ON `FKToSomeLookupTable`  (`IpChatterId`) using HASH;

ALTER TABLE `FKToSomeLookupTable` ADD CONSTRAINT `FK_FKToSomeLookupTable_FKToSomeTableWithExtraMetaData` 
FOREIGN KEY (`FKToSomeTableWithExtraMetaDataId`) REFERENCES `FKToSomeTableWithExtraMetaData` (`Id`)
ON DELETE CASCADE;

ALTER TABLE `FKToSomeLookupTable` ADD CONSTRAINT `FK_FKToSomeLookupTable_IpChatter` 
FOREIGN KEY (`IpChatterId`) REFERENCES `IpChatter` (`Id`)
ON DELETE CASCADE;