mysql - 大型 UPDATE [...] SELECT FROM 导致并行 UPDATE/DELETE 终止

Question

我试图通过示例来解释我的问题。我有一个长期运行的声明，例如

UPDATE <table_A>
INNER JOIN <table_B> ON [...]
LEFT JOIN <table_C> ON [...]
LEFT JOIN <table_D> ON [...]
LEFT JOIN <table_E> ON [...]
SET <table_A>.<col_A>=X
WHERE <table_A>.<col_A>=Y AND COALESCE(<table_C>.<id>,<table_D>.<id>,<table_E>.<id> IS NULL

此语句在大表上运行(其中两个每个表包含 7+ 百万行)。更新运行 3-5 分钟。在另一个 session 中是在高并发下完成的

UPDATE <table_C> SET <col_A>=Z WHERE <id> IN ([...])

或

DELETE FROM <table_C> WHERE <id> IN ([...])

当大UPDATE运行，然后这些并发 UPDATE和 DELETES一两分钟后死于锁定等待超时或死锁。全部JOIN列被索引(标准索引)。我已经尝试过

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
[BIG UPDATE];
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

但这并没有帮助。 <table_A> 上的数据一致性不是那么重要(如果它包含不再存在于<table_C> ... <table_E> 中的行也没问题)。最重要的是，小UPDATE/DELETE关于 <table_C> ... <table_E>正在处理中。

Answer 1

由于在实时数据库上运行这么大的更新通常不是一个好主意，我建议您分解大更新。

这不是最优化的方法，但我相信你会设法自己优化它。

循环运行:

1. SELECT Id, ColA FROM TableA ORDER BY Id DESC LIMIT 10 OFFSET(迭代)*10
2. 第二个循环，从以前的结果中获取行，其中 tableA.colA=Y
   2.1. SELECT Id FROM TableB WHERE ID=id_from_current_iteration
   2.2. SELECT Id FROM TableC WHERE ID=id_from_current_iteration
   2.3 如果前面两个查询都返回null则进行下一步，否则进行下一次迭代2.4 更新 TableA SET ColA=X WHERE ID=id_from_current_iteration

换句话说 - 避免连接。
这将比单次更新花费更长的时间，但它会起作用。
优化它的第一步是批处理查询。