mysql - 在一个巨大的表上添加一个有效的索引

Question

我有一个包含超过 3400 万行(并且还在增长)的 MySQL 数据库表。

CREATE TABLE `sensordata` (
  `userID` varchar(45) DEFAULT NULL,
  `instrumentID` varchar(10) DEFAULT NULL,
  `utcDateTime` datetime DEFAULT NULL,
  `dateTime` datetime DEFAULT NULL,
  `data` varchar(200) DEFAULT NULL,
  `dataState` varchar(45) NOT NULL DEFAULT 'Original',
  `gps` varchar(45) DEFAULT NULL,
  `location` varchar(45) DEFAULT NULL,
  `speed` varchar(20) NOT NULL DEFAULT '0',
  `unitID` varchar(5) NOT NULL DEFAULT '1',
  `parameterID` varchar(5) NOT NULL DEFAULT '1',
  `originalData` varchar(200) DEFAULT NULL,
  `comments` varchar(45) DEFAULT NULL,
  `channelHashcode` varchar(12) DEFAULT NULL,
  `settingHashcode` varchar(12) DEFAULT NULL,
  `status` varchar(7) DEFAULT 'Offline',
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=98772 DEFAULT CHARSET=utf8

我每分钟从多个线程(至少 400 个线程)访问这个表以将数据插入表中。随着表的增长，读取和写入数据的速度越来越慢。一个 SELECT 查询过去大约需要 25 秒，然后我添加了一个唯一索引

唯一索引 idx_userInsDate (userID,instrumentID,utcDateTime)

这将读取时间从 25 秒减少到几毫秒，但增加了插入时间，因为它必须为每条记录更新索引。此外，如果我同时从多个线程运行 SELECT 查询，则查询返回数据所需的时间太长。

这是一个示例查询

Select dateTime from sensordata WHERE userID = 'someUserID' AND instrumentID = 'someInstrumentID' AND dateTime between 'startDate' AND 'endDate' order by dateTime asc;

有人可以帮助我改进表架构或添加有效索引以提高性能吗？

提前致谢

Answer 1

PRIMARY KEY 是 UNIQUE 键。扔掉多余的 UNIQUE(id) !

id 是否被任何其他表引用？如果没有，那就一起摆脱它。取而代之的是

PRIMARY KEY ( userID, instrumentID, utcDateTime)

也就是说，如果该三元组保证是唯一的。您提到 DST - 使用数据类型 TIMESTAMP 而不是 DATETIME。这样做，您可以根据需要转换为 DATETIME，从而消除其中一列。

那个索引(PK)几乎不占用空间，因为它与 InnoDB 中的数据“聚集”在一起。

您的表因所有这些 VARCHARs 而变得非常臃肿。例如，status 可以缩减为 1 字节的 ENUM。其他的可以归一化。 speed 之类的东西可以是 4 字节的 FLOAT 或一些更小的 DECIMAL，具体取决于您需要的范围和精度。

对于 34M 宽的行，您最近可能已经超出了您拥有的 RAM 的缓存能力。通过使行变窄，您将推迟溢出。

为什么要攻击索引？在允许插入行之前检查每个 UNIQUE(包括 PRIMARY)索引。通过将其降低到 1 个索引，可以最大限度地降低那里的成本。 (InnoDB 确实需要一个 PRIMARY KEY。)

INT 是 4 个字节。你有十亿种乐器吗？也许instrumentID可以是SMALLINT UNSIGNED，2个字节，最大64K？考虑所有其他 ID。

您有 400 个INSERT/分钟，对吗？那还不错。如果你达到 400/秒，我们需要换个话题。

(“填充因子”在 MySQL 中不可调整，因为它没有太大区别。)

你有多少内存？ innodb_buffer_pool_size 的设置是什么？最佳值是 可用 RAM 的 70% 左右。

让我们看看您的主要查询；可能还有其他问题需要解决。