MySQL发号问题的分析和改进

这是学习笔记的第 2253 篇文章

读完需要

5

速读仅需3分钟

关于发号器的使用，其实有一个大背景，那就是关于主键的一些设计问题，在MySQL中如果一张表没有主键，实际的数据处理就有点麻烦了。

因为在InnoDB存储引擎中，表都是按照主键的顺序进行存放的，我们叫做聚簇索引表或者索引组织表（IOT）

（1）显式的创建主键Primary key。

（2）判断表中是否有非空唯一索引，如果有，则为主键。

（3）如果都不符合上述条件，则会生成UUID的一个隐式主键（6字节大）

可以使用类似的SQL来看到这个隐藏列，select _rowid from test ;

这和主键有什么关系？主要是因为有些时候我们创建主键就是为了创建而创建，没有实际的业务含义，所以会形成一种使用习惯，那就是启用自增列。

自增列的问题很多，有些几句话还说不清楚，大体有如下的一些问题

• 自增列没有业务含义

• 过度依赖自增列

• 自增列和状态值主键并存，反而影响业务逻辑和性能

• MySQL历史遗留bug，在MySQL 8.0该问题才修复

到了这里，我们的需求也基本明确了，我们所说的发号器其实就是要确保每次取到的ID号都是唯一的，当然也显而易见是趋势递增的。

我来说一个初版的发号器实现，假设我们创建一张表test_inc,假设按照业务逻辑，自增列的初始值为1000，则建表语句为：

create table test_inc(id int primary key auto_increment,flag varchar(20)) 
engine=innodb auto_increment=1000;
alter table test_inc modify flag varchar(20) unique;

建表语句为：

mysql> show create table test_inc\G
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `flag` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `flag` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1

使用replace into的逻辑来进行id的初始化：

replace into test_inc(flag) values('1');

数据结果为：

mysql> select *from test_inc;
+------+------+
| id   | flag |
+------+------+
| 1000 | 1    |
+------+------+
1 row in set (0.00 sec)


mysql> replace into test_inc(flag) values('1');
Query OK, 2 rows affected (0.01 sec)


mysql> select *from test_inc;
+------+------+
| id   | flag |
+------+------+
| 1001 | 1    |
+------+------+
1 row in set (0.00 sec)

得到当前的写入id值为：

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|             1001 |
+------------------+
1 row in set (0.00 sec)

按照这个逻辑在多会话并发中依然可以得到期望中的ID自增效果，这些也是last_insert_id()本身的功能范围，就不再赘述了。 

但是这种方案在RDS环境中会碰到一个很尴尬的问题，那就是RDS的服务会做内部切换，而切换后的ID值就会重置。

假设从库的id当前值为1002，在从库切换后，会提升为主库，即可以实现读写，那么在新主库上执行replace into语句结果就会让人奇怪，完整的模拟过程如下：

mysql> select * from test_inc;
+------+------+
| id   | flag |
+------+------+
| 1002 | 1    |
+------+------+
1 row in set (0.00 sec)


mysql>  replace into test_inc(flag) values('1');
Query OK, 2 rows affected (0.02 sec)


mysql> select * from test_inc;
+------+------+
| id   | flag |
+------+------+
| 1001 | 1    |
+------+------+
1 row in set (0.00 sec)


mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|             1001 |
+------------------+
1 row in set (0.00 sec)

碰到这种情况就很尴尬了。好端端的自增ID一下子被打回了原形，而如果结合主从复制的过程和replace into的逻辑，其实也不难分析出这个问题。

简而言之，对于自增列的使用，在如上的场景中是不能够胜任ID自增的逻辑的，可能会产生断层，我们可以通过别的方式来实现。 

在数据库中不难发现这样的设计成平，比如Oracle里面的sequence就是一个例子。 

我们干脆来实现下这个sequence的简单逻辑吧。 

mysql> create table sequence(id int primary key auto_increment);
Query OK, 0 rows affected (0.05 sec)


mysql> insert into sequence values(last_insert_id());
Query OK, 1 row affected (0.01 sec)

接下来需要做两类场景的测试，

验证方法一样：

mysql> select * from sequence;
+------+
| id   |
+------+
| 1003 |
+------+
1 row in set (0.00 sec)


mysql> update sequence set id=last_insert_id(id+1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from sequence;
+------+
| id   |
+------+
| 1004 |
+------+
1 row in set (0.00 sec)


mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|             1004 |
+------------------+
1 row in set (0.00 sec)

一种是多会话状态下验证ID自增的情况，使用last_insert_id()

另外一种则是在从库端直接刷新验证，通过测试可以快速验证得到这种方式的可行性。

当然这个还没有考虑复杂的并发场景，如果想提高吞吐量，可以考虑分布式ID的玩法。

QQ群号：763628645

QQ群二维码如下， 添加请注明：姓名+地区+职位，否则不予通过

订阅我的微信公众号“杨建荣的学习笔记”，第一时间免费收到文章更新。别忘了加星标，以免错过新推送提示。

7

近期热文

你可能也会对以下话题感兴趣。点击链接就可以查看。

• 如何优化MySQL千万级大表，我写了6000字的解读

• 小白学MySQL要多久?我整理了10多个问题的答案

• 说说我的新书《MySQL DBA工作笔记》
  

• 《凤凰项目》读书笔记（一）
  

• 使用Python分析北京积分落户数据，分析完我陷入了深思
  

• MySQL的主键命名挺任性，就这么定了
  

• 华裔教授发现二次方程极简解法,我默默的做了下验算
  

• 回答:我不小心把公司的数据库给删了，该不该离职？
  

• 迁移到MySQL的业务架构演进实战
  

• 数据库修改密码风险高，如何保证业务持续，这几种密码双活方案可以参考
  

• MySQL业务双活的初步设计方案

• 一道经典的MySQL面试题，答案出现三次反转
  

• 业务双活的数据切换思路设计（下）
  

• 业务双活的数据切换思路设计（一）
  

• MySQL中的主键和rowid,看似简单,其实有一些使用陷阱需要注意