Mysql45讲-实践（四）

May 09, 2022 19460

自增主键为什么不是连续的？

自增主键，由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。

自增主键不能保证连续递增

1
2
3
4
5
6
7
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

自增值保存在哪儿？

在这个空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据，再执行 show create table 命令，就可以看到如下图所示的结果：

自动生成的 AUTO_INCREMENT 值

表定义里面出现了一个 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2。

其实，这个输出结果容易引起这样的误解：自增值是保存在表结构定义里的。

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

不同的引擎对于自增值的保存策略不同：

  1. MyISAM 引擎的自增值保存在数据文件中。

  2. InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：

    1. 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

      举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。

      也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。

    2. 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

自增值修改机制

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

  1. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；

  2. 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

    根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。

    假设，某次要插入的值是 X，当前的自增值是 Y。

    1. 如果 X<Y，那么这个表的自增值不变；
    2. 如果 X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。

自增值的修改时机

导致自增主键 id 不连续的原因

  1. 唯一键冲突
  2. 事务回滚
  3. 批量插入数据

自增值为什么不能回退：为了提升性能

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

  1. 假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
  2. 事务 B 正确提交了，但事务 A 出现了唯一键冲突。
  3. 如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
  4. 接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。

解决这个主键冲突，有两种方法：

  1. 每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在。
  2. 把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

这两个方法都会导致性能问题

自增锁的优化

自增 id 锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

  1. MySQL 5.0 版本

    自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放。

  2. MySQL 5.1.22 版本

    新增参数 innodb_autoinc_lock_mode，默认值是 1

    1. 这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；
    2. 这个参数的值被设置为 1 时：
      1. 普通 insert 语句，自增锁在申请之后就马上释放；
      2. 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
    3. 这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。

在生产上，尤其是有 insert … select 这种批量插入数据(insert … select、replace … select 和 load data 语句)的场景时，从并发插入数据性能的角度考虑，我建议你这样设置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 这样做，既能提升并发性，又不会出现数据一致性问题。

对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：

  1. 语句执行过程中，第一次申请自增 id，会分配 1 个；
  2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
  3. 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
  4. 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

问题：在 binlog_format=statement 时，语句 A 先获取 id=1，然后语句 B 获取 id=2；接着语句 B 提交，写 binlog，然后语句 A 再写 binlog。这时候，如果 binlog 重放，是不是会发生语句 B 的 id 为 1，而语句 A 的 id 为 2 的不一致情况呢？

自增 id 的生成顺序，和 binlog 的写入顺序可能是不同的

1
2
create table t(id int auto_increment primary key);
insert into t values(null);

insert 语句的 binlog

可以看到，在 insert 语句之前，还有一句 SET INSERT_ID=1。这条命令的意思是，这个线程里下一次需要用到自增值的时候，不论当前表的自增值是多少，固定用 1 这个值。

这个 SET INSERT_ID 语句是固定跟在 insert 语句之前的,主库上语句 A 的 id 是 1，语句 B 的 id 是 2，但是写入 binlog 的顺序先 B 后 A，那么 binlog 就变成：

1
2
3
4
SET INSERT_ID=2;
语句B；
SET INSERT_ID=1;
语句A；

在备库上语句 B 用到的 INSERT_ID 依然是 2，跟主库相同

因此，即使两个 INSERT 语句在主备库的执行顺序不同，自增主键字段的值也不会不一致。

insert语句的锁为什么这么多？

insert … select 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

在可重复读隔离级别下，binlog_format=statement 时执行：

1
insert into t2(c,d) select c,d from t;

需要对表 t 的所有行和间隙加锁。

执行序列：

并发 insert 场景

实际的执行效果是，如果 session B 先执行，由于这个语句对表 t 主键索引加了 (-∞,1]这个 next-key lock，会在语句执行完成后，才允许 session A 的 insert 语句执行。

但如果没有锁的话，就可能出现 session B 的 insert 语句先执行，但是后写入 binlog 的情况。于是，在 binlog_format=statement 的情况下，binlog 里面就记录了这样的语句序列：

1
2
insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

这个语句到了备库执行，就会把 id=-1 这一行也写到表 t2 中，出现主备不一致。

执行 insert … select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

insert 循环写入

要往表 t2 中插入一行数据，这一行的 c 值是表 t 中 c 值的最大值加 1。

