案例分析：MySQL 并行复制竟然比单线程慢？

走在路途 發表於 2025-10-20 12:10:00

案例分析：MySQL 并行复制竟然比单线程慢？

最近碰到一个 case，发现在特定场景下，并行复制竟然比单线程复制要慢。
<h1 data-tool="mdnice编辑器">现象</h1>
从某个时间点开始，从库的复制延迟持续增加，且没有下降的趋势。
数据库版本：8.0.40，事务隔离级别 RC（Read Committed），并行重放线程数（replica_parallel_workers）为 8。
<h1 data-tool="mdnice编辑器">分析过程</h1>
通过<code>show slave status\G</code>查看，发现<code>Relay_Master_Log_File</code>和<code>Exec_Master_Log_Pos</code>都在变化，只不过变化得比较慢。
刚开始怀疑是主库写入量较大导致的，后来通过<code>mysql-binlog-time-extractor</code>（具体用法可参考：分享一个 MySQL binlog 分析小工具）分析，发现主库的写入量在刚开始出现延迟时（2025-09-01 09:30）并不大，反倒是写入量大的时间段（2025-09-01 04:57:53 - 2025-09-01 05:02:42）没有出现延迟。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>+--------------------+------------------------+---------------------+---------------------+-----------+------------------------------------+ | Log_name           | File_size              | Start_time          | End_time            | Duration  | GTID                               | +--------------------+------------------------+---------------------+---------------------+-----------+------------------------------------+ | binary-log.005565  | 1302499830 (1.21 GB)   | 2025-09-01 04:57:53 | 2025-09-01 04:58:22 | 00:00:29  | 1284696693-1284699126              | | binary-log.005566  | 1105002721 (1.03 GB)   | 2025-09-01 04:58:22 | 2025-09-01 04:58:23 | 00:00:01  | 1284699127-1284699312              | | binary-log.005567  | 1273545902 (1.19 GB)   | 2025-09-01 04:58:23 | 2025-09-01 05:02:33 | 00:04:10  | 1284699313-1284728539              | | binary-log.005568  | 1287820910 (1.20 GB)   | 2025-09-01 05:02:33 | 2025-09-01 05:02:42 | 00:00:09  | 1284728540-1284729282              |   ... | binary-log.005633  |   58514304 (55.80 MB)  | 2025-09-01 09:12:53 | 2025-09-01 09:17:53 | 00:05:00  | 1286735216-1286786118              | | binary-log.005634  |   58955596 (56.22 MB)  | 2025-09-01 09:17:53 | 2025-09-01 09:22:53 | 00:05:00  | 1286786119-1286834568              | | binary-log.005635  |   71508778 (68.20 MB)  | 2025-09-01 09:22:53 | 2025-09-01 09:27:53 | 00:05:00  | 1286834569-1286880281              | | binary-log.005636  |  107107179 (102.15 MB) | 2025-09-01 09:27:53 | 2025-09-01 09:32:53 | 00:05:00  | 1286880282-1286942223              | | binary-log.005637  |  530205055 (505.64 MB) | 2025-09-01 09:32:53 | 2025-09-01 09:37:53 | 00:05:00  | 1286942224-1287246612              | | binary-log.005638  |  546754562 (521.43 MB) | 2025-09-01 09:37:53 | 2025-09-01 09:42:53 | 00:05:00  | 1287246613-1287562930              | | binary-log.005639  |  528677634 (504.19 MB) | 2025-09-01 09:42:53 | 2025-09-01 09:47:53 | 00:05:00  | 1287562931-1287868985              | +--------------------+------------------------+---------------------+---------------------+-----------+------------------------------------+ </code></pre>
查看该实例的错误日志，发现有大量的锁等待超时报错。
需要注意的是，这个实例的事务隔离级别是 RC。在该级别下，MySQL 通常只会加记录锁。此外，该实例启用了 WRITESET 并行复制，MySQL 会根据事务修改的主键或唯一索引来判断是否可并行执行。换句话说，如果两个事务在主键或唯一索引上存在冲突，它们将无法并行重放。理论上，在这种机制组合下，从库在重放过程不应发生锁等待超时。
随后使用<code>binlog_summary.py</code>（具体用法可参考：Binlog分析利器-binlog_summary.py）对延迟开始时段的四个 binlog 文件（ binary-log.005636 ~ binary-log.005639 ）进行了分析，发现这些 binlog 的操作模式十分相似：操作次数排名前两位的均为同一张表<code>biz_schema.tbl_product_service_mapping01</code>的 DELETE 与 INSERT 操作。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code># python3 binlog_summary.py -f binary-log.005636.txt -c opr --new TABLE_NAME         DML_TYPE           NUMS                biz_schema.tbl_product_service_mapping01 INSERT             71271               biz_schema.tbl_product_service_mapping01 DELETE             67434   ...     </code></pre>
写了个简单的脚本测试了下，发现对于相同的唯一索引值，<code>INSERT</code>操作总是出现在对应的<code>DELETE</code>操作之后，于是写了个脚本将 <code>DELETE</code>操作涉及的记录提取出来并插入到测试库中，然后将相关 binlog 当作 relay log 进行重放。
为了排除其它表的干扰，在重放时设置了<code>replicate-do-table = biz_schema.tbl_product_service_mapping01</code>，只重放这一张表。
下面是具体的重放步骤：
1. 初始化 relay log：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>CHANGE MASTER TO MASTER_HOST='dummy'; STOP SLAVE; RESET SLAVE ALL; </code></pre>
执行上述命令后，MySQL 会在当前数据目录下生成两个文件：
<ul class="list-paddingleft-1">
<li><code>instance-20250903-0701-relay-bin.000001</code>（第一个 relay log 文件）</li>
<li><code>instance-20250903-0701-relay-bin.index</code>（relay log 索引文件）</li>
</ul>
其中，instance-20250903-0701 是主机名。
2. 替换掉 relay log：
用 binary-log.005636 替换掉 instance-20250903-0701-relay-bin.000001，并修改该文件的属主。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code># cp binary-log.005636 /data/mysql/3306/data/instance-20250903-0701-relay-bin.000001 # chown mysql.mysql /data/mysql/3306/data/instance-20250903-0701-relay-bin.000001 </code></pre>
3. 启动 SQL 线程进行重放：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>CHANGE MASTER TO RELAY_LOG_FILE='instance-20250903-0701-relay-bin.000001', RELAY_LOG_POS=1, MASTER_HOST='dummy'; START SLAVE SQL_THREAD; </code></pre>
结果发现能成功重放，且重放过程中未出现任何报错。
测试了三次，重放时间分别为 362.74s、352.69s、361.75s，平均耗时 359.06 秒。
每次重放过程中，错误日志中都出现了多次锁等待超时错误。
<pre data-tool="mdnice编辑器"><span class="wx_imgbc_placeholder" data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg"><code>2025-09-21T07:53:45.257279-00:00 260 Slave SQL for channel '': Worker 5 failed executing transaction '9206ff59-2d95-4a02-88cf-04d97adfdd65:1286917678' at master log , end_log_pos 63251784; Could not execute Write_rows event on table biz_schema.tbl_product_service_mapping01; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log FIRST, end_log_pos 63251784, Error_code: MY-001205 </code></pre>
很显然，锁等待超时是并行重放导致的。
如果是单线程重放，就能规避这个问题，于是将<code>replica_parallel_workers</code>设置为 1，重新执行相同的测试，三次重放时间分别为 82.39s、83.40s、83.43s，平均仅 83.07 秒。
想不到，单线程重放竟然比多线程快了四倍多。
接下来，重点分析下锁等待超时问题。
<h1 data-tool="mdnice编辑器">为什么会出现锁等待？</h1>
以下是出现锁等待时，<code>sys.innodb_lock_waits</code>的输出：
<pre data-tool="mdnice编辑器"><span class="wx_imgbc_placeholder" data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg"><code>mysql> select * from sys.innodb_lock_waits\G *************************** 1.row ***************************                 wait_started: 2025-10-1213:11:29                     wait_age: 08:00:33                wait_age_secs: 28833                 locked_table: `biz_schema`.`tbl_product_service_mapping01`          locked_table_schema: biz_schema            locked_table_name: tbl_product_service_mapping01       locked_table_partition: NULL    locked_table_subpartition: NULL                 locked_index: tbl_product_service_pk                  locked_type: RECORD               waiting_trx_id: 5221288          waiting_trx_started: 2025-10-1213:11:22              waiting_trx_age: 08:00:40      waiting_trx_rows_locked: 35    waiting_trx_rows_modified: 34                  waiting_pid: 10                waiting_query: INSERT IGNORE INTO tbl_product ... (10512475, 1073743289)      ,               waiting_lock_id: 140256432120808:10011:67:240:263:140256317861168            waiting_lock_mode: X,GAP,INSERT_INTENTION              blocking_trx_id: 5221291                 blocking_pid: 14               blocking_query: INSERT IGNORE INTO tbl_product ...       (10512476, 1073743289)               blocking_lock_id: 140256432125848:9282:67:240:263:140256317891856           blocking_lock_mode: S,GAP         blocking_trx_started: 2025-10-1213:11:22             blocking_trx_age: 08:00:40     blocking_trx_rows_locked: 35   blocking_trx_rows_modified: 34      sql_kill_blocking_query: KILL QUERY 14 sql_kill_blocking_connection: KILL 14 *************************** 2.row ***************************                 wait_started: 2025-10-1213:11:29                     wait_age: 08:00:33                wait_age_secs: 28833                 locked_table: `biz_schema`.`tbl_product_service_mapping01`          locked_table_schema: biz_schema            locked_table_name: tbl_product_service_mapping01       locked_table_partition: NULL    locked_table_subpartition: NULL                 locked_index: tbl_product_service_pk                  locked_type: RECORD               waiting_trx_id: 5221291          waiting_trx_started: 2025-10-1213:11:22              waiting_trx_age: 08:00:40      waiting_trx_rows_locked: 35    waiting_trx_rows_modified: 34                  waiting_pid: 14                waiting_query: INSERT IGNORE INTO tbl_product ...       (10512476, 1073743289)                waiting_lock_id: 140256432125848:9282:67:240:260:140256317892560            waiting_lock_mode: X,GAP,INSERT_INTENTION              blocking_trx_id: 5221289                 blocking_pid: 12               blocking_query: NULL             blocking_lock_id: 140256432123832:9816:67:240:260:140256317879376           blocking_lock_mode: S,GAP         blocking_trx_started: 2025-10-1213:11:22             blocking_trx_age: 08:00:40     blocking_trx_rows_locked: 34   blocking_trx_rows_modified: 33      sql_kill_blocking_query: KILL QUERY 12 sql_kill_blocking_connection: KILL 12 2rowsinset (0.01 sec) </code></pre>
可以看出：
<ul class="list-paddingleft-1">
<li>PID 10 的 INSERT 操作被 PID 14 持有的 S,GAP 锁阻塞。</li>
<li>PID 14的 INSERT 操作又被 PID 12 持有的 S,GAP 锁阻塞。</li>
</ul>
使用<code>performance_schema.data_locks</code>可以获取更详细的锁信息，包括被锁定的数据行：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>SELECT   w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,   w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,   l1.LOCK_MODE AS waiting_lock_mode,   l1.LOCK_DATA AS waiting_lock_data,   l2.LOCK_MODE AS blocking_lock_mode,   l2.LOCK_DATA AS blocking_lock_data FROM performance_schema.data_lock_waits AS w JOIN performance_schema.data_locks AS l1 ON w.REQUESTING_ENGINE_LOCK_ID = l1.ENGINE_LOCK_ID JOIN performance_schema.data_locks AS l2 ON w.BLOCKING_ENGINE_LOCK_ID = l2.ENGINE_LOCK_ID; +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ | waiting_trx_id | blocking_trx_id | waiting_lock_mode      | waiting_lock_data        | blocking_lock_mode | blocking_lock_data       | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ |        5221288 |         5221291 | X,GAP,INSERT_INTENTION | 10512476, 1, 18158557178 | S,GAP              | 10512476, 1, 18158557178 | |        5221291 |         5221289 | X,GAP,INSERT_INTENTION | 10512477, 1, 18158557146 | S,GAP              | 10512477, 1, 18158557146 | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ 2 rows in set (0.00 sec) </code></pre>
接下来从<code>show processlist</code>的输出中看看 PID 10、14、12 这三个线程的状态。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>mysql> show processlist; +----+-----------------+-----------+-----------+---------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User            | Host      | db        | Command | Time    | State                                       | Info                                                                                                 | +----+-----------------+-----------+-----------+---------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+ |  5 | event_scheduler | localhost | NULL      | Daemon  |    1813 | Waiting on empty queue                      | NULL                                                                                                 | |  8 | root            | localhost | biz_schema | Query   |       0 | init                                        | show processlist                                                                                     | |  9 | system user     |           | NULL      | Query   |    1588 | Waiting for dependent transaction to commit | NULL                                                                                                 | | 10 | system user     |           | biz_schema | Query   | 3585949 | Applying batch of row changes (write)       | INSERT IGNORE INTO tbl_product_service_mapping01       (         c1,         c2 | | 11 | system user     |           | NULL      | Query   | 3585949 | Waiting for preceding transaction to commit | NULL                                                                                                 | | 12 | system user     |           | NULL      | Query   | 3585949 | Waiting for preceding transaction to commit | NULL                                                                                                 | | 13 | system user     |           | NULL      | Query   | 3585949 | Waiting for preceding transaction to commit | NULL                                                                                                 | | 14 | system user     |           | biz_schema | Query   | 3585949 | Applying batch of row changes (write)       | INSERT IGNORE INTO tbl_product_service_mapping01       (         c1,         c2 | | 15 | system user     |           | NULL      | Query   | 3585949 | Waiting for preceding transaction to commit | NULL                                                                                                 | | 16 | system user     |           | NULL      | Query   | 3585949 | Waiting for an event from Coordinator       | NULL                                                                                                 | | 17 | system user     |           | NULL      | Query   | 3585949 | Waiting for an event from Coordinator       | NULL                                                                                                 | +----+-----------------+-----------+-----------+---------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+ 11rowsinset, 1warning (0.00 sec) </code></pre>
PID 12 的执行用户是<code>system user</code>，说明它是并行重放的工作线程，其状态为<code>Waiting for preceding transaction to commit</code>，表示该线程正在等待它前面的事务提交完成。
而 PID 10 和 PID 14 的状态均为<code>Applying batch of row changes (write)</code>。从字面上看，似乎是在执行批量写入操作，但实际上，这两个线程正在等待锁。
如果执行的是<code>SHOW FULL PROCESSLIST</code>，<code>Info</code>列的<code>INSERT IGNORE</code>操作中还可以看到具体要插入的唯一索引值。借助这些唯一索引值，可以在 binlog 中精确定位对应的执行位置，便于分析事务执行顺序和锁等待情况。
不过，对于 PID 12，由于<code>Info</code>列为<code>NULL</code>，无法直接看到具体的 DML 操作，因此难以定位其执行内容。
为了解决这个问题，我在<code>Slave_worker::slave_worker_exec_event</code>函数中，在调用<code>ev->do_apply_event_worker(this)</code>的前后分别添加了日志打印。这样，就能清楚地看到每个工作线程正在执行的 event 的 binlog 位置点信息。
<pre data-tool="mdnice编辑器"><span class="wx_imgbc_placeholder" data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg"><code>int Slave_worker::slave_worker_exec_event(Log_event *ev) {   ...   ulong thread_id = thd->thread_id();   ulong log_pos = static_cast<ulong>(ev->common_header->log_pos); std::string msg = "Executing event: worker_thread_id=" +                   std::to_string(thread_id) +                   ", master_log_pos=" +                   std::to_string(log_pos);   LogErr(INFORMATION_LEVEL, ER_CONDITIONAL_DEBUG, msg.c_str());   ret = ev->do_apply_event_worker(this);   msg = "Done executing event: worker_thread_id=" +       std::to_string(thread_id) +       ", master_log_pos=" +       std::to_string(log_pos);   LogErr(INFORMATION_LEVEL, ER_CONDITIONAL_DEBUG, msg.c_str()); return ret; } </code></pre>
下面是锁等待发生时，PID 10、12、14 正在执行的 binlog event 位置点信息：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code># PID 10 grep 'worker_thread_id=10' /data/mysql/3306/data/mysqld.err | tail -1 2025-10-12T13:11:22.639120-00:00 10 Executing event: worker_thread_id=10, master_log_pos=63245428 # PID 12 grep 'worker_thread_id=12' /data/mysql/3306/data/mysqld.err | tail -1 2025-10-12T13:11:22.725638-00:00 12 Executing event: worker_thread_id=12, master_log_pos=63248672 # PID 14 grep 'worker_thread_id=14' /data/mysql/3306/data/mysqld.err | tail -1 2025-10-12T13:11:22.646870-00:00 14 Executing event: worker_thread_id=14, master_log_pos=63251784 </code></pre>
可以看到，PID 10 对应的事务在 binlog 中的位置早于 PID 12。
当参数<code>replica_preserve_commit_order</code>设置为 ON 时，从库必须严格按照主库的提交顺序依次提交事务，因此 PID 12 必须等待 PID 10 提交完成才能继续执行。
结合锁依赖关系，就形成了一个循环等待的局面：
<ul class="list-paddingleft-1">
<li>PID 10 等待 PID 14 持有的 S,GAP 锁；</li>
<li>PID 14 等待 PID 12 持有的 S,GAP 锁；</li>
<li>PID 12 因提交顺序限制，必须等待 PID 10 提交事务。</li>
</ul>
最终，这种环路导致三个线程相互阻塞，直到锁等待超时，MySQL 才会重新执行这些事务。
<h1 data-tool="mdnice编辑器">模拟从库的重放操作</h1>
根据获取到的 PID 10、12、14 对应的 event 位置点信息，我们可以还原出锁等待发生时这三个线程正在执行的具体操作：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>worker_thread_id=10 master_log_pos=63245428: insert c1 = 10512475 的所有记录，如(10512475,1),(10512475,20)... worker_thread_id=12 master_log_pos=63248672: insert c1 = 10512477 的所有记录，如(10512477,1),(10512477,20)... worker_thread_id=14 master_log_pos=63251784: insert c1 = 10512476 的所有记录，如(10512476,1),(10512476,20)... </code></pre>
这里的记录值对应的是表的联合唯一索引，其中<code>c1</code>是联合索引的第一列。
值得注意的是，在这些<code>INSERT</code>操作之前，binlog 中还存在针对相同<code>c1</code>值的<code>DELETE</code>操作：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>delete c1 = 10512475 的所有记录，如(10512475,1),(10512475,20)... delete c1 = 10512477 的所有记录，如(10512477,1),(10512477,20)... delete c1 = 10512476 的所有记录，如(10512476,1),(10512476,20)... </code></pre>
也就是说，业务实际上是通过 DELETE + INSERT 的方式实现数据更新。
为了进一步分析锁等待问题，我打印了重放过程中每个<code>INSERT</code>操作的具体内容。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>2025-10-12T13:11:22.639267-00:00 10 Inserted row: (18158557112, 10512475, 1, ...) 2025-10-12T13:11:22.640729-00:00 10 Inserted row: (18158557113, 10512475, 20, ...) 2025-10-12T13:11:22.642004-00:00 10 Inserted row: (18158557114, 10512475, 26, ...) 2025-10-12T13:11:22.643344-00:00 10 Inserted row: (18158557115, 10512475, 123, ...) 2025-10-12T13:11:22.644262-00:00 12 Inserted row: (18158557146, 10512477, 1, ...) 2025-10-12T13:11:22.644663-00:00 10 Inserted row: (18158557116, 10512475, 131, ...) 2025-10-12T13:11:22.646250-00:00 12 Inserted row: (18158557147, 10512477, 20, ...) 2025-10-12T13:11:22.647020-00:00 14 Inserted row: (18158557178, 10512476, 1, ...) 2025-10-12T13:11:22.647192-00:00 10 Inserted row: (18158557117, 10512475, 133, ...) </code></pre>
其中，第一个值是自增主键，后两个值是唯一索引列。
从输出可以看到，这三个事务的插入操作是交叉执行的。
<h3 data-tool="mdnice编辑器">模拟从库重放过程</h3>
下面通过一个实验来模拟从库的重放操作。
首先，在会话 1 中创建测试表并插入数据。
<pre data-tool="mdnice编辑器"><span class="wx_imgbc_placeholder" data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg"><code>session1> create table test.t1(id bigint auto_increment primary key,c1 int,c2 int,unique key(c1,c2)); Query OK, 0 rows affected (0.06 sec) session1> insert into test.t1(c1,c2) values(10512475, 1),(10512475, 2),(10512476, 1),(10512476, 2),(10512477, 1),(10512477, 2); Query OK, 6 rows affected (0.04 sec) Records: 6  Duplicates: 0  Warnings: 0 session1> select * from test.t1; +----+----------+------+ | id | c1       | c2   | +----+----------+------+ |  1 | 10512475 |    1 | |  2 | 10512475 |    2 | |  3 | 10512476 |    1 | |  4 | 10512476 |    2 | |  5 | 10512477 |    1 | |  6 | 10512477 |    2 | +----+----------+------+ 6 rows in set (0.00 sec) </code></pre>
其次，在会话 2 中针对另外一张表执行<code>FLUSH TABLES FOR EXPORT</code>操作，至于为什么要执行这个操作，后续加锁分析部分会解释。
<pre data-tool="mdnice编辑器"><span class="wx_imgbc_placeholder" data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg"><code>session2> flush tables test.t2 for export; Query OK, 0 rows affected (0.01 sec) </code></pre>
接着，在会话 1 中删除表中数据。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>session1> delete from test.t1; Query OK, 6 rows affected (0.02 sec) </code></pre>
接着分别创建三个新的会话，执行如下操作：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>session3> begin; Query OK, 0 rows affected (0.00 sec) session3> insert into test.t1(c1,c2,id) values(10512475,1,100); Query OK, 1 row affected (0.01 sec) session4> begin; Query OK, 0 rows affected (0.00 sec) session4> insert into test.t1(c1,c2,id) values(10512476,1,18158557178); Query OK, 1 row affected (0.00 sec) session5> begin; Query OK, 0 rows affected (0.00 sec) session5> insert into test.t1(c1,c2,id) values(10512477,1,18158557146); Query OK, 1 row affected (0.01 sec) </code></pre>
继续在会话 3 和会话 4 中插入数据。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>session3> set session innodb_lock_wait_timeout=5000; Query OK, 0 rows affected (0.00 sec) session3> insert into test.t1(c1,c2) values(10512475, 2); -- 阻塞中... session4> set session innodb_lock_wait_timeout=5000; Query OK, 0 rows affected (0.00 sec) session4> insert into test.t1(c1,c2) values(10512476, 2); -- 阻塞中... </code></pre>
接着在会话 2 中执行<code>UNLOCK TABLES</code>操作释放表锁。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>session2> unlock tables; Query OK, 0 rows affected (0.00 sec) </code></pre>
在会话 5 中查看锁等待信息。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>+----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ | waiting_trx_id | blocking_trx_id | waiting_lock_mode      | waiting_lock_data        | blocking_lock_mode | blocking_lock_data       | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ |          23228 |           23229 | X,GAP,INSERT_INTENTION | 10512477, 1, 18158557146 | S,GAP              | 10512477, 1, 18158557146 | |          23225 |           23228 | X,GAP,INSERT_INTENTION | 10512476, 1, 18158557178 | S,GAP              | 10512476, 1, 18158557178 | +----------------+-----------------+------------------------+--------------------------+--------------------+--------------------------+ 2 rows in set (0.01 sec) </code></pre>
可以看到，该结果与重放过程中出现锁等待时的输出完全一致。
<h1 data-tool="mdnice编辑器">加锁分析</h1>
接下来，我们重点分析一下：为什么在 RC（Read Committed）事务隔离级别下会产生 GAP 锁？
毕竟，在大多数人的印象中，RC 隔离级别下只会存在记录锁，而不会出现间隙锁。
事实上，这与 INSERT 操作之前执行的 DELETE 操作 有直接关系。
在前面的例子中，我们在 binlog 中发现，在执行 INSERT 操作之前，存在针对相同记录的 DELETE 操作。
在 MySQL 中，DELETE 操作并不会立即物理删除数据，而是将记录标记为“已删除”（delete-marked），等待后台的 purge 线程异步清理。这意味着在逻辑删除之后，这些记录仍然可能暂时保留在索引页中。
当随后执行 INSERT 操作时，如果待插入的记录在唯一索引上与某条“已标记删除但尚未清除”的记录键值相同，MySQL 会执行如下加锁行为：
<ul class="list-paddingleft-1">
<li>对该索引项加上 S 锁；</li>
<li>同时，对该索引项的间隙（即该记录与下一条记录之间的范围）加上 S,GAP 锁。</li>
</ul>
下面我们通过一个简化的实验来验证这一点。
<h3 data-tool="mdnice编辑器">实验验证</h3>
在前面的重放示例中，我们执行了<code>FLUSH TABLES FOR EXPORT</code>操作。
执行这个操作的目的，是为了暂停 purge 线程，从而保留 delete-marked 记录，便于重现这种锁行为。
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code># 会话 1：创建测试表并插入数据 session1> create table test.t1(id bigint auto_increment primary key,c1 int,c2 int,unique key(c1,c2)); Query OK, 0 rows affected (0.07 sec) session1> insert into test.t1(c1,c2) values(10512476, 1),(10512476, 2); Query OK, 2 rows affected (0.04 sec) Records: 2  Duplicates: 0  Warnings: 0 session1> select * from test.t1; +----+----------+------+ | id | c1       | c2   | +----+----------+------+ |  1 | 10512476 |    1 | |  2 | 10512476 |    2 | +----+----------+------+ 2 rows in set (0.00 sec) # 会话 2：暂停 purge 线程 session2> flush tables test.t2 for export; Query OK, 0 rows affected (0.03 sec) # 会话 1：删除数据 session1> delete from test.t1; Query OK, 2 rows affected (0.01 sec) # 会话 3：开启事务并插入数据 session3> begin; Query OK, 0 rows affected (0.00 sec) session3> insert into test.t1(c1,c2,id) values(10512476,1,18158557178); Query OK, 1 row affected (0.01 sec) # 查看锁信息 session3> select object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from  performance_schema.data_locks; +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data                | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | test          | t1          | NULL       | TABLE     | IX        | GRANTED     | NULL                     | | test          | t1          | c1         | RECORD    | S         | GRANTED     | 10512476, 1, 1           | | test          | t1          | c1         | RECORD    | S,GAP     | GRANTED     | 10512476, 2, 2           | | test          | t1          | c1         | RECORD    | S,GAP     | GRANTED     | 10512476, 1, 18158557178 | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ 4 rows in set (0.00 sec) </code></pre>
<h3 data-tool="mdnice编辑器">锁行为解释</h3>
在插入 <code>(10512476, 1, 18158557178)</code> 这条记录时：
<ul class="list-paddingleft-1">
<li>
由于 <code>(10512476, 1)</code> 仍存在于索引中（虽然被标记删除），MySQL 会对该记录加上 S 锁；
</li>
<li>
同时，对<code>(10512476, 1)</code> 的下一条记录 <code>(10512476, 2)</code> 加上 S,GAP 锁，防止该间隙范围内被其他事务插入新记录；
</li>
<li>
此外，插入的新记录 <code>(10512476, 1, 18158557178)</code> 还会继承下一条记录 <code>(10512476, 2)</code> 的 GAP 锁。
</li>
</ul>
其中：
<ul class="list-paddingleft-1">
<li>前两种锁的加锁逻辑是在<code>row_ins_scan_sec_index_for_duplicate()</code>中实现的；</li>
<li>锁继承的逻辑是在<code>lock_rec_add_to_queue()</code>中实现的。</li>
</ul>
当执行<code>UNLOCK TABLES</code>后，purge 线程恢复运行，会清理掉之前的 delete-marked 记录，对应的锁也会被释放。但可以看到，新插入记录自身的 GAP 锁仍然保留：
<pre data-tool="mdnice编辑器"><span data-cacheurl="" data-remoteid="" data-lazy-bgimg="https://mmbiz.qpic.cn/mmbiz_svg/BO1qQiajiacVlibSwibjXiaMnyugHDAJficjcBMzia3uz8EVxq3lQI4iaNZibkopGmumFpnRofyNhqm0IJDZ1DVn4GzXYnZRAsGqFuEKib/640?wx_fmt=svg&from=appmsg" data-fail="0"><code>session2> unlock tables; Query OK, 0 rows affected (0.05 sec) session3> select object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from  performance_schema.data_locks; +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data                | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ | test          | t1          | NULL       | TABLE     | IX        | GRANTED     | NULL                     | | test          | t1          | c1         | RECORD    | S,GAP     | GRANTED     | 10512476, 1, 18158557178 | +---------------+-------------+------------+-----------+-----------+-------------+--------------------------+ 2 rows in set (0.01 sec) </code></pre>
<h1 data-tool="mdnice编辑器">优化方案</h1>
针对上面分析的锁等待案例，优化主要可以从应用侧和数据库侧两方面入手。
<h3 data-tool="mdnice编辑器">1. 应用侧优化</h3>
应用层面的改进主要集中在索引设计与更新逻辑上：
<ul class="list-paddingleft-1">
<li>将原本的唯一索引改为普通二级索引。</li>
<li>将自增主键去掉，直接用原来的唯一索引列作为主键。与普通唯一索引不同，主键在插入时，即使遇到已经删除的记录，也不会额外加 S,GAP 锁。</li>
<li>优化更新逻辑。尽量避免通过<code>DELETE + INSERT</code>的方式更新数据，可以考虑使用<code>UPDATE</code>或者其他业务逻辑调整，以减少对间隙锁的触发。</li>
</ul>
<h3 data-tool="mdnice编辑器">2. 数据库侧优化</h3>
将<code>replica_preserve_commit_order</code>设置为 OFF，允许从库在遇到事务等待环路时，独立提交事务，而无需等待其他事务完成。
不过需要注意的是，如果使用的 Group Replication，会要求该参数必须为 ON。
下表展示了不同方案下的从库重放性能对比：
<table>
<thead>
<tr><th>方案</th><th>三次平均执行时间（秒）</th></tr>
</thead>
<tbody>
<tr>
<td>唯一索引 + replica_parallel_workers = 8</td>
<td>359.06</td>
</tr>
<tr>
<td>唯一索引 + replica_parallel_workers = 1</td>
<td>83.07</td>
</tr>
<tr>
<td>普通索引 + replica_parallel_workers = 8</td>
<td>33.50</td>
</tr>
<tr>
<td>唯一索引 + replica_parallel_workers = 8 + replica_preserve_commit_order = OFF</td>
<td>21.11</td>
</tr>
</tbody>
</table>
<h1 data-tool="mdnice编辑器">参考资料</h1>
<ol class="list-paddingleft-1">
<li>https://help.aliyun.com/zh/polardb/polardb-for-mysql/resolve-the-unique-key-check-problem-in-mysql</li>
<li>http://mysql.taobao.org/monthly/2015/06/02/</li>
<li>https://zhuanlan.zhihu.com/p/28797400192</li>
<li>https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html</li>
</ol> 
来源：https://www.cnblogs.com/ivictor/p/19152369

頁: [1]

琼殿技术论坛's Archiver

案例分析：MySQL 并行复制竟然比单线程慢？