使用 FK 更新行时的 MySQL 性能 [英] MySQL performance when updating row with FK

问题描述

我有两张桌子

spies     | 
--------- | 
id        | PK
weapon_id | FK
name      |   


weapons
--------- |
id        | PK
name      | 

我试图澄清这两个 SQL 更新是否有区别(使用 MySQL innoDB 时)

I'm trying to clarify whether there is a difference in these two SQL updates (when using MySQL innoDB)

查询 1:

UPDATE spies SET name = 'Bond', Weapon_id = 1 WHERE id = 1

或

查询 2:

UPDATE spies SET name = 'Bond' WHERE id = 1

我听说当使用 FK 更新一行时会在父级上创建只读锁(不确定这是否正确).

I have heard that when the updating a row with a FK creates read-only lock (not sure if that's the correct term) on the parent.

使用查询 2 会避免对父表的锁定吗?

Would using Query 2 avoid the lock on the parent table?

推荐答案

考虑以下架构:(为了方便起见，保留了 Rem stmts) :

-- drop table if exists spies;
create table spies
(   id int primary key,
    weapon_id int not null,
    name varchar(100) not null,
    key(weapon_id),
    foreign key (weapon_id) references weapons(id)
)engine=InnoDB;

-- drop table if exists weapons;
create table weapons
(   id int primary key,
    name varchar(100) not null
)engine=InnoDB;

insert weapons(id,name) values (1,'slingshot'),(2,'Ruger');
insert spies(id,weapon_id,name) values (1,2,'Sally');
-- truncate table spies;

现在，我们有 2 个进程，P1 和 P2.最好测试一下 P1 可能是 MySQL Workbench，而 P2 是 MySql 命令行窗口.换句话说，您必须将其设置为单独的连接并且正确.您必须仔细观察以适当的方式逐步运行这些程序(在下面的叙述中进行了描述)，并查看其对其他流程窗口的影响.

Now, we have 2 processes, P1 and P2. Best to test where P1 is perhaps MySQL Workbench and P2 is a MySql Command-line window. In other words, you have to set this up as separate connections and right. You would have to have a meticulous eye for step-by-step running these in the proper fashion (described in the Narrative below) and see its impact on the other process window.

考虑以下查询，请记住，未包含在显式事务中的 mysql 查询本身就是隐式事务.但在下面，我明确表示:

Consider the following queries, keeping in mind that a mysql query not wrapped in an explicit transaction is itself an implicit transaction. But below, I swung for explicit:

第一季度:

START TRANSACTION;
-- place1
UPDATE spies SET name = 'Bond', weapon_id = 1 WHERE id = 1;
-- place2
COMMIT;

第 2 季度:

START TRANSACTION;
-- place1
UPDATE spies SET name = 'Bond' WHERE id = 1;
-- place2
COMMIT;

Q3:

START TRANSACTION;
-- place1
SELECT id into @mine_to_use from weapons where id=1 FOR UPDATE; -- place2
-- place3
COMMIT;

第 4 季度:

START TRANSACTION;
-- place1
SELECT id into @mine_to_use from spies where id=1 FOR UPDATE; -- place2
-- place3
COMMIT;

Q5(查询的大杂烩):

Q5 (hodge podge of queries):

SELECT * from weapons;
SELECT * from spies;

叙述

Q1: 当 P1 开始 Q1 并到达 place2 时，它在两个id=1 行的武器和间谍表(共 2 行，每个表 1 行).这可以通过 P2 开始运行 Q3，到达 place1，但在 place2 上阻塞，并且只有在 P1 开始调用 COMMIT 时才被释放来证明.我刚才所说的关于 P2 运行 Q3 的所有内容都与 P2 运行 Q4 相同.总之，在 P2 屏幕上，place2 会冻结，直到 P1 Commit.

Narrative

Q1: When P1 starts to begin Q1, and gets to place2, it has obtained an exclusive row-level update lock in both tables weapons and spies for the id=1 row (2 rows total, 1 row in each table). This can be proved by P2 starting to run Q3, getting to place1, but blocking on place2, and only being freed when P1 gets around to calling COMMIT. Everything I just said about P2 running Q3 is ditto for P2 running Q4. In summary, on the P2 screen, place2 freezes until the P1 Commit.

再次说明隐式事务.您真正的 Q1 查询将非常快速地执行此操作，并且从它出来将进行隐式提交.但是，如果您要运行更多耗时的例程，则前一段将其分解.

A note again about implicit transactions. Your real Q1 query is going to perform this very fast and coming out of it will do an implicit commit. However, the prior paragraph breaks it down were you to have more time-costly routines running.

Q2: 当 P1 开始 Q2 并到达 place2 时，它在两个id=1 行的武器和间谍表(共 2 行，每个表 1 行).但是，P2 在 Q3 阻止 weapons 方面没有问题，但 P2 在 place2 spies 运行 Q4 时遇到了阻止问题.

Q2: When P1 starts to begin Q2, and gets to place2, it has obtained an exclusive row-level update lock in both tables weapons and spies for the id=1 row (2 rows total, 1 row in each table). However, P2 has no issues with Q3 blocking weapons, but P2 has block issues running Q4 at place2 spies.

因此，Q1 和 Q2 之间的差异归结为 MySQL 知道 FK 索引与 UPDATE 中的列不相关，并且手册在下面的注 1 中说明了这一点.

So, the differences between Q1 and Q2 come down to MySQL knowing that the FK index is not relevant to a column in the UPDATE, and the manual states that in Note1 below.

当 P1 运行 Q1 时，P2 对 Q5 类型的查询的只读非锁获取没有问题.唯一的问题是 P2 根据适当的隔离级别看到的数据再现.

When P1 runs Q1, P2 has no problems the read-only non-lock aquiring Q5 types of queries. The only issues are what data renditions P2 sees based on the ISOLATION LEVEL in place.

注意 1:来自题为 InnoDB 中不同 SQL 语句设置的锁:

如果在表上定义了 FOREIGN KEY 约束，则任何插入、更新、或删除需要检查集的约束条件在它查看以检查记录的记录上共享记录级锁约束.InnoDB 也会在以下情况下设置这些锁约束失败.

If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

以上就是为什么Q2:的行为使得P2可以自由地执行更新或获取更新对武器.这是因为引擎没有在 Weapon_id 上使用 P1 执行更新，因此在该表中没有行级锁.

The above is why the behavior of Q2: is such that P2 is free to perform an UPDATE or acquire an UPDATE exclusive momentary lock on weapons. This is because the engine is not performing an UPDATE with P1 on weapon_id and thus does not have a row-level lock in that table.

要将其拉回到 50,000 英尺，最关心的是在隐式事务(没有 START/COMMIT 的事务)或在 COMMIT 之前的显式事务中持有锁的持续时间.理论上可以无限期地禁止对等进程获取其对更新的需求.但是每次获取该锁的尝试都受其的设置控制innodb_lock_wait_timeout.这意味着，默认情况下，大约 60 秒后超时.要查看您的设置，请运行:

To pull this back to 50,000 feet, one's biggest concern is the duration at which a lock is held either in an implicit transaction (one with no START/COMMIT), or explicit transaction before a COMMIT. A peer process can be prohibited from acquiring its need for an UPDATE in theory indefinitely. But each attempt at acquiring that lock is governed by its setting for innodb_lock_wait_timeout. What that means is, by default, after about 60 seconds it times out. For a view of your setting, run:

select @@innodb_lock_wait_timeout;

对我来说，目前是 50(秒).

For me, at the moment, it is 50 (seconds).

这篇关于使用 FK 更新行时的 MySQL 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！