关键词：
本文来源：原创投稿
* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
内容提纲
一、简介
二、Update 生命周期
2.1 连接器
2.2 分析器
2.3 优化器
2.4 执行器
2.4.1 事务执行阶段
2.4.2 事务提交阶段
2.4.3 假设事务 ROLLBACK
三、影响事务提交延迟的几种情况
四、Update 更新慢的排查思路
五、常见问题
一、简介
在开始排错之前我们需要知道 Update 在 MySQL 中的生命周期是什么，MySQL 如何执行一个事务的。
理解了如何执行，我们才知道如何去排查故障。
二、Update 生命周期
Server 层阶段
2.1 连接器
客户端发起一个 TCP 请求后，MySQL Server 端会负责通信协议处理、线程处理、账号认证、安全检查。
2.2 分析器
MySQL Server 端对一个 SQL 请求进行词法分析（识别 select、from），然后会对语法 进行分析判断语法是否正确。
2.3 优化器
优化器会分析 SQL 语句，选择合适的索引，根据预结果集判断是否使用全表扫描。
2.4 执行器
InnoDB 引擎层阶段
2.4.1 事务执行阶段
1） 请求进入 InnoDB 引擎后，首先判断该事务涉及到的数据页是否在 BP 中，不存在则会从磁盘中加载此事务涉及的数据页到 BP 缓冲池中，并对相应的索引数据页加锁
思考？
数据是如何从磁盘加载到 BP 中的?
BP 中的新老生代是如何交替及回收?
如何对相应数据加?
解答：
通过 B+Tree 读取到磁盘的索引页加载到 BP 缓冲池中。
1、通过 space id 和 page no 哈希计算之后把索引页加载到指定的 buffer pool instance 中。
2、判断 Free List 是否有空闲页可用（innodb_buffer_pool_pages_free、innodb_buffer_pool_wait_free），没有 则淘汰脏页或 LRU List 的 old。
3、将数据页加载到Free List 中，然后加载到 LRU List 的 old 区的 midpoint（头部）。
4、通过二分查找法，找该页对应的记录，试图给该事物涉及到的行记录加上排他锁。
(1) 判断该事物当前记录的行锁被其他事物占用的话，需要进入锁等待。
(2) 进入锁等待后，同时判断会不会由于自己的加入导致了死锁。
(3) 检测到没有锁等待和不会造成死锁后，行记录加上排他锁。
2） 将修改前的数据写入到 Undo 中，修改后将回滚针执行 Undo log 中修改前的行
思考？
为什么要写Undo 日志?
Undo 的存储方式是什么?
解答：
Undo log 一般是逻辑日志，记录每行记录。有两个作用：提供回滚和 MVCC。
事务因为某些原因需要回滚时，可以借助 Undo 日志进行回滚，保证事务的一致性 在事务的不同隔离级别需要通过Undo log 实现。
当读取某一行加锁的数据时，可以通过 Undo log 实现（比 如：RR 级别），事务不结束，Undo log 就不删除
Undo log 的存储方式是用段（segment）记录在表空间中。
InnoDB 存储引擎对 Undo 采用段方式管理，rollack segment 称为回滚段，每个回滚段有 1024 个 。
Undo log segment，5.6 之后可以通过 innodb_undo_logs 自定义多少个回滚段，默认 128 个。
Undo log 默认存储在共享表空间中，开启了 innodb_file_per_table 将存在独立表空间中。
3）写 redo log buffer 在 BP 中对数据进行修改操作，并将修改后的值写入到 redo log buffer 中等待异步 sync到磁盘
思考？
什么时候写入 redo log buffer？
commit 后 log buffer 如何落盘到 redo log？
日志刷盘规则是什么？
解答：
什么时候写入 redo log buffer。
1、先通过状态值 Innodb_log_waits 判断 redo log buffer 是否够用，不够用就等待。
2、在 BP 缓冲池的 LRU List 中 old 区的 midpont 中对改数据页的行记录的字段值做更新操作。
3、把修改之后的字段值写入到redo log buffer 中，并给 LSN 加上当前 redo log 写入的长度（写入长度为length 的 redo log，LSN 就会加上 length）。
4、因为 redo group commit，事务所产生的 redo log buffer 可能会和其他事务一同 flush 并且 sync 到磁盘上。
5、字段值在 BP 缓冲池更新成功后，对应的数据页就是脏页。
什么时候落盘到 redo log 中。
1、每次会将 log buffer 中的日志写入到 log file（这里指 os buffer），然后在调用系统的 fsync 操作进行落盘。
在 commit 之后，通过 innodb_flush_log_at_trx_commit 来决定什么时候将 log buffer 刷盘。
2、值为 1（默认为 1）：事务每次提交会写入 log buffer-->然后写入 os buffer--->调用系统 fsync 刷到 log file on disk。
3、值为 0：事务提交是先写入 log buffer-->每秒写入 os buffer 并调用 fsync 落盘（最多丢失 1s 数据）。
4、值为 2：每次提交只写入 os buffer，然后每秒调用 fsync()将 os buffer 的日志写入到 log file on disk（最多丢失 1s 数据）。
日志刷盘规则。
默认情况下事务每次提交会刷盘，是因为 innodb_flush_log_at_trx_commit 的值为 1。
这只是 InnoDB 在有 commit 动作后才会将日志刷盘，属于 InnoDB 存储引擎刷盘规则之一。
日志刷盘的几种规则 ：
1、发出 commit 动作之后。由 innodb_flush_log_at_trx_commit 控制。
2、每秒刷一次，刷新频率由 innodb_flush_log_at_timeout 值决定，默认为 1，刷日志频率与 commit 动作无关。
3、当 log buffer 中使用内存超过一半。
4、当有 checkpoint 时，checkpoint（数据页刷盘）在一定程度上代表刷盘时日志所处的 LSN 位置。
Checkpoint 刷盘规则。
InnoDB 中，数据刷盘的规则只有一个：checkpoint，触发 checkpoint 后，会将 BP 中脏数据和脏日志页都刷新到磁盘。
Checkpoint 分为两种：
Sharp checkpoint：在重用 redo log 文件时（切割日志），将所有记录到 redo log 中对应的脏数据刷新到磁盘。
Fuzzy checkpoint：一次刷新一小部分日志到磁盘，并非所有脏日志。
1、master thread checkpoint：master 线程每秒或每 10 秒刷一定比例脏页到磁盘。
2、Flush_lru_list checkpoint：5.6 之后通过 innodb_page_cleaners 变量指定 page cleaner 线程个数。
3、Async/sync/ flush checkpoint，同步刷盘还是异步刷盘。
4、Dirty page too much checkpoint ：脏页怠惰强制触发检查点，保证缓存空间空闲，由变量innodb_max_dirty_pages_pct 控制。
4）写 binlog cache
同时将修改的信息按照 event 格式记录到 binlog_cache 中，等待落盘。
如果 binlog cache 不够用时，会写入到 binlog 临时文件。
思考？
事务 binlog event 的写入流程是什么？
解答：
一旦有事务提交，binlog cache 和 binlog 临时文件都会释放（已经写入 binlog file） 同一事务包含多个 DML 会共用同一个 binlog cache 和 binlog 临时文件。
1、事务开启。
2、执行 dml 语句，dml 语句第一次执行时会分配 binlog cache。
3、执行 dml 语句期间生成的 event 不断写入 binlog cache。
4、binlog cache 满了事务还没执行完，会将 binlog cache 中的数据写入到 binlog 临时文件同时清空 binlog cache，临时文件大小大于 max_binlog_cache_size 则报 error 1197。
5、事务提交，整个 binlog cache 和 binlog 临时文件数据全部写入 binlog file，释放 binlog cache（IO_CACHE） 和 binlog 临时文件 binlog 临时文件大小为 0，保留文件描述符。
6、断开连接，释放 IO_CACHE。
5）写 change buffer
如果这个事务需要在二级索引上做修改，写入到 change buffer page 中，等待之后，事务需要读取该二级索引时进行 merge。
思考？
什么时候会用到 change buffer？
为什么仅适用于普通索引页？
哪些场景会触发刷新 change buffer？
什么业务不适合/适合开启 change buffer？
change buffer 相关参数有哪些？
解答：
什么时候会用到 change buffer。
MySQL 5.5 之前叫 insert buffer，只针对 insert，之后叫 change buffer 对 delete 和 Update 也有效。
在对普通索引数据页不在 BP 中，对页进行写操作，不会将磁盘数据加载到缓冲池中，仅仅记录缓冲变更（可以理解为只记录操作变更，不做真实数据操作）。
等待数据被读取时，将数据 merge 到 BP 中，目的是降低写操作磁盘 IO，提高性能。
为什么仅适用于普通索引页。
唯一索引或主键索引每次修改操作时，InnoDB 必须进行唯一性检查。
即使索引页不在缓冲池，也会去读取磁盘页，一次随机 IO（通过 B+tree 查找数据页），一次顺序 IO（写 redo log）避免不了。
那些场景会触发刷新 change buffer。
1、数据页被访问。
2、master thread 每隔 10s 会进行操作。
3、数据库 BP 不够用时。
4、数据库正常关闭时。
5、redo log 写满时（几乎不会出现，redo log 被写满数据库处于无法写入状态）。
什么业务不适合/适合开启 change buffer。
不适合：
1、数据库都是唯一索引。
2、写入一个数据后，立刻读取上述场景，在写操作之后，本来就要进入 BP 中，此时 change buffer 反而成了累赘。
适合：
1、数据库大部分是非唯一索引。
2、业务是写多读少，或者写后不是立刻读。
3、读写分离下主库可以使用。
可以使用 change buffer，减少一次随机 IO，优化定期批量写磁盘。
change buffer 相关参数有哪些。
show global variables like '%innodb_change_buffer%';
innodb_change_buffer_max_size；
配置写缓冲的大小，占整个缓冲池的比例，默认值是 25%，最大值是 50%（写多读少才需调大，读多写少 25%就够）。
innodb_change_buffering;
配置那些写操作启用写缓冲，可以设置成 all/none/inserts/deletes 等。
2.4.2 事务提交阶段
打开 binlog 选项之后，执行事务提交会进入二阶段提交模式（prepare 阶段和 commit 阶段。
两阶段涉及两个参数（sync_binlog和innodb_flush_log_at_trx_commit）。
1）事务提交分为 prepare 阶段与 commit 阶段（两阶段提交）
事务的 commit 操作在存储引擎和 server 层采用内部 XA。
两阶段提交协议保证事务的一致性，主要保证 redo log 和 binlog 的原子性。
2）Redo log prepare
写入 redo log 处于 prepare 阶段，并且写入事务的 xid。
将redo log buffer刷新到redo log磁盘文件中，用于崩溃恢复。
刷盘的方式由innodb_flush_log_at_trx_commit 决定。
3）Binlog write&fync: 执行器把 binlog cache 里的完整事务和 redo log prepare 中的 xid 写入到 binlog 中
Dump 线程会从 binlog cache 里把 event 主动发送给 slave 的 I/O 线程，同时执行 fsync 刷盘（大事务的话比 较耗时）并清空 binlog cache。
Binlog 刷盘的方式由 sync_binlog 决定。binlog 写入完成，事务就算成功。
总结：
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写入到 binlog file 中。
当 sync_binlog 为 1 时，binlog 落盘后才会通知 dump thread 进行主从复制。
4）Redo log commit commit
提交阶段中，该事务产生的 redo log 已经 sync 到磁盘中，在 redo log 里标记 commit，说明事务提交成功。
5）事务提交，释放行记录持有的排它锁
6）Binlog 和 redo log 落盘后触发刷新脏页操作
先把该脏页复制到 doublewrite buffer 里，再把 dobulewrite buffer 里的数据，刷新到共享表空间(ibdata)，然后脏页刷新到磁盘中，此时内存页和数据页一致。
思考？
BP 中的脏页刷盘机制是什么？
解答：
当 InnoDB 中脏页比例超过 innodh_max_dirty_pages_pct_lwm 的值时，开始刷盘。
2.4.3 假设事务 ROLLBACK
因为系统异常或显示回滚，所有数据变更会变成原来的，通过回滚日志中数据进行恢复。
对于 in-place（原地）更新，将数据回滚到最老版本。
对于 delete+insert 方式，标记删除的记录清理删除标记，同时把插入的聚集索引和二级索引记录也会删除。
三、影响事务提交延迟的几种情况
在事务执行阶段：
1、锁等待
1、RR 模式下 insert 锁等待 gap lock 锁等待导致。
2、Insert 等待 MDL 锁导致 。
3、Table lock。
2、IO 方面
1、慢 sql 导致 io 高。
2、其他程序占用比价高。
3、BP 命中率比较低。
4、并发导致。
5、innodb buffer pool 不够用。
6、Update、delete 更新数据行数大（>W）。
3、Buffer 方面
1、redo log buffer 是否够用通过 Innodb_log_waits 确认。
2、Redo log buffer 刷盘方式通过 innodb_flush_log_at_trx_commit。
3、Binlog cache 是否够用，创建临时文件、消耗 IO。
4、Change buffer 是否够用。
4、落盘延迟
1、sync_binlog 参数。
2、binlog_group_commit_sync_delay 参数。
3、innodb_flush_commit 参数。
4、查看 innodb_buffer_pool 的命中率,查看脏页刷新频率效果。
四、Update 更新慢的排查思路
排查思路:
1、查看当时实例系统性能情况（IO、CPU、memory），排除系统性能干扰
如果 CPU 高、IO 高、wa 大：
先排查慢 SQL，再查当前并发数，一般是大量并发慢 SQL 导致。
如果 CPU 高、IO 中、wa 小：
排查慢 SQL，在查看当前并发数，一般是单个计算 SQL 导致。
如果 CPU 低、IO 高、wa 低：
排查当前占用 io 高的线程，有可能是 page clean 导致或日志刷新频繁导致。
2、检查 MySQL状态
查看 mysql porcesslist，查看当前是否有 wait lock（表锁，行锁，meata lock 等）。
查看 mysql processlist，是否有大量 send data、init、commit、clean up 状态。
查看 mysql processlist，计算并发，排查是否有并发压力。
查看 innodb buffer pool 命中率，排查 buffer 是否够用。
查看 mysql tmp，是否够用，open tables 是否等于 table_open_cache。
3、分析 SQL 语句
通过 explain 分析 SQL 的执行情况，是否走索引，是否存在 union。
通过 explain 分析 SQL 的执行情况，是否存在大表驱动小表，多表 join。
检查 SQL 是否存在产生额外临时表。
使用 profile 分析单条 SQL 语句。
4、分析应用程序执行 SQL 慢的时间
观察是单个 SQL 执行慢，还是所有语句都慢。
慢的 SQL 的时间是否有规律，有助于排查 MysSQL 的相关参数。
5、抓包及 strace 分析
使用 tcpdump 进行抓包，分析是 MySQL 返回慢，还是网络慢。
使用 strace 分析 MySQL 内部哪里慢，哪个函数导致的。
五、常见问题
1、Update 全表更新一个字段，数据量为 10w，更新特别慢。
2、Update 引起死锁问题。
3、Update 几百条数据消耗了 10s。
4、Update 同一个表，有些更新快，有些更新慢。
Update的问题还不止于此，通过阅读本篇文章，相信您对如何发现、排查、解决Update可能引发的问题，有了更进一步的认识。
在本小节中，列举了部分常见的案例，希望对您学习有所帮助。此外，也推荐您自己动手搭建测试环境，发现新的问题。
Enjoy GreatSQL :)
文章推荐：
扫码加入GreatSQL/MGR交流QQ群
点击文末“阅读原文”直达老叶专栏
使用windbg分析多线程临界区死锁问题分享(代码片段)
目录1、多线程死锁场景及多线程锁的类型1.1、发生死锁的场景说明1.2、锁的类型2、问题实例说明3、使用Windbg初步分析4、进一步分析死锁4.1、使用!locks命令查看临界区对象信息4.2、通过占用临界区锁的线程id找到目标线程4.3、如... 查看详情
rpc服务超时排查思路
...栈，有没有hang住线程的请求，有没有阻塞等待，有没有死锁等- 6 查看详情
一篇文章搞清jvm死锁问题及排查
关于死锁，一直是面试和日常开发中的熟悉话题，本文将进行一下探讨：什么是死锁出现死锁的原因如何避免死锁代码中死锁问题怎么排查@目录1.什么是死锁2.出现死锁的原因3.如何预防和避免死锁4.实战JVM死锁问题排查4.1死锁代... 查看详情
解决一次mysql死锁问题
参考技术A多线程开启事务处理。每个事务有多个update操作和一个insert操作（都在同一张表）。默认隔离级别：RepeatableRead只有hotel_id=2和hotel_id=11111的数据逻辑删除原有数据插入新的数据根据现有数据情况，update的时候没有数据被... 查看详情
记录一次mysql死锁排查过程(代码片段)
背景以前接触到的数据库死锁，都是批量更新时加锁顺序不一致而导致的死锁，但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终... 查看详情
记录一次mysql死锁排查过程(代码片段)
背景以前接触到的数据库死锁，都是批量更新时加锁顺序不一致而导致的死锁，但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终... 查看详情
20210519使用jstack命令排查线程死锁问题(代码片段)
问题: 针对线上多线程死锁、阻塞，跑着跑着就卡住了；查看线上线程池的状态；jstack主要用于生成java虚拟机当前时刻的线程快照。线程快照是当前java虚拟机内每一条线程正在执行的方法堆栈的集合，生成线程... 查看详情
flinkonyarn（下）：常见问题与排查思路(代码片段)
...FlinkonYARN应用解读系列文章，分为上、下两篇。上篇分享了基于FLIP-6重构后的资源调度模型介绍 FlinkonYARN应用启动全流程，本文将根据社区大群反馈，解答客户端和FlinkCluster的常见问题，分享相关问题的排查思路... 查看详情
使用windbg分析多线程临界区死锁问题分享(代码片段)
目录1、多线程死锁场景及多线程锁的类型1.1、发生死锁的场景说明1.2、锁的类型2、问题实例说明3、使用Windbg初步分析4、进一步分析死锁4.1、使用!locks命令查看临界区对象信息4.2、通过占用临界区锁的线程id找到目标线程4.3、如... 查看详情
网站打开慢如何排查？
...用户个例，还是全部自己把自己当做用户去测试。如果没问题，就从客户的角度去思考，如果自己测试同样有问题，按照下面思路排查。1.路是否通的问题a.pingwww.baidu.com高速路有没有修好如果ping是通的，不丢包。http服务器的问... 查看详情
性能测试分析实践分享！
参考技术A对于压力测试结果的分析没有一个系统的思路，在压力测试结果不符合性能指标时无从下手，也无法向开发提出有效的优化性能的方法。在对多个项目分析后，总结出一个通用的分析思路，可以快速定位性能瓶颈。整... 查看详情
同时执行UPDATE时出现死锁
...并遇到死锁错误。该场景是由10个不同的用户同时插入和更新数据库。我上网查了，还是没找到解决的办法。这里附上我涉及死锁的示例代码。谁能给我一些解决死锁的建议？提前谢谢你。SampleController:onSubmit 查看详情
java死锁排查和javacpu100%排查的步骤整理(转)
...其器简介本篇整理两个排查问题的简单技巧，一个是java死锁排查，这个一般在面试的时会问到，如果没有写多线程的话，实际中遇到的机会不多；第二个是javacpu100%排查，这个实际的开发中，线的应用出现这个问题可能性比较大... 查看详情
让bug无处藏身，java线上问题排查思路常用工具
...总结了一些常见的线上应急现象和对应排查步骤和工具。分享的主要目的是想让对线上问题接触少的同学有个预先认知，免得在遇到实际问题时手忙脚乱。只不过这里先提示一下。在线上应急过程中要记住，只有一个总体目标：... 查看详情
oracle数据库用update语句更新无效。
...要具体排查，当然最直接的是删除表空间，重新建立参考技术A你这问题也太。。。。内容呢？sql语句呢？错误信息呢？如果按你说的无效的话，可能是你没有commit，所以导致你更新以后没有提交到数据库中。你再次查询的时候... 查看详情
ethtool原理介绍和解决网卡丢包排查思路
参考技术A之前记录过处理因为LVS网卡流量负载过高导致软中断发生丢包的问题，RPS和RFS网卡多队列性能调优实践，对一般人来说压力不大的情况下其实碰见的概率并不高。这次想分享的话题是比较常见服务器网卡丢包现象排查... 查看详情
mysql死锁排查
参考技术A一、showENGINEINNODBstatus查看死锁位置，分析。二、首先解决死锁可以从死锁发生的条件入手，最容易解决的就是更改获取资源的顺序；其次是避免长事务，让事务执行的时间尽可能少，让事务的覆盖范围尽可能小，长事... 查看详情
UPDATE 上的 SQL Server 死锁
...程序出现死锁问题。数据库是SQLServer2005，当2个线程尝试更新同一个表时会发生死锁。我不明白这种情况，我希望有人能帮助我。这是死锁图：<deadlock-list><deadlockvictim="process3a0ac58"> 查看详情