Mysql实战笔记（十）实践（9）

奥兰治的威廉
180 阅读10分钟

二十九、insert语句的锁为什么这么多？

三十、怎么最快地复制一张表？

怎么在两张表中拷贝数据：
如果可以控制对源表的扫描行数和加锁范围很小的话，可以简单地使用 insert ... select 语句即可实现。当然，为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。

mysqldump 方法

一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句。

导出 CSV 文件

另一种方法是直接将结果导出成.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 整个表处于只读状态，直到执行 unlocktables 命令后才释放读锁；
  2. 在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件（比如 TB 级别），每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

三十一、grant之后要跟着flush privileges吗？

create user 'ua'@'%' identified by 'pa';
这条语句的逻辑是创建一个用户ua’@’%，密码是 pa。注意，在 MySQL 里面，用户名 (user)+ 地址 (host) 才表示一个用户，因此 ua@ip1 和 ua@ip2 代表的是两个不同的用户。
这条命令做了两个动作：

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

这个时刻用户 ua 在 user 表中的状态如下图所示：

在 MySQL 中，用户权限是有不同的范围的。

全局权限

全局权限，作用于整个 MySQL 实例，这些权限信息保存在 mysql 库的 user 表里。如果我要给用户 ua 赋一个最高权限的话，语句是这么写的：
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 语句赋予的权限，你可以使用下面这条命令：
revoke all privileges on *.* from 'ua'@'%';
这条 revoke 命令的用法与 grant 类似，做了如下两个动作：

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

db 权限

除了全局权限，MySQL 也支持库级别的权限定义。如果要让用户 ua 拥有库 db1 的所有权限，可以执行下面这条命令： grant all privileges on db1.* to 'ua'@'%' with grant option;
基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs 中。这条 grant命令做了如下两个动作：

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

每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断。因此，会马上影响到已经存在的连接。

表权限和列权限

除了 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;

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

因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。

flush privileges 使用场景

当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。
这种不一致往往是由不规范的操作导致的，比如直接用 DML 语句操作系统权限表。

三十二、分区表

三十三、自增id用完怎么办？

表定义自增值 id

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'

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

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

Xid

redo log 和binlog 相配合的时候，提到了它们有一个共同的字段叫作 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 的场景。

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。

thread_id

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

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

但是，你不会在 show processlist 里看到两个相同的 thread_id。这是因为 MySQL 设计了一个唯一数组的逻辑。

每种自增 id 有各自的应用场景，在达到上限后的表现也不同：

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