MyISAM与InnoDB的快速插入和复合唯一键 [英] MyISAM vs InnoDB for quick inserts and a composite unique key

问题描述

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

Context: I'm creating a multi-threaded application that will be inserting/updating rows very frequently.

最初我有下表:

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

但是，在进行了一些研究之后，我发现MySQL对MyISAM表使用表级锁定，一次只允许一个会话更新这些表(

However after doing some research I found that MySQL uses table-level locking for MyISAM tables permitting only one session to update those tables at a time (source). Not good for a multi-threaded application making frequent changes to the table.

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

As such, it was suggested that I switch from a composite primary key to an auto-generated primary key with a unique index for id/state. This would allow for quick inserts while still enforcing the unique combination of the 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 )，所以我想切换到以下内容:

InnoDB however avoids table locks and instead uses row-level locking (source) so I thought of switching over to the following:

#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使用聚簇索引来组织数据，而二级索引则需要多次查找.一个用于辅助索引，另一个用于主键(源).因此，我想切换到以下内容:

But after reading up about InnoDB, I discovered InnoDB organizes data using a clustered index and secondary indexes require multiple look ups. One for the secondary index and another for the primary key (source). As such I'm debating switching to the following:

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

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

I'm wondering if all my assumptions are correct:

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

推荐答案

1是，2是，3是，4是.

1-yes, 2-yes, 3-yes, 4-yes.

还...

• 您真的需要BIGINT吗? INT UNSIGNED中的40亿个值不够吗? (并节省一半的空间.)大概id是其他表的PK吗?如果是这样，该表也需要更改.
• 可以将state标准化吗?还是变成了ENUM?再次节省空间.

• Do you really need BIGINT? Won't 4 billion values in INT UNSIGNED suffice? (And save half the space.) Presumably id is the PK of some other table? If so, that table would need changing, too.
• Can state be normalized? Or turned into an ENUM? Again saving space.

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

Item 3 is worse than mentioned because of the need to lock on two unique keys.

这篇关于MyISAM与InnoDB的快速插入和复合唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！