企业内网网站,中国企业排名100强,建设个人网站需要什么条件,如何做自动交易网站文章目录 1.grant之后要跟着flush privileges吗#xff1f;1.1 全局权限1.2 db 权限1.3 表权限和列权限1.4 flush privileges 使用场景 2. 要不要使用分区表?2.1 分区表是什么?2.2 分区表的引擎层行为2.3 分区策略2.4 分区表的 server 层行为2.5 分区表的应用场景 3. 自增Id… 文章目录 1.grant之后要跟着flush privileges吗1.1 全局权限1.2 db 权限1.3 表权限和列权限1.4 flush privileges 使用场景 2. 要不要使用分区表?2.1 分区表是什么?2.2 分区表的引擎层行为2.3 分区策略2.4 分区表的 server 层行为2.5 分区表的应用场景 3. 自增Id用完怎么办?3.1 表定义自增值id3.2 InnoDB 系统自增 row_id3.3 Xid3.4 Innodb trx_id3.5 thread_id 1.grant之后要跟着flush privileges吗
在 MySQL 里面grant 语句是用来给用户赋权的。
先创建一个用户
create user ua% identified by pa;创建一个用户’ua’’%’密码是 pa。注意在 MySQL 里面用户名 (user) 地址 (host) 才表示一个用户因此 uaip1 和 uaip2 代表的是两个不同的用户。 这条命令做了两个动作
磁盘上往 mysql.user 表里插入一行由于没有指定权限所以这行数据上所有表示权限的字段的值都是 N内存里往数组 acl_users 里插入一个 acl_user 对象这个对象的 access 字段值为 0。
图 1 就是这个时刻用户 ua 在 user 表中的状态。 在 MySQL 中用户权限是有不同的范围的。按照用户权限范围从大到小的顺序依次说明。
1.1 全局权限
全局权限作用于整个 MySQL 实例这些权限信息保存在 mysql 库的 user 表里.。给用户 ua赋一个最高权限的话语句是这么写的
grant all privileges on *.* to ua% with grant option;这个 grant 命令做了两个动作
磁盘上将 mysql.user 表里用户’ua’’%这一行的所有表示权限的字段的值都修改为‘Y’内存里从数组 acl_users 中找到这个用户对应的对象将 access 值权限位修改为二进制的“全 1”。
在这个 grant 命令执行完成后如果有新的客户端使用用户名 ua 登录成功MySQL 会为新连接维护一个线程对象然后从 acl_users 数组里查到这个用户的权限并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句所有关于全局权限的判断都直接使用线程对象内部保存的权限位。
基于上面的分析
grant 命令对于全局权限同时更新了磁盘和内存。命令完成后即时生效接下来新创建的连接会使用新的权限。对于一个已经存在的连接它的全局权限不受 grant 命令的影响。 需要说明的是一般在生产环境上要合理控制用户权限的范围。我们上面用到的这个 grant 语句就是一个典型的错误示范。如果一个用户有所有权限一般就不应该设置为所有 IP 地址都可以访问。 如果要回收上面的 grant 语句赋予的权限可以使用下面这条命令
revoke all privileges on *.* from ua%;这条 revoke 命令的用法与 grant 类似做了如下两个动作
磁盘上将 mysql.user 表里用户’ua’’%这一行的所有表示权限的字段的值都修改为“N”内存里从数组 acl_users 中找到这个用户对应的对象将 access 的值修改为 0。
1.2 db 权限
除了全局权限MySQL 也支持库级别的权限定义。如果要让用户 ua 拥有库 db1 的所有权限可以执行下面这条命令
grant all privileges on db1.* to ua% with grant option;基于库的权限记录保存在 mysql.db 表中在内存里则保存在数组 acl_dbs 中。
这条 grant 命令做了如下两个动作
磁盘上往 mysql.db 表中插入了一行记录所有权限位字段设置为“Y”内存里增加一个对象到数组 acl_dbs 中这个对象的权限位为“全 1”。
图 2 就是这个时刻用户 ua 在 db 表中的状态。 每次需要判断一个用户对一个数据库读写权限的时候都需要遍历一次 acl_dbs 数组根据 user、host 和 db 找到匹配的对象然后根据对象的权限位来判断。 也就是说grant 修改 db 权限的时候是同时对磁盘和内存生效的。
grant 操作对于已经存在的连接的影响在全局权限和基于 db 的权限效果是不同的。 图中 set global sync_binlog 这个操作是需要 super 权限的。 可以看到虽然用户 ua 的 super 权限在 T3 时刻已经通过 revoke 语句回收了但是在 T4 时刻执行 set global 的时候权限验证还是通过了。这是因为 super 是全局权限这个权限信息在线程对象中而 revoke 操作影响不到这个线程对象。
在 T5 时刻去掉 ua 对 db1 库的所有权限后在 T6 时刻 session B 再操作 db1 库的表就会报错“权限不足”。这是因为 acl_dbs 是一个全局数组所有线程判断 db 权限都用这个数组这样 revoke 操作马上就会影响到 session B。 这里在代码实现上有一个特别的逻辑如果当前会话已经处于某一个 db 里面之前 use 这个库的时候拿到的库权限会保存在会话变量中。 可以看到在 T6 时刻session C 和 session B 对表 t 的操作逻辑是一样的。但是 session B 报错而 session C 可以执行成功。这是因为 session C 在 T2 时刻执行的 use db1拿到了这个库的权限在切换出 db1 库之前session C 对这个库就一直有权限。
1.3 表权限和列权限
除了 db 级别的权限外MySQL 支持更细粒度的表权限和列权限。 其中表权限定义存放在表 mysql.tables_priv 中列权限定义存放在表 mysql.columns_priv 中。这两类权限组合起来存放在内存的 hash 结构 column_priv_hash 中。
两类权限的赋权命令如下
create table db1.t1(id int, a int);grant all privileges on db1.t1 to ua% with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO ua% with grant option;这两个权限每次 grant 的时候都会修改数据表也会同步修改内存中的 hash 结构。因此对这两类权限的操作也会马上影响到已经存在的连接。 flush privileges 命令会清空 acl_users 数组然后从 mysql.user 表中读取数据重新加载重新构造一个 acl_users 数组。也就是说以数据表中的数据为准会将全局权限内存数组重新加载一遍。
也就是说如果内存的权限数据和磁盘数据表相同的话不需要执行 flush privileges。而如果我们都是用 grant/revoke 语句来执行的话内存和数据表本来就是保持同步更新的。 因此正常情况下grant 命令之后没有必要跟着执行 flush privileges 命令。
1.4 flush privileges 使用场景
当数据表中的权限数据跟内存中的权限数据不一致的时候flush privileges 语句可以用来重建内存数据达到一致状态。 这种不一致往往是由不规范的操作导致的比如直接用 DML 语句操作系统权限表。我们来看一下下面这个场景 图 4 使用 flush privileges T3 时刻虽然已经用 delete 语句删除了用户 ua但是在 T4 时刻仍然可以用 ua 连接成功。原因就是这时候内存中 acl_users 数组中还有这个用户因此系统判断时认为用户还正常存在。
在 T5 时刻执行过 flush 命令后内存更新T6 时刻再要用 ua 来登录的话就会报错“无法访问”了。
直接操作系统表是不规范的操作这个不一致状态也会导致一些更“诡异”的现象发生。比如前面这个通过 delete 语句删除用户的例子就会出现下面的情况
图 5 不规范权限操作导致的异常 由于在 T3 时刻直接删除了数据表的记录而内存的数据还存在。这就导致了
T4 时刻给用户 ua 赋权限失败因为 mysql.user 表中找不到这行记录而 T5 时刻要重新创建这个用户也不行因为在做内存判断的时候会认为这个用户还存在。 小结 grant 语句会同时修改数据表和内存判断权限的时候使用的是内存数据。因此规范地使用 grant 和 revoke 语句是不需要随后加上 flush privileges 语句的。 flush privileges 语句本身会用数据表的数据重建一份内存权限数据所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的所以尽量不要使用这类语句。 在使用 grant 语句赋权时可能还会看到这样的写法grant super on *.* to ua% identified by pa; 这条命令加了 identified by ‘密码’ 语句的逻辑里面除了赋权外还包含了如果用户’ua’’%不存在就创建这个用户密码是 pa如果用户 ua 已经存在就将密码修改成 pa。(一种不建议的写法因为这种写法很容易就会不慎把密码给改了。) 2. 要不要使用分区表?
2.1 分区表是什么?
先创建一个表 t
CREATE TABLE t (ftime datetime NOT NULL,c int(11) DEFAULT NULL,KEY (ftime)
) ENGINEInnoDB DEFAULT CHARSETlatin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE InnoDB,PARTITION p_2018 VALUES LESS THAN (2018) ENGINE InnoDB,PARTITION p_2019 VALUES LESS THAN (2019) ENGINE InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE InnoDB);
insert into t values(2017-4-1,1),(2018-4-1,1);图 1 表 t 的磁盘文件 在表 t 中初始化插入了两行记录按照定义的分区规则这两行记录分别落在 p_2018 和 p_2019 这两个分区上。
可以看到这个表包含了一个.frm 文件和 4 个.ibd 文件每个分区对应一个.ibd 文件。 也就是说
对于引擎层来说这是 4 个表对于 Server 层来说这是 1 个表。
2.2 分区表的引擎层行为
举个在分区表加间隙锁的例子目的是说明对于 InnoDB 来说这是 4 个表。
图 2 分区表间隙锁示例 初始化表 t 的时候只插入了两行数据 ftime 的值分别是‘2017-4-1’ 和’2018-4-1’ 。session A 的 select 语句对索引 ftime 上这两个记录之间的间隙加了锁。如果是一个普通表的话那么 T1 时刻在表 t 的 ftime 索引上间隙和加锁状态应该是图 3 这样的。
图 3 普通表的加锁范围 也就是说‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。那么sesion B 的两条插入语句应该都要进入锁等待状态。
但是从上面的实验效果可以看出session B 的第一个 insert 语句是可以执行成功的。这是因为对于引擎来说p_2018 和 p_2019 是两个不同的表也就是说 2017-4-1 的下一个记录并不是 2018-4-1而是 p_2018 分区的 supremum。所以 T1 时刻在表 t 的 ftime 索引上间隙和加锁的状态其实是图 4 这样的 由于分区表的规则session A 的 select 语句其实只操作了分区 p_2018因此加锁范围就是图 4 中深绿色的部分。
所以session B 要写入一行 ftime 是 2018-2-1 的时候是可以成功的而要写入 2017-12-1 这个记录就要等 session A 的间隙锁。
图 5 就是这时候的 show engine innodb status 的部分结果。
图 5 session B 被锁住信息 看完 InnoDB 引擎的例子再来一个 MyISAM 分区表的例子。 用 alter table t enginemyisam把表 t 改成 MyISAM 表然后再用下面这个例子说明对于 MyISAM 引擎来说这是 4 个表。
图 6 用 MyISAM 表锁验证 在 session A 里面我用 sleep(100) 将这条语句的执行时间设置为 100 秒。由于 MyISAM 引擎只支持表锁所以这条 update 语句会锁住整个表 t 上的读。但看到的结果是session B 的第一条查询语句是可以正常执行的第二条语句才进入锁等待状态。
这正是因为 MyISAM 的表锁是在引擎层实现的session A 加的表锁其实是锁在分区 p_2018 上。因此只会堵住在这个分区上执行的查询落到其他分区的查询是不受影响的。
使用分区表的一个重要原因就是单表过大。那么如果不使用分区表的话就是要使用手动分表的方式。 手动分表和分区表区别:
比如按照年份来划分我们就分别创建普通表 t_2017、t_2018、t_2019 等等。手工分表的逻辑也是找到需要更新的所有分表然后依次执行更新。在性能上这和分区表并没有实质的差别。分区表和手工分表一个是由 server 层来决定使用哪个分区一个是由应用层代码来决定使用哪个分表。因此从引擎层看这两种方式也是没有差别的。
这两个方案的区别主要是在 server 层上。从 server 层看我们就不得不提到分区表一个被广为诟病的问题打开表的行为。
2.3 分区策略
每当第一次访问一个分区表的时候MySQL 需要把所有的分区都访问一遍。一个典型的报错情况是这样的如果一个分区表的分区很多比如超过了 1000 个而 MySQL 启动的时候open_files_limit 参数使用的是默认值 1024那么就会在访问这个表的时候由于需要打开所有的文件导致打开表文件的个数超过了上限而报错。
下图就是创建的一个包含了很多分区的表 t_myisam执行一条插入语句后报错的情况。
图 7 insert 语句报错 这条 insert 语句明显只需要访问一个分区但语句却无法执行。 这个表用的是 MyISAM 引擎。使用 InnoDB 引擎的话并不会出现这个问题。
MyISAM 分区表使用的分区策略我们称为通用分区策略generic partitioning每次访问分区都由 server 层控制。通用分区策略是 MySQL 一开始支持分区表的时候就存在的代码在文件管理、表管理的实现上很粗糙因此有比较严重的性能问题。MySQL 5.7.9 开始InnoDB 引擎引入了本地分区策略native partitioning。这个策略是在 InnoDB 内部自己管理打开分区的行为。 MySQL 从 5.7.17 开始将 MyISAM 分区表标记为即将弃用 (deprecated) 从 MySQL 8.0 版本开始就不允许创建 MyISAM 分区表了只允许创建已经实现了本地分区策略的引擎。目前来看只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。 2.4 分区表的 server 层行为
如果从 server 层看的话一个分区表就只是一个表。 如图 8 和图 9 所示分别是这个例子的操作序列和执行结果图。 图 8 分区表的 MDL 锁 图 9 show processlist 结果 可以看到虽然 session B 只需要操作 p_2017 这个分区但是由于 session A 持有整个表 t 的 MDL 锁就导致了 session B 的 alter 语句被堵住。 分区表在做 DDL 的时候影响会更大。 如果使用的是普通分表那么当你在 truncate 一个分表的时候肯定不会跟另外一个分表上的查询语句出现 MDL 锁冲突。
小结一下
MySQL 在第一次打开分区表的时候需要访问所有的分区在 server 层认为这是同一张表因此所有分区共用同一个 MDL 锁在引擎层认为这是不同的表因此 MDL 锁之后的执行过程会根据分区表规则只访问必要的分区。 关于“必要的分区”的判断就是根据 SQL 语句中的 where 条件结合分区规则来实现的。比如我们上面的例子中where ftime‘2018-4-1’根据分区规则 year 函数算出来的值是 2018那么就会落在 p_2019 这个分区。 但是如果这个 where 条件改成 where ftime‘2018-4-1’虽然查询结果相同但是这时候根据 where 条件就要访问 p_2019 和 p_others 这两个分区。 如果查询语句的 where 条件中没有分区 key那就只能访问所有分区了。当然这并不是分区表的问题。即使是使用业务分表的方式where 条件中没有使用分表的 key也必须访问所有的分表。 2.5 分区表的应用场景
分区表的一个显而易见的优势是对业务透明相对于用户分表来说使用分区表的业务代码更简洁。还有分区表可以很方便的清理历史数据。
如果一项业务跑的时间足够长往往就会有根据时间删除历史数据的需求。这时候按照时间分区的分区表就可以直接通过 alter table t drop partition … 这个语法删掉分区从而删掉过期的历史数据。
alter table t drop partition … 操作是直接删除分区文件效果跟 drop 普通表类似。与使用 delete 语句删除数据相比优势是速度快、对系统影响小。 小结 这里介绍的是 server 层和引擎层对分区表的处理方式。是以范围分区range为例介绍的。实际上MySQL 还支持 hash 分区、list 分区等分区方法。可以在需要用到的时候再翻翻MySql手册
实际使用时分区表跟用户分表比起来有两个绕不开的问题一个是第一次访问的时候需要访问所有分区另一个是共用 MDL 锁。因此如果要使用分区表就不要创建太多的分区。
分区并不是越细越好。实际上单表或者单分区的数据一千万行只要没有特别大的索引对于现在的硬件能力来说都已经是小表了。分区也不要提前预留太多在使用之前预先创建即可。比如如果是按月分区每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区要及时的 drop 掉。
至于分区表的其他问题比如查询需要跨多个分区取数据查询性能就会比较慢基本上就不是分区表本身的问题而是数据量的问题或者说是使用方式的问题了。 思考 假设现在要创建一个自增字段 id。MySQL 要求分区表中的主键必须包含分区字段。如果要在表 t 的基础上做修改你会怎么定义这个表的主键呢为什么这么定义呢
答 : 由于 MySQL 要求主键包含所有的分区字段所以肯定是要创建联合主键的。 有两种可选一种是 (ftime, id)另一种是 (id, ftime)。 如果从利用率上来看应该使用 (ftime, id) 这种模式。因为用 ftime 做分区 key说明大多数语句都是包含 ftime 的使用这种模式可以利用前缀索引的规则减少一个索引。
这时的建表语句是:
CREATE TABLE t (id int(11) NOT NULL AUTO_INCREMENT,ftime datetime NOT NULL,c int(11) DEFAULT NULL,PRIMARY KEY (ftime,id)
) ENGINEMyISAM DEFAULT CHARSETlatin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE MyISAM,PARTITION p_2018 VALUES LESS THAN (2018) ENGINE MyISAM,PARTITION p_2019 VALUES LESS THAN (2019) ENGINE MyISAM,PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE MyISAM);建议是要尽量使用 InnoDB 引擎。InnoDB 表要求至少有一个索引以自增字段作为第一个字段所以需要加一个 id 的单独索引。
CREATE TABLE t (id int(11) NOT NULL AUTO_INCREMENT,ftime datetime NOT NULL,c int(11) DEFAULT NULL,PRIMARY KEY (ftime,id),KEY id (id)
) ENGINEInnoDB DEFAULT CHARSETlatin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE InnoDB,PARTITION p_2018 VALUES LESS THAN (2018) ENGINE InnoDB,PARTITION p_2019 VALUES LESS THAN (2019) ENGINE InnoDB,PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE InnoDB);当然把字段反过来创建成 PRIMARY KEY (id,ftime),KEY id (ftime)也是可以的。 3. 自增Id用完怎么办?
MySQL 里有很多自增的 id每个自增 id 都是定义了初始值然后不停地往上加步长。虽然自然数是没有上限的但是在计算机里只要定义了表示这个数的字节长度那它就有上限。
比如无符号整型 (unsigned int) 是 4 个字节上限就是 2^32-1。
3.1 表定义自增值id
表定义的自增值达到上限后的逻辑是再申请下一个 id 时得到的值保持不变。 通过下面这个语句序列验证一下
create table t(id int unsigned auto_increment primary key) auto_increment4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE t (id int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT4294967295;
*/insert into t values(null);
//Duplicate entry 4294967295 for key PRIMARY第一个 insert 语句插入数据成功后这个表的 AUTO_INCREMENT 没有改变还是 4294967295就导致了第二个 insert 语句又拿到相同的自增 id 值再试图执行插入语句报主键冲突错误。
在建表的时候需要考察的表是否有可能达到这个上限如果有可能就应该创建成 8 个字节的 bigint unsigned。
3.2 InnoDB 系统自增 row_id
创建的 InnoDB 表没有指定主键那么 InnoDB 会创建一个不可见的长度为 6 个字节的 row_id。
InnoDB 维护了一个全局的 dict_sys.row_id 值所有无主键的 InnoDB 表每插入一行数据都将当前的 dict_sys.row_id 值作为要插入数据的 row_id然后把 dict_sys.row_id 的值加 1。
实际上在代码实现时 row_id 是一个长度为 8 字节的无符号长整型 (bigint unsigned)。但是InnoDB 在设计时给 row_id 留的只是 6 个字节的长度这样写到数据表中时只放了最后 6 个字节所以 row_id 能写到数据表中的值就有两个特征
row_id 写入表中的值范围是从 0 到 2^48-1当 dict_sys.row_id2^48时如果再有插入数据的行为要来申请 row_id拿到以后再取最后 6 个字节的话就是 0。
也就是说写入表的 row_id 是从 0 开始到 2^48-1。达到上限后下一个值就是 0然后继续循环。 在 InnoDB 逻辑里申请到 row_idN 后就将这行数据写入表中如果表中已经存在 row_idN 的行新写入的行就会覆盖原有的行。
可以通过 gdb 修改系统的自增 row_id 来实现验证这个结论。 可以看到在用 gdb 将 dict_sys.row_id 设置为 2^48之后再插入的 a2 的行会出现在表 t 的第一行因为这个值的 row_id0。之后再插入的 a3 的行由于 row_id1就覆盖了之前 a1 的行因为 a1 这一行的 row_id 也是 1。
从这个角度看我们还是应该在 InnoDB 表中主动创建自增主键。因为表自增 id 到达上限后再插入数据时报主键冲突错误是更能被接受的。 毕竟覆盖数据就意味着数据丢失影响的是数据可靠性报主键冲突是插入失败影响的是可用性。而一般情况下可靠性优先于可用性。
3.3 Xid
redo log 和 binlog 相配合的时候提到了它们有一个共同的字段叫作 Xid。它在 MySQL 中是用来对应事务的。
Xid 在 MySQL 内部是怎么生成的呢 MySQL 内部维护了一个全局变量 global_query_id每次执行语句的时候将它赋值给 Query_id然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。而 global_query_id 是一个纯内存变量重启之后就清零了。所以你就知道了在同一个数据库实例中不同事务的 Xid 也是有可能相同的。
但是 MySQL 重启之后会重新生成新的 binlog 文件这就保证了同一个 binlog 文件里Xid 一定是惟一的。
虽然 MySQL 重启不会导致同一个 binlog 里面出现两个相同的 Xid但是如果 global_query_id 达到上限后就会继续从 0 开始计数。从理论上讲还是就会出现同一个 binlog 里面出现相同 Xid 的场景。
因为 global_query_id 定义的长度是 8 个字节这个自增值的上限是 2^64-1。要出现这种情况必须是下面这样的过程
执行一个事务假设 Xid 是 A接下来执行 2^64次查询语句让 global_query_id 回到 A再启动一个事务这个事务的 Xid 也是 A。
不过2^64这个值太大了大到可以认为这个可能性只会存在于理论上。
3.4 Innodb trx_id
Xid 和 InnoDB 的 trx_id 是两个容易混淆的概念。
Xid 是由 server 层维护的。 InnoDB 内部使用 Xid就是为了能够在 InnoDB 事务和 server 之间做关联。但是InnoDB 自己的 trx_id是另外维护的。
InnoDB 内部维护了一个 max_trx_id 全局变量每次需要申请一个新的 trx_id 时就获得 max_trx_id 的当前值然后并将 max_trx_id 加 1。
InnoDB 数据可见性的核心思想是 每一行数据都记录了更新它的 trx_id当一个事务读到一行数据的时候判断这个数据是否可见的方法就是通过事务的一致性视图与这行数据的 trx_id 做对比。 对于正在执行的事务可以从 information_schema.innodb_trx 表中看到事务的 trx_id。 session B 里我从 innodb_trx 表里查出的这两个字段第二个字段 trx_mysql_thread_id 就是线程 id。显示线程 id是为了说明这两次查询看到的事务对应的线程 id 都是 5也就是 session A 所在的线程。
可以看到T2 时刻显示的 trx_id 是一个很大的数T4 时刻显示的 trx_id 是 1289看上去是一个比较正常的数字。这是什么原因呢
实际上在 T1 时刻session A 还没有涉及到更新是一个只读事务。而对于只读事务InnoDB 并不会分配 trx_id。 也就是说
在 T1 时刻trx_id 的值其实就是 0。而这个很大的数只是显示用的。直到 session A 在 T3 时刻执行 insert 语句的时候InnoDB 才真正分配了 trx_id。所以T4 时刻session B 查到的这个 trx_id 的值就是 1289。
需要注意的是除了显而易见的修改类语句外如果在 select 语句后面加上 for update这个事务也不是只读事务。
实验的时候发现不止加 1。这是因为
update 和 delete 语句除了事务本身还涉及到标记删除旧数据也就是要把数据放到 purge 队列里等待后续物理删除这个操作也会把 max_trx_id1 因此在一个事务中至少加 2InnoDB 的后台操作比如表的索引信息统计这类操作也是会启动内部事务的因此你可能看到trx_id 值并不是按照加 1 递增的。
T2 时刻查到的这个很大的数字是怎么来的呢 其实这个数字是每次查询的时候由系统临时计算出来的。它的算法是把当前事务的 trx 变量的指针地址转成整数再加上 2^48。使用这个算法就可以保证以下两点 因为同一个只读事务在执行期间它的指针地址是不会变的所以不论是在 innodb_trx 还是在 innodb_locks 表里同一个只读事务查出来的 trx_id 就会是一样的。如果有并行的多个只读事务每个事务的 trx 变量的指针地址肯定不同。这样不同的并发只读事务查出来的 trx_id 就是不同的。 为什么还要再加上 2^48呢 在显示值里面加上 248目的是要保证只读事务显示的 trx_id 值比较大正常情况下就会区别于读写事务的 id。但是trx_id 跟 row_id 的逻辑类似定义长度也是 8 个字节。 因此在理论上还是可能出现一个读写事务与一个只读事务显示的 trx_id 相同的情况。不过这个概率很低并且也没有什么实质危害可以不管它。 只读事务不分配 trx_id有什么好处呢 这样做可以减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务是不影响数据的可见性判断的。所以在创建事务的一致性视图时InnoDB 就只需要拷贝读写事务的 trx_id。可以减少 trx_id 的申请次数。在 InnoDB 里即使你只是执行一个普通的 select 语句在执行过程中也是要对应一个只读事务的。所以只读事务优化后普通的查询语句不需要申请 trx_id就大大减少了并发事务申请 trx_id 的锁冲突。 由于只读事务不分配 trx_id一个自然而然的结果就是 trx_id 的增加速度变慢了。 但是max_trx_id 会持久化存储重启也不会重置为 0那么从理论上讲只要一个 MySQL 服务跑得足够久就可能出现 max_trx_id 达到 248-1 的上限然后从 0 开始的情况。 当达到这个状态后MySQL 就会持续出现一个脏读的 bug,复现一下这个bug
首先我们需要把当前的 max_trx_id 先修改成 248-1。注意这个 case 里使用的是可重复读隔离级别。具体的操作流程如下 由于已经把系统的 max_trx_id 设置成了 2^48-1 所以在 session A 启动的事务 TA 的低水位就是 2^48-1。
在 T2 时刻session B 执行第一条 update 语句的事务 id 就是 2^48-1而第二条 update 语句的事务 id 就是 0 了这条 update 语句执行后生成的数据版本上的 trx_id 就是 0。
在 T3 时刻session A 执行 select 语句的时候判断可见性发现c3 这个数据版本的 trx_id小于事务 TA 的低水位因此认为这个数据可见。但这个是脏读。
由于低水位值会持续增加而事务 id 从 0 开始计数就导致了系统在这个时刻之后所有的查询都会出现脏读的。
并且MySQL 重启时 max_trx_id 也不会清 0也就是说重启 MySQL这个 bug 仍然存在。
3.5 thread_id
其实线程 id 才是 MySQL 中最常见的一种自增 id。平时我们在查各种现场的时候show processlist 里面的第一列就是 thread_id。
thread_id 的逻辑:系统保存了一个全局变量 thread_id_counter每新建一个连接就将 thread_id_counter 赋值给这个新连接的线程变量。
thread_id_counter 定义的大小是 4 个字节因此达到 2^32-1 后它就会重置为 0然后继续增加。但是不会在 show processlist 里看到两个相同的 thread_id。是因为 MySQL 设计了一个唯一数组的逻辑给新线程分配 thread_id 的时候逻辑代码是这样的
do {new_id thread_id_counter;
} while (!thread_ids.insert_unique(new_id).second);小结 每种自增 id 有各自的应用场景在达到上限后的表现也不同
表的自增 id 达到上限后再申请时它的值就不会改变进而导致继续插入数据时报主键冲突的错误。row_id 达到上限后则会归 0 再重新递增如果出现相同的 row_id后写的数据会覆盖之前的数据。Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值但是概率极小可以忽略不计。InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来所以我们文章中提到的脏读的例子就是一个必现的 bug好在留给我们的时间还很充裕。thread_id 是我们使用中最常见的而且也是处理得最好的一个自增 id 逻辑了。 来自林晓斌《MySql实战45讲》
