【原】MySQL row格式的两个问题

 题记：今天讲两个不怎么少见，但知道的人比较少的事情。

作者简介：

刘伟 云和恩墨开源解决方案事业部首席架构师

多年一线互联网企业DBA经历，对MySQL、NoSQL，PostgreSQL等各类开源数据库均有涉猎，负责开发管理过数千实例规模数据库项目，并带领团队开发了MySQL数据库的监控、备份等自动化组件，对超大规模数据库运维平台的开发及管理有丰富经验。

在MySQL的一般场景中，通常我们推荐将复制格式设置为ROW格式，这样所有变更的数据都会被记录到binlog，可以对数据达到最好的保护，万一发生DML误操作，可以直接从binlog恢复数据。

但row格式的设置，会带来许多问题，运维也会相对复杂些。接下来我们通过两个真实的案例说明直接使用row格式复制出现的问题。

1

无索引表导致的延迟问题

原因简述 

row格式的binlog event，在执行的时候，对于每一条数据，是通过类似翻译为对应SQL语句的方式逐条执行的。对于UPDATE及DELETE语句，如果表上面没有索引，或者已有索引区分度太低的话，执行速度会非常缓慢。 

案例分析

这是我们在为客户排查数据库主从延迟时发现的问题：客户的核心数据库对外提供在线访问，并且应用系统的展示界面是直接读取数据库从库，数据库主从延迟会直接导致页面展示错误，造成经济损失。

在检查到主从同步延迟当时我们使用show slave status命令，（而实际上用心跳表方式更准确），由于业务系统非常重要，多个从库之间延迟严重，为了尽快解决问题，当时使用xtrabackup工具在线备份主库，并替换掉线上从库，暂时解决问题。

但延迟问题本身并没有得到解决。在随后的系统运行中，延迟仍然会产生，于是进行深入检查。

因为客户的binlog_format设置为row，复制进程也只是延迟，show slave status的relay_master_log_file,exec_master_log_pos两个变量一直没有变化，暂时判断为有操作大量数据的DML语句存在。

为了验证，我们解析了对应的binlog日志以及event。但实际结果，对应的event是一个delete event，涉及的数据只有三千多条，这个数量本身的操作并不应该是导致延迟。

然后我们直接查看对应的表结构，以及表的数据量。

 mysql

create table x

( i int,

  b varchar(10),

  v varchar(10),

  x_type varchar(10),

  index idx_type (x_type) );

表的数据量也只有几十万条。但是经询问开发人员得知，这个表每小时会插入几千条数据，之后再删除掉某一type的数据，这是表上新添加的规则，结合DBA的说法，这个增删数据的逻辑的导致问题的根源。 

解决方法

 解决办法有以下几种

1. 创建主键索引。如果目前表内数据本身支持创建唯一或者主键索引，可以直接建立索引解决问题。

2. 创建所有列的联合索引。如果表确实没有办法创建主键或者唯一索引，则尝试对所有列合并添加一个联合索引。

3. 创建部分列的联合索引。由于MySQL索引长度限制，如果确实没有办法创建所有列的索引，找出区分度比较大的几列创建索引。

4. 为表建立自增主键。以上都不能实现的话，为表创建自增id列并设置为主键。这种情况有可能会对应用造成影响，需要预先测试。

实际操作步骤 

1. 停止同步。这种情况除了会导致sql延迟，也会导致sql线程持有非常多的行锁，stop slave语句的执行会比较慢需要等待，也可以直接kill掉线程，然后change master切换同步位点到问题event前的位置。

2. 为表加索引或者主键。

3. 启动同步。

4. 观察追上同步。

延伸讨论 

MySQL中有一个参数，slave_rows_search_algorithms 可以控制row格式下，mysql执行event时候，搜索对应行的方式。

很多ORM框架由于对MySQL兼容不足，没有针对性的主键索引建立，在row格式下，会出现延迟。但在statement格式复制的情况下，未必会出现类似的问题。

常规建议：如果要使用row格式复制，需要保证自己数据库里面所有的innodb都必须有主键或者唯一键，这样才能避免由于没有合适索引导致的从库延迟问题。 

另外，如果开发实用select以及insert语句的时候，都是明确指定列的方式使用的话，可以直接使用解决办法4最简单。这也是很多SQL规范推荐SQL语句需要明确指定列的原因之一。

2

从库alter语句导致同步中断

原因简述 

MySQL row格式复制下，主从库之间同一个表如果列的类型不匹配，MySQL会尝试转码，如果转码失败（类型不兼容），则复制中断。

案例分析 

问题来源为，客户主从复制中断，SQL线程报错信息为： 

Last_SQL_Errno: 1677

        Last_SQL_Error: Column 1 of table 'test.t' cannot be converted from type 'varchar' to type 'int'

这个错误明确说MySQL转码失败，询问DBA后，得知之前为了给表添加一列（开发要求列顺序必须为指定顺序）：

mysql

alter table a add column cl int

DBA试图采用的方式为： 

1. 先为从库修改。

2. 在主库设置session的sql_log_off为0，修改从库。

然而执行完第一步后，就发现同步出现错误。直接原因确定为alter语句。经我们询问得知，出于安全考虑，数据库为最近才被修改为row格式，这次给表添加列是DBA在row环境下，初次执行这种类型的DDL语句。

新增的列由于被添加到列顺序中间，导致主库上是字符的列的位置，对应到从库上成了一个数字类型的列，MySQL无法转码，只能报错。

解决方法 

• 最方便的办法，也是最后采用的方法，就是从主库直接xtrabackup备份，之后恢复作为从库提供服务。

• 另外一个办法是，在从库上把对应表新增的列去掉，重新启动同步。之后找合适的办法进行DDL的执行。 

延伸讨论 

由于row格式的event里面，只会按照主库的列顺序保存数据，并不会保存列名称，在从库的执行也是按照列顺序对应的，因此，row格式下修改列顺序并不是一个安全行为。

DDL的执行，在MySQL 5.7的时候，基本上都做到了在线改表。但主库在线修改完成之后，从库上的执行也会导致从库延迟，虽然不会阻塞从库的SELECT语句，但延迟本身就很难接受，因此实际情况中，如果无法接受延迟，还是需要考虑pt-osc或者gh-osc工具。

3

总结

MySQL的row格式复制对数据安全的保护，以及主从数据一致的保证是非常重要的，一般来说都建议设置成row格式。在MySQL 5.7之后已经作为复制的默认格式，但带来的运维方式方面的变更，以及对运维手段人员的要求也增高不少，如果需要设置，还是需要多加注意的。

如何加入"云和恩墨大讲堂"微信群