MySQL -使用显式事务会影响查询性能吗？

Question

在MySQL 5.6中，使用InnoDB，我有一个相当大的表，称为A；(大约400万行)，在其中我需要更新一个字段。有关更新内容的条件保存在另一个表B中；两个表之间的关系也保存在另一个表C中；因此，我用两个联接创建了update语句，如下所示：

UPDATE A a JOIN (B b, C c) ON (conditions) SET a.myField = 'myValue'

现在，我在我的目标数据库的转储上测试了这个语句，在一个等价的机器上。因为我需要这个转储用于其他目的，所以我使用一个事务来测试这个东西，以便之后回滚：

START TRANSACTION;
UPDATE [...]
ROLLBACK;

这给了我相当好的性能(我认为)我的更新，大约17秒和8秒的回滚。

我的问题是:如果我按原样运行这条语句，而不是在目标数据库中的事务中运行，我会获得相同的性能吗？换句话说，使用显式事务是否会影响性能，如果是，如何影响？

我试着在这个问题上查看文档，但是在这个主题上它是相当稀少的。

Answer 1

1:使用自动提交自动提交每个事务：

SET autocommit = ON;
UPDATE ...

2:显式地将单个语句转换为事务。这与#1“完全相同”。

BEGIN;     (aka START TRANSACTION;)
UPDATE ...;
COMMIT;

3:交易中的多个语句：

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

4:不要使用这个；总有一天你会忘记使用COMMIT：

SET autocommit = OFF;
UPDATE ...;
...
eventually: COMMIT;

5:不建议。它可能会因“日志”溢出而导致减速：

BEGIN;
a million statements
COMMIT;

备注：

• 每个SQL都有一些小开销(网络传输、解析等)。
• 每个事务都是一个明显的开销。特别是有磁盘I/O使其崩溃安全。因此，#3比#1或#2的两个副本更快。
• 有关完整性和速度之间的权衡，请参见innodb_flush_log_at_trx_commit。

建议：

• 第一要务:做应用程序需要做的事情--这就是，在每个事务中放置适当数量的语句。
• 较低的优先级:适当时将多个语句组合在一起，从而分摊事务开销。
• 在INSERT的情况下，更好的做法是有一个多行的INSERT语句。Davit的评论显示，在一笔交易中放入多个INSERTs，可以加快9倍的速度。这类似于单个批处理插入的效果。
• 只在适当的时候使用ROLLBACK。这是昂贵的。这是因为InnoDB对COMMIT进行了优化，并且必须做额外的工作来撤消。
• 为了提高性能，这整个讨论都是在清单上进行的。更重要的是索引和查询的制定。
• 数千行DELETE和UPDATE在日志中有很大的开销和压力。最好把他们拆散。

Answer 2

一个不完整的答案，但尽管如此，它在这里。

根据评论中的建议，我做了一些测试，并获得了以下性能。问题的查询修改了400万行中的35万行。

使用上面提到的显式事务花费了大约24秒，而仅查询就花费了20秒。

结果显示，使用显式事务似乎比单个查询花费更大。但我不明白为什么，所以我得去查一下。

关于我的系统变量的一些细节：

innodb_buffer_pool_size和innodb_log_file_size从缺省值增加了一个因子3。为了这个特定的测试目的，我将query_cache_type设置为0(从1)。之后，我们的应用程序的性能急剧下降，但是由于它是一台测试机器，所以很好。

另外，MySQL服务器是通过系统%d管理的，我们将LimitNOFILE参数修改为8192。这个文件的其他参数我认为是不相关的。

关于查询，EXPLAIN EXTENDED显示只使用了req和eq_ref连接类型。

最后，我将对目标数据库进行单一查询，因为查询速度更快。