在MySQL (Innodb)中选择之后，更新等待锁的时间太长了

Question

我有：：getPicture (yii2 ORM)，它从表中选择一行，然后从该行中更新一个字段。表结构只包含id (PK)、path (VARCHAR(500))和seen (int(1))。已查看的列已编入索引。

我的伪代码：

SELECT * FROM pics WHERE id=:id LIMIT 1;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

当客户端并行请求10+图片时(对于不同的Id)，一些更新会意外地挂起1-10秒。在这种情况下，我看到Innodb_row_lock_waits每次都会增加。

在没有更新的情况下，我的代码一直运行得非常快。

我尝试过使用事务，更新延迟(我不确定我是否正确地尝试过)。

是否有选择和更新相同行的最佳实践？我应该提供哪些补充资料来澄清这个问题？

更新1服务器配置

Server: CPU: 1.5 Ghz, 1 Core, RAM 8Gb
Debian: 7.8
MySQL: 5.5.41

更新2

我已经将SQL代码更改为此，但情况并没有改变。此外，我还删除了“查看”列的索引，但对此代码没有任何影响。

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
END;

在mysql-sl.log中，我看到下一个条目：

# Time: 150210  2:03:24
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 13.011485  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1423530204;
commit;
# Time: 150210  2:03:34
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 9.765468  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 1
SET timestamp=1423530214;
UPDATE `pics` SET seen=1 WHERE id=315 LIMIT 1;

更新3

表中的行数约为70行。

显示创建表图；

CREATE TABLE `pics` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `image` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `archive` tinyint(1) NOT NULL DEFAULT '0',
 `seen` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `tbl_picture_timestamp` (`timestamp`),
 KEY `tbl_picture_archive` (`archive`),
 KEY `tbl_picture_seen` (`seen`),
) ENGINE=InnoDB AUTO_INCREMENT=319 DEFAULT CHARSET=utf8

从图片中选择RowsReturned (1) id=315；(大约花费0.000035s)

RowsReturned 1

Answer 1

也许你应该强加一个选择以进行更新

SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

我以前也提到过

• Mar 08, 2013：DB锁是如何绑定到连接和会话的？
• Mar 12, 2013：如何防止应用程序中出现死锁？ (参见机制3)

更新2015-02-05 22:22 EST

总是会有行锁强加，没有办法绕过它。然而，你的代码和我的代码有什么区别呢？

想想看。在代码中运行SELECT并不能保护行不受更改。您所看到的行锁将在UPDATE期间发生。我的代码在SELECT期间将锁强加在行和所有相应的索引项上。这使得UPDATE有较少的压力。

这与MySQL文档是一致的

一个选择..。对于UPDATE，读取最新的可用数据，在它读取的每一行上设置独占锁。因此，它设置的锁与已搜索的SQL更新在行上设置的锁相同。

更新2015-02-10 22:05 EST

@RickJames：seen字段上的索引似乎已经指出了您的问题。

看看InnoDB体系结构( Percona CTO Vadim Tkachenko的图片)

请注意图片中的两个结构

• 使用InnoDB缓冲池(IB_IBP)插入缓冲区
• 使用系统表空间插入缓冲区(更称为ibdata1) (IB_SYSTBLSPC)

这些结构负责更新非唯一索引。

现在，请注意设置seen=1对id = 315的影响。

假设seen为0或1(基数为2)

• seen上只有0和1作为值的索引使得索引与管理两个链接列表没有什么不同，每个链表都是由主键id排序的。
• 更改0到1(或1到0)将把信息放入IB_IBP
• 当需要将更改写入pics.ibd 时
  • 将更改后的数据页的副本写入双缓冲区
  • 索引定位所需的信息从IB_IBP传递到IB_SYSTBLSPC

• id 315的索引条目从seen=0的索引侧移到索引的另一端，其中seen=1

如果您要更改基准测试中的归档列，这些策略也适用于archive索引。

除了InnoDB缓冲池内和外的索引之外，不要忘记这个问题是从哪里开始的:所有的行锁。

把一些关于排锁的东西记在脑子里。当发出行锁时，还可以锁定整个页。该页可以是多个主键值的位置。我有另一篇文章讨论了页面锁(Dec 31,2012：查询被困在非常简单的计数查询上)

请在你的评论中注意，你说过

更重要的是，在大多数情况下，它能快速工作。

为什么不是所有的案子？因为我刚才提到的原因。@RickJames首先提到了seen指数，他建议删除该索引是正确的。按照同样的思路，我建议也删除archive索引(特别是在基准测试中修改归档值时)。在不删除这些索引的情况下，看看您对InnoDB基础设施施加的所有压力，即使该表有70行。如果有几千或数百万排的话，情况会更糟。

Answer 2

行锁在事务的持续时间内保持。如果存在争用，首先要做的事情之一是确保在更新行和提交之间的代码路径尽可能紧凑。

能够看到这些锁的有用诊断：

• SHOW CREATE TABLE pics
• SHOW ENGINE INNODB STATUS
• SELECT * FROM information_schema.innodb_trx

索引结构影响锁定(因此影响SHOW CREATE TABLE)。

SHOW ENGINE INNODB STATUS和innodb_trx将显示关于哪些行锁是冲突的以及哪些语句的信息。