MySQL数据库如果update更新0行，会写数据吗？-duidaima 堆代码

MySQL数据库如果update更新0行，会写数据吗？

发布于 1天前
 22 热度

 0 评论

旧人不归
0 粉丝 37 篇博客

分析两个场景:
1.更新id列，更新前后值一样，也就是Rows matched: 1 Changed: 0，会写数据吗？

mysql> update cjc.t1 set id=5 where id=5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

2.更新id列，没有匹配的值，也就是Rows matched: 0 Changed: 0，会写数据吗？

mysql> update cjc.t1 set id=5 where id=5000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

注意:不考虑触发器、主外键约束、ON UPDATE CURRENT_TIMESTAMP 等场景。
回答这个问题前，先思考可能会向哪些文件写入数据:

1:t1.ibd
2:redo log file
3:undo log file
4:binlog
5.Doublewrite Buffer Files(ib_*.dblwr)

测试环境说明:
MySQL 8.0.33
binlog_format=ROW
测试结果:
以上两个场景，有数据写入的文件有:

1:redo log file
2:undo log file
3.Doublewrite Buffer Files(ib_*.dblwr)

没有数据写入的文件有:

1:t1.ibd
2:binlog

如果是 binlog_format=STATEMENT，理论上会binlog有数据写入。
测试过程如下:
查看参数:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

创建测试数据:

use cjc;
CREATE TABLE t1(id int,time time);
insert into t1 values(1,now());
insert into t1 values(2,now());
insert into t1 values(3,now());
insert into t1 values(4,now());
insert into t1 values(5,now());
flush logs;

记录更新前的信息:

mysql> select * from cjc.t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 14:28:46 |
|    2 | 14:28:49 |
|    3 | 14:28:52 |
|    4 | 14:31:04 |
|    5 | 14:42:35 |
+------+----------+
5 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Innodb_data_writes | 1629  |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Innodb_os_log_written | 282112 |
+-----------------------+--------+
1 row in set (0.00 sec)

数据文件 t1.ibd:

ls -lrth /mysqldata/13309/data/cjc/t1.ibd 
-rw-r----- 1 mysql mysql 112K May 28 14:42 /mysqldata/13309/data/cjc/t1.ibd

stat /mysqldata/13309/data/cjc/t1.ibd 
  File: ‘/mysqldata/13309/data/cjc/t1.ibd’
  Size: 114688          Blocks: 224        IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 6089419     Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-28 14:28:21.624665631 +0800
Modify: 2025-05-28 14:42:35.527944136 +0800
Change: 2025-05-28 14:42:35.527944136 +0800
 Birth: -
undo 文件undo_001,undo_002:

ls -lrth /mysqldata/13309/undo/undo_00*
-rw-r----- 1 mysql mysql 16M May 28 14:42 /mysqldata/13309/undo/undo_002
-rw-r----- 1 mysql mysql 16M May 28 14:42 /mysqldata/13309/undo/undo_001

stat /mysqldata/13309/undo/undo_00*
  File: ‘/mysqldata/13309/undo/undo_001’
  Size: 16777216        Blocks: 32768      IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 33800442    Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-22 11:09:11.493664080 +0800
Modify: 2025-05-28 14:42:51.532240296 +0800
Change: 2025-05-28 14:42:51.532240296 +0800
 Birth: -
  File: ‘/mysqldata/13309/undo/undo_002’
  Size: 16777216        Blocks: 32768      IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 33800443    Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-22 11:09:11.497664152 +0800
Modify: 2025-05-28 14:42:51.532240296 +0800
Change: 2025-05-28 14:42:51.532240296 +0800
 Birth: -
binlog文件:mysql-bin.000021

ls -lrth /mysqldata/13309/binlog/mysql-bin.000021
-rw-r----- 1 mysql mysql 237 May 28 14:42 /mysqldata/13309/binlog/mysql-bin.000021

stat /mysqldata/13309/binlog/mysql-bin.000021
  File: ‘/mysqldata/13309/binlog/mysql-bin.000021’
  Size: 237             Blocks: 8          IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 101937603   Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-28 14:42:50.748225953 +0800
Modify: 2025-05-28 14:42:50.744225880 +0800
Change: 2025-05-28 14:42:50.744225880 +0800
 Birth: -
redo log file文件:#ib_redo0

ls -lrth /mysqldata/13309/redo/#innodb_redo/#ib_redo0 
-rw-r----- 1 mysql mysql 64M May 28 14:44 /mysqldata/13309/redo/#innodb_redo/#ib_redo0

stat /mysqldata/13309/redo/#innodb_redo/#ib_redo0 
  File: ‘/mysqldata/13309/redo/#innodb_redo/#ib_redo0’
  Size: 67108864        Blocks: 131072     IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 6089452     Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-22 11:09:11.965672530 +0800
