MySQL8.0 没有主键不单只从库复制延迟，还可能会导致复制报错 ...

背景

昨天我研读了源码大佬八怪的新文章《MySQL:主从 HASH SCAN 算法可能导致从库数据错误》，我将其内容概括如下：如果主从复制的行搜索算法设置为使用 HASH SCAN，从库数据可能会与主库不同，导致从库直接抛出复制异常。

结合文章细节，我再补充一些理解:

1. 这个问题只在 MySQL8.0 中出现，不在 5.7 中发生，因为两者的代码实现有所不同。
2. 数据不一致时会导致从库复制报错，而不是复制正常，所以我们能及时发现问题，避免数据最终不一致导致数据丢失或数据错乱。
3. 腾讯 TXSQL 团队在 MySQL8.0.22 版本就发现了这个问题，并向官方提供了补丁，但官方未采纳，最新 MySQL 版本下 Bug 可能仍然存在。
4. 遇到此 Bug 需要使用 HASH SCAN 算法搜索行记录，并且至少需要两行记录的 crc32 值相同（即哈希冲突）。并且运气不佳，从库更新/删除的是另一行 crc32 值相同的记录。
5. 八怪表示他遇到过很多次这种情况，他的解决办法是给表添加主键然后重做从库。

分析

MySQL 在使用 row 格式进行主从复制时，默认情况下，从库重放事务时会按行搜寻主键索引，如果没有主键索引，那么会使用唯一索引来定位行。但是，如果表中没有主键或唯一索引，就会根据 slave_rows_search_algorithms(在 MySQL8.0 中，默认值是 INDEX_SCAN,HASH_SCAN)设定的算法选择定位具体行的方法。INDEX_SCAN 表示全索引扫描，而 HASH_SCAN 表示 HASH 扫描。优先使用 INDEX_SCAN，然后使用 HASH_SCAN。如果这两种算法都无法匹配，就会执行 TABLE_SCAN 算法，全表扫描，这可能导致复制严重延迟。我在以前写的《MySQL 备库复制延迟的原因及解决办法》一文的第六点有详细介绍。

所以，这个 bug 在 8.0 版本中的出现场景是：表上没有任何索引(包括主键、唯一索引、普通索引)，而且表的数据量越大，出现这个问题的可能性就越大。

我推测，官方一直没有修复这个 bug 的原因可能是：

1. 这个 Bug 会导致从库复制异常，但不会导致数据不一致而复制仍然正常。我们会有感知，所以他不会导致数据不一致。(潜台词: Bug 不够严重)
2. 官方建议表必须有主键。(潜台词: 没有必要为那些违反开发规范的开发者埋单)

因此，我认为最佳的解决方案是在我的文章《8.0.30，一个值得上车 MGR 的版本》中所描述的那样，建议默认开启 8.0.30 新增的 sql_generate_invisible_primary_key 参数，我简称其为 GIPK 模式。

这样，如果你建表时忘记加主键，MySQL 会自动帮你创建一个隐式主键，避免你的复制延迟，也避免了踩到 HASH SCAN 行搜索算法 Bug 的坑。

好消息:dbops 默认设置了 GIPK

dbops 是一款提供生产级别 MySQL 部署的 playbook 工具，欢迎提 issue。

MySQL、Percona、GreatSQL 的模版都默认设置了。使用 dbops 部署 MySQL 可以从源头上避免这个坑。

[root@openEuler22 8.0]# find . -type f | xargs grep -Hn "sql_generate_invisible_primary_key"
./my.cnf.j2:238:loose-sql_generate_invisible_primary_key=on # off
./percona-my.cnf.j2:232:loose-sql_generate_invisible_primary_key=on # off
./greatsql-my.cnf.j2:232:loose-sql_generate_invisible_primary_key=on # off

[root@openEuler22 8.0]# pwd
/usr/local/dbops/mysql_ansible/roles/mysql_server/templates/8.0

gitee 仓库地址:

https://gitee.com/fanderchan/dbops

Enjoy MySQL!