1
insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围，就是表 t 索引 c 上的 (3,4]和 (4,supremum]这两个 next-key lock，以及主键索引上 id=4 这一行。

执行流程:从表 t 中按照索引 c 倒序，扫描第一行，拿到结果写入到表 t2 中。

整条语句的扫描行数是 1。

把这样的一行数据插入到表 t 中：

1
insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

慢查询日志:

慢查询日志 -- 将数据插入表 t

Rows_examined 的值是 5

explain 结果:

explain 结果.png

从 Extra 字段可以看到“Using temporary”字样，表示这个语句用到了临时表。执行过程中，需要把表 t 的内容读出来，写入临时表。

在执行这个语句前后查看 Innodb_rows_read 的结果:

查看 Innodb_rows_read 变化

这个语句执行前后，Innodb_rows_read 的值增加了 4。因为默认临时表是使用 Memory 引擎的，所以这 4 行查的都是表 t，也就是说对表 t 做了全表扫描。

执行过程:

  1. 创建临时表，表里有两个字段 c 和 d。
  2. 按照索引 c 扫描表 t，依次取 c=4、3、2、1，然后回表，读到 c 和 d 的值写入临时表。这时，Rows_examined=4。
  3. 由于语义里面有 limit 1，所以只取了临时表的第一行，再插入到表 t 中。这时，Rows_examined 的值加 1，变成了 5。

这个语句会导致在表 t 上做全表扫描，并且会给索引 c 上的所有间隙都加上共享的 next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。

这个语句的执行为什么需要临时表?

原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。

insert 唯一键冲突

在可重复读（repeatable read）隔离级别下执行

session B 要执行的 insert 语句进入了锁等待状态。

session A 执行的 insert 语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。

这时候，session A 持有索引 c 上的 (5,10]共享 next-key lock（读锁）。

insert into … on duplicate key update

1
insert into t values(11,10,10) on duplicate key update d=100;

会给索引 c 上 (5,10] 加一个排他的 next-key lock（写锁）

insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

现在表 t 里面已经有了 (1,1,1) 和 (2,2,2) 这两行

主键 id 是先判断的，MySQL 认为这个语句跟 id=2 这一行冲突，所以修改的是 id=2 的行。

需要注意的是，执行这条语句的 affected rows 返回的是 2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert 和 update 都认为自己成功了，update 计数加了 1， insert 计数也加了 1。