Modify: 2025-05-28 14:44:32.266053036 +0800
Change: 2025-05-28 14:44:32.266053036 +0800
 Birth: -

执行 update 操作，更新前后值相同:

mysql> update cjc.t1 set id=5 where id=5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

有数据写入:update 前 1629 后 1652;

# 堆代码 duidaima.com
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Innodb_data_writes | 1652  |
+--------------------+-------+
1 row in set (0.00 sec)

有数据写入:update 前 282112 后 283136;

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Innodb_os_log_written | 283136 |
+-----------------------+--------+
1 row in set (0.00 sec)

数据文件 t1.ibd:update 前后没有数据写入

ls -lrth /mysqldata/13309/data/cjc/t1.ibd 
-rw-r----- 1 mysql mysql 112K May 28 14:42 /mysqldata/13309/data/cjc/t1.ibd

stat /mysqldata/13309/data/cjc/t1.ibd 
  File: ‘/mysqldata/13309/data/cjc/t1.ibd’
  Size: 114688          Blocks: 224        IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 6089419     Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-28 14:28:21.624665631 +0800
Modify: 2025-05-28 14:42:35.527944136 +0800
Change: 2025-05-28 14:42:35.527944136 +0800
 Birth: -

undo 文件undo_001,undo_002:update 前后有数据写入

ls -lrth /mysqldata/13309/undo/undo_00*
-rw-r----- 1 mysql mysql 16M May 28 14:44 /mysqldata/13309/undo/undo_001
-rw-r----- 1 mysql mysql 16M May 28 14:44 /mysqldata/13309/undo/undo_002

stat /mysqldata/13309/undo/undo_00*
  File: ‘/mysqldata/13309/undo/undo_001’
  Size: 16777216        Blocks: 32768      IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 33800442    Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-22 11:09:11.493664080 +0800
Modify: 2025-05-28 14:44:31.534039941 +0800
Change: 2025-05-28 14:44:31.534039941 +0800
 Birth: -
  File: ‘/mysqldata/13309/undo/undo_002’
  Size: 16777216        Blocks: 32768      IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 33800443    Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-22 11:09:11.497664152 +0800
Modify: 2025-05-28 14:44:31.542040084 +0800
Change: 2025-05-28 14:44:31.542040084 +0800
 Birth: -

binlog文件:mysql-bin.000021:update 前后没有数据写入
如果 binlog_format=STATEMENT ，理论上会有写入。

ls -lrth /mysqldata/13309/binlog/mysql-bin.000021
-rw-r----- 1 mysql mysql 237 May 28 14:42 /mysqldata/13309/binlog/mysql-bin.000021

stat /mysqldata/13309/binlog/mysql-bin.000021
-rw-r----- 1 mysql mysql 237 May 28 14:42 /mysqldata/13309/binlog/mysql-bin.000021
mysql@SATEST-DAW-004:/home/mysql$stat /mysqldata/13309/binlog/mysql-bin.000021
  File: ‘/mysqldata/13309/binlog/mysql-bin.000021’
  Size: 237             Blocks: 8          IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 101937603   Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-28 14:42:50.748225953 +0800
Modify: 2025-05-28 14:42:50.744225880 +0800
Change: 2025-05-28 14:42:50.744225880 +0800
 Birth: -

redo log file文件:#ib_redo0:update 前后有数据写入

ls -lrth /mysqldata/13309/redo/#innodb_redo/#ib_redo0 
-rw-r----- 1 mysql mysql 64M May 28 14:47 /mysqldata/13309/redo/#innodb_redo/#ib_redo0

stat /mysqldata/13309/redo/#innodb_redo/#ib_redo0 
  File: ‘/mysqldata/13309/redo/#innodb_redo/#ib_redo0’
  Size: 67108864        Blocks: 131072     IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 6089452     Links: 1
Access: (0640/-rw-r-----)  Uid: (  621/   mysql)   Gid: (  621/   mysql)
Access: 2025-05-22 11:09:11.965672530 +0800
Modify: 2025-05-28 14:47:12.280858841 +0800
Change: 2025-05-28 14:47:12.280858841 +0800
 Birth: -

使用 strace 跟踪写入操作过程:
查看 mysqld pid = 27960

ps -ef | grep mysqld

查看当前 Connection id:776

mysql> \s
--------------
mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id:          776
Current database:       cjc
mysql> SELECT THREAD_ID,THREAD_OS_ID,PROCESSLIST_ID,NAME FROM performance_schema.threads where PROCESSLIST_ID=776;
+-----------+--------------+----------------+---------------------------+
| THREAD_ID | THREAD_OS_ID | PROCESSLIST_ID | NAME                      |
+-----------+--------------+----------------+---------------------------+
|       823 |        28029 |            776 | thread/sql/one_connection |
+-----------+--------------+----------------+---------------------------+
1 row in set (0.00 sec)

