mysql - MyISAM vs InnoDB 用于快速插入和复合唯一键

Question

上下文:我正在创建一个将非常频繁地插入/更新行的多线程应用程序。

最初我有下表:

#TABLE 1
CREATE TABLE `example` (
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `state`))
ENGINE = MyISAM;

然而，在做了一些研究之后，我发现 MySQL 对 MyISAM 表使用表级锁定，一次只允许一个 session 更新这些表 (source)。不适合频繁更改表的多线程应用程序。

因此，有人建议我从复合主键切换到自动生成的主键，该主键具有 id/state 的唯一索引。这将允许快速插入，同时仍然强制执行 id/state 的唯一组合。

#TABLE 2
CREATE TABLE `example` (
  `key` BIGINT(20) NOT NULL,
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`key`),
  UNIQUE INDEX `ID_STATE` (`id` ASC, `state` ASC))
ENGINE = MyISAM;

然而，InnoDB 避免了表锁，而是使用行级锁定(source)，所以我考虑切换到以下内容:

#TABLE 3
CREATE TABLE `example` (
  `key` BIGINT(20) NOT NULL,
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`key`),
  UNIQUE INDEX `ID_STATE` (`id` ASC, `state` ASC))
ENGINE = InnoDB;

但是在阅读了 InnoDB 之后，我发现 InnoDB 使用聚集索引组织数据，而二级索引需要多次查找。一个用于二级索引，另一个用于主键 ( source )。因此，我正在考虑切换到以下内容:

#TABLE 4
CREATE TABLE `example` (
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `state`))
ENGINE = InnoDB;

我想知道我所有的假设是否正确:

1. MyISAM 表为 INSERTS、UPDATES 和 DELETES 锁定整个表，一次只允许一个 session 更新这些表
2. InnoDB 比 MyISAM 更快地处理带有复合主键的 INSERTS。这是因为 InnoDB 与 MyISAM 不同，不会锁定整个表来扫描和保留新的主键。
3. 在使用 InnoDB 时，我应该创建复合主键而不是复合唯一索引，因为二级索引需要多次查找。
4. 我应该使用表 4

Answer 1

1-是，2-是，3-是，4-是。

还有...

• 你真的需要BIGINT吗？ INT UNSIGNED 中的 40 亿个值是否足够？ (并节省一半的空间。)大概id是其他表的PK？如果是这样，那张 table 也需要更改。
• state 可以规范化吗？或者变成一个ENUM？再次节省空间。

第 3 项比提到的更糟糕，因为需要锁定两个唯一键。