MySQL Sink: Investigate alternative encoding of multi-row UPDATE statements to improve performance
@wk989898 is already working on this.
Since Nov 20, 2025.
- Dominant language
- Go
- Stars
- 56
- Forks
- 63
- Avg merge
- 2d 20h
- Merged PRs (30d)
- 34
Description
Is your feature request related to a problem?
Inherited from the Old Architecture, a multi-row UPDATE statement is currently generated like this:
-- create table tbl (col1 int not null, col2 int not null, col3 int, col4 int, col5 int, primary key (col1, col2));
UPDATE tbl SET
col3 = CASE
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
END,
col4 = CASE
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
END,
col5 = CASE
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
WHEN col1 = ? AND col2 = ? THEN ?
END
WHERE
(col1 = ? AND col2 = ?) OR
(col1 = ? AND col2 = ?) OR
(col1 = ? AND col2 = ?) OR
(col1 = ? AND col2 = ?)
;
However the CASE expressions make the statement very complex, and also blows up the number of arguments. In the downstream monitoring we find that parsing takes up 96% of execution time (customer was using v7.1.1 but the same principle applies).
Ironically when the customer restarted the changefeed which forced automatic safe-mode, the sink performance becomes higher because the generated DELETE and REPLACE statements are much simpler
DELETE FROM tbl
WHERE
(col1 = ? AND col2 = ?) OR
(col1 = ? AND col2 = ?) OR
(col1 = ? AND col2 = ?) OR
(col1 = ? AND col2 = ?)
;
REPLACE INTO tbl VALUES
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?);
Describe the feature you'd like
Consider alternatives to which avoids those complicated CASE expressions. For instance we could use a REPLACE or INSERT INTO ON DUPLICATE KEY UPDATE statement instead:
INSERT INTO tbl VALUES
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
col3 = VALUES(col3),
col4 = VALUES(col4),
col5 = VALUES(col5);
This does not have the exact same behavior as UPDATE though. When the list of rows contained rows missing in downstream, an UPDATE will ignore such rows while REPLACE and INSERT INTO ON DUPLICATE KEY UPDATE will add them.
When the downstream is MySQL 8+, one could use a multi-table UPDATE with a VALUES table to recover the expected behavior
update
tbl old,
(values
row(?, ?, ?, ?, ?),
row(?, ?, ?, ?, ?),
row(?, ?, ?, ?, ?),
row(?, ?, ?, ?, ?)
) new
set
old.col3 = new.column_2,
old.col4 = new.column_3,
old.col5 = new.column_4
where
old.col1 = new.column_0 and
old.col2 = new.column_1
;
but TiDB does not support VALUES statement (pingcap/tidb#21486). The VALUES statement can be simulated with a bunch of SELECT FROM DUAL combined by UNION ALL:
update
tbl old,
(
select ? col1, ? col2, ? col3, ? col4, ? col5 union all
select ? col1, ? col2, ? col3, ? col4, ? col5 union all
select ? col1, ? col2, ? col3, ? col4, ? col5 union all
select ? col1, ? col2, ? col3, ? col4, ? col5
) new
set
old.col3 = new.col3,
old.col4 = new.col4,
old.col5 = new.col5
where
old.col1 = new.col1 and
old.col2 = new.col2
;
This seems to work fine down to TiDB v5.1.4, but I haven't tested the parsing performance compared with the UPDATE CASE WHEN and the REPLACE variants.
Also note that the generated plan for UPDATE SELECT UNION ALL is significantly worse, which generated an IndexJoin instead of doing BatchPointGet directly.
This is the plan for a 4-row UPDATE CASE WHEN against a 1000-row 5-column table:
+-------------------------+---------+------+--------------------------------------+------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+---------+------+--------------------------------------+------------------------------+
| Update_4 | N/A | root | | N/A |
| └─Batch_Point_Get_6 | 4.00 | root | table:tbl, index:PRIMARY(col1, col2) | keep order:false, desc:false |
+-------------------------+---------+------+--------------------------------------+------------------------------+
These are the plans for REPLACE and INSERT INTO ON DUPLICATE KEY UPDATE:
+----------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+----------+---------+------+---------------+---------------+
| Insert_1 | N/A | root | | N/A |
+----------+---------+------+---------------+---------------+
This is the plan for UPDATE SELECT UNION ALL:
+--------------------------------------+----------+-----------+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+----------+-----------+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Update_18 | N/A | root | | N/A |
| └─Projection_22 | 10000.00 | root | | test.tbl.col1, test.tbl.col2, test.tbl.col3, test.tbl.col4, test.tbl.col5, test.tbl._tidb_rowid, Column#27, Column#28, Column#29, Column#30, Column#31 |
| └─IndexJoin_26 | 10000.00 | root | | inner join, inner:IndexLookUp_25, outer key:Column#27, Column#28, inner key:test.tbl.col1, test.tbl.col2, equal cond:eq(Column#27, test.tbl.col1), eq(Column#28, test.tbl.col2) |
| ├─Union_36(Build) | 4.00 | root | | |
| │ ├─Projection_39 | 1.00 | root | | 1->Column#27, 2->Column#28, 13->Column#29, 14->Column#30, 15->Column#31 |
| │ │ └─TableDual_40 | 1.00 | root | | rows:1 |
| │ ├─Projection_42 | 1.00 | root | | 2->Column#27, 3->Column#28, 14->Column#29, 15->Column#30, 16->Column#31 |
| │ │ └─TableDual_43 | 1.00 | root | | rows:1 |
| │ ├─Projection_45 | 1.00 | root | | 3->Column#27, 4->Column#28, 15->Column#29, 16->Column#30, 17->Column#31 |
| │ │ └─TableDual_46 | 1.00 | root | | rows:1 |
| │ └─Projection_48 | 1.00 | root | | 4->Column#27, 5->Column#28, 16->Column#29, 17->Column#30, 18->Column#31 |
| │ └─TableDual_49 | 1.00 | root | | rows:1 |
| └─IndexLookUp_25(Probe) | 1.00 | root | | |
| ├─IndexRangeScan_23(Build) | 1.00 | cop[tikv] | table:old, index:PRIMARY(col1, col2) | range: decided by [eq(test.tbl.col1, Column#27) eq(test.tbl.col2, Column#28)], keep order:false, stats:pseudo |
| └─TableRowIDScan_24(Probe) | 1.00 | cop[tikv] | table:old | keep order:false, stats:pseudo |
+--------------------------------------+----------+-----------+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
If we switched to UPDATE SELECT UNION ALL we may have improved the parsing time but make execution time much slower.
Describe alternatives you've considered
No response
Teachability, Documentation, Adoption, Migration Strategy
No response
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.