mysql - INSERT ... ON DUPLICATE UPDATE - 锁定等待超时

Question

我正在为大型 InnoDB 表上的文件INSERT .. ON DUPLICATE KEY UPDATE 苦苦挣扎。

我的 values 表保存属于客户的每个实体的详细信息。一个实体对于一个特定的键只能有一个值。因此，当发生变化时，我们正在更新相同的内容。该表如下所示:

CREATE TABLE `key_values` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL COMMENT 'customer/tenant id',
  `key_id` int(11) NOT NULL COMMENT 'reference to the keys',
  `entity_id` bigint(20) NOT NULL,
  `value` text,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_id` (`client_id`,`entity_id`,`key_id`),
  KEY `client_id_2` (`client_id`,`key_id`)
) ;

所有的写入查询都是以下形式:

INSERT INTO `key_values` 
  (client_id, key_id, entity_id,value)
values
  (23, 47, 147, 'myValue'), (...), (...)...
ON DUPLICATE KEY UPDATE value = values(value);

• 该表目前大约有 3.5 亿条记录，并且增长速度非常快。
• 通常可以通过实时集成对表进行写入从离线源中插入少于 10 行或大量 25K。
• 对于给定的客户端，一次只能运行一个批量操作。这是减少 insert
之间的行锁
• 锁定等待超时时间设置为 50 秒

目前，当离线事件有时(并非总是)发生时，我们会遇到锁定等待超时。如果不避免超时，可能会发生哪些变化？

• 目前无法更改设计(分片/分区/集群)。
• REPLACE 是另一个候选人，但我不想在生产中将删除权限授予代码中的任何内容。
• INSERT IGNORE 然后 UPDATE 是一个很好的选择，但它会带来很大的改进吗？

我还有哪些其他选择？

提前感谢所有建议和回答。

Answer 1

关于锁等待超时，这可以通过mysql配置设置来改变innodb_lock_wait_timeout除了在 my.cnf 中更改它之外，还可以动态修改(无需重新启动 mysql)。

关于锁等待，mysql需要考虑的一件事是默认的事务隔离级别，即REPEATABLE READ。此设置的副作用是，对于您可能期望的读取，会发生更多的锁定(特别是如果您有 SQL Server 背景，其默认传输级别为 READ COMMITTED)。现在，如果您不需要 REPEATABLE READ，您可以在查询中使用 SET TRANSACTION ISOLATION LEVEL 语法更改您的 tran iso 级别，或者对于整个服务器，使用配置设置 transaction-isolation .我建议使用 READ COMMITTED，并考虑在您的应用程序中是否还有其他地方甚至可以接受“更脏”的读取(在这种情况下您可以使用 READ UNCOMMITTED。