主键在表中的位置会影响性能吗

主键可能会引起数据位置频繁变动，影响性能。主键将会添加到表上的所以其他索引中，因此会降低性能，影响插入速度。

因为自动增长，在手动要插入指定ID的记录时会显得麻烦，尤其是当系统与其它系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突。特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时，自增主键将是你的噩梦。

主键的数据类型选择。

关于主键的类型选择上最常见的争论是用整型还是字符型的问题，关于这个问题高性能MySQL一书中有明确论断，整数通常是标识列的最好选择，因为它很快且可以使用AUTOINCREAMENT，如果可能，应该避免使用字符串类型作为标识列，因为很消耗空间，且通常比数字类型慢。

第 4 篇 提过自增主键，让主键索引尽量 递增顺序插入 ， 避免页分裂 ，索引 更紧凑 。

不能保证连续递增 。 什么情况出现 “空洞”？主键冲突、回滚、批量申请。

id 自增主键、c 唯一索引

insert into t values(null, 1, 1)show create table

AUTO_INCREMENT=2，下一次插入，生成 id=2。

表的结构定义存放在后缀名为 .frm 的文件中，不会保存自增值。

MyISAM 保存 数据文件 ； InnoDB 自增值保存 内存里（MySQL 8.0记录redo log 中） ，重启 恢复为重启前值

MySQL 5.7 前，没持久化。最大值 max(id)=10，AUTO_INCREMENT=11。删除 id=10 的行，AUTO_INCREMENT 还是 11。重启AUTO_INCREMENT 0。 重启修改AUTO_INCREMENT 。

id 被定义为 AUTO_INCREMENT，

1. 插入时 id 字段指定 0、null 或 未指定值 ，AUTO_INCREMENT 填自增字段 ；

2.  id 指定具体值， 用指定值 。

插入 X，自增值 Y。

1.   X（2）<Y（3），自增值不变 ；

2.  如果 （2）X≥Y（1），前自增值 改为新自增值 X

新自增值生成算法是 ：自增的初始值auto_increment_offset(默认1)开始， 步长auto_increment_increment (默认1)为，持续叠加，第一个大于 X 值，作为新的自增值。

ps：用的 不全是默认值 。如双 M 主备 结构里 双写 ，auto_increment_increment= 2 ，让自增 id 都是 奇数 ，另一都是 偶数 ， 避免主键冲突 。

插入的值 >= 当前自增值，新自增值就是“准备插入的值 +1”；否则不变。

两个参数都设置为 1 的时候，自增主键 id 却不能保证是连续的，这是什么原因呢？

已经有了 (1,1,1)   insert into t  values(null, 1, 1)

1.  写入一行(0,1,1)

2.  没有指定自增 id 的值， t 当前的自增值 2 ；

3.  改成 (2 ,1,1)； 自增值改成 3

4.  已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。

没有插入成功， 自增值不再改回去。不连续。

自增值不能回退：提升性能

两个并行事务，加锁顺序申请。

1.   A 申请到了 id=2， B  id=3， t 的自增值是 4，

2.   B 提交， A 出现唯一键冲突。

3.  如允许A 自增 id 回退， t 改回 2 ，问题：id=3 再申请到 id=3（已有） “主键冲突”

1. 申请前判断 ，存在跳过。成本高。因为，本来申请 id 是一个很快的 *** 作，现在还要再去主键索引树上判断 id 是否存在。

2. 锁范围扩大 ，事务完 提交 ， 下一个再申请 自增 id。粒度太大，并发能力下降。

0： 5.0 策略， 语句结束释放 ；

1 (默认)： 普通 insert ，自增锁 申请后释放 ；

                insert …select 批量插入， 语句结束释放 ；

2 ：申请后释放锁

ps： 5.0 版本，自增锁， 语句级别 。申请 表 自增锁，结束释放， 影响并发度 。

数据的一致性

t1 插入4 行，创建相同结构表 t2，同时向 t2 插入。

