前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL发号问题的分析和改进

MySQL发号问题的分析和改进

作者头像
jeanron100
发布2020-07-28 15:17:51
6060
发布2020-07-28 15:17:51
举报
文章被收录于专栏:杨建荣的学习笔记

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

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

(1)显式的创建主键Primary key。

(2)判断表中是否有非空唯一索引,如果有,则为主键。

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

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

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

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

  • 自增列没有业务含义
  • 过度依赖自增列
  • 自增列和状态值主键并存,反而影响业务逻辑和性能
  • MySQL历史遗留bug,在MySQL 8.0该问题才修复

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

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

代码语言:javascript
复制
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;

建表语句为:

代码语言:javascript
复制
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的初始化:

代码语言:javascript
复制
replace into test_inc(flag) values('1');

数据结果为:

代码语言:javascript
复制
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值为:

代码语言:javascript
复制
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语句结果就会让人奇怪,完整的模拟过程如下:

代码语言:javascript
复制
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的简单逻辑吧。

代码语言:javascript
复制
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)

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

验证方法一样:

代码语言:javascript
复制
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的玩法。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-07-24,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档