怎么最快地复制一张表？

  1. 如果可以控制对源表的扫描行数和加锁范围很小的话，简单地使用 insert … select语句即可实现。
  2. 为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
  create procedure idata()
  begin
    declare i int;
    set i=1;
    while(i<=1000)do
      insert into t values(i,i,i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call idata();

create database db2;
create table db2.t like db1.t

先创建一个表 db1.t，并插入 1000 行数据，同时创建一个相同结构的表 db2.t

mysqldump 方法

使用 mysqldump 命令将数据导出成一组 INSERT 语句

1
mysqldump -h
host -P


port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
  1. –single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT(立即开启一个事务，否则事务是再执行到第一个sql语句的时候创建的) 的方法；
  2. –add-locks 设置为 0，表示在输出的文件结果里，不增加” LOCK TABLES t WRITE;“ ；
  3. –no-create-info 的意思是，不需要导出表结构；
  4. –set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；
  5. –result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

通过这条 mysqldump 命令生成的 t.sql 文件中就包含了如图所示的 INSERT 语句

mysqldump 输出文件的部分结果

一条 INSERT 语句里面会包含多个 value 对，这是为了后续用这个文件来写入数据的时候，执行速度可以更快。

如果希望生成的文件中一条 INSERT 语句只插入一行数据的话，可以在执行 mysqldump 命令时，加上参数–skip-extended-insert。

然后，可以通过下面这条命令，将这些 INSERT 语句放到 db2 库里去执行

1
mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

source 并不是一条 SQL 语句，而是一个

客户端命令

mysql 客户端执行这个命令的流程是这样的：

  1. 打开文件，默认以分号为结尾读取一条条的 SQL 语句；
  2. 将 SQL 语句发送到服务端执行。

也就是说，服务端执行的并不是这个“source t.sql“语句，而是 INSERT 语句。所以，不论是在慢查询日志（slow log），还是在 binlog，记录的都是这些要被真正执行的 INSERT 语句。

导出 CSV 文件

直接将结果导出成.csv 文件

1
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

需要注意如下几点:

  1. 这条语句会将结果保存在服务端。如果你执行命令的客户端和 MySQL 服务端不在同一个机器上，客户端机器的临时目录下是不会生成 t.csv 文件的。
  2. into outfile 指定了文件的生成位置（/server_tmp/），这个位置必须受参数 secure_file_priv 的限制。参数 secure_file_priv 的可选值和作用分别是：
    1. 如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；
    2. 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
    3. 如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select … into outfile 操作。
  3. 这条命令不会帮你覆盖文件，因此你需要确保 /server_tmp/t.csv 这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。
  4. 这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开。

得到.csv 导出文件后，就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。

1
load data infile '/server_tmp/t.csv' into table db2.t;

这条语句的执行流程如下所示:

  1. 打开文件 /server_tmp/t.csv，以制表符 (\t) 作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
  2. 启动事务
  3. 判断每一行的字段数与表 db2.t 是否相同：
    1. 若不相同，则直接报错，事务回滚；
    2. 若相同，则构造成一行，调用 InnoDB 引擎接口，写入到表中。
  4. 重复步骤 3，直到 /server_tmp/t.csv 整个文件读入完成，提交事务。

问题：如果 binlog_format=statement，这个 load 语句记录到 binlog 里以后，怎么在备库重放呢？

由于 /server_tmp/t.csv 文件只保存在主库所在的主机上，如果只是把这条语句原文写到 binlog 中，在备库执行的时候，备库的本地机器上没有这个文件，就会导致主备同步停止。

所以，这条语句执行的完整流程，其实是下面这样的：

  1. 主库执行完成后，将 /server_tmp/t.csv 文件的内容直接写到 binlog 文件中。
  2. 往 binlog 文件中写入语句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`。
  3. 把这个 binlog 日志传到备库。
  4. 备库的 apply 线程在执行这个事务日志时：
    1. 先将 binlog 中 t.csv 文件的内容读出来，写入到本地临时目录 /tmp/SQL_LOAD_MB-1-0 中；
    2. 再执行 load data 语句，往备库的 db2.t 表中插入跟主库相同的数据。

load data 的同步流程

注意，这里备库执行的 load data 语句里面，多了一个“local”。它的意思是“将执行这条命令的客户端所在机器的本地文件 /tmp/SQL_LOAD_MB-1-0 的内容，加载到目标表 db2.t 中”。

load data 命令有两种用法：

  1. 不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；
  2. 加上“local”，读取的是客户端的文件，只要 mysql 客户端有访问这个文件的权限即可。这时候，MySQL 客户端会先把本地文件传给服务端，然后执行上述的 load data 流程

select …into outfile 方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。

mysqldump 提供了一个–tab 参数，可以同时导出表结构定义文件和 csv 数据文件。这条命令的使用方法如下：

1
mysqldump -h
host -P


port -u
user ---single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --tab=


secure_file_priv

这条命令会在 $secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据。

物理拷贝方法

问题：直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下，是否可行呢？

一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的。

在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

  1. 执行 create table r like t，创建一个相同表结构的空表；
  2. 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
  3. 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
  4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
  5. 执行 unlock tables，这时候 t.cfg 文件会被删除；
  6. 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

注意点：

  1. 在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；
  2. 在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

三种方法的优缺点

  1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
    1. 必须是全表拷贝，不能只拷贝部分数据；
    2. 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
    3. 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
  2. 用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
  3. 用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

grant之后要跟着flush privileges吗？

1
create user 'ua'@'%' identified by 'pa';

这条语句的逻辑是创建一个用户’ua’@’%’，密码是 pa。注意，在 MySQL 里面，用户名 (user)+ 地址 (host) 才表示一个用户。

这条命令做了两个动作：

  1. 磁盘上，往 mysql.user 表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是 N；
  2. 内存里，往数组 acl_users 里插入一个 acl_user 对象，这个对象的 access 字段值为 0。

用户 ua 在 user 表中的状态

mysql.user 数据行

全局权限

全局权限，作用于整个 MySQL 实例，这些权限信息保存在 mysql 库的 user 表里。

如果要给用户 ua 赋一个最高权限的话，语句是这么写的：

1
grant all privileges on *.* to 'ua'@'%' with grant option;

这个 grant 命令做了两个动作：

  1. 磁盘上，将 mysql.user 表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为‘Y’；
  2. 内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。

在这个 grant 命令执行完成后，如果有新的客户端使用用户名 ua 登录成功，MySQL 会为新连接维护一个线程对象，然后从 acl_users 数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位

基于上面的分析我们可以知道：

  1. grant 命令对于全局权限，同时更新了磁盘和内存。命令完成后即时生效，接下来新创建的连接会使用新的权限。
  2. 对于一个已经存在的连接，它的全局权限不受 grant 命令的影响。

如果要回收上面的 grant 语句赋予的权限，可以使用下面这条命令：

1
revoke all privileges on *.* from 'ua'@'%';

这条 revoke 命令的用法与 grant 类似，做了如下两个动作：

  1. 磁盘上，将 mysql.user 表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为“N”；
  2. 内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 的值修改为 0。

db 权限

除了全局权限，MySQL 也支持库级别的权限定义。

如果要让用户 ua 拥有库 db1 的所有权限，可以执行下面这条命令：

1
grant all privileges on db1.* to 'ua'@'%' with grant option;

基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs 中。这条 grant 命令做了如下两个动作：

  1. 磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；
  2. 内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。

这个时刻用户 ua 在 db 表中的状态:

mysql.db 数据行

每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断。

grant 修改 db 权限的时候，是同时对磁盘和内存生效的

对于全局权限，因为全局权限存储在线程对象中，所以修改用户的全局权限后，不会影响到已经存在的连接；

对于数据库权限，因为acl_dbs是一个全局数组，修改用户的数据库权限，acl_dbs也会立马随之修改，线程对象可以立刻读到，所以会直接影响到已经存在的连接。

表权限和列权限

除了 db 级别的权限外，MySQL 支持更细粒度的表权限和列权限。

其中，表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。

这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中。

这两类权限的赋权命令如下：

1
2
3
4
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;

跟 db 权限类似，这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接。

flush privileges 命令

flush privileges 命令会清空 acl_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。

对于 db 权限、表权限和列权限，MySQL 也做了这样的处理

也就是说，如果内存的权限数据和磁盘数据表相同的话，不需要执行 flush privileges。而如果我们都是用 grant/revoke 语句来执行的话，内存和数据表本来就是保持同步更新的。因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。

flush privileges 使用场景

当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。

直接用 DML 语句操作系统权限表

使用 flush privileges

T3 时刻虽然已经用 delete 语句删除了用户 ua，但是在 T4 时刻，仍然可以用 ua 连接成功。原因就是，这时候内存中 acl_users 数组中还有这个用户，因此系统判断时认为用户还正常存在。

在 T5 时刻执行过 flush 命令后，内存更新，T6 时刻再要用 ua 来登录的话，就会报错“无法访问”了

要不要使用分区表？

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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);

表 t 的磁盘文件

在表 t 中初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在 p_2018 和 p_2019 这两个分区上。

这个表包含了一个.frm 文件和 4 个.ibd 文件，每个分区对应一个.ibd 文件。也就是说：

  1. 对于引擎层来说，这是 4 个表；
  2. 对于 Server 层来说，这是 1 个表。
  1. MySQL 在第一次打开分区表的时候，需要访问所有的分区；
  2. 在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；
  3. 在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。
  1. 分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。
  2. 分区也不要提前预留太多，在使用之前预先创建即可。
  3. 对于没有数据的历史分区，要及时的 drop 掉。

自增id用完怎么办？

MySQL 里有很多自增的 id，每个自增 id 都是定义了初始值，然后不停地往上加步长。虽然自然数是没有上限的，但是在计算机里，只要定义了表示这个数的字节长度，那它就有上限。比如，无符号整型 (unsigned int) 是 4 个字节，上限就是 232-1。

表定义自增值 id

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。

1
2
3
4
5
6
7
8
9
10
11
12
create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

第一个 insert 语句插入数据成功后，这个表的 AUTO_INCREMENT 没有改变（还是 4294967295），就导致了第二个 insert 语句又拿到相同的自增 id 值，再试图执行插入语句，报主键冲突错误。

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 能写到数据表中的值，就有两个特征：

  1. row_id 写入表中的值范围，是从 0 到 248-1；

  2. 当 ‘dict_sys.row_id=248‘时，如果再有插入数据的行为要来申请 row_id，拿到以后再取最后 6 个字节的话就是 0。(写入表的 row_id 是从 0 开始到 248-1。达到上限后，下一个值就是 0，然后继续循环。)

    在 InnoDB 逻辑里，申请到 row_id=N 后，就将这行数据写入表中；如果表中已经存在 row_id=N 的行，新写入的行就会覆盖原有的行。

Xid

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 的场景。

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。

事务的 trx_id

session B 里，从 innodb_trx 表里查出的这两个字段，第二个字段 trx_mysql_thread_id 就是线程 id。显示线程 id，是为了说明这两次查询看到的事务对应的线程 id 都是 5，也就是 session A 所在的线程。

实际上，在 T1 时刻，session A 还没有涉及到更新，是一个只读事务。而对于只读事务，InnoDB 并不会分配 trx_id。也就是说：

  1. 在 T1 时刻，trx_id 的值其实就是 0。而这个很大的数，只是显示用的。
  2. 直到 session A 在 T3 时刻执行 insert 语句的时候，InnoDB 才真正分配了 trx_id。所以，T4 时刻，session B 查到的这个 trx_id 的值就是 1289。

除了修改类语句外，如果在 select 语句后面加上 for update，这个事务也不是只读事务。

  1. update 和 delete 语句除了事务本身，还涉及到标记删除旧数据，也就是要把数据放到 purge 队列里等待后续物理删除，这个操作也会把 max_trx_id+1， 因此在一个事务中至少加 2；
  2. InnoDB 的后台操作，比如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx_id 值并不是按照加 1 递增的。

T2 时刻查到的这个很大的数字是怎么来的呢？

这个数字是每次查询的时候由系统临时计算出来的。它的算法是：把当前事务的 trx 变量的指针地址转成整数，再加上 248。使用这个算法，就可以保证以下两点：

  1. 因为同一个只读事务在执行期间，它的指针地址是不会变的，所以不论是在 innodb_trx 还是在 innodb_locks 表里，同一个只读事务查出来的 trx_id 就会是一样的。
  2. 如果有并行的多个只读事务，每个事务的 trx 变量的指针地址肯定不同。这样，不同的并发只读事务，查出来的 trx_id 就是不同的。
  3. 在显示值里面加上 248，目的是要保证只读事务显示的 trx_id 值比较大，正常情况下就会区别于读写事务的 id。但是，trx_id 跟 row_id 的逻辑类似，定义长度也是 8 个字节。因此，在理论上还是可能出现一个读写事务与一个只读事务显示的 trx_id 相同的情况。不过这个概率很低，并且也没有什么实质危害，可以不管它。

只读事务不分配 trx_id，有什么好处呢？

  1. 一个好处是，这样做可以减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务，是不影响数据的可见性判断的。所以，在创建事务的一致性视图时，InnoDB 就只需要拷贝读写事务的 trx_id。
  2. 另一个好处是，可以减少 trx_id 的申请次数。在 InnoDB 里，即使你只是执行一个普通的 select 语句，在执行过程中，也是要对应一个只读事务的。所以只读事务优化后，普通的查询语句不需要申请 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 设置成了 248-1，所以在 session A 启动的事务 TA 的低水位就是 248-1。

在 T2 时刻，session B 执行第一条 update 语句的事务 id 就是 248-1，而第二条 update 语句的事务 id 就是 0 了，这条 update 语句执行后生成的数据版本上的 trx_id 就是 0。

在 T3 时刻，session A 执行 select 语句的时候，判断可见性发现，c=3 这个数据版本的 trx_id，小于事务 TA 的低水位，因此认为这个数据可见。

但，这个是脏读。

由于低水位值会持续增加，而事务 id 从 0 开始计数，就导致了系统在这个时刻之后，所有的查询都会出现脏读的。

并且，MySQL 重启时 max_trx_id 也不会清 0，也就是说重启 MySQL，这个 bug 仍然存在。

thread_id

线程 id（thread_id）才是 MySQL 中最常见的一种自增 id。

thread_id 的逻辑：系统保存了一个全局变量 ‘thread_id_counter’，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量。

thread_id_counter 定义的大小是 4 个字节，因此达到 232-1 后，它就会重置为 0，然后继续增加。

但是，不会在 show processlist 里看到两个相同的 thread_id。因为 MySQL 设计了一个唯一数组的逻辑，给新线程分配 thread_id 的时候，逻辑代码是这样的：

1
2
3
do {
  new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

总结：

  1. 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
  2. row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
  3. Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
  4. InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，脏读的例子就是一个必现的 bug。
  5. thread_id 是最常见的，而且也是处理得最好的一个自增 id 逻辑。
查看评论