如果 B 申请自增值后马上释放自增锁 ，情况：

B 先插入了两个记录，( 1 ,1,1)、( 2 ,2,2)；

A 来申请自增 id 得到 id=3，插入了（ 3 ,5,5)；

B 插入两条记录 ( 4 ,3,3)、 (5 ,4,4)。

B 本身就没要求 t2 跟A 相同。如果binlog_format=statement，binlog 里id 连续。数据不一致。

问题原因：B 的 insert 语句，生成id 不连续。 statement 格式的 binlog 串行执行， 执行不出来 。

1.  批量插入 数据语句， 固定生成连续 id 值 。语句结束释放

2.  binlog如实记录进来，备库执行，不依赖于自增主键生成。innodb_autoinc_lock_mode = 2，binlog_format = row。 生产上，尤其 insert … select 批量插入数据时， 提升并发性，不会数据一致性。

批量插入数据包含：  insert … select 、 replace … select 和 load data 

普通 insert 多个 value 情况，innodb_autoinc_lock_mode 设置 1，精确计算出要多少个 id 的， 一次性申请，释放 。

1.  第一次申请自增 id，会分配 1 个；

2.  第二次申请自增 id，会分配 2 个；

3. 第三次申请自增 id，会分配 4 个； 依此类推，上一次的两倍

实际上t2 中插入 4 行， 分三次 ，1，第二次id=2 和 id=3， 第三次id=4 到 id=7。

 id=5 到 id=7 浪费掉

再执行 insert into t2  values(null, 5,5)，实际上插入的数据就是（ 8 ,5,5)。

自增值存储。

MyISAM 里，被写数据文件上。 InnoDB 中，记录内存的。重启前后不变。

自增值改变时机，回滚不能回收自增 id。

innodb_autoinc_lock_mode，控制自增值申请锁范围。并发性能考虑，设置为 2，binlog_format =row。

最后例子，执行 insert into t2(c,d) select c,d from t隔离级别是 可重复读 （repeatable

read），binlog_format=statement。 所有记录和间隙加锁 。为什么这么做？

如果 insert …select 有其他线程 *** 作原表，不会导致逻辑错误。如不加锁，就是快照读(执行期间，一致性视图是不会修改)。

不对t表所有记录和间隙加锁，，可重复读，其他提交t2看不到。但binlog=statement，备库或基于binlog恢复临时库t2看到，不一致。

自增id和写binlog是有先后顺序的。binlog=statement，A获取id=1，B获取id=2，B提交，写binlog，再A写binlog。

如果binlog重放，不会出现不一致，B的id为1，A的id为2的情况

因为binlog 记录 自增值语句 前 ，前面多一句， 指定“自增ID值多少” ，对应主库自增值

业务有两个 MySQL 集群是通过 MQ 进行同步的，昨晚 MQ 出现异常，报了很多主键冲突，想请 dba 帮忙校验一下两个集群的数据是否一致。

当接到这个需求的时候并没当回事，隐约有点印象 pt-table-checksum 能通过 dsn 实现 MySQL 的数据校验，所以当时就应承下来了。不曾想，啪啪打脸，回想起来真是草率了。

本文参考的是 pt-table-checksum 的校验逻辑，基于数据块去遍历每个表，然后比对 checksum 的值判断该块是否一致，本文主要是想聊聊我在实现数据校验脚本过程中遇到的问题以及解决思路，希望对大家有帮助。

利用线上的配置文件搭建一套主从环境。

这个用例将通过 dsn 方式连接从库。

这个用例将通过 dsn 方式连接从库，但是会将从库的复制链路 stop 掉，并清空复制信息。

熟悉 pt-table-checksum 的朋友应该都知道，该工具是基于主键(非空唯一键)进行扫描数据行，其实这个逻辑针对整型单列主键实现起来很简单，但是如果是联合主键且是字符型，好像就没那么简单了，有兴趣的可以思考一下。下面我先说一下大致的逻辑：

