文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html 记一次MySQL 主从延迟分析处理过程，聊聊我的思路。
某业务数据库每次跑批都会出现从库延迟告警的情况，本文总结了这次从库延迟排查的全过程，并分析了导致从库延迟的原因及解决方案。 MySQL 的主从架构在很多场景下都在使用，同时 MySQL 的同步延迟也是很多 DBA、运维、开发的同学经常面对的问题之一。本文围绕同步延迟的场景之一：无主键表，来看看延迟产生的原因，以及应对的策略。 当然，最简单的办法是给表建个主键，不过在关于这个问题，其实还有一些其他的方式可以尝试。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html MySQL 的主从同步原理图： 简而言之，在主库上的数据变化记录在 binlog 中之后通过网络传到从库并记录在 relaylog 中，之后再由 sql 线程在从库上“再执行一遍”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html 当数据库使用 row 模式的时候，binlog 会记录所有的数据变更，这也意味着一个 update 或者 delete 语句如果修改了非常多的数据，那么每一行数据的变化都会记录到 binlog 中，最终会产生非常多的 binlog 日志。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html 从库在处理这些日志时，每一行数据的变化都会去尝试定位具体的数据，然后再判断是不是需要执行操作来完成数据变更。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html
那么可以想象得到，如果在某张大表上 update 或者 delete 一些数据，而这张表没有索引，那么定位数据的时候就会变成全表扫描，且 update 或者 delete 的每一行数据都会触发一次全表扫描，从库会产生非常大的延迟。 显然，在从库上临时先加点索引是一个很好的办法，那么除了索引以外，还有什么其他的办法吗？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html MySQL 在这类场景下，有一个专门的参数来调整从库定位数据的方法： slave_rows_search_algorithms 可以看到设置了 HASH SCAN 之后，非主键和唯一索引的情况下，会使用 Hash scan 算法来定位数据，而且这个参数变更之后是即时生效的，那么看起来遇到类似问题的时候，可以通过调整这个参数来尝试解决延迟的问题？ 本次测试环境使用云数据库 MySQL，配置为 4 核 8GB 内存。测试数据使用 sysbench 生成，单表 2000 万行数据，且没有主键和唯一索引。 测试时修改了 binlog_row_image 的值为 FULL。这个参数是可以动态修改的，所以调整起来没有什么额外的成本。 测试项目为 delete 语句，影响的行数为约 300 万行，随机删除数据。 where 条件无索引场景1：表没有其他索引。 场景2：表有其他优质索引（数据区分度高）。 where 条件有索引场景3：表没有其他的索引。 场景4：表有其他的优质索引（数据区分度高）。 图例中 1-Table 代表场景1下，Table_Scan，Index_Scan 设置下的延迟时间，1-Hash 代表场景 1 下，Hash_Scan，Index_Scan 设置下的延迟时间。 场景 1 下，完全没有索引的时候延迟时间超过 24 小时，完全没有对比意义，因此取值为 -1。 在模拟的随机 delete 大量数据的场景下，Hash Scan 并没有能减少延迟时间，反而增加了 50%~800% 当从库可以利用索引时，如果索引的区分度较高，那么延迟的时间会明显减少，减少幅度为 75%~95% 对比场景 2 和场景 3 的情况当从库有多个索引，且 where 条件本身有索引的时候，会直接使用 where 条件的索引，如果 where 条件没有索引则会用到其他的索引。 看起来 Hash Scan 似乎是成了负优化的样子，随机修改数据的场景使用 Table Scan 会稍微好一些。 那么 Hash Scan 在哪些场景能发挥作用呢？考虑到 Hash 算法的特点，调整一下测试项目，以较低区分度的列为准，挑选出重复值比较多的 top10 的值进行删除，总共约 1400 行，例如： delete from test where a in (9951634 ,10010874 ,10031037 ,10041605 ,9999038 ,10045918 ,10047948 ,10000971 ,10045729 ,9956622 )那么再来测试一下场景1，这次也加上 update 的测试项目，where 条件与 delete 保持一致。 可以很明显的看出来，当完全无索引，且修改重复行数较多的数据时，Hash Scan 的效果要好很多，且随着受影响行数变多，两种参数设置的情况下，延迟时间都会增长，Hash Scan 的效果会相对越明显。 特殊情况 关于 binlog_row_image 这个参数，FULL 和 MINIMAL 的差别在于 MINIMAL 记录的是主键信息和 where 条件列的内容，但是 FULL 会记录表中所有列的内容。 而 slave_rows_search_algorithms 会按照主键->唯一索引->辅助索引 的顺序来依次尝试，因此在场景2（where 条件无索引，表有其他优质索引）的时候，FULL 的情况下会自动利用其他的索引，而 MINIMAL 没有记录其他列的信息，只能用全表扫描。 五、总结 确保每个表都有主键是最好的解决办法，如果确实有客观原因，那至少保证 where 条件全部能利用到索引。 当问题已经发生了，可以根据实际出问题的 SQL 语句，把参数改为 INDEX_SCAN,HASH_SCAN 来减少延迟时间。可以在information_schema.innodb_trx中看到同步的速度，确认修改参数之后是否有提升。 如果使用了 FULL 的话，用 Table Scan 在大多数时候没什么问题。 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html 
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/60394.html