[Mysql] mysql update一条语句过程遇到的问题

1、因为业务已停掉不担心会出现大量的锁等待事件，因此也没有考虑批量去更新（数据量有1700万左右），直接尝试去跑这一条update语句，第一次跑出现以下问题：

1
2

UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL';
error：Multi-statement TRANSACTION required more THAN 'max_binlog_cache_size' bytes of STORAGE; increase this mysqld variable AND try again

2、查看binlog_cache_size，最大值为1G：

mysql> show variables like '%binlog_cache_size%';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| binlog_cache_size     | 4194304    |
| max_binlog_cache_size | 1073741824 |
+-----------------------+------------+
2 rows in set (0.00 sec)

3、尝试去调高max_binlog_cache_size，在此执行update语句：

mysql> set global max_binlog_cache_size=4073741824;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%binlog_cache_size%';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| binlog_cache_size     | 4194304    |
| max_binlog_cache_size | 4073738240 |
+-----------------------+------------+
2 rows in set (0.00 sec)

4、将至调到4G、8G去执行该语句，均报以上的错误，考虑用批量更新的方法去处理该update问题，尝试每次更新500万数据，binlog_cache_size仍然不满足，每次更新100万，执行正常：

mysql> UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id >=1 and id <= 1000000;
Query OK, 974190 rows affected (2 min 3.48 sec)
Rows matched: 974190  Changed: 974190  Warnings: 0
...
...
mysql> UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id >=16000000 and id <= 17000000;
Query OK, 982238 rows affected (2 min 37.44 sec)
Rows matched: 982238  Changed: 982238  Warnings: 0

每100万更新耗时为2min 40s左右

5、上述方法适用于当前表有主键id且递增的情况。如果该id值从其他关联表获取，可考虑一下方法：

1	mysql> UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id in (select id from (select id from trade_order ORDER BY id ASC LIMIT 1,1000000) as tmp);

该方法涉及到一个子查询，总体执行速度会下降，根据取值范围和当前实际更新的行数，耗时在4min左右：

1
2
3

UPDATE trade_order SET state = 'CANCELLED' where state='CANCEL' and id in (select id from (select id from trade_order ORDER BY id ASC LIMIT 1,1000000) as tmp);
Query OK, 981631 rows affected (3 min 49.27 sec)
Rows matched: 981631  Changed: 981631  Warnings: 0