使用 strace 跟踪写入相关的系统调用:
跟踪线程

strace -f -p 28029 -e trace=file,write,pwrite64,fsync,fdatasync -o /mysqldata/dbtmpfile/mysql_trace_20250528.log

跟踪主进程

strace -f -p 27960 -e trace=file,write,pwrite64,fsync,fdatasync -s 256 -o /mysqldata/dbtmpfile/mysql_trace_all_20250528.log

参数说明:
-e trace=write,pwrite64,fsync,fdatasync`：仅跟踪文件写入和同步操作。
-o：将输出保存到文件。
再次执行:

mysql> update cjc.t1 set id=5 where id=5;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

执行 strace 的窗口，按 Ctrl+C 终止 strace
查看 mysql_trace_20250528.log:

ls -lrth mysql_trace_20250528.log 
-rw-r--r-- 1 root root 1.1K May 28 15:44 mysql_trace_20250528.log

分析 trace 日志:

cat mysql_trace_20250528.log 
27995 pwrite64(11</mysqldata/13309/redo/#innodb_redo/#ib_redo0>, "\0\1e\242\0\223\0'\0\0\0\1_\0006\0\4\373\357_\08\373\377\2\373\357_\0<\0\4"..., 512, 46868992) = 512
27993 fsync(11</mysqldata/13309/redo/#innodb_redo/#ib_redo0>) = 0
27987 pwrite64(5</mysqldata/13309/data/#ib_16384_0.dblwr>, "\302U\310\266\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\0\2\313B\223\0\7\0\0\0\0\0\0"..., 16384, 786432) = 16384
27982 fsync(10</mysqldata/13309/data/ibdata1>) = 0
27991 stat("/mysqldata/13309/redo/#innodb_redo/#ib_redo0", {st_mode=S_IFREG|0640, st_size=67108864, ...}) = 0
27991 openat(AT_FDCWD, "/mysqldata/13309/redo/#innodb_redo/#ib_redo0", O_RDWR) = 48</mysqldata/13309/redo/#innodb_redo/#ib_redo0>
27991 lstat("/mysqldata", {st_mode=S_IFDIR|0755, st_size=156, ...}) = 0
27991 lstat("/mysqldata/13309", {st_mode=S_IFDIR|0755, st_size=143, ...}) = 0
27991 lstat("/mysqldata/13309/redo", {st_mode=S_IFDIR|0755, st_size=26, ...}) = 0
27991 lstat("/mysqldata/13309/redo/#innodb_redo", {st_mode=S_IFDIR|0750, st_size=4096, ...}) = 0
27991 pwrite64(48</mysqldata/13309/redo/#innodb_redo/#ib_redo0>, "\0\0\0\0\0\0\0\0\0\0\0\0\2\313B\223\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512, 1536) = 512
27991 fsync(48</mysqldata/13309/redo/#innodb_redo/#ib_redo0>) = 0
27991 fsync(48</mysqldata/13309/redo/#innodb_redo/#ib_redo0>) = 0

查看帮助信息:

man 2 pwrite64
pwrite() writes up to count bytes from the buffer starting at buf to the file descriptor fd at offset offset.  
The file offset is not changed.
The file referenced by fd must be capable of seeking.
SELECT THREAD_ID,THREAD_OS_ID,PROCESSLIST_ID,NAME FROM performance_schema.threads where THREAD_OS_ID in (27995,27993,27987,27982,27991);
+-----------+--------------+----------------+---------------------------------------------+
| THREAD_ID | THREAD_OS_ID | PROCESSLIST_ID | NAME                                        |
+-----------+--------------+----------------+---------------------------------------------+
|        15 |        27982 |           NULL | thread/innodb/io_write_thread               |
|        20 |        27987 |           NULL | thread/innodb/page_flush_coordinator_thread |
|        24 |        27991 |           NULL | thread/innodb/log_checkpointer_thread       |
|        26 |        27993 |           NULL | thread/innodb/log_flusher_thread            |
|        28 |        27995 |           NULL | thread/innodb/log_writer_thread             |
+-----------+--------------+----------------+---------------------------------------------+
5 rows in set (0.00 sec)

其中:ib_16384_0.dblwr 属于 Doublewrite Buffer Files。

 用户评论

MySQL技术
 125 成员 |  214 话题
+我要提问 +随便写写

可能感兴趣的话题

你知道聚簇索引和非聚簇索引的区别吗？

把数据库从mysql迁移到TiDB感觉SQL查询的速度很慢，原因出在哪里？

Mysql登陆密码忘记了该怎么办？

MySQL中的自增长ID用完了该怎么办？