找出MySQL延迟数千秒的罪魁祸首Bottlenecks，从数据库层面来说，可能是SQL效率低下或大批量写入造成的。本文案例将分析一个由binlog格式引起的延迟问题。看完本文，遇到这样的告警，相信你能瞬间定位到问题所在！Part2:关键参数分析binlog_format属性值命令行格式--binlog-format=format系统变量binlog_formatScopeGlobal,SessionDynamic是Type(>=5.5.31-ndb-7.2.13)enumerationType(>=5.5.15-ndb-7.2.1,<=5.5.30-ndb-7.2.12)enumerationTypeenumeration默认值(>=5.5.31-ndb-7.2.13)MIXED默认值(>=5.5.15-ndb-7.2.1,<=5.5.30-ndb-7.2.12)STATEMENT默认STATEMENT有效值(>=5.5.31-ndb-7.2.13)ROWSTATEMENTMIXEDValidValues(>=5.5.15-ndb-7.2.1,<=5.5.30-ndb-7.2.12)ROWSTATEMENTMIXEDValidValuesROWSTATEMENTMIXED众所周知，binlog_format是一个参数设置binlog格式，我们可以配置成三种格式：STATEMENT、MIXED、ROW，三种格式都可以动态调整。我们的线上制作库统一配置为MIXED格式。MIXED格式会根据不同的场景使用不同的格式在STATEMENT格式和ROW格式之间切换。mysql>showglobalvariableslike'binlog_format';+------------+--------+|Variable_name|Value|+---------------+--------+|binlog_format|MIXED|+----------------+--------+1rowinset(0.08sec)Part3:知识Reserve对于MIXED格式，在以下情况下binlog会自动转为ROW格式记录：1.NDB引擎2.SQL语句中包含UUID()函数。3、自我成长领域更新。4.包含插入延迟语句。5.使用用户定义函数(UDF)。6.使用临时表。7.?还有一种情况会导致混合格式转为ROW，本文将转载。实战Part1：监控我们可以看到，凌晨2:00，从库的延迟急剧增加，但是此时从库的机器负载和网卡还没有达到瓶颈。Part2：延迟原因分析我们可以看到，从2:06开始，binlog的刷新速度非常快，一个1.1GB的binlog文件，几十秒就可以填满。这样基本可以确定是书写量过大造成的。写量过大有两种情况：简单的业务量增加是因为QPS增加导致的；binlog转为ROW格式导致存储内容激增。我们可以使用pt工具pt-query-digest或者命令行来分析binlog做了哪些操作。如果使用pt-query-digest，可以结合mysqlbinlog命令来分析日志。第3部分：rootcasedeletefromtablenamewherexxxxlimit100；这种语法会将MIXED格式的binlog转为ROW格式的记录，而笔者案例中的这张表包含了一个很大的TEXT字段，每次删除都会将整个很大的TEXT字段带入binlog，从而导致binlog激增，当从库赶不上主库，有延迟的时候。Part4：解决方案找到根本原因后，解决起来就很容易了。找到相关开发去掉deletefromtablewherexxxlimit的用法，避免记录成行格式。Warning：警告其实delete/updatelimit,insert.....selectlimit的用法很危险，容易出问题。如果真的要使用这种方式，还需要结合orderby语句来保证limit的有效性。当遇到这样的语句时：当使用STATEMENT模式时，会发出警告，指出该语句对于基于语句的复制是不安全的。使用STATEMENT模式时包含LIMIT的DML语句会发出警告，即使它们也有ORDERBY子句（因此是确定性的）。这是一个已知的问题。(BUG#42851)使用MIXED模式时，使用行的模式复制语句。Part5:官方文档当以MIXED日志记录格式运行时，服务器会在以下情况下自动从基于语句的日志记录切换到基于行的日志记录：当DML语句更新NDBCLUSTER表时。当函数包含UUID()时。当一个或多个更新具有AUTO_INCREMENT列的表并调用触发器或存储函数。与所有其他不安全的语句一样，如果binlog_format=STATEMENT，这会生成警告。当执行任何INSERTDELAYED时。当涉及对UDF的调用时。如果语句按行记录并且执行该语句的会话有任何临时表，按行记录用于所有后续语句（访问临时表的语句除外），直到该会话使用的所有临时表都被删除。无论是否实际记录了任何临时表，都是如此。临时表不能使用行记录基于格式；因此，一旦使用基于行的日志记录，所有后续语句都使用g那张桌子不安全。服务器通过将会话期间执行的所有语句视为不安全的来近似这种情况，直到会话不再包含任何临时表。当使用FOUND_ROWS()或ROW_COUNT()时。（错误#12092，错误#302）使用USER()、CURRENT_USER()或CURRENT_USER时。（缺陷#28086）当一条语句引用一个或多个系统变量时。(Bug#31168)可以看到在官方文档中，当MIXED格式会在转换为ROW格式时，并没有提到limit语句会将MIXED格式转换为ROW，在很多国内的书籍和博客。本文记录这个案例，希望能解决这个问题，以后可能会遇到。问题读者可以节省处理时间，尽快定位问题根源官方文档在使用limit语法时将MIXED格式转换为ROW格式，在其他章节中描述如下：Statement-basedreplicationofLIMITclausesinDELETE,UPDATE,andINSERT...SELECTstatementsisunsafesincetheorder未定义受影响的行。（只有当它们还包含ORDERBY子句时，才能使用基于语句的复制正确复制此类语句。）遇到这样的语句时：使用STATEMENT模式时，现在会警告该语句对于基于语句的复制不安全发布。使用STATEMENT模式时，即使包含LIMIT的DML语句也具有ORDERBY子句（因此具有确定性），也会发出警告。这是一个已知的问题。（缺陷号42851）当使用MIXED模式时，语句现在使用基于行的模式自动复制。总结通过这个案例，我们可以了解到binlog_format在什么情况下会从MIXED格式转换为ROW格式，以及常见的延迟原因和解决方法。由于作者水平有限，写作时间仓促，文章中难免存在一些错误或不准确的地方。不当之处敬请广大读者批评指正。
