MySQL 案例：无主键表产生的延迟

前言

在 MySQL 的主从架构在很多场景下都在使用，同时 MySQL 的同步延迟也是很多 DBA、运维、开发的同学经常面对的问题之一。本文围绕同步延迟的场景之一：无主键表，来看看延迟产生的原因，以及应对的策略。当然，从标题上也能看出来，给表建个主键是最好的办法，不过在关于这个问题，其实还有一些其他的方式可以尝试。

原理简介

MySQL 的同步原理可以参考下图：

同步简图

简而言之，在主库上的数据变化记录在 binlog 中之后通过网络传到从库并记录在 relaylog 中，之后再由 sql 线程在从库上“再执行一遍”。

当数据库（绝大多数公有云产品）使用 row 模式的时候，binlog 会记录所有的数据变更，这也意味着一个 update 或者 delete 语句如果修改了非常多的数据，那么每一行数据的变化都会记录到 binlog 中，最终会产生非常多的 binlog 日志。从库在处理这些日志时，每一行数据的变化都会去尝试定位具体的数据，然后再判断是不是需要执行操作来完成数据变更。

那么可以想象得到，如果在某张大表上 update 或者 delete 一些数据，而这张表没有索引，那么定位数据的时候就会变成全表扫描，且 update 或者 delete 的每一行数据都会触发一次全表扫描，从库会产生非常大的延迟。

显然，在从库上临时先加点索引是一个很好的办法，那么除了索引以外，还有什么其他的办法吗？

一个 MySQL 的参数

MySQL 在这类场景下，有一个专门的参数来调整从库定位数据的方法：slave_rows_search_algorithms

参考官方文档的参数设置表：

可以看到设置了 HASH SCAN 之后，非主键和唯一索引的情况下，会使用 Hash scan 算法来定位数据，而且这个参数变更之后是即时生效的，那么看起来遇到类似问题的时候，可以通过调整这个参数来尝试解决延迟的问题？

测试一下

本次测试环境使用腾讯云数据库 MySQL，配置为 4 核 8GB 内存。测试数据使用 sysbench 生成，单表 2000 万行数据，且没有主键和唯一索引。例如：delete from sbtest1 where k > 10090000。

测试时修改了 binlog_row_image 的值为 FULL，因为腾讯云数据库 MySQL 默认设置为 MINIMAL 来节省磁盘空间，但是这个参数在一定程度上会影响 slave_rows_search_algorithms 的效果，具体的细节参考特殊情况部分。

PS：这个参数是可以动态修改的，所以调整起来没有什么额外的成本。

测试项目为 delete 语句，影响的行数为约 340 万行，非连续的行（并非按照自增主键范围来删除），可以当做是没有什么规律，随机删除的数据。模拟的场景和结果如下：

• 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 sbtest11 where k 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中看到同步的速度，确认修改参数之后是否有提升。

考虑到腾讯云数据库 MySQL 的默认设置，Hash Scan 对于“粗心”的用户是相对好一些的选择。

如果自建库使用了 FULL（原生 MySQL 的默认值） 的话，用 Table Scan 在大多数时候没什么问题。