第一步：判断 _min_rowid 是否为空，为空就取该表的第一行，并记作 _min_rowid 。

第二步：根据 _min_rowid 作为条件进行扫描该表，取下一个数据块的数据，记录数据块的最后一行数据的主键值，记录 checksum 的值，并记下 _min_rowid 。

第三步：判断_min_rowid是否为空，非空重复第二步，为空退出检查。

通过上述三个步骤可以看到，如果是单列整型的主键，实现起来很简单，但是问题来了，业务的表的主键五花八门，有的是联合主键，有的是字符型的联合主键，还有整型+字符型的联合主键，那么上述的实现方式显然是有问题的。所以实现起来需要多考虑几个问题：

鉴于存在上述两个问题，可以参考如下实现逻辑：

假如有这么一个联合主键字段 primary key(a,b,c) 都是整型，该如何编写遍历 sql 呢？起初我的想法很简单，具体如下：

至此在编写校验脚本过程遇到的两个问题就算告一段落了，剩下的就是各种逻辑处理了，不过多赘述，有兴趣的可以自行阅读脚本文件。

本着最低程度影响业务，所以取消加锁逻辑。但是又要保证该数据块的数据一致性，如果这个数据块是个热数据，当前正在变更，那么校验的时候难免会不一致。所以只能通过多次校验实现，默认是校验20次，其中有一次校验结果是一致，就认为是一致的，如果前5次校验过程中，这个数据块的数据没有变化，也视为不一致（可能是因为延迟，也可能是真的不一致）。

pt-table-checksum 不校验表结构，改写时添加表结构的校验。

可以基于表的并行校验，可由用户指定并行数，但是脚本有个安全机制，如果用户指定的并行数大于当前 cpu 空闲核心数，就会按当前（空闲核心数-1）作为并行数。

添加网络监控，由用户指定网络上限百分比，当网卡流量超过这个百分比就暂停任务，等待网卡流量低于阈值才会继续任务。这个主要是出于对于中间件（mycat）的场景或者分布式数据库（tidb）的场景。

支持定时任务功能，用户可以使用这个功能规避业务高峰，仅在业务低峰进行数据校验。

不仅限于主从节点的校验，只要目标对象支持 MySQL 的标准 SQL 语法就能做数据校验。

校验逻辑是通过 SQL 采集目标节点的数据库，如果目标数据库系统当前存在异常，无疑是雪上加霜，将会触发未知问题，所以添加超时机制，单次取数据块的阈值是5s，超过5秒就放弃等待重试。测试发现，有时候即便触发超时了，但是 SQL 任务还是会在目标数据库的 processlist 中能看到，所以又添加了一个 kill 机制，超时后会触发一个 kill processlist id 的动作。另外为了避免 kill 错，在每个 SQL 对象添加了一个32位的 md5 值，每次 kill 的时候会校验这个 md5 值。

本工具借鉴 pt-table-checksum 工具思路改写，可以检查随意两个 mysql（支持 mysql sql 语法的数据库）节点的数据一致性。

基于主键以一个块遍历数据表，比对checksum的值，块的大小可通过参数指定。 （1）获取该表的第一个数据块的查询SQL。 （2）将两个目标节点的数据块的checksum的值，记录到临时文件，file1 file2。 （3）比对file1 file2是否一致。

第一步：先开启一个 screen 监控网络

第二步：新开启一个screen执行校验任务

（1）info.log 文件

（2）list目录

（3）md5 目录

（4）pri 目录

（5）res 目录

这是 table 目录下记录某个数据块不一致的一个例子

这是 diff 目录下记录某个数据行不一致的一个例子

（6）skip.log 文件

本工具是参考了 pt-table-checksum 工具的一些思路并结合自身经验进行改写，尚有很多不足之处，仅做学习交流之用， 如有线上环境使用需求，请在测试环境充分测试。

---